PostgreSQL Deep Dive
by Sergey Okinchuk
Migrations Without DowntimeWindow Functions

Chapter Four · SQL Beyond the Basics

SQL Beyond the Basics

The Cartwheel API makes three round trips to draw one screen, one of them spent entirely on adding up a running total in Python. The analytics team finds the latest delivery event per order with a loop of its own. Five topics replace all of it with SQL that runs in one pass, and say what each construct costs when the planner meets it.

5 topics

Chapter 3 ended with a schema Nadia can change without taking checkout down. This chapter is about interrogating it. Everything here is ordinary professional SQL that a course teaching SELECT and JOIN never reaches: computations that run across rows without collapsing them, queries that name their own intermediate steps, a write that is atomic against a concurrent writer inserting the same key, and statements that hand back the rows they just changed.

The Cartwheel API is the reason it matters. The "your recent orders" screen issues one query for the orders, a second to fetch the customer's whole history so the service can add up a running total, and a third for the delivery status of each order. The analytics job that reports the latest event per order pulls a day of delivery_events — around 4 million rows — into application memory and keeps the last one per order_id. Every one of those is a single statement in this chapter, and in each case the interesting part is not the syntax but the plan it produces.

This is where the developer half of the book ends. After five chapters the reader knows what to store, what the engine will guarantee about it, how to change it while the site is up, and how to ask for it in one round trip instead of three. What is still missing is the machinery: why a running total over 40 million rows costs what it costs, and why the last box of strawberries was sold twice. Chapter 5 opens the file and shows what a row physically is.

Five topics, five application loops deleted
Compute across rows without collapsing them→Window functions
Name a query's own intermediate steps, or walk a hierarchy→WITH, WITH RECURSIVE
Write a row atomically against a concurrent writer inserting the same key→INSERT … ON CONFLICT
One row per group, top-N per group, several aggregation levels in one scan→LATERAL · DISTINCT ON · GROUPING SETS
Hand back the rows a write just changed→RETURNING

Topics in This Chapter

Topic 17
Window Functions
A running total that still returns every order, a rank per customer, the previous event's timestamp sitting on this row. The default frame that includes tied rows and quietly breaks the running total, why a rank cannot be filtered in WHERE, and how many sorts five window definitions cost.
Analytic SQL
Topic 18
Common Table Expressions and Recursion
WITH gives a query's stages names, and until 12 it also fenced the planner out of them. What inlining changed, when MATERIALIZED is the right answer, and how a recursive CTE actually executes — a working table, one round at a time, until a round returns nothing.
Query Structure
Topic 19
Upsert with INSERT ON CONFLICT
"Insert it, or update it if it is there" is a race when written as two statements, and the warehouse feed hits that race on every replay. The arbiter index, what EXCLUDED is, why the sequence gaps, and the concurrency difference between ON CONFLICT and MERGE.
Write Patterns
Topic 20
LATERAL, DISTINCT ON, and GROUPING SETS
Three constructs that each delete an application loop: a subquery in FROM that can see the row beside it, one row per group in a single sort, and several aggregation levels in one scan. Plus the generated time axis that makes an empty hour show up as a zero.
Query Shapes
Topic 21
RETURNING and Data-Modifying CTEs
A write statement with a result set, including OLD and NEW side by side since 18. One statement that deletes rows from one table and inserts them into another atomically, and the shared snapshot that decides what each branch is allowed to see.
Atomic Writes