Level 3 · 30 min

Advanced Features

PostgreSQL's advanced SQL features — window functions, CTEs, JSONB operators, and full-text search — enable complex analytical queries that would otherwise require multiple round trips or application-level processing.

Window Functions

Window functions compute values across a set of rows related to the current row, without collapsing rows like GROUP BY does. Syntax: FUNCTION() OVER (PARTITION BY ... ORDER BY ... ROWS/RANGE BETWEEN ...). ROW_NUMBER(): sequential number per partition. RANK()/DENSE_RANK(): ranking with/without gaps for ties. LAG(col, n)/LEAD(col, n): value from n rows before/after. SUM/AVG OVER (): running totals and moving averages. The WINDOW clause names a window definition for reuse. Window functions execute after WHERE, GROUP BY, and HAVING but before ORDER BY and LIMIT — you cannot filter on window function results in the same query; wrap in a CTE or subquery.

CTEs and Recursive Queries

Common Table Expressions (WITH clause) name intermediate result sets. In PostgreSQL 12+, CTEs are inlined by default (the planner can optimize through them). In PostgreSQL 11 and earlier, CTEs are optimization fences: the planner cannot push predicates into a CTE or merge it with the main query. Use WITH MATERIALIZED to force CTE materialization in PostgreSQL 12+. Recursive CTEs (WITH RECURSIVE) process hierarchical data iteratively: the base case runs once, the recursive term joins the CTE to itself until no new rows are produced. Classic use case: traversing an organizational chart (employee → manager → VP → CEO) or a bill of materials. Full-text search in PostgreSQL uses tsvector (the indexed document representation) and tsquery (the search query). From Obe & Hsu: ts_rank considers term frequency and positional weights (A-D representing title/body/etc), while ts_rank_cd considers cover density — how close together the query terms appear in the document. GiST indexes on tsvector columns support fast @@ matching. Window functions compute a result across a partition of rows without collapsing them: RANK() OVER (PARTITION BY dept ORDER BY salary DESC) assigns a rank within each department. The FILTER clause on aggregates (COUNT(*) FILTER (WHERE status='active')) is cleaner than CASE WHEN and can be combined with window functions. LATERAL joins allow a subquery to reference columns from the outer FROM item — equivalent to a correlated subquery but usable as a set-returning function, making them essential for top-N-per-group queries.

JSONB and Full-Text Search

JSONB (binary JSON) stores JSON in decomposed binary format — supports indexed access. Key operators: -> returns JSON element (returns JSON), ->> returns JSON element as text, #> path navigation, @> containment (doc contains subset), ? key existence. For full-text search: to_tsvector('english', text) converts text to a tsvector (list of lexemes). to_tsquery('english', 'search & term') creates a query. @@ is the match operator. ts_rank(tsvector, tsquery) returns a relevance score. GIN index on a tsvector column makes full-text search fast. For frequently searched columns, store tsvector as a computed column with a trigger or generated column to avoid re-parsing text on every query.

Key Takeaways

Window functions don't collapse rows — they add computed columns based on surrounding rows. Use them for running totals, rankings, and comparing rows to adjacent rows.
Recursive CTEs are the SQL way to traverse trees and graphs. They are iterative, not truly recursive — each iteration produces a new set of rows until the recursive term returns empty.
JSONB @> containment queries require a GIN index. Without it, they perform a Seq Scan. Stored tsvectors (materialized as a column) are faster than calling to_tsvector() on every query.

Code example

-- Window function: running total and rank per category\nSELECT\n  category,\n  amount,\n  SUM(amount) OVER (PARTITION BY category ORDER BY date) AS running_total,\n  RANK() OVER (PARTITION BY category ORDER BY amount DESC) AS rank_in_category\nFROM sales;\n\n-- Recursive CTE: org chart\nWITH RECURSIVE org AS (\n  SELECT id, name, manager_id, 0 AS depth FROM employees WHERE manager_id IS NULL\n  UNION ALL\n  SELECT e.id, e.name, e.manager_id, org.depth + 1\n  FROM employees e JOIN org ON e.manager_id = org.id\n)\nSELECT * FROM org ORDER BY depth;