Postgres Advanced Bootcamp

Architecture Deep Dive & Storage Internals

MVCC, the write-ahead log, page layout, TOAST, bloat and the autovacuum machinery. Everything later in the course rests on this.

Learning objectives

• Trace a single UPDATE from client to disk: shared buffers, WAL record, heap tuple versions, index entries, checkpoint.
• Read raw tuple headers with pageinspect and explain xmin, xmax, ctid and infomask hint bits.
• Predict when an update is HOT and when it creates new index entries.
• Compute when autovacuum will trigger on a given table and change that per table.
• Explain transaction ID wraparound, freezing, and what to do when age(datfrozenxid) climbs.

1.1 Process and memory architecture

Postgres is a multi-process server. The postmaster forks one backend per client connection. Background processes include the checkpointer, background writer, WAL writer, autovacuum launcher and its workers, the stats subsystem (shared memory since PG15), the archiver, WAL senders and WAL receivers. PG18 adds asynchronous I/O, controlled by io_method (worker by default, io_uring on supported Linux builds, or sync), which mostly helps sequential scans, bitmap heap scans and vacuum.

Shared memory holds shared_buffers (the buffer pool of 8 KB pages), WAL buffers, the lock table, and the proc array used to build snapshots. Per-backend memory includes work_mem (per sort or hash node, per worker, not per query) and maintenance_work_mem for VACUUM, CREATE INDEX and friends.

1.2 MVCC: tuple versions and snapshots

Postgres never updates a row in place. An UPDATE writes a new tuple version and stamps the old one's xmax with the updating transaction's ID. A DELETE only sets xmax. Readers decide visibility from their snapshot: the xmin horizon, the xmax horizon, and the list of transactions in progress when the snapshot was taken.

• Tuple header (23 bytes, padded to 24): t_xmin, t_xmax, t_cid, t_ctid (pointer to the newer version, or to itself), t_infomask and t_infomask2 (hint bits such as HEAP_XMIN_COMMITTED, the HOT flags, the attribute count), t_hoff, then the null bitmap.
• Snapshots: under READ COMMITTED each statement takes a new snapshot. Under REPEATABLE READ and SERIALIZABLE, one snapshot is used for the whole transaction. SERIALIZABLE adds SSI predicate locks (visible as SIReadLock in pg_locks) and can abort with SQLSTATE 40001.
• Hint bits: the first reader after commit sets hint bits on the tuple, dirtying the page. This is why a plain SELECT right after a bulk load can generate writes, and WAL when wal_log_hints or data checksums are on. PG18 initdb enables data checksums by default.
• Commit log: transaction status lives in pg_xact (CLOG); subtransactions (each SAVEPOINT, and each PL/pgSQL EXCEPTION block entry) live in pg_subtrans. More than 64 subtransactions in one transaction overflows the per-backend cache and can slow every snapshot on the server.

CREATE EXTENSION pageinspect;
CREATE TABLE mvcc_demo (id int PRIMARY KEY, v text);
INSERT INTO mvcc_demo VALUES (1, 'a');
UPDATE mvcc_demo SET v = 'b' WHERE id = 1;

SELECT lp, lp_flags, t_xmin, t_xmax, t_ctid,
       (t_infomask2 & 16384) > 0 AS hot_updated,
       (t_infomask2 & 32768) > 0 AS heap_only
FROM heap_page_items(get_raw_page('mvcc_demo', 0));
-- lp 1: old version, t_xmax = updating xid, t_ctid = (0,2), hot_updated = true
-- lp 2: new version, t_xmin = updating xid, heap_only = true

1.3 Page layout

Every heap and B-tree file is a sequence of 8 KB pages (the BLCKSZ compile-time default). A heap page has:

Each relation also has a free space map (_fsm fork) and a visibility map (_vm fork, 2 bits per page: all-visible and all-frozen). The visibility map is what makes index-only scans skip the heap and lets vacuum skip pages.

Column order matters for space because of alignment padding. A table of (bool, bigint, bool, bigint) wastes 14 bytes per row compared to (bigint, bigint, bool, bool).

1.4 HOT updates and fillfactor

A Heap-Only Tuple update happens when no indexed column changes and the new version fits on the same page. No new index entries are created, and the old version can be pruned during normal page access without vacuum. HOT is the single most effective defence against index bloat on update-heavy tables.

• Lower fillfactor (for example 70 to 90) on hot update tables to leave room on each page.
• Watch n_tup_hot_upd / n_tup_upd in pg_stat_user_tables. n_tup_newpage_upd (PG16+) counts updates that had to move to another page.
• Adding an index on a frequently updated column (such as updated_at) silently disables HOT for those updates. BRIN indexes are the exception: since PG16, updates that only touch BRIN-indexed columns can still be HOT.

1.5 TOAST

A heap tuple must fit on a page, so values larger than about 2 KB are compressed and, if still too large, moved out of line into the table's TOAST relation in chunks of about 2 KB. The heap keeps an 18-byte pointer.

• Trigger: when a row exceeds TOAST_TUPLE_THRESHOLD (about 2 KB), Postgres compresses or moves its widest columns until the row is under toast_tuple_target (default about 2 KB, settable per table).
• Strategies per column: PLAIN (never), MAIN (compress, move out only as a last resort), EXTERNAL (move out, no compression, so fast substring access), EXTENDED (default: compress then move).
• Compression: pglz or lz4 (default_toast_compression, or per column with ALTER TABLE ... SET COMPRESSION lz4). LZ4 is much faster for both writes and reads.
• Updating any column of a row rewrites the heap tuple, but an unchanged TOASTed value is not copied. Updating a big jsonb document by one key rewrites the whole TOASTed value, which is a common source of WAL volume and TOAST bloat.

SELECT c.relname, t.relname AS toast_table,
       pg_size_pretty(pg_relation_size(c.oid))           AS heap,
       pg_size_pretty(pg_relation_size(c.reltoastrelid)) AS toast
FROM pg_class c JOIN pg_class t ON t.oid = c.reltoastrelid
WHERE c.relname = 'documents';

1.6 Write-ahead logging

Every change is written to WAL before the data page is written. A commit is durable once its WAL is flushed (with synchronous_commit = on). Data pages are written lazily by the background writer, backends, and checkpoints.

• LSN: a 64-bit byte position in the WAL stream. Every page header stores the LSN of its last change. pg_current_wal_lsn() and pg_wal_lsn_diff() measure WAL rate and replica lag.
• Segments: 16 MB files in pg_wal/ by default (initdb --wal-segsize to change).
• Checkpoints: triggered by checkpoint_timeout (default 5 min) or max_wal_size (default 1 GB). Spread over checkpoint_completion_target (default 0.9). Watch pg_stat_checkpointer (PG17+, previously in pg_stat_bgwriter): many requested checkpoints means max_wal_size is too small.
• Full-page writes: the first change to a page after a checkpoint logs the whole 8 KB page to protect against torn writes. Frequent checkpoints therefore inflate WAL volume. wal_compression (lz4 or zstd) shrinks these images.
• wal_level: minimal, replica (default, needed for physical replication and PITR), logical (adds data for logical decoding).
• synchronous_commit: off, local, remote_write, on, remote_apply. Setting off risks losing the last few hundred milliseconds of commits on a crash but never corrupts data.
• PG17 WAL summarization (summarize_wal) enables incremental backups with pg_basebackup --incremental and pg_combinebackup. pg_walinspect (PG15+) lets you inspect WAL records in SQL.

1.7 Bloat and vacuum

Dead tuples stay on the page until vacuum (or HOT pruning) removes them. Vacuum can only remove a tuple that is dead to every snapshot, so the oldest of these holds back cleanup for the whole cluster:

• A long-running transaction, or one left idle in transaction.
• An abandoned replication slot (pg_replication_slots.xmin or catalog_xmin).
• A standby with hot_standby_feedback = on running a long query.
• A forgotten prepared transaction (pg_prepared_xacts).

What plain VACUUM does: prunes dead tuples, removes their index entries, marks line pointers reusable, updates the free space map and visibility map, freezes old tuples, and truncates empty pages at the end of the table (which needs a brief ACCESS EXCLUSIVE lock; disable with vacuum_truncate). It does not return space to the OS from the middle of a table. VACUUM FULL and CLUSTER rewrite the table under ACCESS EXCLUSIVE. pg_repack and pg_squeeze rewrite online.

PG17 vacuum stores dead TIDs in a radix-tree TidStore, removing the old 1 GB memory cap and usually finishing index cleanup in a single pass.

Measuring bloat

-- Estimate (fast, uses stats)
SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum, autovacuum_count
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20;

-- Exact (scans the table)
CREATE EXTENSION pgstattuple;
SELECT * FROM pgstattuple('orders');           -- dead_tuple_percent, free_percent
SELECT * FROM pgstatindex('orders_pkey');      -- avg_leaf_density, leaf_fragmentation

1.8 Autovacuum architecture

The autovacuum launcher wakes every autovacuum_naptime (1 min) per database and starts up to autovacuum_max_workers workers (default 3). PG18 autovacuum_worker_slots (default 16) reserves slots so autovacuum_max_workers can be raised with a reload instead of a restart.

A table is vacuumed when:

n_dead_tup  > autovacuum_vacuum_threshold (50)
            + autovacuum_vacuum_scale_factor (0.2) × reltuples
              -- PG18: capped at autovacuum_vacuum_max_threshold (100,000,000)

n_ins_since_vacuum > autovacuum_vacuum_insert_threshold (1000)
                   + autovacuum_vacuum_insert_scale_factor (0.2) × reltuples   -- PG13+

-- and analyzed when changes > 50 + 0.1 × reltuples

With defaults, a 1-billion-row table waits for 200 million dead rows before vacuum starts. That is why large tables need per-table settings:

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.0,
  autovacuum_vacuum_threshold    = 100000,
  autovacuum_vacuum_cost_limit   = 2000,   -- default inherits vacuum_cost_limit = 200
  autovacuum_vacuum_cost_delay   = 1       -- ms; default 2
);

Throttling: each worker accumulates cost (page hit, miss, dirty) and sleeps autovacuum_vacuum_cost_delay after reaching the cost limit. The limit is shared across all running workers, so adding workers without raising the limit does not make vacuum faster. Monitor progress in pg_stat_progress_vacuum.

1.9 Freezing and XID wraparound

Transaction IDs are 32-bit and compared modulo 2³², so any tuple older than about 2 billion transactions would appear to be in the future. Freezing marks old tuples as visible to everyone.

At about 3 million XIDs before wraparound, the server stops assigning new XIDs and refuses writes. Recovery then needs a manual VACUUM in the affected database. MultiXact IDs (used for shared row locks) have a parallel set of settings and can wrap independently. PG18 adds eager freezing of all-visible pages during normal vacuums (vacuum_max_eager_freeze_failure_rate), which spreads freezing work out and makes the eventual aggressive vacuum cheaper.

SELECT datname, age(datfrozenxid) AS xid_age,
       mxid_age(datminmxid)        AS mxid_age
FROM pg_database ORDER BY xid_age DESC;

SELECT relname, age(relfrozenxid) AS xid_age
FROM pg_class WHERE relkind IN ('r','m','t')
ORDER BY 2 DESC LIMIT 10;

Complex Execution & Analytical SQL

Window frames, recursive queries, LATERAL, row-level security and custom aggregates, taught with an eye on how each one executes.

Learning objectives

• Choose between ROWS, RANGE and GROUPS frames and predict their results with ties.
• Write recursive CTEs that terminate safely, with SEARCH and CYCLE clauses.
• Use LATERAL for top-N-per-group and explain the nested loop it produces.
• Implement multi-tenant isolation with RLS that survives connection pooling and does not wreck plans.
• Build a parallel-safe custom aggregate with a combine function.

2.1 Window functions in depth

Window functions run after WHERE, GROUP BY and HAVING, and before ORDER BY and LIMIT. Each distinct window definition generally needs its own sort, so define windows with a shared WINDOW clause and matching PARTITION BY/ORDER BY to let the executor reuse one sort.

• Frame modes: ROWS counts physical rows. RANGE uses value offsets on the single ORDER BY column and treats peers (ties) as one unit. GROUPS counts peer groups.
• Default frame with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With ties, a "running total" includes all peers of the current row, which surprises people.
• Exclusion: EXCLUDE CURRENT ROW | GROUP | TIES | NO OTHERS.
• Useful functions: lag/lead with defaults, first_value/last_value/nth_value (mind the frame), ntile, percent_rank, cume_dist, and ordered-set aggregates like percentile_cont(0.95) WITHIN GROUP (ORDER BY ...).
• Performance: since PG15, the planner can stop early for row_number(), rank(), dense_rank() and count(*) when the outer query filters with a monotonic condition such as rn <= 3 (Run Condition in EXPLAIN).

-- 7-day rolling revenue by calendar date, gaps in data handled correctly
SELECT day, revenue,
       sum(revenue) OVER w7 AS rolling_7d,
       revenue - lag(revenue) OVER (ORDER BY day) AS dod_change
FROM daily_revenue
WINDOW w7 AS (ORDER BY day RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW);

-- Sessionization: new session after 30 min of inactivity
SELECT user_id, ts,
       sum(new_session) OVER (PARTITION BY user_id ORDER BY ts) AS session_no
FROM (
  SELECT user_id, ts,
         (ts - lag(ts) OVER (PARTITION BY user_id ORDER BY ts) > INTERVAL '30 min'
          OR lag(ts) OVER (PARTITION BY user_id ORDER BY ts) IS NULL)::int AS new_session
  FROM events
) s;

2.2 Recursive CTEs

A recursive CTE has a non-recursive term, UNION or UNION ALL, and a recursive term that references the CTE once. Execution is iterative: a working table feeds each round until a round produces no rows.

• UNION removes duplicates at each step and can stop cycles in simple graphs, but it costs a hash on every iteration.
• PG14+ adds SEARCH DEPTH FIRST BY / BREADTH FIRST BY to produce an ordering column, and CYCLE ... SET is_cycle USING path to detect cycles without hand-written path arrays.
• Always add a depth guard for user-controlled data. A runaway recursion fills temp_file_limit or memory.
• Since PG12, non-recursive CTEs are inlined unless referenced more than once or marked MATERIALIZED. Recursive CTEs are always materialized.

WITH RECURSIVE org AS (
  SELECT id, manager_id, name, 1 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.manager_id, e.name, o.depth + 1
  FROM employees e JOIN org o ON e.manager_id = o.id
  WHERE o.depth < 20
)
SEARCH DEPTH FIRST BY name SET ordercol
CYCLE id SET is_cycle USING path
SELECT repeat('  ', depth - 1) || name AS tree FROM org ORDER BY ordercol;

2.3 LATERAL joins

LATERAL lets a subquery or set-returning function in FROM reference columns of earlier items. It is a correlated subquery that can return many rows and many columns. The planner usually executes it as a nested loop, so it is ideal when the outer side is small and the inner side has a supporting index.

-- Top 3 most recent orders per customer, using index (customer_id, created_at DESC)
SELECT c.id, c.name, o.id AS order_id, o.created_at, o.total
FROM customers c
CROSS JOIN LATERAL (
  SELECT id, created_at, total
  FROM orders
  WHERE orders.customer_id = c.id
  ORDER BY created_at DESC
  LIMIT 3
) o
WHERE c.segment = 'enterprise';

Compare this against the window version (row_number() ... WHERE rn <= 3). The window version reads every order and sorts. The LATERAL version does three index probes per customer. Crossover depends on the ratio of customers to orders, and you measure it in Lab D.

2.4 Row-level security

RLS adds policy predicates to every query on a table. Policies are PERMISSIVE (OR-ed together, the default) or RESTRICTIVE (AND-ed). USING filters rows that can be seen or targeted; WITH CHECK validates new rows from INSERT and UPDATE.

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;   -- applies to the table owner too

CREATE POLICY tenant_isolation ON invoices
  USING      (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

-- In the app, per transaction (safe with PgBouncer transaction pooling):
BEGIN;
SELECT set_config('app.tenant_id', '0b6c...', true);   -- true = local to this transaction
SELECT * FROM invoices WHERE status = 'open';
COMMIT;

• Superusers and roles with BYPASSRLS skip policies. Table owners skip them unless FORCE is set.
• Use SET LOCAL or set_config(..., true), never plain SET, when a pooler may hand the connection to another tenant.
• Performance: the policy predicate must be indexable. Lead composite indexes with tenant_id. Functions called in policies should be STABLE and, if they appear inside user-supplied predicates, LEAKPROOF, otherwise the planner cannot push user quals below the security barrier and may lose index usage.
• Views run with the view owner's permissions and bypass the caller's RLS unless created WITH (security_invoker = true) (PG15+).
• Foreign key checks and unique constraints are not filtered by RLS, so they can leak the existence of other tenants' rows. Use tenant-scoped composite keys.

2.5 Custom aggregate functions

An aggregate is a state transition function (SFUNC) over a state type (STYPE), with an optional final function. Adding COMBINEFUNC and PARALLEL = SAFE lets the planner use partial aggregation in parallel workers and in partition-wise aggregation. Adding MSFUNC/MINVFUNC enables efficient moving-window evaluation.

-- Weighted average: sum(value*weight) / sum(weight)
CREATE FUNCTION wavg_sfunc(state numeric[], val numeric, w numeric)
RETURNS numeric[] LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$$ SELECT ARRAY[state[1] + val * w, state[2] + w] $$;

CREATE FUNCTION wavg_combine(a numeric[], b numeric[])
RETURNS numeric[] LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$$ SELECT ARRAY[a[1] + b[1], a[2] + b[2]] $$;

CREATE FUNCTION wavg_final(state numeric[])
RETURNS numeric LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$$ SELECT CASE WHEN state[2] = 0 THEN NULL ELSE state[1] / state[2] END $$;

CREATE AGGREGATE wavg(numeric, numeric) (
  SFUNC = wavg_sfunc, STYPE = numeric[], INITCOND = '{0,0}',
  COMBINEFUNC = wavg_combine, FINALFUNC = wavg_final, PARALLEL = SAFE
);

SELECT product_id, wavg(price, qty) FROM order_items GROUP BY product_id;

For hot paths, write the transition function in C or use an existing extension. SQL-language transition functions are called once per row and are noticeably slower than built-ins.

2.6 Other modern SQL to cover briefly

• MERGE (PG15), with RETURNING and merge_action() (PG17), and WHEN NOT MATCHED BY SOURCE (PG17).
• JSON_TABLE, JSON_EXISTS, JSON_QUERY, JSON_VALUE (PG17).
• PG18 OLD and NEW in RETURNING for INSERT/UPDATE/DELETE/MERGE; virtual generated columns (now the default kind); uuidv7(); temporal PRIMARY KEY/UNIQUE ... WITHOUT OVERLAPS and PERIOD foreign keys.
• GROUPING SETS, ROLLUP, CUBE, FILTER (WHERE ...) on aggregates, and DISTINCT ON.

Elite Indexing & Specialized Strategies

Every index speeds some reads and slows every write. This module is about choosing the index whose trade fits the workload, and proving it with numbers.

Learning objectives

• Design multi-column B-tree indexes from the query's equality, range and sort columns.
• Use partial, expression and covering indexes to shrink index size and enable index-only scans.
• Match workloads to GIN, GiST, SP-GiST, BRIN and pgvector's HNSW and IVFFlat, and tune each.
• Find unused, duplicate and bloated indexes and drop or rebuild them without downtime.

3.1 B-tree design rules

• Column order: equality columns first, then the range or sort column. An index on (tenant_id, status, created_at) serves WHERE tenant_id = $1 AND status = 'open' ORDER BY created_at DESC LIMIT 50 with no sort.
• PG18 Skip scan: a B-tree can now be used when the leading column has no condition, if it has few distinct values. Leading-column order still matters a lot for high-cardinality columns.
• Deduplication (PG13+) stores duplicate keys once with a posting list, shrinking low-cardinality indexes considerably.
• Bottom-up deletion (PG14+) removes version-churn entries before a page split, slowing index bloat from non-HOT updates.
• Sort direction only matters for mixed-direction sorts: ORDER BY a ASC, b DESC needs an index declared (a, b DESC).
• UNIQUE NULLS NOT DISTINCT (PG15+) treats NULLs as equal for uniqueness.

3.2 Partial, expression and covering indexes

-- Partial: index only the 2% of rows the hot query touches
CREATE INDEX CONCURRENTLY orders_pending_idx
  ON orders (created_at) WHERE status = 'pending';

-- Expression: the query must use the identical expression
CREATE INDEX CONCURRENTLY users_email_lower_idx ON users (lower(email));
SELECT * FROM users WHERE lower(email) = lower($1);

-- Covering: INCLUDE payload columns to enable an index-only scan
CREATE INDEX CONCURRENTLY orders_cust_cover_idx
  ON orders (customer_id, created_at DESC) INCLUDE (total, status);

-- Partial unique: one active subscription per account
CREATE UNIQUE INDEX one_active_sub
  ON subscriptions (account_id) WHERE ended_at IS NULL;

• A partial index is only used when the planner can prove the query's WHERE implies the index predicate. With a parameterized status = $1, a generic plan cannot prove it.
• Expression indexes get their own statistics after ANALYZE, which can fix bad estimates on computed predicates even if the index is rarely scanned.
• INCLUDE columns are stored only in leaf pages and are not part of the key, so they cannot be used for searching or ordering.
• Index-only scans still visit the heap for pages that are not all-visible. Vacuum frequency directly affects index-only scan speed.

3.3 Specialized access methods

-- jsonb containment with the smaller opclass
CREATE INDEX events_payload_gin ON events USING gin (payload jsonb_path_ops);
SELECT * FROM events WHERE payload @> '{"type":"checkout","country":"DE"}';

-- No double-booking: exclusion constraint on a range
CREATE EXTENSION btree_gist;
ALTER TABLE bookings ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (room_id WITH =, during WITH &&);

-- 2 TB append-only metrics table
CREATE INDEX metrics_ts_brin ON metrics USING brin (ts) WITH (pages_per_range = 32, autosummarize = on);
SELECT correlation FROM pg_stats WHERE tablename = 'metrics' AND attname = 'ts';  -- want ≈ 1.0

3.4 pgvector for AI workloads

pgvector (0.8.x at the time of writing) adds vector, halfvec (16-bit floats), sparsevec and bit types, with distance operators <-> (L2), <#> (negative inner product), <=> (cosine), <+> (L1), and <~>/<%> (Hamming/Jaccard for bit).

CREATE EXTENSION vector;
CREATE TABLE doc_chunks (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id uuid NOT NULL,
  body      text NOT NULL,
  embedding halfvec(1536) NOT NULL
);
CREATE INDEX ON doc_chunks USING hnsw (embedding halfvec_cosine_ops)
  WITH (m = 16, ef_construction = 128);
CREATE INDEX ON doc_chunks (tenant_id);

-- Filtered ANN search: let the index keep scanning until enough rows pass the filter
SET hnsw.ef_search = 100;
SET hnsw.iterative_scan = relaxed_order;     -- pgvector 0.8+
SELECT id, body, embedding <=> $1 AS distance
FROM doc_chunks
WHERE tenant_id = $2
ORDER BY embedding <=> $1
LIMIT 10;

• Approximate indexes plus a WHERE filter can return fewer than LIMIT rows, because the filter is applied after the index returns its candidates. Iterative scans (0.8+), partial indexes per tenant or category, or partitioning by tenant are the fixes.
• Measure recall against an exact scan (SET enable_indexscan = off) on a sample of real queries. Do not tune ef_search blind.
• halfvec halves storage and index size with negligible recall loss for most embedding models. Binary quantization with bit plus re-ranking is the next step down.
• Hybrid search: combine a full-text ts_rank result set and a vector result set with reciprocal rank fusion in SQL.

3.5 Index hygiene

-- Unused indexes (check every replica too: stats are per node)
SELECT s.relname, s.indexrelname, s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0 AND NOT i.indisunique
ORDER BY pg_relation_size(s.indexrelid) DESC;

-- Rebuild a bloated index online
REINDEX INDEX CONCURRENTLY orders_cust_cover_idx;

-- Test an index without building it
CREATE EXTENSION hypopg;
SELECT * FROM hypopg_create_index('CREATE INDEX ON orders (status, created_at)');
EXPLAIN SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at LIMIT 20;

Query Optimization & Query Planner Mechanics

How the cost-based optimizer turns SQL into a plan tree, how to read what it chose, and how to correct it when its estimates are wrong.

Learning objectives

• Read EXPLAIN (ANALYZE, BUFFERS) bottom-up and find the first node where estimated and actual rows diverge.
• Explain when the planner picks a sequential, index, index-only or bitmap scan, and why.
• Describe the cost model of nested loop, hash and merge joins and predict which one wins.
• Fix estimates with statistics targets, extended statistics and query rewrites before reaching for planner switches.
• Diagnose generic versus custom plan problems with prepared statements.

4.1 The query pipeline

Parser → analyzer → rewriter (views, rules, RLS) → planner/optimizer → executor. The planner enumerates scan paths for each relation, join orders and methods (exhaustively up to geqo_threshold = 12 relations, then with the genetic optimizer), and picks the cheapest total cost, or cheapest startup cost when a LIMIT or cursor favors fast first rows. join_collapse_limit and from_collapse_limit (both 8) bound how much it reorders explicit joins and subqueries.

4.2 The cost model

Sequential scan cost ≈ relpages × seq_page_cost + reltuples × cpu_tuple_cost (plus operator costs for filters). Row estimates come from pg_statistic (view: pg_stats): null fraction, distinct count, most common values with frequencies, histogram bounds, and physical correlation. default_statistics_target (100) controls the sample size and number of MCV and histogram entries.

4.3 Reading EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, WAL)
SELECT c.name, sum(o.total)
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE c.country = 'DE' AND o.created_at >= '2026-09-01'
GROUP BY c.name;

HashAggregate  (cost=48211.20..48236.20 rows=2000 width=40)
               (actual time=412.8..413.4 rows=1874 loops=1)
  Group Key: c.name
  Buffers: shared hit=9120 read=31877
  ->  Hash Join  (cost=1210.00..47961.20 rows=50000 width=18)
                 (actual time=11.2..389.1 rows=612443 loops=1)
        Hash Cond: (o.customer_id = c.id)
        ->  Seq Scan on orders o  (cost=0.00..43520.00 rows=950000 width=14)
                                  (actual time=0.02..201.5 rows=948201 loops=1)
              Filter: (created_at >= '2026-09-01'::date)
              Rows Removed by Filter: 1051799
        ->  Hash  (cost=1085.00..1085.00 rows=10000 width=20)
                  (actual time=10.9..10.9 rows=64120 loops=1)
              Buckets: 65536 (originally 16384)  Batches: 1  Memory Usage: 3843kB
              ->  Seq Scan on customers c  (rows=10000) (actual rows=64120 loops=1)
                    Filter: (country = 'DE')

• Read inside-out, bottom-up. Each node shows estimated cost=startup..total, rows, width, then actuals. Actual time and rows are per loop: multiply by loops.
• Find the first misestimate. Here customers estimated 10,000 rows for DE but got 64,120 (6×), which propagated into a 12× join misestimate. The hash table resized ("originally 16384"). A stale or too-coarse MCV list for country is the likely cause.
• Buffers: shared hit came from cache, read from the OS (page cache or disk), dirtied/written show write side effects, temp read/written means spills to disk. PG18 EXPLAIN ANALYZE includes BUFFERS by default, and index scans report "Index Searches".
• Spills: Sort Method: external merge Disk: 120MB or hash Batches: 8 mean work_mem (times hash_mem_multiplier, default 2.0, for hashes) was too small for that node.
• Other options: VERBOSE (output columns, schema-qualified names), SETTINGS (non-default planner GUCs), WAL, SERIALIZE and MEMORY (PG17), GENERIC_PLAN (PG16, plan a $1 query without values), FORMAT JSON for visualizers like explain.dalibo.com.

4.4 Scan methods

Bitmap heap scan details. If the bitmap exceeds work_mem, it becomes lossy: it remembers whole pages instead of individual tuples, and every tuple on those pages must be rechecked. EXPLAIN shows Heap Blocks: exact=1200 lossy=48000 and Rows Removed by Index Recheck. More work_mem or a more selective index fixes it. Bitmap scans lose the index order, so they cannot satisfy ORDER BY without a sort.

4.5 Join strategies

4.6 Fixing bad estimates

Fix the information first, the plan second.

1. Fresh statistics: ANALYZE after bulk loads. Autovacuum's analyze threshold is 10% of rows by default. PG18 pg_upgrade keeps planner statistics, so major upgrades no longer start with an empty pg_statistic (extended statistics still need a fresh ANALYZE).
2. Bigger samples for skewed columns: ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
3. Extended statistics for correlated columns. The planner assumes independence, so city = 'Munich' AND country = 'DE' is underestimated.

   CREATE STATISTICS addr_stats (dependencies, ndistinct, mcv) ON city, country FROM addresses;
   ANALYZE addresses;

4. Rewrite opaque predicates: WHERE created_at::date = $1 becomes a range; WHERE coalesce(x, 0) = 0 becomes x = 0 OR x IS NULL; functions get correct ROWS/COST estimates and volatility.
5. Correlated subqueries and CTEs: MATERIALIZED CTEs act as an optimization fence; NOT MATERIALIZED allows pushdown. NOT IN (subquery) cannot become an anti-join because of NULL semantics; use NOT EXISTS.

4.7 Planner method configuration parameters

The enable_* parameters do not forbid a plan type. They add a large penalty, so the planner avoids that path unless nothing else is possible. PG18 EXPLAIN reports these as Disabled: true on the node instead of showing an inflated cost.

-- Diagnose, never deploy globally:
BEGIN;
SET LOCAL enable_nestloop = off;
EXPLAIN (ANALYZE, BUFFERS) ... ;   -- does the hash join plan really run faster?
ROLLBACK;

-- If a forced plan is truly needed for one function or role:
ALTER FUNCTION report_monthly() SET enable_nestloop = off;
ALTER ROLE reporting SET random_page_cost = 1.1;

Other levers: plan_cache_mode (auto, force_custom_plan, force_generic_plan) for prepared statements whose generic plan is bad for skewed parameters; pg_hint_plan for per-query hints when all else fails; jit (often worth disabling for OLTP, where compile time exceeds savings); parallel query knobs max_parallel_workers_per_gather, parallel_setup_cost, min_parallel_table_scan_size.

Scaling, Concurrency & Infrastructure Operations

Locks, deadlocks, pooling, migrations that do not take the site down, replication, and when to split data across tables or machines.

Learning objectives

• Name the lock each DDL and DML statement takes and predict what it blocks.
• Read a blocking tree from pg_locks and pg_blocking_pids(), and read a deadlock report.
• Choose a PgBouncer pool mode and size, and know what each mode breaks.
• Execute any common schema change with zero downtime using lock timeouts and expand/contract.
• Compare physical and logical replication and design an HA topology with a stated RPO and RTO.
• Decide between partitioning, sharding and neither.

5.1 Table-level lock modes

Row-level locks live in the tuple header (xmax plus infomask bits, with MultiXacts when shared), not in the lock table: FOR KEY SHARE (taken by FK checks) < FOR SHARE < FOR NO KEY UPDATE (taken by ordinary UPDATE) < FOR UPDATE (taken by DELETE and updates of key columns).

5.2 The lock queue problem

Lock requests queue in order. If a long SELECT holds ACCESS SHARE and a migration requests ACCESS EXCLUSIVE, the migration waits, and every new query (even plain reads) queues behind the migration. A 2-millisecond ALTER TABLE takes the site down because of a 10-minute report.

SET lock_timeout = '3s';         -- give up instead of blocking the queue
SET statement_timeout = '15s';
ALTER TABLE orders ADD COLUMN source text;   -- retry with backoff on failure

5.3 Diagnosing blocking and deadlocks

-- Who blocks whom
SELECT a.pid, a.usename, a.state, a.wait_event_type, a.wait_event,
       pg_blocking_pids(a.pid) AS blocked_by,
       now() - a.xact_start AS xact_age,
       left(a.query, 80) AS query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY xact_age DESC;

-- End the root blocker (cancel first, terminate if it is idle in transaction)
SELECT pg_cancel_backend(12345);
SELECT pg_terminate_backend(12345);

A deadlock is a cycle in the wait-for graph. After deadlock_timeout (default 1s), the waiting backend runs the detector and aborts one transaction with SQLSTATE 40P01. The server log shows both processes, their lock requests and their queries. Turn on log_lock_waits to also log any wait longer than deadlock_timeout.

Common causes and fixes:

• Inconsistent row order: two transactions update rows A then B and B then A. Fix by sorting keys before updating, or updating in one statement.
• Foreign keys: inserting children takes FOR KEY SHARE on the parent, which conflicts with a concurrent key update or delete of the parent.
• Upserts across unique indexes in different orders under concurrency.
• Explicit lock upgrades: two sessions take SHARE then try to upgrade to EXCLUSIVE. Take the strongest lock you will need first.

Patterns to teach: job queues with SELECT ... FOR UPDATE SKIP LOCKED LIMIT n; fail-fast with NOWAIT; application-level mutual exclusion with advisory locks (pg_try_advisory_xact_lock); idle_in_transaction_session_timeout and transaction_timeout (PG17) as safety nets.

5.4 Connection pooling with PgBouncer

• Sizing: database throughput peaks at a small number of active connections, roughly 2 to 4 × CPU cores for OLTP. Start default_pool_size near that per database/user pair and raise it only with measured gains. Total server connections across all pooler instances must stay below max_connections minus reserved and admin slots.
• Key settings: max_client_conn, default_pool_size, reserve_pool_size, server_idle_timeout, query_wait_timeout, server_reset_query (only used in session mode).
• Topology: PgBouncer on each app host (reduces network hops, multiplies server connections) versus a central tier (one global limit, extra hop, needs its own HA). PgBouncer is single-threaded; run several processes with so_reuseport for very high throughput.
• Monitor: SHOW POOLS (cl_waiting, maxwait) and SHOW STATS on the admin console.
• Alternatives: PgCat and PgDog (multi-threaded, with load balancing and sharding features), Odyssey, Supavisor, and cloud proxies such as RDS Proxy.

5.5 Zero-downtime schema migrations

Rule one: every migration sets lock_timeout and retries. Rule two: anything that rewrites or scans a big table under ACCESS EXCLUSIVE gets replaced by a multi-step pattern.

-- Batched backfill: small transactions, resumable, gentle on WAL and replicas
DO $$
DECLARE n int;
BEGIN
  LOOP
    UPDATE orders SET source = 'web'
    WHERE id IN (SELECT id FROM orders WHERE source IS NULL LIMIT 5000 FOR UPDATE SKIP LOCKED);
    GET DIAGNOSTICS n = ROW_COUNT;
    EXIT WHEN n = 0;
    COMMIT;                       -- procedures and DO blocks can commit (PG11+)
    PERFORM pg_sleep(0.05);
  END LOOP;
END $$;

Tooling: Squawk (lints migration SQL for these hazards), strong_migrations (Rails), pgroll and Reshape (automated expand/contract with versioned views).

5.6 Physical vs logical replication

• Synchronous replication: synchronous_standby_names = 'ANY 1 (s1, s2)' with synchronous_commit = on (or remote_apply for read-your-writes on replicas). A sync standby going away stalls commits unless another can take over.
• Slots guarantee WAL retention for a consumer. An abandoned slot fills the disk; cap it with max_slot_wal_keep_size, and PG18 idle_replication_slot_timeout invalidates idle slots.
• Standby conflicts: vacuum on the primary can remove rows a standby query needs. Options: hot_standby_feedback = on (moves bloat to the primary), or raise max_standby_streaming_delay (increases lag).
• Logical replication progress: decoding from standbys (PG16); failover slots synced to standbys with sync_replication_slots (PG17); pg_createsubscriber converts a physical standby into a logical subscriber (PG17); pg_upgrade preserves logical slots (PG17); PG18 replicates stored generated columns and reports conflict counts in pg_stat_subscription_stats.
• HA orchestration: Patroni (with etcd or Consul) is the de facto standard on VMs; CloudNativePG on Kubernetes. Fencing and a single source of truth for "who is primary" matter more than failover speed.
• Backups: pgBackRest or Barman for base backups plus WAL archiving and point-in-time recovery. A replica is not a backup.

5.7 Partitioning and sharding

Declarative partitioning (RANGE, LIST, HASH) splits one logical table into child tables. It pays off for:

• Retention: dropping or detaching a month is instant; DELETE of a month is a bloat event.
• Pruning: at plan time for constants, at execution time for parameters and joins (look for "Subplans Removed").
• Maintenance: vacuum, reindex and freeze per partition; old partitions become all-frozen and are skipped.

Costs and rules: primary keys and unique constraints must include the partition key; queries without the key touch every partition; planning time grows with partition count (keep it in the hundreds to low thousands); attach a pre-loaded table with a matching CHECK constraint already validated to avoid a scan. Use pg_partman to create future partitions and enforce retention.

CREATE TABLE events (
  id         bigint GENERATED ALWAYS AS IDENTITY,
  tenant_id  uuid        NOT NULL,
  created_at timestamptz NOT NULL,
  payload    jsonb,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_10 PARTITION OF events
  FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE events_default PARTITION OF events DEFAULT;

Sharding spreads partitions across machines. Options: Citus (distributed tables by a shard key, reference tables copied to every node, colocation so joins on the shard key stay local), application-level sharding by tenant, or newer proxy-based sharders such as PgDog. Shard only when a single well-tuned primary (vertical scaling plus read replicas) cannot hold the write load or the data size. Cross-shard transactions, joins and schema changes are the costs.