Skip to content
AyoKoding

Overview

Prerequisites

  • Prior topics: 10 · SQL Essentials -- SELECT, JOIN, GROUP BY/HAVING, INSERT/UPDATE/DELETE, foreign keys, basic transactions (COMMIT/ROLLBACK), and the first Python sqlite3 examples this topic assumes as a floor and then extends into a real client-server engine; 4 · Just Enough Python -- the type-annotated Python fluency this topic's data-access-layer examples assume; 11 · Backend Essentials -- the N+1 query scenario that topic introduces at the application layer, which this topic's co-26 (N+1 diagnosis and fix, Examples 54-56) diagnoses and resolves from the database side.
  • Tools & environment: a macOS/Linux terminal; PostgreSQL 18.x (the psql client and a running server -- Docker's postgres:18 image is the simplest way to get one); Python 3.x with psycopg (v3, pip install "psycopg[binary]") installed in a venv; Example 76 additionally needs psycopg-pool (pip install psycopg-pool).
  • Assumed knowledge: comfort writing and running SELECT/JOIN/GROUP BY queries and basic Python scripts (topic 10). No prior exposure to window functions, query planning, or transaction isolation is assumed -- this topic is where those begin.

Why this exists -- the big idea

The problem before the solution: correct SQL can still be catastrophically slow -- the query that flew on 100 rows melts at 10 million, and you cannot see why without the planner. Keep-this-if-you-forget-everything: the database does what you ask, well or badly; EXPLAIN is how you see the how, and an index is a space-and-write-cost bargain you make to buy read speed.

Cross-cutting big ideas, taught here and then reused for the rest of this curriculum: consistency-latency-throughput -- isolation levels and locking trade correctness guarantees against concurrency and speed; abstraction-and-its-cost -- indexes and denormalization buy reads by charging writes and storage.

Confirm your toolchain

Every example in this topic runs against a real, running PostgreSQL 18 server:

$ psql --version
psql (PostgreSQL) 18.4
$ psql -U asqp -d asqp -c "SELECT version();"
PostgreSQL 18.4 on aarch64-unknown-linux-musl, compiled by gcc ...

Every SQL example is a complete, self-contained, runnable .sql file colocated under learning/code/, and every Python example is a complete, self-contained, runnable .py file -- both actually executed against a real PostgreSQL 18.4 instance (via Docker, postgres:18.4-alpine, with shared_preload_libraries = 'pg_stat_statements' for Example 82) to capture the documented output. Every query result, EXPLAIN plan, timing number, and server log excerpt on this topic's pages is a genuine, captured transcript, never a fabricated one -- including the exact PostgreSQL 18 plan-output details that changed from prior versions (Buffers: shown by default under EXPLAIN ANALYZE, fractional row counts on loop-averaged estimates, Index Searches: and Heap Fetches: lines, and the new B-tree skip-scan capability).

How this topic's examples are organized

  • Beginner (Examples 1-28) -- uncorrelated and correlated subqueries, derived tables, common table expressions (WITH) including a first recursive counter and tree walk, the four core window-function building blocks (running total, partitioned average, ranking functions, LAG/LEAD), NTILE quartiles, UNION/INTERSECT/EXCEPT set operations, GROUP BY ROLLUP and grouping sets, FILTER-based conditional aggregation, transactions (BEGIN/COMMIT/ ROLLBACK) and a genuine atomicity-failure demonstration, creating a B-tree index, EXPLAIN and EXPLAIN ANALYZE basics, ANALYZE refreshing planner statistics, FOR UPDATE row locking and the default Read Committed isolation level (both via real two-session Python scripts), and psql's \timing.
  • Intermediate (Examples 29-64) -- rewriting a correlated subquery as a join, cycle-safe recursive CTEs over graphs and a bill-of-materials explosion, moving averages, explicit RANGE vs. ROWS window frames, the FIRST_VALUE/LAST_VALUE default-frame gotcha, PERCENT_RANK/CUME_DIST, top-N-per-group, LATERAL joins, CUBE crosstabs, every specialized index type PostgreSQL ships (composite, covering, partial, expression, hash, GIN, BRIN) with the write-cost and bloat tradeoffs each one carries, all three EXPLAIN join-node types (nested loop with Memoize, hash join, merge join) forced deterministically for teaching, Buffers in a real plan, a stale-statistics misestimate, the N+1 query problem diagnosed and fixed two ways in Python, every classic read anomaly PostgreSQL's isolation levels can and cannot produce (non-repeatable read, PostgreSQL's stronger-than-standard phantom-read prevention, write skew, and a genuine SerializationFailure retry), a real reproduced deadlock and its consistent-ordering fix, advisory locks, and a plain materialized view refresh.
  • Advanced (Examples 65-85) -- a recursive-CTE shortest path over a weighted graph, gaps-and-islands window sessionization, a measured window-function-vs-self-join performance gap (687x on this data), a multi-LATERAL dashboard report, deliberate covering-index design, CREATE STATISTICS for correlated columns, declarative range partitioning with verified pruning and a measured bulk-delete tradeoff against a single big index, denormalization measured honestly in both directions (reads faster, writes costlier), a non-blocking REFRESH ... CONCURRENTLY proven via real thread timing, a connection-pooling benchmark, the full isolation-level anomaly matrix side by side, SERIALIZABLE's measured bookkeeping overhead and real retry rate under contention, buffer-driven I/O tuning, a genuine planner-cost-constant plan flip, slow-query-log triage, pg_stat_statements top-N ranking (with its non-default setup prerequisites stated explicitly), OLTP-normalized vs. OLAP star-schema query shape, COPY vs. row-INSERT bulk-load throughput, and a closing example that threads a window report, index tuning, an N+1 fix, and a concurrency anomaly's reproduction-and-resolution into one workflow.

The 28 concepts this topic covers

  • co-01 · Subqueries and derived tables -- correlated vs. uncorrelated subqueries and subqueries used as derived tables in FROM.
  • co-02 · Common table expressions -- WITH factoring a query into named, readable steps.
  • co-03 · Recursive CTEs -- WITH RECURSIVE walking hierarchies and graphs with a termination guard.
  • co-04 · Window functions -- OVER() computing across a row set without collapsing rows into groups.
  • co-05 · Window frames and partitions -- PARTITION BY / ORDER BY / the ROWS vs. RANGE frame clause.
  • co-06 · Ranking and analytic functions -- ROW_NUMBER/RANK/DENSE_RANK, LAG/LEAD, NTILE, PERCENT_RANK.
  • co-07 · Set operations -- UNION/INTERSECT/EXCEPT and their ALL variants.
  • co-08 · Grouping sets, ROLLUP, CUBE -- multi-level aggregation (subtotals, grand totals, crosstabs) in one pass.
  • co-09 · LATERAL joins -- LATERAL correlating a subquery to each row of the left side.
  • co-10 · Conditional aggregation -- FILTER (WHERE ...) and CASE-based aggregates for pivots.
  • co-11 · ACID properties -- atomicity, consistency, isolation, and durability defined concretely.
  • co-12 · MVCC -- multi-version concurrency control: readers see a snapshot and don't block writers.
  • co-13 · Isolation levels -- Read Committed, Repeatable Read, and Serializable and the anomalies each forbids.
  • co-14 · Read phenomena -- dirty read, non-repeatable read, phantom, and write skew as named anomalies.
  • co-15 · Serializable snapshot isolation -- PostgreSQL SSI detecting dangerous dependency structures and aborting.
  • co-16 · Explicit locking -- row locks (FOR UPDATE/FOR SHARE), advisory locks, and lock modes.
  • co-17 · Database deadlocks -- how the engine detects a lock cycle and how consistent lock ordering avoids it.
  • co-18 · B-tree index mechanics -- the sorted-tree index and why equality, range, and ordering all benefit.
  • co-19 · Composite and covering indexes -- column order, index-only scans, and INCLUDE columns.
  • co-20 · Specialized indexes -- hash, GIN, GiST, and BRIN indexes and the workload each fits.
  • co-21 · Partial and expression indexes -- indexing a predicate subset or a computed expression.
  • co-22 · Index cost tradeoff -- write amplification, bloat, and when an index hurts more than it helps.
  • co-23 · EXPLAIN and EXPLAIN ANALYZE -- estimated vs. actual plans; PostgreSQL 18 shows buffer stats by default.
  • co-24 · Reading a query plan -- scan and join node types (nested loop / hash / merge), cost, rows, width.
  • co-25 · Table statistics and ANALYZE -- the planner depends on ANALYZE stats; stale stats cause bad plans.
  • co-26 · N+1 diagnosis and fix -- the app-side query explosion and its join / batch fixes.
  • co-27 · Denormalization and materialized views -- trading write cost and storage for read speed.
  • co-28 · Partitioning, pooling, and OLTP vs. OLAP -- declarative partitioning, connection pooling, and workload-shaped schema.

Examples by Level

Beginner (Examples 1–28)

Intermediate (Examples 29–64)

Advanced (Examples 65–85)

← Previous: 25 · Advanced Algorithms Drilling · Next: Beginner Examples →

Last updated July 16, 2026

Command Palette

Search for a command to run...