SQL has evolved far beyond the SQL-92 baseline many developers still rely on. Modern SQL—popularized by experts like Markus Winand—broadens the language with arrays, JSON, composite types, recursive queries, and other features that help Postgres handle structured, semi-structured, and unstructured workloads more expressively. This chapter’s goal is to bring those capabilities into your everyday toolbox using a simple music-streaming dataset, while also explaining why adoption lags (habit and heavy ORM reliance) and how modern SQL improves readability, correctness, and performance.
Common table expressions (CTEs) let you structure complex logic into clear steps and can be used for both querying and data modification. Postgres may inline (fold) or materialize CTEs depending on usage; EXPLAIN shows what really runs, and MATERIALIZED/NON MATERIALIZED can guide behavior. Data-modifying CTEs run even if unreferenced, and multiple CTEs in a single query execute on the same snapshot—changes aren’t visible to sibling CTEs unless you reference results directly. Recursive CTEs seed an initial result then iterate with UNION/UNION ALL, making them ideal for hierarchical tasks such as walking song play sequences. You can carry arguments through recursion to compute levels, build path arrays, and accumulate totals like a sequence’s overall play duration.
Window functions compute over related sets of rows while preserving each row in the output, offering a cleaner, faster alternative to self-joins for tasks like totals, running sums, and ranking. Using OVER with PARTITION BY and optional ORDER BY, you can produce per-song totals while listing each listener, calculate running totals via window frames, and rank songs with RANK() based on aggregate measures. Together, CTEs (including recursive ones) and window functions enable concise, readable, and often more efficient SQL that solves real analytical and hierarchical problems directly in Postgres.
A simple database schema for a music streaming service
Records are grouped in windows by song ID.
Window frames with a running total calculation
Summary
Even though it was invented over 50 years ago, like most languages humans speak, SQL continues to evolve to meet the needs of the modern world.
Modern SQL is a category of features and capabilities that were introduced after the SQL-92 standard and allow us to use Postgres beyond the classic relational model and its traditional use cases.
Common table expressions (CTEs) allow us to break down large or complex queries into pieces that are smaller and more readable and manageable.
Recursive queries let us process hierarchical and tree-like structures in SQL.
Window functions allow us to divide data into groups (windows) and perform aggregations, ranking, lag-lead analysis, and other calculations within each window.
FAQ
What is “modern SQL” in Postgres and why does it matter?Modern SQL refers to features added after SQL-92 that extend SQL beyond the classic relational model. In Postgres, this includes arrays, JSON and composite types, common table expressions (CTEs), recursive queries, window functions, and more. These capabilities make queries more readable, reduce boilerplate, simplify complex analytics, and let you work with semi-structured data efficiently.What problems does modern SQL solve that ORMs or old SQL patterns struggle with?Modern SQL often expresses complex logic (ranking, running totals, recursive traversals) concisely and efficiently. ORMs generate generic SQL and may miss performant constructs like window functions or recursive CTEs, leading to verbose self-joins, multiple round trips, or heavy application-side processing. Knowing modern SQL lets you write clearer, faster queries tailored to your workload.What is a CTE and how does the WITH clause work?A common table expression (CTE) is a named subquery defined with WITH that you can reference in a primary statement. Think of it as a query-scoped, readable “temporary view.” It can be used for both SELECT and data-modifying statements (INSERT/UPDATE/DELETE/MERGE), and helps break complex logic into manageable steps.Do CTEs always materialize? How does Postgres optimize them?No. Postgres may inline (fold) a CTE into the main query for better plans. If a CTE is referenced multiple times, Postgres can materialize it automatically. You can force behavior with WITH cte AS MATERIALIZED (store once) or AS NOT MATERIALIZED (recompute on each reference). Use EXPLAIN/EXPLAIN ANALYZE to see the actual plan.When should I use multiple CTEs versus nested subqueries?Prefer the option that’s easiest to read and maintain. Multiple CTEs make the flow explicit and composable; Postgres can still fold them for performance. Subqueries can be compact but harder to follow when logic grows. Compare execution plans; don’t assume one is always faster.How do data‑modifying CTEs work and what does RETURNING do?Data‑modifying CTEs run DML inside WITH and can expose changed rows to the primary statement via RETURNING (e.g., RETURNING * or specific columns). They execute even if not referenced by the primary query. When multiple CTEs exist, they run concurrently unless one references another; changes aren’t visible across CTEs unless you read from the modifying CTE’s result.What is a recursive CTE and how is it structured?A recursive CTE uses WITH RECURSIVE and has two parts: an anchor (non‑recursive term) that seeds initial rows, and a recursive term that repeatedly references the CTE’s current result until no new rows are produced. UNION or UNION ALL combines steps (UNION deduplicates). It’s ideal for hierarchies, sequences, and graph traversals.How can I traverse hierarchical or sequential data (like played_after chains)?Model links with an id and a played_after (parent) column. Use a recursive CTE: the anchor selects the start node(s), and the recursive term joins children on p.played_after = parent.id, accumulating rows until no next link exists. You can also add a level column (start at 1, increment each step) to label depth.How do I carry and accumulate values across recursion (paths, totals)?Declare CTE column aliases in WITH RECURSIVE cte(col1, col2, ...). Initialize them in the anchor (e.g., ARRAY[id] for a path, play_duration for a running total) and update in the recursive term (e.g., path || id, total + play_duration). The primary query can then read the final path and total for each traversal.What are window functions and how do they differ from GROUP BY aggregates?Window functions compute over a set of related rows but do not collapse rows like GROUP BY does. Use OVER (...) to define partitions and frame ordering. Examples: SUM() OVER (PARTITION BY song_id) gives per-song totals while retaining per-user rows; ORDER BY within OVER enables running totals; RANK() OVER (ORDER BY total DESC) ranks items without losing detail rows.
pro $24.99 per month
access to all Manning books, MEAPs, liveVideos, liveProjects, and audiobooks!
Just Use Postgres! ebook for free
