PostgreSQL Cheatsheet

How PostgreSQL stores and versions rows, which index to pick, how to read EXPLAIN ANALYZE, how locks behave, how to run it in production — and step-by-step playbooks for slow queries, bloat, lock waits, replication lag and full disks. Each entry gives the problem, how it works, SQL examples, real-world notes and trade-offs.

Updated: 2026-10-08

29 entries

Quick tables

Reference and comparison tables for quick scanning.

Isolation levels and anomalies in PostgreSQL

What can still happen at each level as PostgreSQL actually implements it, which is stricter than the SQL standard table in places. 'Possible' means your code must handle it.

Isolation levels and anomalies in PostgreSQL
LevelDirty readNon-repeatable readPhantom readLost update (app read-modify-write)Write skewOther serialization anomaly
`READ UNCOMMITTED`Not possible (behaves as Read Committed)PossiblePossiblePossiblePossiblePossible
`READ COMMITTED` (default)Not possiblePossiblePossiblePossible; atomic `SET n = n + 1` or `FOR UPDATE` avoids itPossiblePossible
`REPEATABLE READ`Not possibleNot possibleNot possible (snapshot isolation)Prevented: the later writer fails with `40001`PossiblePossible (for example the read-only anomaly)
`SERIALIZABLE`Not possibleNot possibleNot possiblePreventedPrevented (a transaction aborts with `40001`)Prevented; retry on `40001`

Concurrent writes to the same row are serialized by row locks at every level; the differences above concern reads that inform later writes. Deadlocks (`40P01`) can occur at any level.

Autovacuum parameters and defaults (PG 14-17)

Defaults are the same across PG 14 to 17 unless a row says otherwise. Most can also be set per table with `ALTER TABLE ... SET (...)` using the `autovacuum_` names; the cost limit and delay then apply to that table only.

Autovacuum parameters and defaults (PG 14-17)
ParameterDefaultWhat it doesWhen to change
`autovacuum``on`Enables the autovacuum launcher and workers.Leave on. Anti-wraparound vacuums run even when it is off, so disabling saves nothing.
`autovacuum_naptime``1min`How often the launcher starts a worker in each database.Rarely; lower it only with very many databases or a tiny scale factor on small tables.
`autovacuum_max_workers``3`Maximum concurrent autovacuum workers; changing it needs a restart.Raise with many large tables, and raise the cost limit too since the budget is shared.
`autovacuum_vacuum_threshold``50`Base number of dead tuples; trigger is threshold + scale factor * `reltuples`.Raise per table for tables with constant noise updates.
`autovacuum_vacuum_scale_factor``0.2`Fraction of the table that must be dead before vacuum starts.Lower to `0.01`-`0.05` on tables with millions of rows.
`autovacuum_vacuum_insert_threshold``1000`Inserted tuples since last vacuum that trigger one (PG 13+); sets visibility map bits and freezes.Lower on append-only tables whose index-only scans need fresh visibility maps.
`autovacuum_vacuum_insert_scale_factor``0.2`Fraction of the table inserted that triggers an insert-driven vacuum.Lower on large append-only tables (events, logs).
`autovacuum_analyze_threshold` / `autovacuum_analyze_scale_factor``50` / `0.1`Changed rows needed before auto-analyze refreshes statistics.Lower the factor on large tables with shifting distributions, or run `ANALYZE` after bulk loads.
`autovacuum_vacuum_cost_delay``2ms`Sleep when the cost budget is used up (default was 20ms before PG 12).Lower to `0`-`1ms` when I/O has headroom and vacuum falls behind.
`autovacuum_vacuum_cost_limit``-1` (uses `vacuum_cost_limit` = `200`)Budget per round, shared among running workers; page costs are hit 1, miss 2 (10 before PG 14), dirty 20.Raise to `1000`-`2000` on fast storage; a per-table value takes that table out of the shared balancing.
`autovacuum_work_mem``-1` (uses `maintenance_work_mem` = `64MB`)Memory for tracking dead tuples; when full, vacuum makes extra index passes (PG 17 uses a new memory scheme and lifts the old silent 1 GB cap).Raise for large tables with many dead rows; multiply by workers when sizing RAM.
`autovacuum_freeze_max_age``200000000`Forces an anti-wraparound vacuum when `age(relfrozenxid)` passes it; restart required.Rarely; lowering spreads freezing earlier, raising delays heavy passes but narrows your safety margin.
`vacuum_freeze_min_age` / `vacuum_freeze_table_age``50000000` / `150000000`Minimum tuple age to freeze, and the table age at which a vacuum scans every unfrozen page.Lower the min age on tables that are written once and rarely changed, to freeze while pages are already dirty.
`autovacuum_multixact_freeze_max_age``400000000`Same as the freeze age but for multixact IDs (shared row locks, foreign keys).Watch `mxid_age()` if heavy `FOR SHARE` / foreign key locking is used.
`vacuum_failsafe_age``1600000000`PG 14+: past this age vacuum drops cost delay and skips non-essential work to avoid wraparound.Leave as is; reaching it means freezing has already failed to keep up.

Check live values with `SELECT name, setting, unit, source FROM pg_settings WHERE name LIKE '%vacuum%'` and per-table overrides in `pg_class.reloptions`.

PostgreSQL index types compared

Start with B-tree; move to another type only when the query uses an operator B-tree cannot serve. Sizes and costs are relative and depend on the data and operator class.

PostgreSQL index types compared
TypeOperators it acceleratesGood forWrite cost and sizePitfall
B-tree`<`, `<=`, `=`, `>=`, `>`, `BETWEEN`, `IN`, `IS NULL`; also provides orderingKeys, foreign keys, range filters, `ORDER BY ... LIMIT`, uniquenessModerate per-write cost; deduplication (PG 13+) keeps duplicate-heavy indexes smallerCannot serve `LIKE '%x%'`; prefix `LIKE 'x%'` needs the C collation or `text_pattern_ops`
Hash`=` onlyEquality lookups on long keys where no ordering or uniqueness is neededOften smaller than B-tree for long values; WAL-logged since PG 10No range, no ordering, no `UNIQUE`, no multicolumn, no index-only scans
GIN`@>`, `<@`, `&&` (arrays); `@>`, `?`, `@?`, `@@` (jsonb); `@@` (full text); `LIKE`/`%` with `gin_trgm_ops`Composite values: arrays, jsonb, full-text, trigram searchSlow to build and update, mitigated by the pending list (`fastupdate`); often largeMany rows per key means big bitmap scans; write-heavy tables pay for it
GiSTOverlap, containment, nearest-neighbour (`<->`) on geometry, ranges, trigramsRange types and exclusion constraints, PostGIS geometry, KNN orderingCheaper updates than GIN, slower searches; can be lossy, so rows are recheckedNot for plain equality (use B-tree, or `btree_gist` inside exclusion constraints)
SP-GiSTOperators of partitioned structures: prefix and radix on text, quad/k-d on points, `inet`Unbalanced or clustered data such as IP ranges, phone prefixes, pointsModerate; size depends on how well the data splits into the treeNarrow operator coverage; check the operator class supports your operator
BRIN`<`, `=`, `>` style comparisons through per-block-range summaries (min/max by default)Huge append-only tables whose column correlates with physical order, such as `created_at`Tiny index and cheap writesUseless when values are scattered; only narrows to block ranges, so scans are lossy

Run `SELECT amname FROM pg_am WHERE amtype = 'i'` to list the index access methods installed in your cluster.

Common EXPLAIN nodes and red flags

Read plans bottom-up and inner-to-outer. A node is only a problem when it is large, repeated or mis-estimated; a Seq Scan on a small table is often the right choice.

Common EXPLAIN nodes and red flags
NodeMeaningRed flag
`Seq Scan`Reads the whole table in physical order and filters rows.Large `Rows Removed by Filter` with few rows returned, or a Seq Scan on the inner side of a Nested Loop.
`Index Scan`Walks the index and fetches each matching heap row.Many matches with random heap access (low `correlation`); a `Filter:` that discards most fetched rows because the condition is not in the index.
`Index Only Scan`Answers from the index alone when the visibility map allows.High `Heap Fetches`, meaning the table needs `VACUUM` to set visibility bits.
`Bitmap Index Scan` + `Bitmap Heap Scan`Collects matching row locations into a bitmap, then reads heap pages in physical order.`Heap Blocks: lossy` (bitmap exceeded `work_mem`), or a huge bitmap that touches most of the table.
`Nested Loop`For each outer row, probes the inner side. Great when the outer side is small and the inner side is indexed.Inner node shows a large `loops` count with a Seq Scan or Sort; outer estimate far below the actual rows.
`Hash Join`Builds a hash table from one input and probes it with the other; equality joins only.`Batches` greater than 1 (spilled to disk), or the build side is the big table because of an estimate error.
`Merge Join`Merges two inputs sorted on the join key.An explicit `Sort` under both inputs on large data; usually fine only when indexes provide the order.
`Sort`Orders rows for `ORDER BY`, merge join, `DISTINCT` or window functions.`Sort Method: external merge Disk: ...` means it spilled past `work_mem`.
`HashAggregate` / `GroupAggregate`Groups rows with a hash table, or from pre-sorted input.A hash aggregate with huge group count spilling to disk (PG 13+ can spill); estimated groups far from actual.
`Gather` / `Gather Merge`Collects rows from parallel workers.`Workers Launched` lower than `Workers Planned` (worker pool exhausted), or parallelism on a query that returns most rows.
`Materialize`Buffers an input in memory (or temp file) so it can be rescanned.Appears under a Nested Loop with many loops over a big input, a sign of a bad join choice.
`CTE Scan`Reads the stored result of a materialized CTE.A filter applied above the CTE Scan that could have been pushed into the CTE; fix with `NOT MATERIALIZED` or by moving the condition.

Add `EXPLAIN (ANALYZE, BUFFERS)` to see actual rows, `loops` and buffer counts per node; multiply per-loop time and rows by `loops` for the real totals.

Table-level lock modes and what they conflict with

Read a row as: a session that wants this mode waits if another session already holds any mode in the last column. The relation is symmetric (if A blocks B, B blocks A). Modes never conflict with themselves in the first three rows, which is why many sessions can read and write the same table at once.

Table-level lock modes and what they conflict with
ModeTypical statementsConflicts with (held by another session)
`ACCESS SHARE``SELECT` (plain reads)`ACCESS EXCLUSIVE`
`ROW SHARE``SELECT ... FOR UPDATE / NO KEY UPDATE / SHARE / KEY SHARE``EXCLUSIVE`, `ACCESS EXCLUSIVE`
`ROW EXCLUSIVE``INSERT`, `UPDATE`, `DELETE`, `MERGE` (PG 15+)`SHARE`, `SHARE ROW EXCLUSIVE`, `EXCLUSIVE`, `ACCESS EXCLUSIVE`
`SHARE UPDATE EXCLUSIVE``VACUUM` (not FULL), `ANALYZE`, `CREATE INDEX CONCURRENTLY`, `REINDEX CONCURRENTLY`, `ALTER TABLE ... VALIDATE CONSTRAINT``SHARE UPDATE EXCLUSIVE`, `SHARE`, `SHARE ROW EXCLUSIVE`, `EXCLUSIVE`, `ACCESS EXCLUSIVE`
`SHARE``CREATE INDEX` (without `CONCURRENTLY`)`ROW EXCLUSIVE`, `SHARE UPDATE EXCLUSIVE`, `SHARE ROW EXCLUSIVE`, `EXCLUSIVE`, `ACCESS EXCLUSIVE`
`SHARE ROW EXCLUSIVE``CREATE TRIGGER`, some `ALTER TABLE` forms (for example adding a foreign key)`ROW EXCLUSIVE`, `SHARE UPDATE EXCLUSIVE`, `SHARE`, `SHARE ROW EXCLUSIVE`, `EXCLUSIVE`, `ACCESS EXCLUSIVE`
`EXCLUSIVE``REFRESH MATERIALIZED VIEW CONCURRENTLY`Every mode except `ACCESS SHARE`
`ACCESS EXCLUSIVE``DROP TABLE`, `TRUNCATE`, `VACUUM FULL`, `CLUSTER`, `REINDEX` (on the indexes; the table itself gets `SHARE`), most `ALTER TABLE`, `LOCK TABLE` with no modeEvery mode, including `ACCESS SHARE`

Verified against a local PostgreSQL 16 by holding each mode in one session and requesting each mode with `LOCK TABLE ... NOWAIT` in another. The row-level modes (`FOR KEY SHARE` < `FOR SHARE` < `FOR NO KEY UPDATE` < `FOR UPDATE`) have their own, separate conflict rules and are not shown in `pg_locks`.

Is this DDL safe on a live table?

Even a metadata-only change needs a brief `ACCESS EXCLUSIVE` lock, and that lock queues behind any long transaction while every later query queues behind it. Run all of these with `SET lock_timeout` and retry. Lock names are from the explicit-locking chapter of the docs.

Is this DDL safe on a live table?
OperationLock takenSafe?Safer alternative
`ADD COLUMN` (nullable, or with a constant `DEFAULT`)`ACCESS EXCLUSIVE`, held brieflyYes (PG 11+)Nothing needed; the default is stored in the catalog, no rewrite. Add `NOT NULL` only together with a constant default.
`ADD COLUMN` with a volatile `DEFAULT` (`random()`, `clock_timestamp()`)`ACCESS EXCLUSIVE` for the whole table rewriteNoAdd the column nullable, backfill in batches, then `SET DEFAULT` for new rows.
`CREATE INDEX``SHARE`: blocks writes for the whole buildNo`CREATE INDEX CONCURRENTLY` (`SHARE UPDATE EXCLUSIVE`); check for `INVALID` leftovers if it fails.
`DROP INDEX``ACCESS EXCLUSIVE`Brief, but can queue`DROP INDEX CONCURRENTLY` (cannot run inside a transaction block).
`ADD FOREIGN KEY``SHARE ROW EXCLUSIVE` on both tables, with a validation scanNo`ADD CONSTRAINT ... NOT VALID` (still a brief `SHARE ROW EXCLUSIVE` on both tables), then `VALIDATE CONSTRAINT` in a separate statement.
`ADD CHECK``ACCESS EXCLUSIVE` while the table is scannedNo`ADD CONSTRAINT ... CHECK (...) NOT VALID`, then `VALIDATE CONSTRAINT` (`SHARE UPDATE EXCLUSIVE`).
`SET NOT NULL``ACCESS EXCLUSIVE` with a full scanNoAdd and validate a `CHECK (col IS NOT NULL)` first; PG 12+ then skips the scan.
`ADD PRIMARY KEY` / `ADD UNIQUE``ACCESS EXCLUSIVE` while the index is builtNo`CREATE UNIQUE INDEX CONCURRENTLY`, then `ADD CONSTRAINT ... USING INDEX`.
`ALTER COLUMN TYPE``ACCESS EXCLUSIVE`, usually with a rewrite and index rebuildsNo, except binary-compatible casesExpand/contract: new column, dual-write, batched backfill, switch reads, drop old column. Widening `varchar(n)` or `varchar` to `text` needs no rewrite.
`RENAME` column or table`ACCESS EXCLUSIVE`, held brieflyLocks yes, application noOld code breaks at once. Expand/contract, or a view with the old name while both versions run.
`DROP COLUMN``ACCESS EXCLUSIVE`, held briefly; metadata onlyLocks yes, application noDeploy code that no longer reads or writes the column first; drop it in a later release.
`ATTACH PARTITION``SHARE UPDATE EXCLUSIVE` on the parent (PG 12+), `ACCESS EXCLUSIVE` on the new partition and on the default partition, if anyYes, with a matching `CHECK`Create a validated `CHECK` equal to the partition bound before attaching so the scan is skipped.

The `lock_timeout` setting turns a long queue wait into a fast, retryable error. `statement_timeout` does not help here, because it also kills a legitimate long `VALIDATE`.

Key settings and starting points

Defaults are conservative and fit a small machine. The starting points below are rules of thumb (heuristics), not measurements: change one at a time and compare against your own workload.

Key settings and starting points
ParameterDefault / starting pointNote
`shared_buffers`Default 128MB. Heuristic: about 25% of RAM on a dedicated server.Restart required. The OS page cache holds the rest, so very large values rarely help.
`work_mem`Default 4MB. Raise per role or per query, not globally.Each sort or hash node of each query can use this much, so worst case is roughly `work_mem` x nodes x connections. `EXPLAIN ANALYZE` shows `Disk:` when a sort spills.
`maintenance_work_mem`Default 64MB. Heuristic: a few hundred MB to 1-2GB.Used by `CREATE INDEX`, `VACUUM` and `ALTER TABLE ADD FOREIGN KEY`. Autovacuum workers use `autovacuum_work_mem` if set, else this value.
`effective_cache_size`Default 4GB. Heuristic: 50-75% of RAM.Planner hint only, nothing is allocated. It makes index scans look cheaper when large.
`max_wal_size`Default 1GB. Raise until most checkpoints are timed, not requested.Soft limit that triggers checkpoints. Compare `checkpoints_timed` with `checkpoints_req` in `pg_stat_bgwriter` (`num_timed` / `num_requested` in `pg_stat_checkpointer`, PG 17+). Larger means longer crash recovery.
`random_page_cost`Default 4.0. Heuristic for SSD or NVMe: 1.1 to 2.Tells the planner how expensive a random read is relative to a sequential one. Too high pushes plans away from indexes.
`max_connections`Default 100. Keep it modest and pool in front.Restart required. Each connection is a process with its own memory; see connection-pooling. Standbys must have a value at least as high as the primary.
`idle_in_transaction_session_timeout`Default 0 (off). Heuristic: 30s to a few minutes for OLTP apps.Terminates sessions that sit in a transaction doing nothing, which otherwise hold locks and block vacuum.
`statement_timeout`Default 0 (off). Set per role or per request, not on migrations or batch jobs.Cancels any single statement that runs too long. Prefer it on web-facing roles.
`autovacuum_max_workers`Default 3. Raise only if many tables need vacuuming at the same time.Restart required. Workers share the cost budget (`autovacuum_vacuum_cost_limit`), so more workers alone do not make vacuum faster.

Check where a value comes from with `SELECT name, setting, unit, source, pending_restart FROM pg_settings WHERE name = '...'`.

Monitoring views and functions for troubleshooting

Column names below are checked against PostgreSQL 16. Cumulative counters (`n_dead_tup`, `temp_bytes`, `calls`) only mean something as a rate over time or after a reset.

Monitoring views and functions for troubleshooting
View or functionAnswersKey columnsUsed in
`pg_stat_activity`What is every session doing right now, and is it waiting?`pid`, `state`, `wait_event_type`, `wait_event`, `query_start`, `xact_start`, `state_change`, `backend_xmin`, `query`slow-query, lock-wait, table-bloat
`pg_stat_statements` (extension)Which normalized statements cost the most over time?`queryid`, `calls`, `total_exec_time`, `mean_exec_time`, `rows`, `shared_blks_read`, `temp_blks_written`slow-query
`pg_stat_user_tables`How many dead rows, and when was the table last vacuumed or analyzed?`n_live_tup`, `n_dead_tup`, `n_mod_since_analyze`, `last_autovacuum`, `last_autoanalyze`, `seq_scan`, `idx_scan`table-bloat, slow-query
`pg_stat_user_indexes`Which indexes are used, and which are dead weight?`indexrelname`, `idx_scan`, `last_idx_scan` (PG 16+), `idx_tup_read`, `idx_tup_fetch`slow-query, table-bloat
`pg_locks` and `pg_blocking_pids(pid)`Who holds or waits for which lock, and who blocks whom?`locktype`, `mode`, `granted`, `pid`, `relation`; the function returns an array of blocking pidslock-wait
`pg_stat_replication`How far behind is each standby, per stage?`state`, `sync_state`, `sent_lsn`, `write_lsn`, `flush_lsn`, `replay_lsn`, `write_lag`, `flush_lag`, `replay_lag`replication-lag
`pg_replication_slots`Which slots exist, are they consumed, and how much WAL do they pin?`slot_name`, `active`, `restart_lsn`, `wal_status`, `safe_wal_size`, `xmin`, `catalog_xmin`disk-full-wal, table-bloat, replication-lag
`pg_stat_bgwriter` (PG 17: `pg_stat_checkpointer`)Are checkpoints driven by time or by WAL volume?PG 16: `checkpoints_timed`, `checkpoints_req`, `buffers_checkpoint`. PG 17 moves these to `pg_stat_checkpointer` as `num_timed`, `num_requested`, `buffers_written`disk-full-wal
`pg_stat_database`Temp file volume, rollbacks, deadlocks and cache hits per database.`temp_files`, `temp_bytes`, `deadlocks`, `xact_commit`, `xact_rollback`, `blks_read`, `blks_hit`slow-query, disk-full-wal
`pg_stat_progress_vacuum`How far along is a running vacuum, and in which phase?`pid`, `relid`, `phase`, `heap_blks_total`, `heap_blks_scanned`, `heap_blks_vacuumed`, `index_vacuum_count`; dead-tuple columns: PG 16 `max_dead_tuples`, `num_dead_tuples`; PG 17 `max_dead_tuple_bytes`, `dead_tuple_bytes`, `num_dead_item_ids`table-bloat

On a standby also see `pg_stat_wal_receiver` and `pg_stat_database_conflicts`. `pg_stat_statements` column `blk_read_time` was renamed `shared_blk_read_time` in PG 17 (and `blk_write_time` to `shared_blk_write_time`).

Storage & MVCC

Every UPDATE writes a new tuple version and every query sees a snapshot, which explains dead tuples, HOT updates and why long transactions hurt.

The problem

Readers never block writers in PostgreSQL, yet a table that is updated all day grows, a `SELECT` after a bulk load can write pages, and one forgotten transaction can make every table bloat. All of that follows from one design: rows are immutable versions, and each query decides which version it may see.

How it works

  • Tuple header: every row version in the heap carries `xmin` (the transaction ID that created it), `xmax` (the ID that deleted, updated or row-locked it, 0 if none) and `ctid` (its own page and offset, or the location of the newer version after an UPDATE). `SELECT ctid, xmin, xmax, * FROM t` shows them.
  • Snapshot: a statement (Read Committed) or a whole transaction (Repeatable Read, Serializable) takes a snapshot of which transaction IDs were still running. A version is visible if its `xmin` committed before the snapshot and its `xmax` is unset, aborted, or not yet visible to the snapshot. Readers need no row locks.
  • UPDATE is insert plus mark: it writes a complete new tuple and stamps `xmax` on the old one; DELETE only stamps `xmax`. Old versions stay in the table until no snapshot can see them and vacuum removes them, so update-heavy tables need vacuum to keep their size stable.
  • HOT updates: if no indexed column changes and the new version fits on the same page, PostgreSQL skips new index entries and chains the versions inside the page, which cheap page pruning can reclaim. A lower `fillfactor` (for example 80) leaves room for this; watch `n_tup_hot_upd` against `n_tup_upd` in `pg_stat_user_tables`.
  • Hint bits: commit or abort status lives in `pg_xact`; the first reader to check a tuple caches the answer in a hint bit on the page. So the first `SELECT` after a bulk load can dirty many pages and generate I/O; running `VACUUM` after a large load does that work up front.
  • Wraparound and freezing: transaction IDs are 32-bit and compared in a circle, so a very old row would eventually look like it is in the future. Vacuum freezes old tuples (marks them visible to everyone); autovacuum forces an aggressive pass at `autovacuum_freeze_max_age` (200 million by default) and PostgreSQL finally refuses new IDs to protect data, so watch `age(datfrozenxid)`.
  • Long transactions hurt: an open transaction with a snapshot or an assigned ID pins the oldest-visible horizon (`backend_xmin` / `backend_xid` in `pg_stat_activity`). Vacuum cannot remove any dead version newer than that, in every table of that database, including tables the transaction never touched (other databases are unaffected, but replication slots and standby feedback hold back the whole cluster). Abandoned replication slots and prepared transactions pin it the same way.
  • Row locks are tuple writes: `SELECT ... FOR UPDATE` records the locker in `xmax`, which dirties the page and can emit WAL, so locking many rows just to read them is not free.

Examples

Watch an UPDATE create a new tuple version and a row lock appear in xmax, on a scratch table and without extensions.

sql
CREATE TABLE mvcc_demo (id int PRIMARY KEY, note text) WITH (fillfactor = 70);
INSERT INTO mvcc_demo VALUES (1, 'v1');
SELECT ctid, xmin, xmax, * FROM mvcc_demo;
UPDATE mvcc_demo SET note = 'v2' WHERE id = 1;
-- the visible row moved to a new ctid and has a new xmin
SELECT ctid, xmin, xmax, * FROM mvcc_demo;
BEGIN;
SELECT pg_current_xact_id() AS my_xid;
SELECT id FROM mvcc_demo WHERE id = 1 FOR UPDATE;
-- the row lock is now recorded in xmax
SELECT ctid, xmin, xmax, * FROM mvcc_demo;
ROLLBACK;

Measure how many updates are HOT, then lower fillfactor for update-heavy tables.

sql
-- HOT ratio: a low value on a hot table suggests indexed-column updates or a full page
SELECT relname, n_tup_upd, n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct,
       n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC
LIMIT 10;

-- reserve room on each page so new versions can stay on the same page
-- (only affects newly written pages; rewrite with VACUUM FULL or pg_repack to apply to old ones)
ALTER TABLE orders SET (fillfactor = 85);

Find the sessions that pin the vacuum horizon and check how close tables are to a forced freeze.

sql
-- who is holding the horizon back (oldest first)
SELECT pid, state, xact_start, now() - xact_start AS xact_age,
       backend_xid, backend_xmin, left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_xid IS NOT NULL OR backend_xmin IS NOT NULL
ORDER BY age(coalesce(backend_xid, backend_xmin)) DESC NULLS LAST
LIMIT 10;

-- wraparound distance per database and the oldest tables
SELECT datname, age(datfrozenxid) AS xid_age FROM pg_database ORDER BY 2 DESC;
SELECT c.oid::regclass AS rel, age(c.relfrozenxid) AS xid_age
FROM pg_class c
WHERE c.relkind IN ('r', 'm', 't')
ORDER BY 2 DESC
LIMIT 10;

In practice

  • pageinspect — A contrib extension (superuser) whose `heap_page_items()` shows raw tuple headers, hint bits and HOT chains; use it on a scratch table when the `ctid, xmin, xmax` view is not enough.
  • InnoDB and Oracle — Keep old row versions in a separate undo log and rewrite the row in place; PostgreSQL keeps them in the heap itself, which makes rollback instant but puts the cleanup burden on vacuum.
  • In practice — A frequent cause of unexplained bloat is a single `idle in transaction` session or a stale replication slot; check the horizon holders before tuning autovacuum.

Trade-offs

Pros

  • Readers and writers do not block each other, so mixed read-write load scales well
  • Rollback is free: an aborted transaction's versions are simply invisible
  • Consistent snapshots come cheaply, which also gives snapshot-based backups and long reports

Cons

  • Dead versions cause bloat and require vacuum and monitoring
  • Updates are write-heavy: full new tuple plus index entries unless HOT applies
  • 32-bit transaction IDs add freeze and wraparound as an operational concern

How vacuum reclaims dead tuples, when autovacuum fires, how to tune it per table, and what stops it from making progress.

The problem

Because UPDATE and DELETE leave dead tuples behind, a table that is never vacuumed grows without bound and its queries slow down. Autovacuum usually handles this, until a large hot table outgrows the defaults, or something holds the horizon back and vacuum runs but cannot clean anything.

How it works

  • What vacuum does: removes dead tuples and their index entries, marks the space reusable inside the table (the file normally does not shrink), updates the free space map and visibility map (needed for index-only scans), and freezes old tuples. `VACUUM (ANALYZE)` also refreshes planner statistics.
  • `VACUUM` vs `VACUUM FULL`: plain `VACUUM` runs alongside reads and writes (it only returns empty pages at the end of the file to the OS). `VACUUM FULL` rewrites the whole table and its indexes under `ACCESS EXCLUSIVE`, blocks all access, and needs free disk for the copy; treat it as maintenance-window surgery, or use `pg_repack`.
  • Autovacuum trigger: a table is vacuumed when dead tuples exceed `autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples` (50 + 0.2 * rows by default). Insert-only tables get their own trigger, `autovacuum_vacuum_insert_threshold + autovacuum_vacuum_insert_scale_factor * reltuples` (1000 + 0.2 * rows, PG 13+). Analyze uses 50 + 0.1 * rows.
  • Why the defaults hurt big tables: 20% of 500 million rows is 100 million dead tuples before a vacuum even starts. Set a small scale factor per table, for example `ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 10000)`; small tables rarely need changes.
  • Cost-based delay: vacuum charges I/O against a budget (`vacuum_cost_limit`, 200 by default; `autovacuum_vacuum_cost_limit = -1` means use it) and sleeps `autovacuum_vacuum_cost_delay` (2 ms since PG 12) when it is spent. The budget is shared between running workers, so adding workers alone does not add throughput; raise the limit (for example 1000 to 2000) when autovacuum cannot keep up and I/O has headroom.
  • Freezing: a table whose `age(relfrozenxid)` passes `autovacuum_freeze_max_age` (200 million) gets an anti-wraparound autovacuum even if autovacuum is off, and it will not yield to conflicting lock requests as readily. From PG 14 a failsafe mode skips cost delay and index cleanup when the age passes `vacuum_failsafe_age` (1.6 billion).
  • Monitor with `pg_stat_user_tables`: `n_dead_tup`, `n_live_tup`, `last_autovacuum`, `autovacuum_count`, `n_mod_since_analyze`. A big dead-tuple count with a recent `last_autovacuum` means vacuum ran but could not remove the rows. `VACUUM (VERBOSE)` and `log_autovacuum_min_duration` report what was removed and what was kept.
  • What blocks cleanup: any old snapshot or open transaction in the same database (`backend_xmin`), a replication slot with an old `xmin` or `catalog_xmin` (including an inactive one), a standby with `hot_standby_feedback = on` running a long query, and prepared transactions in `pg_prepared_xacts`. Fix the holder; tuning vacuum cannot help.

Examples

Rank tables by dead tuples and compare with the default autovacuum trigger point.

sql
-- tables most in need: dead tuples, trigger point, last run
SELECT s.relname, s.n_live_tup, s.n_dead_tup,
       round(current_setting('autovacuum_vacuum_threshold')::numeric
             + current_setting('autovacuum_vacuum_scale_factor')::numeric * c.reltuples) AS trigger_at,
       s.last_autovacuum, s.autovacuum_count, s.n_mod_since_analyze
FROM pg_stat_user_tables s
JOIN pg_class c ON c.oid = s.relid
ORDER BY s.n_dead_tup DESC
LIMIT 10;

Set, inspect and reset per-table autovacuum storage parameters, then vacuum by hand with VERBOSE.

sql
-- per-table tuning for a big, hot table
ALTER TABLE events SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_threshold = 5000,
    autovacuum_analyze_scale_factor = 0.02
);
-- check what is set
SELECT relname, reloptions FROM pg_class WHERE relname = 'events';
-- go back to the global defaults
ALTER TABLE events RESET (
    autovacuum_vacuum_scale_factor,
    autovacuum_vacuum_threshold,
    autovacuum_analyze_scale_factor
);
-- run it by hand and read what was kept
VACUUM (VERBOSE, ANALYZE) orders;

List open snapshots, replication slots and prepared transactions that pin the cleanup horizon.

sql
-- everything that can hold back dead-tuple removal
SELECT 'transaction' AS holder, pid::text AS id, backend_xmin::text AS xmin_
FROM pg_stat_activity WHERE backend_xmin IS NOT NULL
UNION ALL
SELECT 'replication slot', slot_name::text, coalesce(xmin, catalog_xmin)::text
FROM pg_replication_slots WHERE xmin IS NOT NULL OR catalog_xmin IS NOT NULL
UNION ALL
SELECT 'prepared xact', gid, transaction::text FROM pg_prepared_xacts;

In practice

  • pg_stat_user_tables — The first stop for dead tuples and last vacuum times; pair it with `log_autovacuum_min_duration = 0` (or a few seconds) to log each autovacuum run.
  • pg_repack — Rebuilds a bloated table online with only brief exclusive locks, the usual choice when `VACUUM FULL` downtime is unacceptable; it needs a primary key or unique index and spare disk.
  • In practice — Raising `autovacuum_vacuum_cost_limit` and lowering scale factors on the few largest tables fixes most autovacuum complaints; turning autovacuum off is almost never the answer.

Trade-offs

Pros

  • Plain VACUUM is online and cheap enough to run continuously
  • Per-table settings let hot tables be vacuumed often without touching the rest
  • Freezing and the visibility map keep scans and index-only scans fast

Cons

  • Defaults are tuned for modest tables and lag behind on very large ones
  • More aggressive vacuuming means more I/O and competes with the workload
  • VACUUM FULL is a blocking rewrite and plain VACUUM rarely shrinks files

The write-ahead log path from commit to disk, the durability and speed trade-offs, and the settings that shape checkpoints.

The problem

Every change is written to the write-ahead log before the data pages, which is what makes commits durable and crash recovery possible. The same mechanism decides your commit latency, your disk I/O pattern, how fast a crashed server restarts, and how much WAL you have to store and ship.

How it works

  • Write path: a change modifies the page in shared buffers and appends a WAL record. At `COMMIT` the commit record is flushed (fsync) to the WAL file, and only then is the client told the commit succeeded. Dirty data pages are written later by the background writer and checkpointer.
  • `synchronous_commit`: `on` (default) waits for the local WAL flush. `off` returns before the flush: a crash can lose the last few commits (bounded at roughly three times `wal_writer_delay`) but never corrupts data, a good trade for logs and counters. `remote_write`, `remote_apply` and `local` matter with synchronous replication. It can be set per transaction. `fsync = off` is different and can corrupt the cluster.
  • Checkpoints write all dirty buffers to the data files and record a redo point, so crash recovery replays only the WAL after the last checkpoint and older WAL can be recycled. They start every `checkpoint_timeout` (5 min default) or when WAL since the last one approaches `max_wal_size` (1 GB default), or on `CHECKPOINT`, shutdown and base backups.
  • Tuning: raise `max_wal_size` (often to tens of GB on write-heavy systems) so checkpoints are time-driven rather than size-driven, and keep `checkpoint_completion_target` at 0.9 (the default since PG 14) so writes are spread out. Longer intervals mean smoother I/O and less WAL, but a longer crash recovery. `log_checkpoints` is on by default since PG 15.
  • `full_page_writes` (on): the first change to a page after a checkpoint logs the whole page, which protects against torn writes. This inflates WAL right after each checkpoint; `wal_compression` shrinks those images at some CPU cost (PG 15+ adds `lz4` and `zstd` besides `pglz`). Do not turn `full_page_writes` off on ordinary hardware.
  • `wal_level`: `replica` (default) supports archiving and physical replication; `logical` adds the information logical decoding and publications need and increases WAL volume somewhat; `minimal` is not usable with replication or WAL archiving. Changing it requires a restart.
  • Positions: `pg_current_wal_lsn()` returns the current write position (not on a standby; use `pg_last_wal_replay_lsn()` there), `pg_wal_lsn_diff(a, b)` gives bytes between two LSNs, and `pg_walfile_name(lsn)` names the segment file. Measure WAL generated by a workload by taking the LSN before and after.
  • Statistics: `pg_stat_wal` has `wal_records`, `wal_fpi`, `wal_bytes` and `wal_buffers_full`. Checkpoint counters (`checkpoints_timed`, `checkpoints_req`) are in `pg_stat_bgwriter` up to PG 16; PG 17 moves them to `pg_stat_checkpointer` (`num_timed`, `num_requested`). Many requested checkpoints mean `max_wal_size` is too small.

Examples

Measure WAL bytes generated by a statement via LSN difference, and read the cluster-wide WAL counters.

sql
-- how much WAL does a workload write?
CREATE TEMP TABLE wal_mark AS SELECT pg_current_wal_lsn() AS lsn;
UPDATE orders SET status = 'paid' WHERE id <= 5000;
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), lsn)) AS wal_written,
       pg_walfile_name(pg_current_wal_lsn()) AS current_segment
FROM wal_mark;

-- cluster-wide WAL counters (PG 14+)
SELECT wal_records, wal_fpi, pg_size_pretty(wal_bytes) AS wal_bytes, wal_buffers_full
FROM pg_stat_wal;

Check how often checkpoints are timed vs requested (the view differs in PG 17), and relax synchronous_commit per transaction.

sql
-- PG 14-16: checkpoint counters live in pg_stat_bgwriter
SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time,
       buffers_checkpoint, buffers_clean, buffers_backend
FROM pg_stat_bgwriter;
-- PG 17+: use pg_stat_checkpointer instead
-- SELECT num_timed, num_requested, write_time, sync_time, buffers_written
-- FROM pg_stat_checkpointer;

-- relax durability for one low-value transaction only
BEGIN;
SET LOCAL synchronous_commit = off;
UPDATE events SET kind = 'view' WHERE id = 1;
COMMIT;

Fewer, smoother checkpoints for a write-heavy server; longer crash recovery is the cost.

properties
# postgresql.conf: a write-heavy starting point (size to your disks and recovery goals)
max_wal_size = 16GB
min_wal_size = 2GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
wal_compression = lz4
log_checkpoints = on

In practice

  • pg_waldump — Ships with PostgreSQL and decodes WAL segments into records, showing which resource managers (heap, btree) produce the volume.
  • pgBackRest — Archives WAL continuously and replays it for point-in-time recovery, so archive health and WAL retention directly affect backups.
  • pg_stat_statements — Records `wal_records`, `wal_fpi` and `wal_bytes` per statement (PG 13+), which finds the queries responsible for WAL volume.

Trade-offs

Pros

  • Sequential WAL writes make durable commits cheap compared with random page writes
  • Checkpoints bound crash recovery time
  • The same log feeds replication, archiving and point-in-time recovery

Cons

  • Commit latency is bounded by fsync speed unless synchronous_commit is relaxed
  • Frequent checkpoints cause I/O spikes and full-page-write WAL inflation
  • Long checkpoint intervals and big max_wal_size mean slower crash recovery and more disk

How large values are stored out of line, why SELECT * on wide rows is costly, and how table and index bloat arises and is measured and removed.

The problem

A table with a `jsonb` or `text` column that holds large documents behaves very differently from the same table with narrow rows, and a table that was once large and busy can stay big long after the data shrank. TOAST explains the first; dead space and bloat explain the second.

How it works

  • TOAST: a heap row must fit an 8 kB page. When a row exceeds about 2 kB (`TOAST_TUPLE_THRESHOLD`, tunable down or up per table with `toast_tuple_target`), PostgreSQL compresses the largest variable-length columns and then moves them out of line into the table's `pg_toast.pg_toast_<oid>` table in chunks of about 2 kB. A single value can be up to 1 GB.
  • Compression: the default is `pglz`; since PG 14 `lz4` is available when the build has it, via `default_toast_compression` or `ALTER TABLE ... ALTER COLUMN ... SET COMPRESSION lz4`. `lz4` is usually much faster for similar ratios. `pg_column_size()` shows stored size and `pg_column_compression()` shows the method (PG 14+).
  • Per-column `STORAGE`: `EXTENDED` (compress, then out of line; default for most types), `EXTERNAL` (out of line, no compression; faster substring access on text and bytea), `MAIN` (compress, keep inline if possible), `PLAIN`. Already-compressed data such as images or gzip blobs gains nothing from compression.
  • Cost of `SELECT *`: every wide column that you fetch must be read from the TOAST table and decompressed, even if the application ignores it. Select only the columns needed, keep small hot columns and big blobs in separate tables when access patterns differ, and note that an UPDATE of other columns does not rewrite untouched toasted values.
  • Bloat arises from dead tuples that vacuum has not yet removed or has removed but left as free space inside the file: long transactions or slots holding the horizon, autovacuum that is too slow for the churn, mass `DELETE`/`UPDATE` of a large share of rows, non-HOT updates that churn indexes. Plain vacuum reuses the space but rarely shrinks the file.
  • Index bloat is separate and often worse: B-tree pages split during updates and are only reclaimed when completely empty, so sparse pages can persist. `REINDEX INDEX CONCURRENTLY` (PG 12+) rebuilds an index online.
  • Estimate bloat cheaply with `n_dead_tup` and table size over time in `pg_stat_user_tables`; measure it exactly with the `pgstattuple` extension (`pgstattuple('t')` scans the table; `pgstattuple_approx()` is faster, `pgstatindex()` covers B-trees). Compare `pg_total_relation_size` to what the live rows should take.
  • Reclaiming space: `VACUUM FULL` or `CLUSTER` rewrite under `ACCESS EXCLUSIVE`; `pg_repack` rebuilds online with brief locks and needs about the table's size in free disk. Prevent recurrence rather than repeat the rewrite: fix the horizon holder, tune autovacuum, and use `fillfactor` below 100 on update-heavy tables so updates can be HOT.

Examples

See a wide value land in the TOAST table, and compare stored size with raw length.

sql
CREATE TABLE doc_demo (id int PRIMARY KEY, body text);
INSERT INTO doc_demo
SELECT g, (SELECT string_agg(md5(random()::text || g || i), '') FROM generate_series(1, 300) i)
FROM generate_series(1, 200) g;
VACUUM ANALYZE doc_demo;

SELECT pg_size_pretty(pg_relation_size('doc_demo')) AS heap,
       pg_size_pretty(pg_relation_size(reltoastrelid)) AS toast,
       pg_size_pretty(pg_total_relation_size('doc_demo')) AS total
FROM pg_class WHERE oid = 'doc_demo'::regclass;

SELECT id, pg_column_size(body) AS stored_bytes, length(body) AS raw_bytes,
       pg_column_compression(body) AS method
FROM doc_demo LIMIT 3;

Measure table and index bloat with pgstattuple (contrib extension, needs privileges to create).

sql
CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- exact (scans the table): dead_tuple_percent and free_percent show bloat
SELECT table_len, tuple_percent, dead_tuple_percent, free_percent
FROM pgstattuple('orders');

-- cheaper estimate that skips all-visible pages
SELECT table_len, approx_tuple_percent, dead_tuple_percent, approx_free_percent
FROM pgstattuple_approx('orders');

-- B-tree density: a low avg_leaf_density suggests a rebuild
SELECT avg_leaf_density, leaf_fragmentation FROM pgstatindex('orders_pkey');

Reclaim space online with pg_repack or REINDEX CONCURRENTLY; VACUUM FULL blocks all access.

bash
# online rebuild with brief locks; needs a PK or unique index and spare disk
pg_repack --dbname="<dbname>" --table="public.orders" --jobs=2

# index only
pg_repack --dbname="<dbname>" --index="public.idx_orders_user_id"

# built in alternatives
psql -d "<dbname>" -c 'REINDEX INDEX CONCURRENTLY idx_orders_user_id'
psql -d "<dbname>" -c 'VACUUM FULL orders'   # ACCESS EXCLUSIVE for the whole rewrite

In practice

  • pgstattuple — Contrib extension that reports exact dead-tuple and free-space percentages; `pgstattuple_approx()` trades accuracy for speed on large tables.
  • pg_repack — Third-party extension plus client that rebuilds tables and indexes online by copying and swapping, the standard alternative to `VACUUM FULL` on live systems.
  • In practice — Large JSON payloads are a common source of surprise read cost: the heap looks small while the TOAST relation holds most of the bytes, so check `pg_total_relation_size`, not just the table.

Trade-offs

Pros

  • TOAST lets rows hold large values transparently, and untouched toasted values are not rewritten on UPDATE
  • Wide values stay out of the heap, so scans that skip them touch fewer pages
  • lz4 gives fast compression with no application change

Cons

  • SELECT * and whole-value access pay for detoasting and decompression
  • Bloat wastes disk, cache and scan time, and plain vacuum seldom returns space to the OS
  • Fixes that shrink files (VACUUM FULL, pg_repack) need locks or spare disk

What Read Committed, Repeatable Read and Serializable actually guarantee in PostgreSQL, which anomalies remain, and how to retry.

The problem

Two correct transactions can still produce a wrong result when they interleave: a double-booked seat, a lost counter increment, two doctors both going off call. Choosing an isolation level is deciding which of these interleavings the database must prevent and which your code handles.

How it works

  • Read Committed (default): each statement sees a fresh snapshot of data committed before it started, so two reads in one transaction can differ (non-repeatable read, phantoms). PostgreSQL has no dirty reads at any level: `READ UNCOMMITTED` is accepted but behaves exactly like Read Committed.
  • Repeatable Read is snapshot isolation in PostgreSQL: the snapshot is taken at the first statement and kept to the end, so rereads and range queries return the same rows (no phantoms, stricter than the SQL standard requires). A write to a row another transaction changed since the snapshot fails with `could not serialize access due to concurrent update` (SQLSTATE `40001`); write skew is still possible.
  • Serializable uses Serializable Snapshot Isolation (SSI): snapshot reads plus tracking of read-write dependencies with non-blocking predicate locks. It aborts a transaction that would complete a dangerous cycle with SQLSTATE `40001`, sometimes a false positive, so every transaction must be retryable. Make queries use indexes to keep predicate locks fine-grained and abort rates low.
  • Write skew: two transactions read an overlapping set, each decides based on it, and each updates a different row, so no write-write conflict is detected (the on-call example: both check that at least two doctors are on duty, then each takes themselves off). Repeatable Read allows it; Serializable prevents it.
  • Lost update: a read-modify-write done in the application (`SELECT` then `UPDATE ... SET n = 5`) loses a concurrent change at Read Committed. Fix with an atomic `UPDATE t SET n = n + 1`, with `SELECT ... FOR UPDATE`, with optimistic version checks, or by running at Repeatable Read and retrying on `40001`.
  • `SELECT ... FOR UPDATE` at Read Committed: it blocks on a row that another transaction has locked, and after that transaction commits, re-evaluates the row against its newest committed version before returning it. At Repeatable Read the same situation raises a serialization failure instead. Locking the rows you read is the classic way to defuse write skew without Serializable.
  • Retry discipline: on `40001` (and `40P01`, deadlock detected) roll back and rerun the whole transaction, including its reads and decisions, from the start. Use a bounded number of attempts with jittered backoff, keep transactions short, and keep external side effects (emails, API calls) outside the retried block or make them idempotent.
  • Choosing: stay on Read Committed with atomic statements and explicit locks for most OLTP code; use Repeatable Read for consistent multi-query reports; use Serializable when an invariant spans several rows and you would rather retry than reason about every interleaving. Set it per transaction with `BEGIN ISOLATION LEVEL ...`, or via `default_transaction_isolation`.

Examples

Pick the isolation level per transaction; READ ONLY DEFERRABLE waits for a safe snapshot and then cannot fail under SSI.

sql
SHOW default_transaction_isolation;

-- consistent multi-query report: one snapshot for both queries
BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;
SELECT count(*) AS orders_total FROM orders;
SELECT status, count(*) FROM orders GROUP BY status ORDER BY 1;
COMMIT;

-- long analytic read that must never abort or be aborted by SSI
BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;
SELECT current_setting('transaction_isolation') AS level, count(*) FROM users;
COMMIT;

Write skew reproduced with two sessions: Repeatable Read lets both commit, Serializable aborts one.

sql
-- skip-exec: needs two concurrent sessions (A and B), run them side by side
CREATE TABLE on_call (doctor text PRIMARY KEY, on_duty boolean NOT NULL);
INSERT INTO on_call VALUES ('alice', true), ('bob', true);

-- A: BEGIN ISOLATION LEVEL REPEATABLE READ;
-- A: SELECT count(*) FROM on_call WHERE on_duty;           -- 2
-- B: BEGIN ISOLATION LEVEL REPEATABLE READ;
-- B: SELECT count(*) FROM on_call WHERE on_duty;           -- 2
-- A: UPDATE on_call SET on_duty = false WHERE doctor = 'alice';
-- B: UPDATE on_call SET on_duty = false WHERE doctor = 'bob';
-- A: COMMIT;   B: COMMIT;   -- both succeed: nobody is on call (write skew)
-- Without Serializable: take SELECT ... FROM on_call WHERE on_duty FOR UPDATE first.
-- With ISOLATION LEVEL SERIALIZABLE one COMMIT fails with SQLSTATE 40001: retry it.
COMMIT;

In practice

  • PostgreSQL SSI — Introduced in PostgreSQL 9.1 and described in the paper 'Serializable Snapshot Isolation in PostgreSQL' (Ports and Grittner, VLDB 2012); it avoids the read locks of classic two-phase locking.
  • Application drivers and ORMs — Most do not retry serialization failures automatically; add a retry wrapper that catches SQLSTATE `40001` and `40P01` around the whole transaction function.
  • In practice — Teams that move to Serializable commonly keep Read Committed for hot, simple paths and apply Serializable only to the few multi-row invariants, to keep abort rates manageable.

Trade-offs

Pros

  • Read Committed is cheap, rarely fails and fits most statement-level logic
  • Repeatable Read gives a stable snapshot for reports without blocking writers
  • Serializable removes whole classes of concurrency bugs without explicit locks

Cons

  • Read Committed allows non-repeatable reads, phantoms, lost updates and write skew
  • Repeatable Read still allows write skew and aborts on concurrent updates
  • Serializable adds overhead and false-positive aborts, so retries are mandatory

Indexes

B-tree, hash, GIN, GiST, SP-GiST and BRIN: which operators each one accelerates, what it costs on writes, and when to pick it.

The problem

Most queries are served by the default B-tree, until someone writes `LIKE '%foo%'`, filters on a jsonb key, searches an array or scans a billion-row log table by time. Then a B-tree is either unusable or enormous, and the question becomes which access method fits the operator in the `WHERE` clause.

How it works

  • An index is chosen by operator, not by column. Each index column has an operator class that lists the operators the index can accelerate. If the query's operator is not in the class, the index is ignored no matter how well the column matches.
  • B-tree is the default and the only type that gives ordering and uniqueness. It serves `=`, ranges, `IN`, `IS NULL` and `ORDER BY ... LIMIT`. For `LIKE 'abc%'` it needs the C collation or a `text_pattern_ops` operator class; it can never serve `LIKE '%abc%'`.
  • Hash serves only `=`. It has been crash-safe (WAL-logged) since PG 10 and can be smaller than a B-tree on long keys, but it cannot enforce uniqueness, order, or cover columns, so it is rarely the right default.
  • GIN is an inverted index: one entry per element or token, pointing to many rows. Use it for arrays (`@>`, `&&`), jsonb (`@>`, `?`, `@?`), full text (`to_tsvector(...) @@ ...`) and, with `pg_trgm`, for `LIKE '%x%'`, `ILIKE` and fuzzy `%` matching. Reads are fast, writes are costly (see `jsonb-indexing` for the pending list).
  • GiST is a balanced tree of bounding predicates: overlap, containment and nearest-neighbour (`ORDER BY col <-> value`) on geometry, ranges and trigrams. It is also the type behind exclusion constraints (for example no two overlapping bookings per room). SP-GiST covers space-partitioned structures such as radix trees for text prefixes, k-d trees for points and `inet` ranges.
  • BRIN stores a summary (min and max by default) per range of heap blocks, 128 blocks by default (`pages_per_range`). It is tiny and cheap to maintain, and wins on huge append-only tables where the column follows physical order, such as `created_at`. Check `pg_stats.correlation`; near 0 means BRIN will not help. PG 14 added the `minmax-multi` and `bloom` operator classes for less well-ordered data.
  • Extensions add operator classes: `pg_trgm` (`gin_trgm_ops`, `gist_trgm_ops`), `btree_gin` and `btree_gist` (scalar equality inside GIN or GiST, needed for multicolumn GIN or exclusion constraints on plain columns). They are shipped in contrib, so confirm `CREATE EXTENSION` works on your managed or packaged build before relying on them.
  • Every index is paid for on every `INSERT`, on updates that touch an indexed column (which also rules out HOT updates), in WAL volume and in vacuum work. Add one for a query you can name, and drop indexes whose `pg_stat_user_indexes.idx_scan` stays at zero over a full business cycle.

Examples

Trigram GIN index so `LIKE '%x%'` stops scanning the whole table (needs `pg_trgm`; the plan depends on data and version).

sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_email_trgm ON users USING gin (email gin_trgm_ops);
ANALYZE users;
EXPLAIN (COSTS OFF)
SELECT id FROM users WHERE email LIKE '%abc%';
-- fuzzy match: similarity above pg_trgm.similarity_threshold (default 0.3)
SELECT id FROM users WHERE email % 'alice@example.com' LIMIT 5;

BRIN for a large, naturally ordered time column, plus a check that the column is physically correlated.

sql
CREATE TABLE metrics_log (
    id bigserial PRIMARY KEY,
    logged_at timestamptz NOT NULL DEFAULT now(),
    value double precision
);
INSERT INTO metrics_log (logged_at, value)
SELECT now() - make_interval(secs => g), random()
FROM generate_series(1, 100000) AS g
ORDER BY 1;
CREATE INDEX idx_metrics_log_brin ON metrics_log USING brin (logged_at) WITH (pages_per_range = 32);
ANALYZE metrics_log;
-- correlation near 1 or -1 means BRIN can prune block ranges
SELECT attname, correlation FROM pg_stats WHERE tablename = 'metrics_log' AND attname = 'logged_at';
SELECT pg_size_pretty(pg_relation_size('idx_metrics_log_brin')) AS brin_size,
       pg_size_pretty(pg_relation_size('metrics_log')) AS table_size;

In practice

  • pg_trgm — The contrib extension behind fast `LIKE '%x%'`, `ILIKE` and typo-tolerant search; it indexes three-character sequences, so very short search terms (under three characters) still scan widely.
  • PostGIS — Spatial columns are indexed with GiST (and optionally SP-GiST or BRIN), which is why GiST is the default answer for geometry and nearest-neighbour queries.
  • In practice — Hash and SP-GiST are niche. Most production schemas are B-tree plus a handful of GIN indexes for jsonb, arrays or search, and BRIN on a few giant log or event tables.

Trade-offs

Pros

  • Matching the access method to the operator turns scans of whole tables into targeted lookups.
  • BRIN gives time-range pruning on huge tables at a tiny fraction of B-tree size.

Cons

  • Every extra index slows writes, adds WAL and vacuum work, and can block HOT updates.
  • GIN and GiST indexes are bigger and slower to build; wrong operator classes are silently ignored by the planner.

Design multi-column B-tree indexes (column order, leftmost prefix), partial and expression indexes, INCLUDE columns, and create them without blocking writes.

The problem

A single-column index per filter rarely matches real queries such as `WHERE user_id = ? AND status = ? ORDER BY created_at DESC LIMIT 20`. One well-ordered multi-column index, sometimes restricted by a predicate or extended with payload columns, can replace three single-column ones and avoid a sort. The hard part is column order and creating it on a live table.

How it works

  • Column order rule of thumb: equality columns first, then the range column, then the sort column when it can be satisfied. An index on `(user_id, status, created_at DESC)` serves `user_id = ? AND status = ? ORDER BY created_at DESC LIMIT 20` with no sort step. A range on a middle column stops later columns from being used as index conditions.
  • Leftmost-prefix rule: a B-tree can use the leading columns efficiently. `(user_id, status)` serves `user_id = ?` and `user_id = ? AND status = ?`, but a filter on `status` alone usually cannot use it well and may fall back to a sequential scan or a full index scan. Put the column you always filter on first; newer releases (PG 18) can skip over a missing leading column in some cases, but do not design for that.
  • Sort direction matters only when mixed: an index `(a, b DESC)` can be read forward or backward, so it serves `ORDER BY a, b DESC` and `ORDER BY a DESC, b`, but not `ORDER BY a, b`. Add `NULLS FIRST` or `NULLS LAST` to match the query when nulls exist (default: nulls sort last ascending).
  • Partial index: `CREATE INDEX ... WHERE status = 'new'` indexes only the matching rows, so it is small and cheap on writes. The planner uses it only when it can prove the query's `WHERE` implies the predicate; a parameterised predicate (`status = $1`) in a generic plan cannot be proven, so keep the literal in the query text.
  • Unique partial index implements soft delete: `UNIQUE (email) WHERE deleted_at IS NULL` forbids duplicate live emails while allowing any number of deleted rows. `NULLS NOT DISTINCT` (PG 15+, `UNIQUE NULLS NOT DISTINCT (a, b)`) makes null values compare equal, closing the default hole where several rows with a null column all pass a unique constraint.
  • Expression index: `CREATE INDEX ... ON users (lower(email))` serves `WHERE lower(email) = ?` only if the query uses the identical expression. The expression must be immutable; `now()` or a time zone dependent cast is rejected.
  • Covering index: `INCLUDE (total)` stores extra columns in the leaf pages only (not in the search key) so a query can be answered from the index alone (B-tree since PG 11, GiST since PG 12, SP-GiST since PG 14). Included columns do not provide ordering or uniqueness; they make the index larger, so include only what a hot query reads (see `index-only-scans`).
  • Create on live tables with `CREATE INDEX CONCURRENTLY` (not inside a transaction block). It takes longer and waits for old transactions, and if it fails or is cancelled it leaves an invalid index that still costs writes: find it via `pg_index.indisvalid = false`, `DROP INDEX CONCURRENTLY` it and retry. `REINDEX INDEX CONCURRENTLY` (PG 12+) rebuilds bloated indexes the same way.

Examples

Equality then sort: one index serves the filter and the ORDER BY ... LIMIT; a partial index serves a narrow hot subset; an expression index serves case-insensitive lookup.

sql
CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);
CREATE INDEX idx_orders_new_created ON orders (created_at) WHERE status = 'new';
CREATE INDEX idx_users_lower_email ON users (lower(email));
ANALYZE orders;
ANALYZE users;
EXPLAIN (COSTS OFF)
SELECT id, total FROM orders WHERE status = 'paid' ORDER BY created_at DESC LIMIT 20;
EXPLAIN (COSTS OFF)
SELECT id FROM orders WHERE status = 'new' AND created_at > now() - interval '1 hour';
EXPLAIN (COSTS OFF)
SELECT id FROM users WHERE lower(email) = 'someone@example.com';

Soft delete with a unique partial index, and NULLS NOT DISTINCT (PG 15+) for nullable unique keys.

sql
CREATE TABLE accounts (
    id bigserial PRIMARY KEY,
    email text NOT NULL,
    deleted_at timestamptz
);
CREATE UNIQUE INDEX uq_accounts_email_live ON accounts (email) WHERE deleted_at IS NULL;
INSERT INTO accounts (email) VALUES ('a@example.com');
UPDATE accounts SET deleted_at = now() WHERE email = 'a@example.com';
INSERT INTO accounts (email) VALUES ('a@example.com');

CREATE TABLE user_prefs (
    user_id bigint,
    pref_key text,
    UNIQUE NULLS NOT DISTINCT (user_id, pref_key)
);
INSERT INTO user_prefs VALUES (1, NULL);

Covering index, online build, and finding an invalid index left by a failed CONCURRENTLY build.

sql
CREATE INDEX CONCURRENTLY idx_orders_user_created_cov
    ON orders (user_id, created_at DESC) INCLUDE (total);
SELECT c.relname, i.indisvalid
FROM pg_index AS i
JOIN pg_class AS c ON c.oid = i.indexrelid
WHERE i.indrelid = 'orders'::regclass;
-- If indisvalid is false, drop it and retry:
-- DROP INDEX CONCURRENTLY idx_orders_user_created_cov;

In practice

  • pg_stat_user_indexes — `idx_scan` and the size functions show which indexes are used and what they cost; run it before adding a new index so you extend an existing one instead of stacking overlapping ones.
  • HypoPG — Extension that creates hypothetical indexes visible to plain `EXPLAIN`, so you can test whether the planner would use a design before paying for the build.
  • In practice — A frequent cause of surprise is an index that exists but is not used: an expression mismatch (`lower(email)` vs `email`), a type cast on the column, or a `text` pattern query on a non-C collation without `text_pattern_ops`.
  • Online schema tools — `CREATE INDEX CONCURRENTLY` is the standard way migration tools add indexes without a long write lock; see `schema-migrations-online` for the surrounding migration rules.

Trade-offs

Pros

  • A well-ordered composite index removes sorts and serves several query shapes with one structure.
  • Partial and expression indexes keep size and write cost low while matching exactly the hot queries.

Cons

  • Wrong column order or a mismatched expression yields an index the planner never uses.
  • Concurrent builds are slower and can leave invalid indexes; included columns grow the index.

Why an index-only scan still visits the heap: the visibility map, VACUUM, Heap Fetches in EXPLAIN, and why SELECT * defeats it.

The problem

You built a covering index, `EXPLAIN` says `Index Only Scan`, and the query is still slow because it keeps visiting the table. PostgreSQL's MVCC means the index alone cannot say whether a row is visible to your snapshot, so an index-only scan depends on how much of the table is marked all-visible.

How it works

  • Prerequisites: the index type supports it (B-tree always; GiST and SP-GiST for some operator classes; not hash, GIN or BRIN), and every column the query references, in `SELECT`, `WHERE`, `ORDER BY` and joins, is in the index as a key or `INCLUDE` column.
  • Visibility: index entries carry no transaction information, so for each match the executor checks the visibility map. If the heap page is marked all-visible, the heap is skipped; otherwise it fetches the heap tuple to check visibility. That fetch is what `EXPLAIN (ANALYZE)` prints as `Heap Fetches`.
  • The visibility map bit is set by `VACUUM` (and autovacuum) and cleared by any `INSERT`, `UPDATE` or `DELETE` on that page. A table with constant churn, or one that autovacuum rarely reaches, shows large `Heap Fetches` even though the plan node says `Index Only Scan`.
  • Read the plan like this: `Heap Fetches: 0` is the ideal; a number close to the row count means the optimisation is not working. Compare `pg_class.relallvisible` to `relpages` for a quick measure of how much of the table is all-visible.
  • Append-mostly tables are the best case. Since PG 13 autovacuum also triggers on inserts (`autovacuum_vacuum_insert_threshold`), which sets visibility bits on freshly loaded pages; before PG 13 a pure-insert table could stay unvacuumed and have an empty visibility map for a long time.
  • Key columns versus `INCLUDE` columns: put columns you filter or sort on in the key; put columns that are only returned in `INCLUDE`. Included columns are not searchable or sortable, but keep the internal pages of the tree smaller and can be of any type that fits in the page.
  • `SELECT *` defeats it: any column not in the index forces a heap fetch, and wide tables rarely have all columns in one index. Select the specific columns, or accept a normal index scan. The planner may also choose a plain index scan or bitmap scan when it expects most pages not to be all-visible.
  • The cost model uses `relallvisible` from the last `VACUUM` or `ANALYZE`, so statistics lag is real: right after a bulk load or large update, vacuum the table and then re-run `EXPLAIN (ANALYZE)` before judging an index.

Examples

Heap Fetches before and after VACUUM (the exact numbers vary; compare them to the row count).

sql
CREATE INDEX idx_orders_user_created_cov
    ON orders (user_id, created_at DESC) INCLUDE (total);
VACUUM (ANALYZE) orders;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT total FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 5;
-- Writes clear visibility-map bits on the touched pages:
UPDATE orders SET total = total + 1 WHERE user_id = 42;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT total FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 5;
VACUUM orders;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT total FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 5;

How much of each table is all-visible, as a quick health check.

sql
SELECT c.relname,
       c.relpages,
       c.relallvisible,
       round(100.0 * c.relallvisible / NULLIF(c.relpages, 0), 1) AS pct_all_visible
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind = 'r'
ORDER BY c.relpages DESC;

In practice

  • EXPLAIN (ANALYZE) — The `Heap Fetches` line under an `Index Only Scan` node is the direct measurement; use `BUFFERS` to see the actual page reads as well.
  • autovacuum — Controls how fresh the visibility map is; tune per table with `autovacuum_vacuum_scale_factor` and the insert threshold for large append-heavy tables rather than only globally.
  • In practice — A frequent cause of a covering index that does not pay off is a hot, frequently updated table: the visibility map is invalidated faster than vacuum restores it, so benchmark on production-like churn.

Trade-offs

Pros

  • When pages are all-visible, the query never touches the heap, which saves random I/O and cache space.
  • Works for ordered reads with `LIMIT` and for counting or aggregating narrow columns.

Cons

  • Benefit disappears on churny tables; the plan looks identical but `Heap Fetches` is high.
  • Wide `INCLUDE` lists bloat the index and slow writes; covering a query can make the next change to it more expensive.

Choose between GIN (jsonb_ops, jsonb_path_ops), expression B-tree indexes and generated columns for jsonb queries, and know when to promote a key to a column.

The problem

jsonb makes it easy to store semi-structured data, and just as easy to write queries that scan the whole table because no index matches the operator you used. The index you need depends on whether you search by containment, by key existence, or by one specific path.

How it works

  • Default GIN (`jsonb_ops`) indexes every key and value and supports `@>`, `?`, `?|`, `?&`, `@?` and `@@`. It is the most flexible and the largest.
  • `jsonb_path_ops` GIN hashes whole paths and values and supports only `@>`, `@?` and `@@`. It is usually smaller and faster for containment, but cannot serve the key-existence operators `?`, `?|` and `?&`.
  • Containment (`payload @> '{"channel":"web"}'`) uses the GIN index; the equivalent-looking `payload ->> 'channel' = 'web'` does not, because it is a different expression. Pick one form for hot queries and index for it.
  • One hot key: an expression B-tree index `((payload ->> 'channel'))` is smaller than a GIN index, supports ranges and sorting (with casts such as `((payload ->> 'amount')::numeric)`), and can be combined with other columns. The query must repeat the exact expression.
  • A function call such as `jsonb_path_exists(payload, '$.coupon')` is opaque to the planner and is not index-assisted. Use the operator forms `@?` and `@@`, which `jsonb_path_ops` and `jsonb_ops` do support, to get an index scan.
  • Generated columns (`GENERATED ALWAYS AS (payload ->> 'channel') STORED`, PG 12+) materialise a hot key as a real, typed column that can be indexed, constrained and have statistics. Adding a stored generated column rewrites the table, so plan it as a migration.
  • Promote a key to a real column when it appears in most queries, joins or foreign keys, needs constraints or accurate planner statistics, or is updated often. The planner has little statistics for values inside jsonb, so selectivity estimates for predicates there are weak, a frequent cause of bad plans.
  • GIN write cost: with `fastupdate` on (default), new entries go to an unsorted pending list that is merged into the index when it exceeds `gin_pending_list_limit` (default 4MB), or by vacuum or `gin_clean_pending_list()`. Inserts are fast on average but an unlucky one pays the flush, and reads must also scan the pending list. For latency-sensitive writes consider `ALTER INDEX ... SET (fastupdate = off)`, or a smaller limit.

Examples

Containment with a jsonb_path_ops GIN index, and the jsonpath operator form that can use it.

sql
CREATE INDEX idx_orders_payload_gin ON orders USING gin (payload jsonb_path_ops);
ANALYZE orders;
EXPLAIN (COSTS OFF)
SELECT id FROM orders WHERE payload @> '{"channel": "web", "coupon": true}';
EXPLAIN (COSTS OFF)
-- note: ?, ?| and ?& need the default jsonb_ops class instead.
SELECT id FROM orders WHERE payload @? '$.coupon ? (@ == true)';

One hot key: an expression index, or a generated column that you can index and constrain.

sql
CREATE INDEX idx_orders_channel ON orders ((payload ->> 'channel'));
EXPLAIN (COSTS OFF)
SELECT id FROM orders WHERE payload ->> 'channel' = 'api' AND user_id = 3;

ALTER TABLE orders
    ADD COLUMN channel text GENERATED ALWAYS AS (payload ->> 'channel') STORED;
CREATE INDEX idx_orders_channel_col ON orders (channel, created_at DESC);
ANALYZE orders;
SELECT channel, count(*) FROM orders GROUP BY channel ORDER BY 2 DESC;

Turn off the GIN pending list for latency-sensitive writes (trade-off: slower average insert).

sql
CREATE INDEX idx_events_data_gin ON events USING gin (data);
ALTER INDEX idx_events_data_gin SET (fastupdate = off);
SELECT reloptions FROM pg_class WHERE relname = 'idx_events_data_gin';
SHOW gin_pending_list_limit;

In practice

  • PostgreSQL jsonb — Documents its own trade-off: the default `jsonb_ops` class is general, `jsonb_path_ops` is the compact choice for containment-only workloads.
  • pg_stat_user_indexes — Check `idx_scan` and `pg_relation_size` for GIN indexes on jsonb; they are often the largest indexes in a database and are easy to leave unused.
  • In practice — A common pattern is to start with a jsonb column and a GIN index, then promote the two or three keys that every dashboard filters on into generated or real columns once the access pattern is clear.

Trade-offs

Pros

  • One GIN index serves flexible ad hoc containment queries over arbitrary keys.
  • Expression indexes and generated columns give B-tree speed, ranges and statistics for the hot keys.

Cons

  • GIN indexes are large and write-heavy; a mismatch between query form and index class silently falls back to a sequential scan.
  • Values inside jsonb have weak statistics, so planner estimates for them are often poor.

Query performance

Read a plan by comparing estimated and actual rows, loops and buffers, and remember that ANALYZE really executes the statement.

The problem

A query is slow and you need to know why, not guess. `EXPLAIN` shows the plan the planner chose; `EXPLAIN ANALYZE` runs the statement and adds what really happened. The skill is comparing the two: where the estimate diverged from reality, which node repeated, and how many pages were touched.

How it works

  • `EXPLAIN` only plans: each node shows `cost=startup..total` in arbitrary planner units and an estimated `rows`. `EXPLAIN (ANALYZE, BUFFERS, SETTINGS)` also executes the query and adds `actual time=first..last`, real `rows`, and `loops`.
  • `ANALYZE` really executes the statement, so an `UPDATE`, `DELETE` or `INSERT` really changes data and takes locks. Wrap DML in `BEGIN; ... ROLLBACK;`. Also note that timing every node adds overhead; `TIMING OFF` keeps row counts if the clock calls distort a fast query.
  • Compare estimated `rows` with actual `rows` node by node, starting at the deepest one. A factor-of-10 gap or more is the classic root cause of a bad join order or scan type; fix the estimate (statistics) before tuning anything else.
  • Actual time and rows are per loop: for a node with `loops=4000`, multiply by 4000 for the totals. A cheap inner node repeated thousands of times is how Nested Loop plans get slow.
  • `BUFFERS` adds `Buffers: shared hit=N read=M`: hit is served from shared buffers, read came from the OS or disk, `dirtied` and `written` show write work. Counting 8 kB pages touched is more stable than milliseconds. `BUFFERS` is automatic with `ANALYZE` in the newest releases (PG 18) but must be requested explicitly on PG 14-17.
  • Wasted work lines: `Rows Removed by Filter` (rows fetched and thrown away: an index does not cover the condition), `Rows Removed by Index Recheck`, and for `Index Only Scan` the `Heap Fetches` count (visibility map not set, so the table is visited anyway).
  • `SETTINGS` (PG 12+) lists planner parameters changed from defaults, so a plan pasted into a ticket shows a non-default `work_mem` or `random_page_cost`. `FORMAT JSON` produces output that plan visualisers such as explain.depesz.com, explain.dalibo.com or pgAdmin can render; do not paste plans containing sensitive literals into public tools.
  • For queries you cannot reproduce by hand, `auto_explain` logs plans of slow statements automatically (`log_min_duration`, optional `log_analyze`, `log_buffers`, `sample_rate`). `pg_stat_statements` ranks normalised statements by `total_exec_time`, `mean_exec_time` and `calls`, which tells you which plan to look at first.

Examples

Before and after an index: compare Rows Removed by Filter, Buffers and Execution Time.

sql
-- No index on orders.user_id yet: expect a Seq Scan, `Rows Removed by Filter: 100000`
-- and about 1350 buffers touched (fixture-sized table).
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT id, total
FROM orders
WHERE user_id = 42 AND status = 'paid';

CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Now a Bitmap Heap Scan over idx_orders_user_id; only ~5 rows are
-- visited and the leftover status filter removes all of them (0 rows returned), well under a millisecond.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total
FROM orders
WHERE user_id = 42 AND status = 'paid';

Explain a write safely inside a transaction that is rolled back; emit JSON for tools.

sql
-- ANALYZE executes the UPDATE. Roll back so nothing sticks.
CREATE INDEX idx_orders_user_id ON orders (user_id);

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders
SET status = 'cancelled'
WHERE user_id = 42;
ROLLBACK;

-- JSON output for plan visualisers
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT count(*) FROM orders WHERE user_id = 42;

Always-on instrumentation: sampled plans for slow statements plus statement statistics.

properties
# postgresql.conf (pg_stat_statements needs a restart; auto_explain can
# also be loaded per session with LOAD or via session_preload_libraries)
shared_preload_libraries = 'pg_stat_statements,auto_explain'
compute_query_id = auto
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.sample_rate = 0.1

Find the statements that cost the most in total before opening any single plan.

sql
-- skip-exec: pg_stat_statements must be in shared_preload_libraries (restart)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       rows,
       left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

In practice

  • pg_stat_statements — Contrib extension that aggregates statistics per normalised statement; columns such as `total_exec_time` and `mean_exec_time` exist from PG 13 (older releases used `total_time`). Needs `shared_preload_libraries` and a restart.
  • auto_explain — Contrib module that writes the plan of any statement slower than `log_min_duration` to the server log; `log_analyze` adds actual rows but costs timing overhead on every statement, so use `sample_rate` on busy systems.
  • explain.depesz.com / explain.dalibo.com / pgAdmin — Plan visualisers that take text or JSON output and highlight the slowest nodes and row-estimate errors. Treat the plan as data that may leak table and column names.
  • In practice — A frequent mistake is running `EXPLAIN ANALYZE` on a cold, tiny dataset or with different bind values than production; the plan and timing then say nothing about the real problem. Reproduce with representative data and parameters.

Trade-offs

Pros

  • Shows real rows, loops and I/O per node, so you fix the actual bottleneck instead of guessing.
  • `BUFFERS` numbers are reproducible across runs, unlike wall-clock time on a shared machine.

Cons

  • `ANALYZE` executes the statement: side effects, locks and load on production.
  • Per-node timing adds overhead and a warm cache can hide the cost of a cold run; one run is a sample, not a benchmark.

The planner picks plans from sampled column statistics, so stale or blind statistics (correlated columns, skew) produce bad row estimates.

The problem

The planner never looks at the data while planning; it estimates row counts from statistics sampled by `ANALYZE`. When those numbers are stale, too coarse or blind to correlation between columns, the planner picks the wrong join order or scan and a query that used to take milliseconds takes minutes.

How it works

  • `ANALYZE` samples the table (roughly 300 rows times the statistics target, so about 30 000 rows at the default target of 100) and stores per-column stats in `pg_statistic`, readable through the `pg_stats` view. Autovacuum runs it automatically after enough changes.
  • Key `pg_stats` columns: `n_distinct` (negative means a fraction of the row count, `-1` means unique), `null_frac`, `most_common_vals` with `most_common_freqs`, `histogram_bounds`, and `correlation` (physical order versus sort order; near 1 or -1 makes range index scans cheap).
  • Stale statistics are common after a bulk load, a large `DELETE`, or a data distribution change: autovacuum's analyze threshold is relative to table size (`autovacuum_analyze_scale_factor`, 0.1 by default), so a big table can drift a long time. Run `ANALYZE table` manually right after bulk loads and check `last_autoanalyze` and `n_mod_since_analyze` in `pg_stat_user_tables`.
  • `default_statistics_target` (default 100) sets histogram and MCV list size. For a skewed or high-cardinality column with bad estimates, raise it per column with `ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 500` and re-run `ANALYZE`; it costs more planning time and `ANALYZE` time, so do it selectively.
  • Per-column stats assume columns are independent, so for `WHERE city = 'x' AND zip = 'y'` the selectivities are multiplied and the estimate is too low. `CREATE STATISTICS name (dependencies, ndistinct, mcv) ON a, b FROM t` (mcv needs PG 12+) teaches the planner about functional dependencies, combined distinct counts and common value combinations; run `ANALYZE` afterwards and inspect `pg_stats_ext`.
  • Prepared statements get a custom plan (using the bound values) for the first five executions; after that the planner may switch to a cached generic plan if its estimated cost is not worse than the average custom plan. A generic plan cannot see the parameter, so skewed columns suffer. `plan_cache_mode` (PG 12+) can be `auto`, `force_custom_plan` or `force_generic_plan`; `pg_prepared_statements` shows `generic_plans` and `custom_plans` counts (PG 14+).
  • Cost parameters matter too: the default `random_page_cost = 4` assumes spinning disks and makes index scans look pricey. On SSD or well-cached storage a value around 1.1 to 2 is a commonly used starting point, but change it for a test session first and compare plans.
  • Statistics only describe columns, not expressions: for `WHERE lower(email) = ...` the planner uses a default guess unless an expression index exists (which gathers its own stats) or you use `CREATE STATISTICS ... ON (expression)` (PG 14+).

Examples

Inspect pg_stats, raise the target for one column, and check how stale a table is.

sql
-- What the planner believes about orders (n_distinct < 0 is a fraction)
SELECT attname,
       n_distinct,
       null_frac,
       left(most_common_vals::text, 40) AS mcv,
       correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname IN ('status', 'user_id', 'created_at');

-- Finer histogram for one column, then refresh just that column
ALTER TABLE orders ALTER COLUMN user_id SET STATISTICS 500;
ANALYZE orders (user_id);

-- Are statistics fresh? n_mod_since_analyze counts rows changed since
SELECT relname, last_analyze, last_autoanalyze, n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

Extended statistics fix the row estimate for correlated columns (seen: ~200 estimated before, ~2000 after, 2000 actual).

sql
-- region_id is fully determined by city_id (region = city / 5)
CREATE TABLE addresses AS
SELECT g AS id, g % 50 AS city_id, (g % 50) / 5 AS region_id
FROM generate_series(1, 100000) AS g;
ANALYZE addresses;

-- Independent-column estimate: about 200 rows, actual is 2000
EXPLAIN SELECT * FROM addresses WHERE city_id = 7 AND region_id = 1;

CREATE STATISTICS addresses_city_region (dependencies, ndistinct, mcv)
    ON city_id, region_id FROM addresses;
ANALYZE addresses;

-- Now the estimate is roughly 2000 rows (about 2030 seen), close to the real 2000
EXPLAIN SELECT * FROM addresses WHERE city_id = 7 AND region_id = 1;

Generic versus custom plans: the generic estimate ignores the parameter value.

sql
-- Skewed column: 'failed' is 0.1% of rows
CREATE TABLE jobs AS
SELECT g AS id, CASE WHEN g % 1000 = 0 THEN 'failed' ELSE 'done' END AS state
FROM generate_series(1, 200000) AS g;
CREATE INDEX idx_jobs_state ON jobs (state);
ANALYZE jobs;

PREPARE jobs_by_state (text) AS SELECT * FROM jobs WHERE state = $1;

-- Custom plan sees 'failed': estimate in the low hundreds of rows
SET plan_cache_mode = force_custom_plan;
EXPLAIN EXECUTE jobs_by_state('failed');

-- Generic plan sees only $1: estimate is 100000 rows (half the table)
SET plan_cache_mode = force_generic_plan;
EXPLAIN EXECUTE jobs_by_state('failed');

RESET plan_cache_mode;
SELECT name, generic_plans, custom_plans FROM pg_prepared_statements;

In practice

  • pg_stats / pg_stats_ext — The two views to read before touching any setting; `pg_stats_ext` shows extended statistics objects and their collected data.
  • autovacuum — Triggers `ANALYZE` when changes exceed `autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * reltuples`; per-table overrides with `ALTER TABLE ... SET (autovacuum_analyze_scale_factor = 0.02)` suit large hot tables.
  • HypoPG — Extension that creates hypothetical indexes so you can see whether the planner would use them without building them.
  • In practice — A recurring cause of sudden regressions is a freshly bulk-loaded or freshly restored table that nobody analysed (after a restore, run `ANALYZE` on everything; `pg_upgrade` does not carry statistics over before PG 18, and PG 18 still skips extended statistics); the planner then works from empty or missing stats.

Trade-offs

Pros

  • Better statistics are a cheap, non-invasive fix: no query rewrite, no hints, and every query on the column benefits.
  • Extended statistics repair correlated-column estimates that no index can fix.

Cons

  • A higher statistics target increases `ANALYZE` and planning time, and the sample is still a sample (rare values can be missed).
  • Extended statistics must be created by hand for the column combinations you know about; they are not automatic.
  • Forcing `plan_cache_mode` trades one failure mode for another: custom plans cost planning time on every call, generic plans ignore skew.

Nested loop, hash and merge join each win in different situations; know how the planner picks, when hashes spill, and how EXISTS, NOT IN, LATERAL and CTEs change the shape.

The problem

A join can be executed three ways and the planner picks one from row estimates and available indexes. When it picks wrong, or when the query shape forces a poor plan (a `NOT IN` with NULLs, an inline CTE you thought was cached), you need to recognise the node, know why it was chosen and which lever moves it.

How it works

  • Nested Loop: for each outer row, probe the inner side. Chosen when the outer side is small and the inner side has an index on the join key. Cost grows with outer rows times inner probe cost, so a wrong outer estimate (`rows=1`, actual 50 000) is the classic disaster.
  • Hash Join: build a hash table on the smaller input, scan the other and probe. Equality conditions only. Good for large unsorted inputs without useful indexes. If the table exceeds `work_mem` times `hash_mem_multiplier` (default 2.0 from PG 15), it is split into `Batches` and spilled to temp files, visible as `Batches: 16` in `EXPLAIN ANALYZE`.
  • Merge Join: both inputs sorted on the join key, then merged in one pass. Chosen when indexes already provide the order or both sides are huge. Without index order it needs two `Sort` nodes, which is usually why the planner prefers a hash join instead.
  • Join order: for up to `join_collapse_limit` (default 8) tables the planner searches orders exhaustively; beyond that it follows the written order of explicit `JOIN`s. `from_collapse_limit` does the same for subqueries, and above `geqo_threshold` (12) the genetic optimiser takes over. Raising the limits increases planning time quickly.
  • `enable_nestloop`, `enable_hashjoin`, `enable_mergejoin` are diagnostic switches, not fixes: turn one off in a session to see whether the planner has a better-looking alternative, then fix the cause (statistics, an index, `work_mem`).
  • Semi and anti joins: `WHERE EXISTS (SELECT 1 FROM ...)` and `IN (subquery)` become Semi Joins (stop at the first match), `NOT EXISTS` becomes an Anti Join. `NOT IN (subquery)` is different: if the subquery returns even one NULL, `x NOT IN (...)` is never true and you get zero rows, and the planner cannot use an Anti Join, falling back to a hashed SubPlan or a slow rescan. Prefer `NOT EXISTS`.
  • `LATERAL` lets a subquery reference columns from earlier FROM items, the tool for top-N per group (`CROSS JOIN LATERAL (... ORDER BY ... LIMIT 3)`). It executes as a Nested Loop, so an index on the inner filter plus sort columns makes each of the `loops` cheap.
  • CTEs: since PG 12 a non-recursive, side-effect-free CTE referenced once is inlined like a subquery, so predicates can be pushed into it. A CTE referenced twice, or marked `MATERIALIZED`, is computed once and read through `CTE Scan`, which acts as an optimisation fence. Use `NOT MATERIALIZED` to force inlining and `MATERIALIZED` to compute an expensive step exactly once.

Examples

See hash Batches grow when work_mem is small, and force a Merge Join for comparison (session-local, diagnostic only).

sql
-- A tiny work_mem forces the hash table to spill (64kB is the minimum).
SET work_mem = '64kB';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT o.id, u.email
FROM orders o
JOIN users u ON u.id = o.user_id;
-- Hash node reports "Batches: 16" here; with default 4MB it is "Batches: 1".
RESET work_mem;

-- Diagnostic only: ban hash and nested loop to see the merge alternative.
CREATE INDEX idx_orders_user_id ON orders (user_id);
SET enable_hashjoin = off;
SET enable_nestloop = off;
EXPLAIN (COSTS OFF)
SELECT o.id, u.email
FROM orders o
JOIN users u ON u.id = o.user_id;
RESET enable_hashjoin;
RESET enable_nestloop;

The NOT IN null trap: a single NULL empties the result; NOT EXISTS behaves as intended.

sql
CREATE TEMP TABLE blocked (user_id bigint);
INSERT INTO blocked VALUES (1), (NULL);

-- One NULL in the subquery: NOT IN is never true -> 0 rows
SELECT count(*) AS not_in
FROM users
WHERE id NOT IN (SELECT user_id FROM blocked);

-- NOT EXISTS ignores the NULL -> 19999 rows, and plans as an Anti Join
SELECT count(*) AS not_exists
FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM blocked b WHERE b.user_id = u.id
);

LATERAL top-N per group: Nested Loop with loops=4000 over an Index Scan under Limit.

sql
-- Latest 3 orders per German user. The composite index serves both the
-- filter and the ORDER BY, so each of the 4000 loops is a tiny Index Scan.
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT u.id, last3.id AS order_id, last3.total
FROM users u
CROSS JOIN LATERAL (
    SELECT o.id, o.total
    FROM orders o
    WHERE o.user_id = u.id
    ORDER BY o.created_at DESC
    LIMIT 3
) AS last3
WHERE u.country = 'DE';

MATERIALIZED turns a CTE into an optimisation fence; compare with the inlined default.

sql
-- Default (PG 12+): referenced once, so inlined; the predicate reaches orders.
EXPLAIN (COSTS OFF)
WITH recent AS (SELECT * FROM orders WHERE status = 'paid')
SELECT * FROM recent WHERE user_id = 42;

-- MATERIALIZED: computed in full first, then filtered in a CTE Scan.
EXPLAIN (COSTS OFF)
WITH recent AS MATERIALIZED (SELECT * FROM orders WHERE status = 'paid')
SELECT * FROM recent WHERE user_id = 42;

In practice

  • PostgreSQL planner — Join-order search is exhaustive below `geqo_threshold` FROM items, with `join_collapse_limit`/`from_collapse_limit` bounding how far explicit joins and subqueries are merged into one search, and the genetic optimiser (GEQO) takes over at `geqo_threshold`; ORMs that emit 10+ joins can hit those limits.
  • In practice — A frequent source of surprise is `NOT IN (SELECT nullable_col ...)`; many teams standardise on `NOT EXISTS` in code review for exactly this reason.
  • In practice — Before PG 12 every CTE was materialised, so older advice to "use CTEs as optimisation fences" or "never use CTEs" both predate the current behaviour; check the version before applying it.

Trade-offs

Pros

  • Understanding the three strategies lets you read a plan and name the lever: index, statistics, `work_mem` or query shape.
  • `NOT EXISTS`, `LATERAL` and explicit `MATERIALIZED`/`NOT MATERIALIZED` give you control without planner hints.

Cons

  • Raising `work_mem` to avoid hash spills is per node and per query, so it multiplies across concurrent sessions and can exhaust RAM.
  • Raising `join_collapse_limit` improves plans for large joins at the cost of planning time, which can dominate short queries.
  • `enable_*` switches affect the whole session; leaving one off in production code hides the real problem.

OFFSET gets slower with every page; seek past the last row you saw with a row-value comparison on an index that matches the sort.

The problem

`LIMIT 20 OFFSET 80000` has to produce and discard 80 000 rows before returning 20, so page N costs more than page N-1 and deep pages time out. Keyset (seek) pagination remembers where the previous page ended and asks the index to start from there, making every page cost about the same.

How it works

  • OFFSET cost: the executor must read and skip every preceding row, even with an index. On the fixture, `OFFSET 80000` over `ORDER BY created_at DESC, id DESC` showed `actual rows=80020` flowing through the node under `Limit`, and the time grows linearly with the offset.
  • Keyset: remember the sort key of the last row and ask for rows strictly after it: `WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20`. The row-value comparison is standard SQL and PostgreSQL can turn it into an index range condition (`Index Cond: (ROW(created_at, id) < ROW(...))`).
  • The index must match the sort: `CREATE INDEX ... ON orders (created_at DESC, id DESC)` (or the same columns ascending, scanned backwards). All columns must sort in the same direction for the row comparison to be a single range; mixed directions need an `OR` expansion.
  • Always add a unique tie-break column (`id`) to the sort. Timestamps repeat, and without a unique final key rows can be skipped or duplicated between pages. The cursor handed to the client is the pair of values, usually encoded opaquely.
  • Keyset pages cost O(page size) from any position: the fixture's seek query touched 23 buffers (the cost does not grow with cursor depth), versus tens of thousands of rows read for the offset version.
  • Trade-offs: no jump to "page 500", no arbitrary re-sorting without a matching index, and the cursor must be built from non-null sort columns (add `NULLS` handling or a `COALESCE` expression index if the column is nullable). Inserts and deletes between requests do not shift pages, which is an advantage over OFFSET.
  • Total counts: `SELECT count(*)` has to visit every qualifying row (an Index Only Scan helps only when the visibility map is current), so avoid it on every page request. Options: show "next/previous" only; cap with `SELECT count(*) FROM (SELECT 1 ... LIMIT 1001) s` to display "1000+"; or take `pg_class.reltuples` as an approximate total for an unfiltered table.
  • Use OFFSET only where pages are few and bounded (admin screens with small tables). When the product genuinely needs random page jumps on a large set, consider a precomputed page-to-key map or search-engine-style approximation rather than raw OFFSET.

Examples

OFFSET reads 80 000 rows to return 20; the keyset query is an index range scan that stops after 20 (about 23 buffers).

sql
CREATE INDEX idx_orders_created_id ON orders (created_at DESC, id DESC);

-- Page 4000 with OFFSET: the scan produces ~80020 rows and throws 80000 away.
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT id, created_at, total
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 80000;

-- Keyset: resume after the last row of the previous page ($1, $2 = its keys).
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, BUFFERS)
SELECT id, created_at, total
FROM orders
WHERE (created_at, id) < ('2026-09-30 00:00:00+00', 50000)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Capped and estimated totals instead of an exact count on every page.

sql
-- Cheap total variants (avoid exact count(*) on every request)
-- Option 1: capped count: stop after 1001 matches, show "1000+"
SELECT count(*) AS capped
FROM (SELECT 1 FROM orders WHERE status = 'paid' LIMIT 1001) AS s;

-- Option 2: the planner estimate for the whole table (unfiltered, approximate)
SELECT reltuples::bigint AS approx_rows
FROM pg_class
WHERE oid = 'orders'::regclass;

In practice

  • Row-value comparison — `(a, b) < (x, y)` is standard SQL and compares lexicographically; PostgreSQL supports it in `WHERE` and can use a matching btree index for it.
  • GraphQL cursor connections / REST `next_cursor` — The usual API shape for keyset pagination: an opaque cursor encoding the sort key tuple, returned with each page.
  • In practice — `reltuples` is only refreshed by `VACUUM`, `ANALYZE` and some index builds, and is `-1` for a never-analysed table on PG 14+; it is a rough dashboard number, not a filtered count.

Trade-offs

Pros

  • Constant cost per page and stable results while rows are inserted or deleted.
  • Uses the same index that serves the ordinary first page, so there is nothing extra to maintain.

Cons

  • No random page access and no "page X of Y" without a separate count.
  • Needs a unique, indexed, preferably non-null sort key and one index per sort order users can choose.
  • The cursor is tied to the sort and filter it was issued for; changing either invalidates it.

Replace per-row round trips with array parameters, multi-row writes, upserts and COPY, and spot N+1 patterns from call counts.

The problem

An application loads 100 orders, then runs one query per order for its items: 101 round trips where one or two would do. Network latency and per-statement overhead dominate, not the database's work. The same applies to writes: inserting rows one at a time is orders of magnitude slower than sending them together.

How it works

  • Detect N+1 server-side: in `pg_stat_statements`, a cheap statement with an enormous `calls` count and a tiny `mean_exec_time` (for example `SELECT ... FROM order_items WHERE order_id = $1`) is the signature. Correlate with application traces or ORM logs to find the loop.
  • Fix reads with a set-based query: `WHERE id = ANY($1)` with a single array parameter, or a `JOIN`. An array parameter keeps one statement text (one `pg_stat_statements` row, one cached plan), whereas a giant `IN (1,2,3,...)` list makes a new text for every length, bloats the statement cache and parse time, and hits the 65 535 bind-parameter limit if each value is a placeholder.
  • Batch writes in three tiers: multi-row `INSERT ... VALUES (...), (...), ...` for hundreds of rows; `INSERT ... SELECT * FROM unnest($1::type[], $2::type[])` to pass whole columns as arrays with a constant statement text; and `COPY ... FROM STDIN` (or `\copy` in psql) for bulk loads, which is the fastest path in PostgreSQL.
  • Upsert with `INSERT ... ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col` (or `DO NOTHING`) makes a batch idempotent without a read-modify-write loop. It needs a unique index or constraint on the conflict target. A single statement must not affect the same key twice ("cannot affect row a second time"), so de-duplicate the batch first.
  • `RETURNING` sends back generated ids and updated values in the same round trip, removing the follow-up `SELECT`. Updates can also be batched with `UPDATE ... FROM unnest(...) AS v(id, status) WHERE t.id = v.id`.
  • Size batches by bytes and time, not only rows: a few hundred to a few thousand rows per statement is a typical starting range. Giant batches hold locks and a transaction open for longer, enlarge WAL bursts, make failures more expensive to retry, and block autovacuum cleanup; measure and keep each transaction short.
  • Prepared statements save parse and plan work on repeated shapes. Behind PgBouncer in transaction pooling mode, protocol-level prepared statements break unless the pooler tracks them (`max_prepared_statements`: added in PgBouncer 1.21, default 200 since 1.24, earlier releases defaulted to off; it does not track SQL-level `PREPARE`). Otherwise use simple protocol or the driver's no-prepare setting.
  • Round trips can also be cut by pipelining (libpq pipeline mode from PG 14, supported by some drivers) or by moving a loop into one SQL statement or function; but a single big statement is easier to reason about than hidden loops in PL code.

Examples

Replace N per-id queries (or a giant IN list) with one = ANY($1) array parameter.

sql
-- One round trip for many ids: the array stays a single parameter.
PREPARE orders_by_ids (bigint[]) AS
    SELECT id, total FROM orders WHERE id = ANY ($1);
EXECUTE orders_by_ids('{1,2,3,500}');

-- Same idea for the children: no per-order query.
SELECT order_id, sku, qty
FROM order_items
WHERE order_id = ANY ('{1,2,3,500}'::bigint[]);

Batch upsert with unnest and ON CONFLICT DO UPDATE; RETURNING avoids a follow-up SELECT.

sql
CREATE TABLE sku_prices (
    sku text PRIMARY KEY,
    price numeric(12, 2) NOT NULL,
    updated_at timestamptz NOT NULL DEFAULT now()
);

-- Whole batch as column arrays; the statement text never changes.
INSERT INTO sku_prices (sku, price)
SELECT * FROM unnest('{A-1,A-2,A-3}'::text[], '{9.50,12.00,3.25}'::numeric[])
    AS t (sku, price)
ON CONFLICT (sku) DO UPDATE
    SET price = EXCLUDED.price, updated_at = now()
RETURNING sku, price;

-- Re-running with a changed price updates instead of failing.
INSERT INTO sku_prices (sku, price)
SELECT * FROM unnest('{A-3,A-4}'::text[], '{3.75,1.00}'::numeric[])
    AS t (sku, price)
ON CONFLICT (sku) DO UPDATE
    SET price = EXCLUDED.price, updated_at = now()
RETURNING sku, price;

Update many rows with different values in one statement.

sql
-- Batched update from arrays instead of one UPDATE per order.
UPDATE orders AS o
SET status = v.status
FROM unnest('{1,2}'::bigint[], '{paid,shipped}'::text[]) AS v (id, status)
WHERE o.id = v.id
RETURNING o.id, o.status;

Use COPY (\copy from a client) for bulk loads; stage then upsert when rows need merging.

bash
# Bulk load: COPY streams rows with minimal per-row overhead.
psql "$DATABASE_URL" -c "\copy order_items (order_id, sku, qty) from 'items.csv' with (format csv, header true)"

# Many threads of single-row INSERTs are far slower than one COPY.
# Load into a staging table first if you need validation or upsert:
#   COPY staging FROM STDIN ...; INSERT INTO target SELECT ... ON CONFLICT ...;

In practice

  • pg_stat_statements — The `calls` and `mean_exec_time` columns expose N+1 patterns from the server side without touching application code.
  • PgBouncer — Transaction pooling breaks session state such as server-side prepared statements unless `max_prepared_statements` is non-zero (PgBouncer 1.21+; default 200 since 1.24); check your PgBouncer version and driver settings (for example the `prepareThreshold=0` option in the PostgreSQL JDBC driver).
  • ORMs (ActiveRecord, Hibernate, SQLAlchemy, Django) — Offer eager loading (`includes`, `select_related`, `selectinload`, `JOIN FETCH`) specifically to turn N+1 into one or two set-based queries; `selectinload` and `prefetch_related` fetch the children with a batched `IN` on the parent keys.
  • COPY — Built-in bulk load/unload command (`COPY FROM`/`COPY TO`, `\copy` in psql); loaders such as pgloader and most drivers' bulk APIs use the COPY protocol.

Trade-offs

Pros

  • Cuts network round trips and per-statement overhead, usually the biggest win for chatty applications.
  • Array parameters and `unnest` keep statement text constant, which helps plan caching and `pg_stat_statements` grouping.
  • Upsert with `RETURNING` makes retries idempotent and removes read-modify-write races.

Cons

  • Large batches lengthen transactions, hold locks, and make a single failure roll back everything; chunk and size them.
  • `ON CONFLICT DO UPDATE` writes a new row version even for unchanged values unless guarded with a `WHERE` clause, creating bloat.
  • `COPY` has no per-row conflict handling; you need a staging table to merge or validate.

Locks & concurrency

Which table-level and row-level locks each statement takes, why a waiting ALTER TABLE stalls every later query, and how to see who blocks whom.

The problem

A migration that should take milliseconds freezes the whole application, or a report query blocks writers for minutes. PostgreSQL has eight table-level lock modes and four row-level ones, and what hurts is rarely the statement you are running but the queue it joins. Know which statement takes which mode, and how to see who is waiting for whom.

How it works

  • Table-level modes run from weakest to strongest: `ACCESS SHARE` (`SELECT`), `ROW SHARE` (`SELECT ... FOR UPDATE`), `ROW EXCLUSIVE` (`INSERT`/`UPDATE`/`DELETE`), `SHARE UPDATE EXCLUSIVE` (`VACUUM`, `CREATE INDEX CONCURRENTLY`), `SHARE` (plain `CREATE INDEX`), `SHARE ROW EXCLUSIVE`, `EXCLUSIVE`, `ACCESS EXCLUSIVE` (`DROP`, `TRUNCATE`, `VACUUM FULL`, most `ALTER TABLE`). The `lock-conflicts` table above lists exactly which pairs collide.
  • Everyday reads and writes (`ACCESS SHARE`, `ROW EXCLUSIVE`) do not conflict with each other at table level, so readers never block writers. Writers only wait on each other at the row level (or on a duplicate unique key), when two sessions touch the same row.
  • Row-level modes are `FOR KEY SHARE`, `FOR SHARE`, `FOR NO KEY UPDATE` and `FOR UPDATE`. A plain `UPDATE` that does not change a key column takes `FOR NO KEY UPDATE`; one that changes a column of a unique index usable by a foreign key, and `DELETE`, take `FOR UPDATE`. Row locks live in the tuple header, not in the lock table, so they do not appear in `pg_locks`; a waiter shows up as waiting on a `transactionid` or `tuple` lock instead.
  • Foreign-key checks take `FOR KEY SHARE` on the referenced parent row. That conflicts only with `FOR UPDATE` (a `DELETE` or key change on the parent), not with `FOR NO KEY UPDATE`, which is why updating non-key columns of a parent while children are being inserted is safe.
  • The queue gotcha: locks are granted in order. If a long `SELECT` holds `ACCESS SHARE` and an `ALTER TABLE` queues for `ACCESS EXCLUSIVE`, every query that arrives afterwards queues behind the `ALTER`, even plain reads. The table looks frozen although the real culprit is the first long transaction.
  • Defence: run DDL with `SET lock_timeout = '2s'` (in PG the default is 0, wait forever) and retry on failure, so a blocked migration gives up instead of stalling traffic. Pair it with a `statement_timeout` for the work itself.
  • To see a pile-up, join `pg_stat_activity` with `pg_blocking_pids(pid)`: sessions with a non-empty array are waiting, and the pids in it are the blockers (follow the chain to the root). `pg_locks` has the raw rows: `granted = false` is a waiter, and since PG 14 its `waitstart` says since when.
  • Set `log_lock_waits = on` to log any lock wait longer than `deadlock_timeout` (1s by default); it gives you a trail after the incident without needing to catch it live.
  • A lock is held until the transaction ends (`COMMIT`/`ROLLBACK`); there is no manual early release for table or row locks (only `ROLLBACK TO SAVEPOINT` drops locks taken after the savepoint). An idle open transaction therefore keeps its locks indefinitely (see `idle-in-transaction`).

Examples

Table locks show up in pg_locks (RowShareLock and RowExclusiveLock on orders plus the transaction id); the row lock itself does not.

sql
BEGIN;
    SELECT id FROM orders WHERE id = 1 FOR UPDATE;
    UPDATE orders SET status = 'paid' WHERE id = 1;
    SELECT locktype, relation::regclass AS rel, mode, granted
    FROM pg_locks
    WHERE pid = pg_backend_pid() AND locktype IN ('relation', 'transactionid')
    ORDER BY locktype, mode;
COMMIT;

Give DDL a short lock_timeout: if it cannot get ACCESS EXCLUSIVE in 2s it fails with SQLSTATE 55P03 instead of stalling every query behind it. Retry from the caller.

sql
BEGIN;
    SET LOCAL lock_timeout = '2s';
    ALTER TABLE orders ADD COLUMN note text;
COMMIT;

Who is waiting and for whom. Empty result means nobody is blocked right now; chase the blocked_by pids until you reach one that is not itself blocked.

sql
SELECT a.pid,
       pg_blocking_pids(a.pid) AS blocked_by,
       a.wait_event_type,
       a.wait_event,
       now() - a.query_start AS running_for,
       left(a.query, 60) AS query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY a.query_start;

The lock-queue pile-up reproduced: C is blocked by B, B by A. Cancelling or committing A releases the whole chain.

sql
-- skip-exec: needs three sessions
-- Session A (long reader, holds ACCESS SHARE):
BEGIN;
SELECT count(*) FROM orders;
-- ...leave it open

-- Session B (migration, queues for ACCESS EXCLUSIVE behind A):
ALTER TABLE orders ADD COLUMN note text;

-- Session C (an ordinary read, now queued behind B; hangs until A finishes or a timeout fires):
SELECT count(*) FROM orders;

In practice

  • PostgreSQL wiki: Lock Monitoring — Publishes ready-made pg_locks and pg_stat_activity queries for blocking trees; a common starting point for lock dashboards.
  • Migration tools — Online-migration helpers and many in-house runbooks wrap DDL in a short lock_timeout with a retry loop for exactly the queue problem described here.
  • In practice — A frequent cause of a 'sudden outage' during deploys is an ALTER TABLE queued behind a forgotten long transaction, not the ALTER itself being slow.

Trade-offs

Pros

  • Fine-grained modes let readers and writers run concurrently on the same table
  • Strict FIFO queueing prevents DDL from starving behind an endless stream of readers
  • Every lock wait is observable through pg_locks, pg_stat_activity and pg_blocking_pids

Cons

  • FIFO queueing is exactly what lets one waiting ALTER TABLE freeze unrelated reads
  • No default lock_timeout: forgetting to set one turns a blocked migration into an outage
  • Row locks are invisible in pg_locks, so diagnosing them takes a second step through transactionid waits

How opposite lock order, foreign keys and upserts produce `deadlock detected`, and the habits that remove it: consistent ordering, short transactions, retry on 40P01.

The problem

Two transactions each hold a lock the other needs, so neither can proceed. PostgreSQL notices, aborts one of them with `ERROR: deadlock detected` and lets the other continue. The error is not a bug in the database; it is a signal that your code takes locks in inconsistent order or holds them too long.

How it works

  • The classic shape is opposite order: transaction A updates row 1 then row 2, transaction B updates row 2 then row 1. Each ends up waiting for the other's transaction to finish.
  • A session that waits on a lock does not check for cycles immediately. After `deadlock_timeout` (1s by default) it runs the detector; if it finds a cycle it aborts itself with SQLSTATE `40P01`. Normally the session whose check fires first aborts itself, not necessarily the one that did less work; the docs say not to rely on which transaction is chosen.
  • The client sees `deadlock detected` with `DETAIL: Process X waits for ShareLock on transaction N; blocked by process Y`. The statements involved are only in the server log, and `HINT: See server log for query details.` points you there.
  • Read the log message as a cycle: each line says which process waits on which transaction, followed by the query each process was running. Find the two code paths that touch the same rows in different order.
  • Primary fix: lock in a consistent order. When a transaction must update several rows, take them sorted by key first with `SELECT ... WHERE id IN (...) ORDER BY id FOR UPDATE`, then update. Do the same for multi-row `INSERT ... ON CONFLICT`: sort the input rows by the conflict key.
  • Keep transactions short: no network calls, user think time or queue waits between the first lock and `COMMIT`. A deadlock needs time to form; the shorter the window, the rarer it is.
  • Foreign keys add hidden locks: an insert into a child table takes `FOR KEY SHARE` on the parent row, so two transactions that insert children of the same parent and then each try to `DELETE` or change the key of that parent can deadlock. Upserts deadlock when batches overlap and arrive in different row order.
  • Retry is the safety net, not the cure: catch SQLSTATE `40P01`, roll back and rerun the whole transaction a few times with a small random delay. `40001` (serialization failure) deserves the same handling.
  • Count them with `pg_stat_database.deadlocks` and alert on a rising rate; an occasional one in a busy system is tolerable, a steady trickle means a code path to fix.

Examples

Minimal opposite-order deadlock. The side whose detector fires first aborts; the other proceeds as soon as the victim rolls back.

sql
-- skip-exec: needs two sessions
-- Session A                                   -- Session B
BEGIN;                                         BEGIN;
UPDATE orders SET status = 'paid'
 WHERE id = 1;
                                               UPDATE orders SET status = 'paid'
                                                WHERE id = 2;
UPDATE orders SET status = 'paid'
 WHERE id = 2;   -- waits for B
                                               UPDATE orders SET status = 'paid'
                                                WHERE id = 1;   -- cycle
-- after deadlock_timeout one side gets: ERROR:  deadlock detected  (SQLSTATE 40P01)
COMMIT;

Lock the whole batch in key order before changing it. Every worker that does this takes the same locks in the same sequence, so no cycle can form.

sql
BEGIN;
    SELECT id
    FROM orders
    WHERE id IN (30, 10, 20)
    ORDER BY id
    FOR UPDATE;

    UPDATE orders SET status = 'shipped' WHERE id IN (30, 10, 20);
COMMIT;

Batch upserts: sort the incoming rows by the conflict key. Two overlapping batches in different orders can deadlock; sorted ones queue up instead.

sql
CREATE TABLE counters (k int PRIMARY KEY, n bigint NOT NULL DEFAULT 0);

INSERT INTO counters (k, n)
SELECT k, 1
FROM unnest(ARRAY[7, 3, 5]) AS t(k)
ORDER BY k
ON CONFLICT (k) DO UPDATE SET n = counters.n + excluded.n;

The log path depends on your install (log_directory, or journald). The report lists each process, the lock it waits for and its query.

bash
# how many deadlocks has this database seen since the last stats reset?
psql -c "SELECT datname, deadlocks FROM pg_stat_database WHERE datname = current_database();"

# find the full report (both queries) in the server log
grep -A8 "deadlock detected" /var/log/postgresql/postgresql-16-main.log

In practice

  • PostgreSQL log — Logs the full cycle (every process, the lock it waits for, and its query) at ERROR level when a deadlock is detected; that text is usually all you need to find the two code paths.
  • ORMs and drivers — Drivers expose the SQLSTATE (for example psycopg's DeadlockDetected exception class, or SQLState 40P01 in JDBC), so retry-on-deadlock can be a narrow, explicit handler rather than a blanket retry.
  • In practice — Deadlocks in production commonly come from batch jobs and API handlers touching the same rows in different order, and from unsorted bulk upserts. Sorting the keys fixes most of them.

Trade-offs

Pros

  • Automatic detection: PostgreSQL resolves the cycle by aborting one transaction instead of hanging forever
  • Consistent lock ordering removes the problem at its source without extra infrastructure
  • The server log gives the exact queries involved

Cons

  • Detection only runs after deadlock_timeout, so the participants stall for about a second first
  • The victim loses all its work and the application must be written to retry
  • Hidden locks (foreign keys, upserts, triggers) make some cycles hard to see from the application code alone

Build a work queue on a table: claim rows with `FOR UPDATE SKIP LOCKED`, avoid waiting with `NOWAIT`, and know where this stops being a good idea.

The problem

You need a work queue and already run PostgreSQL. Naive `SELECT ... FOR UPDATE LIMIT 1` makes every worker wait on the same first row, so the workers run one at a time. `SKIP LOCKED` lets each worker take the next row nobody else holds, turning a table into a concurrent queue.

How it works

  • `FOR UPDATE SKIP LOCKED` returns only rows that can be locked right now and silently skips rows another transaction has locked. Combined with `ORDER BY id LIMIT n` it hands each worker a disjoint batch. Available since PG 9.5.
  • `FOR UPDATE NOWAIT` is the other non-waiting variant: it raises an error (SQLSTATE `55P03`) instead of waiting if any selected row is locked. Use it when contention means something is wrong; use `SKIP LOCKED` when contention is normal.
  • Claim in one statement: a CTE selects ids with `FOR UPDATE SKIP LOCKED LIMIT n`, and an outer `UPDATE ... SET status = 'running' ... RETURNING` marks and returns them. Commit right away so the lock is held for milliseconds, and process the job outside that transaction.
  • Because the claim transaction commits before the work starts, a crashed worker leaves a row stuck in `running`. Store `locked_at` and a retry counter, and run a reaper that returns rows older than the job timeout to `queued` (with a cap on attempts, then a dead-letter status).
  • Holding the claim transaction open for the whole job also works (a crash releases the lock automatically), but it pins a connection and a snapshot per job, blocks vacuum from cleaning behind it and ties up `max_connections`. Prefer claim-then-commit with a lease.
  • Visibility trap: `SKIP LOCKED` gives an inconsistent view by design. Locked rows simply vanish from your result, so never use it for counts, existence checks or anything that needs a complete answer, and do not expect strict FIFO: a skipped older row will be processed after newer ones.
  • Index for the claim: a partial index such as `CREATE INDEX ON jobs (id) WHERE status = 'queued'` keeps the claim query fast no matter how many finished rows the table holds.
  • Bloat: every claim and every completion creates a dead row version, so a hot queue table churns quickly. Delete or archive finished rows (or drop old partitions), give the table aggressive autovacuum settings (`autovacuum_vacuum_scale_factor` near 0.01-0.05), and keep long transactions out of the database, since one old snapshot stops cleanup and the claim index fills with dead entries.
  • Know the limits: no consumer groups, replay, fan-out, backpressure or retention policy, and a high-throughput queue puts write load and WAL on your primary database. Past a few thousand jobs per second, or when multiple consumers must each see the stream, use a real broker.

Examples

Claim up to 3 jobs atomically. Run it from every worker; each gets a different set. Do the real work after this statement commits.

sql
CREATE TABLE jobs (
    id bigserial PRIMARY KEY,
    status text NOT NULL DEFAULT 'queued',
    payload jsonb NOT NULL,
    attempts int NOT NULL DEFAULT 0,
    locked_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_jobs_queued ON jobs (id) WHERE status = 'queued';
INSERT INTO jobs (payload)
SELECT jsonb_build_object('order_id', id) FROM orders LIMIT 10;

WITH next AS (
    SELECT id
    FROM jobs
    WHERE status = 'queued'
    ORDER BY id
    FOR UPDATE SKIP LOCKED
    LIMIT 3
)
UPDATE jobs j
SET status = 'running', attempts = j.attempts + 1, locked_at = now()
FROM next
WHERE j.id = next.id
RETURNING j.id, j.payload;

SKIP LOCKED hands B different rows without waiting; NOWAIT fails fast when it meets a locked row (SQLSTATE 55P03).

sql
-- skip-exec: needs two sessions
-- Session A:
BEGIN;
SELECT id FROM jobs WHERE status = 'queued' ORDER BY id
FOR UPDATE SKIP LOCKED LIMIT 2;          -- returns ids 1, 2

-- Session B, while A is still open:
SELECT id FROM jobs WHERE status = 'queued' ORDER BY id
FOR UPDATE SKIP LOCKED LIMIT 2;          -- returns ids 3, 4 immediately
-- NOWAIT fails fast when the first row is locked: ERROR:  could not obtain lock on row in relation "jobs"
SELECT id FROM jobs ORDER BY id
FOR UPDATE NOWAIT LIMIT 1;

Reaper for crashed workers plus a per-table autovacuum override for the churn. Run the reaper on a timer, not inside workers.

sql
CREATE TABLE jobs_demo (
    id bigserial PRIMARY KEY,
    status text NOT NULL DEFAULT 'queued',
    attempts int NOT NULL DEFAULT 0,
    locked_at timestamptz
);
ALTER TABLE jobs_demo SET (autovacuum_vacuum_scale_factor = 0.02);

-- reaper: requeue stalled jobs, park the ones that keep failing
UPDATE jobs_demo
SET status = CASE WHEN attempts >= 5 THEN 'dead' ELSE 'queued' END,
    locked_at = NULL
WHERE status = 'running' AND locked_at < now() - interval '10 minutes';

In practice

  • River, Oban, Graphile Worker — Job libraries (Go, Elixir, Node.js) that are built on PostgreSQL and fetch work with FOR UPDATE SKIP LOCKED.
  • Django, SQLAlchemy — Both expose it directly: select_for_update(skip_locked=True) and with_for_update(skip_locked=True), so ORM code can claim rows without raw SQL.
  • In practice — Transactional enqueueing is the real selling point: the job row commits atomically with the business change, which a separate broker cannot give you without an outbox.

Trade-offs

Pros

  • No extra infrastructure; the job is enqueued in the same transaction as the data it belongs to
  • Workers scale out without blocking each other, and failures are inspectable with plain SQL
  • Easy to add priorities, delayed jobs and dedupe with ordinary columns and indexes

Cons

  • Heavy insert/update/delete churn causes bloat and vacuum pressure on a hot table
  • No built-in fan-out, consumer groups, replay or backpressure
  • Skipped rows mean no strict ordering, and queue load shares CPU, I/O and WAL with your OLTP workload

Application-defined locks on a number you choose: session vs transaction level, try vs blocking, pooler pitfalls, and where they fit (cron dedupe, leader election).

The problem

Sometimes the thing you need to serialize is not a row: a nightly job that must run on one node, a per-tenant import, a migration runner. Advisory locks let the application lock an arbitrary number through PostgreSQL, with no table involved. The database enforces mutual exclusion but attaches no meaning to the key.

How it works

  • Key space: one `bigint`, or two `int4` values (useful as namespace + id, such as `(1001, tenant_id)`). The two forms are separate key spaces and never collide with each other. In `pg_locks` they appear with `locktype = 'advisory'`; a single bigint is split across `classid`/`objid` with `objsubid = 1`, a pair uses `objsubid = 2`.
  • `pg_advisory_lock(key)` blocks until it gets the lock; `pg_try_advisory_lock(key)` returns `true`/`false` immediately. Use the try form for cron dedupe and leader election, where losing means 'someone else is doing it, skip'.
  • Session-level locks (`pg_advisory_lock`, `pg_try_advisory_lock`) are held until you call `pg_advisory_unlock` or the session ends. They ignore transaction boundaries: `COMMIT` and `ROLLBACK` do not release them, and the same session can take the same key several times and must unlock the same number of times.
  • Transaction-level locks (`pg_advisory_xact_lock`, `pg_try_advisory_xact_lock`) release automatically at the end of the transaction and have no unlock function. Prefer them whenever the protected work fits in one transaction; they cannot leak.
  • Pooling hazard: with a transaction-mode pooler such as PgBouncer, consecutive statements may run on different server connections, so a session-level lock can be taken on one and never unlocked on another. The lock then leaks on a pooled connection until it is recycled. Use xact-level locks, or a dedicated direct connection that lives as long as the lock.
  • Leader election and cron dedupe: every instance runs `SELECT pg_try_advisory_lock(7001)` on a long-lived dedicated connection; the one that gets `true` is the leader and keeps that connection open. When it dies or loses its connection, PostgreSQL drops the lock and another instance wins on its next try.
  • Choosing keys: use fixed constants for global jobs, `(namespace, id)` pairs for per-entity locks, and keep a registry of namespaces in code. Hashing a string to a bigint works but can collide, and a collision silently serializes unrelated work.
  • Limits: advisory locks live in shared memory (the lock table is sized from `max_locks_per_transaction`) and are lost on failover or crash, since they are not replicated. A lock held on the old primary is gone on the new one, so two nodes can believe they lead. They are a convenience for a single database, not a Redlock-style guarantee across failover; add a fencing token if correctness depends on it.

Examples

Transaction-level try-lock with a (namespace, id) key. It disappears at COMMIT; got_it would be false if another session held the same key.

sql
BEGIN;
    SELECT pg_try_advisory_xact_lock(1001, 42) AS got_it;
    SELECT locktype, classid, objid, objsubid, mode, granted
    FROM pg_locks
    WHERE locktype = 'advisory' AND pid = pg_backend_pid();
COMMIT;

A session-level lock survives COMMIT (still_held is 1). Behind a transaction-mode pooler the matching unlock may land on another connection, which is how these locks leak.

sql
BEGIN;
    SELECT pg_advisory_lock(42);
COMMIT;

SELECT count(*) AS still_held
FROM pg_locks
WHERE locktype = 'advisory' AND pid = pg_backend_pid();

SELECT pg_advisory_unlock(42) AS released;

Leader election / cron dedupe. The lock belongs to the connection, so keep that connection open for as long as the instance is the leader.

sql
-- skip-exec: needs two sessions
-- Session A:
SELECT pg_try_advisory_lock(7001);   -- true: A is the leader

-- Session B:
SELECT pg_try_advisory_lock(7001);   -- false: skip the cron run

-- Session A disconnects (or calls pg_advisory_unlock(7001)); then in B the same call returns true: B takes over
SELECT pg_try_advisory_lock(7001);

In practice

  • Rails (ActiveRecord) — Takes a PostgreSQL advisory lock around migrations so two deploying nodes do not run them concurrently.
  • PgBouncer — Its feature compatibility notes list session-level advisory locks as unsupported in transaction pooling mode, for exactly the leak described above.
  • GoodJob — A Rails background-job library that uses advisory locks to ensure a job is run by one worker at a time.
  • In practice — Advisory locks are popular for 'only one instance runs this cron' and for per-tenant serialization, because they need no extra table or service; the failover caveat is what to remember.

Trade-offs

Pros

  • No table, no cleanup rows: the lock vanishes with the transaction or connection
  • try variants make skip-if-busy logic one line
  • Cheap and fast, since locks live in shared memory

Cons

  • Session-level locks leak or misbehave behind transaction poolers
  • Not replicated, so they give no mutual exclusion across a failover
  • Keys are bare numbers with no ownership or metadata, so collisions and forgotten namespaces are easy

Sessions that open a transaction and then sit idle hold locks and pin the vacuum horizon; find them and bound them with timeouts.

The problem

A session ran `BEGIN` (or an ORM did it for you), did some work and then went quiet without `COMMIT`. In `pg_stat_activity` it shows `idle in transaction`. It looks harmless because it uses no CPU, yet it keeps its row and table locks, and its snapshot stops vacuum from removing dead rows anywhere in the database.

How it works

  • State: `idle in transaction` means the transaction is open and the backend is waiting for the client's next command. `idle in transaction (aborted)` means a statement failed and the client has not issued `ROLLBACK`; the transaction can do nothing but roll back.
  • Locks: everything it locked stays locked until the transaction ends. A forgotten `UPDATE` holds its row locks; a forgotten `ALTER TABLE` or `LOCK TABLE` holds `ACCESS EXCLUSIVE` and blocks the whole table (see `lock-modes`).
  • Vacuum: an open transaction holds back the xmin horizon (`backend_xmin` in `pg_stat_activity`). Dead rows newer than that horizon cannot be removed by autovacuum in any table, so bloat grows everywhere. Old prepared transactions and stale replication slots hold the horizon the same way.
  • Typical causes: an application that opens a transaction, then calls a slow external API or waits for user input; an ORM or driver with autocommit off that never commits on an early-return path; a connection leaked to the pool still inside a transaction; a human running `BEGIN` in a console and going to lunch.
  • `idle_in_transaction_session_timeout` terminates a session that stays idle inside a transaction longer than the limit (the client gets `FATAL: terminating connection due to idle-in-transaction timeout`). Default 0 = disabled. Set it per role or database, for example 30-60s for OLTP applications.
  • `statement_timeout` bounds a single statement and `lock_timeout` bounds time spent waiting for locks. Neither fires while the session is idle between statements, which is why `idle_in_transaction_session_timeout` is needed as well. PG 17 adds `transaction_timeout` to cap the total duration of a transaction, whether idle or not.
  • Find offenders: filter `pg_stat_activity` on `state LIKE 'idle in transaction%'` and order by `now() - state_change` (how long idle) and `now() - xact_start` (how long the transaction is open). `backend_xmin` and `backend_xid` show who pins the horizon.
  • Kill with `pg_terminate_backend(pid)`; this closes the connection and rolls back its transaction. `pg_cancel_backend` only cancels a running statement and does nothing to an idle session. You need to be a superuser, a member of `pg_signal_backend`, or the same role.
  • Fix the source rather than only the symptom: keep transactions to the database work, pool-side limits (`HikariCP` leak detection, PgBouncer `idle_transaction_timeout`), and a `finally`/context-manager that always commits or rolls back.

Examples

Oldest open transactions first. query shows the last statement the session ran, which usually points at the code path that forgot to commit.

sql
SELECT pid,
       usename,
       application_name,
       state,
       now() - state_change AS idle_for,
       now() - xact_start AS xact_age,
       backend_xmin,
       left(query, 60) AS last_query
FROM pg_stat_activity
WHERE state LIKE 'idle in transaction%'
ORDER BY xact_start;

Role-level defaults apply to new connections only. Pick numbers from your longest legitimate transaction; migration and batch roles need their own, longer limits.

bash
# per-role and per-database defaults (new sessions pick them up)
psql -c "ALTER ROLE app_rw SET idle_in_transaction_session_timeout = '30s';"
psql -c "ALTER ROLE app_rw SET statement_timeout = '15s';"
psql -c "ALTER ROLE app_rw SET lock_timeout = '5s';"

# PG 17+: cap the whole transaction, idle or not
psql -c "ALTER ROLE app_rw SET transaction_timeout = '60s';"

Terminate sessions idle in a transaction for over 10 minutes in this database. Review the previous query's output first; this rolls their work back.

sql
SELECT pid,
       pg_terminate_backend(pid) AS terminated
FROM pg_stat_activity
WHERE datname = current_database()
  AND pid <> pg_backend_pid()
  AND state IN ('idle in transaction', 'idle in transaction (aborted)')
  AND now() - state_change > interval '10 minutes';

In practice

  • PgBouncer — Has its own idle_transaction_timeout setting (in seconds) that disconnects a client left idle inside a transaction; a pool-side backstop that also protects against drivers that leak.
  • HikariCP — The JDBC pool offers leakDetectionThreshold to log connections held for too long, a quick way to find code that forgets to return or commit.
  • In practice — A frequent cause of unexplained table bloat and of migrations that hang is one forgotten idle-in-transaction session, often from a debugging console or a crashed worker whose connection was never closed.

Trade-offs

Pros

  • A server-side timeout bounds the damage of any client bug, in any language
  • Per-role settings let strict limits apply to the app and looser ones to batch jobs
  • Terminating the session releases locks and the vacuum horizon immediately

Cons

  • The client loses its connection and transaction mid-flight and must reconnect and retry
  • A too-tight timeout kills legitimate workflows such as interactive admin sessions and slow multi-step batches
  • It cures the symptom; the leak or slow call inside the transaction is still in the code

Operations

Why one OS process per connection makes max_connections a capacity limit, and how pgbouncer pool modes trade features for fewer server connections.

The problem

Every PostgreSQL connection is a dedicated server process with its own memory, so a few thousand app connections can exhaust RAM and scheduler time long before the queries do any real work. Raising `max_connections` to absorb a connection storm usually makes the database slower, not faster. A pooler multiplexes many client connections onto a small, steady set of server connections.

How it works

  • Process-per-connection: each backend costs memory (catalog caches, per-query `work_mem`) and adds work to every snapshot and lock-table scan. Idle connections are not free.
  • `max_connections` (default 100, restart required) is a hard cap; `superuser_reserved_connections` keeps a few slots for admins. Size it from RAM and cores, not from the number of app instances.
  • More connections do not mean more throughput: once active queries exceed what the CPUs and disks can run, extra ones only queue and add contention. Throughput typically plateaus, then falls.
  • Rule of thumb (heuristic): active server connections near `cores * 2 + effective disk spindles`, a formula that the HikariCP pool-sizing guide attributes to the PostgreSQL project. Treat it as a starting point and load-test.
  • App-side pools (HikariCP, pgx, SQLAlchemy) reuse connections per process. With many app instances the total is `instances * pool_size`, which is what hits the server; this is why an external pooler helps.
  • pgbouncer `pool_mode = session`: a server connection is held for the whole client session. Safest, least multiplexing.
  • `pool_mode = transaction`: the server connection returns to the pool at `COMMIT` or `ROLLBACK`. Best multiplexing and the usual choice for web apps, but session state is not safe between transactions.
  • `pool_mode = statement`: returns after every statement, so multi-statement transactions are rejected. Rarely useful.
  • What breaks in transaction mode: session-level `SET` (use `SET LOCAL` inside a transaction), session advisory locks (`pg_advisory_lock`; use `pg_advisory_xact_lock`), `LISTEN`/`NOTIFY` (needs a long-lived session, connect to the server directly), temp tables and `WITH HOLD` cursors across transactions.
  • Prepared statements: before pgbouncer 1.21 server-side named prepared statements break in transaction mode. 1.21+ tracks protocol-level prepared statements when `max_prepared_statements` is above 0 (default 200 from 1.24); SQL-level `PREPARE` is still not tracked.

Examples

Minimal pgbouncer.ini: many clients, 20 server connections per database/user pair.

bash
cat > pgbouncer.ini <<'EOF'
[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 20
reserve_pool_size = 5
max_prepared_statements = 100
EOF

Ask pgbouncer itself: cl_waiting above 0 with high maxwait means the pool is too small or queries are slow.

bash
psql -h 127.0.0.1 -p 6432 -U pgbouncer -d pgbouncer -c "SHOW POOLS;"
psql -h 127.0.0.1 -p 6432 -U pgbouncer -d pgbouncer -c "SHOW STATS;"

How close to the cap are you, and who is holding the connections?

sql
SELECT current_setting('max_connections')::int AS max_connections,
       count(*) AS in_use,
       count(*) FILTER (WHERE state = 'active') AS active,
       count(*) FILTER (WHERE state = 'idle') AS idle,
       count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_tx
FROM pg_stat_activity
WHERE backend_type = 'client backend';

SELECT usename, application_name, state, count(*) AS connections
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY usename, application_name, state
ORDER BY connections DESC;

In practice

  • pgbouncer — The most common standalone pooler; lightweight and single-threaded per process. Admin console via the virtual `pgbouncer` database (`SHOW POOLS`, `SHOW STATS`).
  • PgCat and Odyssey — Alternative poolers; PgCat and Odyssey are both multi-threaded. Check each project's docs for prepared-statement support before switching.
  • In practice — A frequent cause of 'too many connections' is many app replicas each holding a pool of 20-50 connections that are mostly idle. Fix the total with a pooler, not by raising `max_connections`.

Trade-offs

Pros

  • Thousands of clients share tens of server connections, keeping memory and contention bounded.
  • Absorbs reconnect storms and lets you restart or fail over the database behind a stable endpoint.

Cons

  • Transaction mode removes session features: session `SET`, session advisory locks, `LISTEN`, temp tables.
  • One more hop and component to run, monitor and keep highly available.
  • A too-small pool shows up as client-side waiting rather than a database error, which is easy to misread as slow queries.

Streaming vs logical replication, synchronous commit, replication slots, hot standby conflicts, promotion and how to avoid split-brain.

The problem

One server is one point of failure and one place to run every read. Replication gives you a copy to fail over to and replicas to read from, but each choice (sync or async, physical or logical) decides how much data you can lose and how stale reads can be. Failover itself is the dangerous part: promoting the wrong node or leaving two primaries running corrupts the story of your data.

How it works

  • Physical streaming replication ships WAL to standbys that replay it byte for byte: the whole cluster, same major version, read-only (`hot_standby = on`). Asynchronous by default, so a crash of the primary can lose the last commits.
  • Synchronous commit: `synchronous_commit = on` plus `synchronous_standby_names` makes commit wait for the standby's WAL flush. `remote_write` waits for write to OS, `remote_apply` waits until replayed and visible to queries, which helps read-your-writes.
  • Quorum syntax: `synchronous_standby_names = 'ANY 1 (s1, s2)'` needs any one of the listed standbys; `'FIRST 1 (s1, s2)'` uses priority order. If no listed standby is reachable, commits block, so list at least two for availability.
  • Logical replication: publications on the source (`CREATE PUBLICATION`) and subscriptions on the target (`CREATE SUBSCRIPTION`); needs `wal_level = logical`. Works across major versions and for a subset of tables, and the target stays writable.
  • Not replicated logically: DDL (apply schema changes on both sides first, subscriber before publisher for added columns), sequence values (resync before cutover), large objects. `UPDATE` and `DELETE` need a primary key or `REPLICA IDENTITY`.
  • Replication slots keep WAL until the consumer has it. An abandoned or lagging slot retains WAL without limit and can fill the disk; watch `pg_replication_slots` and set `max_slot_wal_keep_size` (PG 13+) as a safety valve.
  • Hot standby query conflicts: replaying WAL may need to remove rows a standby query still reads. The standby either delays replay up to `max_standby_streaming_delay` (default 30s) or cancels the query with 'conflict with recovery'. `hot_standby_feedback = on` avoids most cancels but can bloat the primary.
  • Promotion: `pg_ctl promote` or `SELECT pg_promote()` on the standby (PG 12+ for the function). The old primary cannot simply rejoin; rebuild it or use `pg_rewind` after confirming it is stopped.
  • Failover tooling: Patroni (leader key in etcd, Consul or ZooKeeper), repmgr, or the managed-service failover of a cloud provider. Do not script promotion by hand under pressure.
  • Split-brain and fencing: two nodes accepting writes is worse than downtime. A safe design fences the old primary (stop it, cut its network or storage, or a watchdog) before promoting. Read-your-writes caveat: with async replicas a read right after a write can miss it, so route such reads to the primary or wait on LSN.

Examples

On the primary: per-standby lag and the WAL each replication slot is holding back.

sql
SELECT application_name, client_addr, state, sync_state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

SELECT slot_name, slot_type, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

On a standby: how far behind is replay, in time. Only meaningful if the primary is writing.

sql
-- skip-exec: only valid on a standby server
SELECT pg_is_in_recovery() AS is_standby,
       pg_last_wal_receive_lsn() AS received,
       pg_last_wal_replay_lsn() AS replayed,
       now() - pg_last_xact_replay_timestamp() AS replay_delay;

Logical replication of two tables, and a synchronous quorum of two standbys.

sql
-- skip-exec: needs wal_level = logical, a second server and a restart
-- On the source:
CREATE PUBLICATION pub_orders FOR TABLE orders, order_items;

-- On the target (tables must already exist with the same columns):
CREATE SUBSCRIPTION sub_orders
    CONNECTION 'host=primary.internal dbname=shop user=repl'
    PUBLICATION pub_orders;

-- Streaming replication: commit waits for any one of two standbys.
ALTER SYSTEM SET synchronous_standby_names = 'ANY 1 (standby_a, standby_b)';
SELECT pg_reload_conf();

Promote a standby by hand (Patroni or a managed service does this for you).

bash
psql -h "<standby-host>" -U postgres -c "SELECT pg_promote(wait => true, wait_seconds => 60);"
psql -h "<standby-host>" -U postgres -c "SELECT pg_is_in_recovery();"

In practice

  • Patroni — A template for HA clusters that keeps the leader key in a distributed store (etcd, Consul, ZooKeeper); the node that holds it is the primary, others follow.
  • repmgr — Replication manager that automates standby setup, monitoring and switchover/failover, with optional automatic failover through its daemon.
  • pg_rewind — Resynchronises a former primary with the new timeline so it can follow as a standby without a full base backup. Needs `wal_log_hints` or data checksums.
  • In practice — The usual outage after a failover is not the promotion but clients still pointing at the old primary and a forgotten slot filling the disk. Test failover regularly.

Trade-offs

Pros

  • A standby gives fast recovery and a place to run read-only queries and backups.
  • Synchronous or quorum commit can bring data loss on failover to zero committed transactions.
  • Logical replication allows zero-downtime major upgrades and selective table sync.

Cons

  • Async replication can lose recent commits and serve stale reads.
  • Synchronous commit adds network latency to every commit and can stall writes if standbys vanish.
  • Slots and `hot_standby_feedback` trade standby stability for disk usage and bloat on the primary.
  • Automated failover needs correct fencing; a bad setup produces split-brain.

Logical dumps vs physical base backups plus WAL archiving, how PITR replays to a target time, and why only a tested restore counts.

The problem

Replicas protect against hardware loss, not against `DROP TABLE`, a bad migration or corruption, because they faithfully copy the mistake. You need backups that let you go back in time, and a rehearsed procedure to restore them. A backup that was never restored is only a hope.

How it works

  • Logical backup: `pg_dump` writes one database as SQL or an archive (`-Fc` custom, `-Fd` directory with `-j` parallel jobs); `pg_restore` loads it. Portable across versions, but slow for big data and restores to the dump moment only.
  • Roles and tablespaces are cluster-wide and not in `pg_dump`: back them up with `pg_dumpall --globals-only` alongside.
  • Physical backup: `pg_basebackup` copies the data directory of the whole cluster while it runs (`-X stream` includes the WAL needed for consistency). Same major version, much faster to restore for large databases.
  • Continuous archiving: with `wal_level` at least `replica`, set `archive_mode = on` and an `archive_command` (or `archive_library` in PG 15+) that copies each finished WAL segment to durable storage. `archive_command` must return non-zero on failure and never overwrite an existing file.
  • Point-in-time recovery: restore a base backup, set `restore_command` to fetch archived WAL, set a target (`recovery_target_time`, `recovery_target_lsn`, `recovery_target_name`), and create an empty `recovery.signal` file (PG 12+; older versions used recovery.conf). The server replays WAL up to the target, then pauses or promotes per `recovery_target_action`.
  • Pick the target just before the mistake and check the data before promoting; once promoted the new timeline cannot be rewound. Recover on a separate machine and copy back only what you need.
  • Tools: pgBackRest and WAL-G manage base backups, incremental/differential backups, compression, parallelism, retention and archive verification. Barman is another option. Prefer them over hand-written scripts.
  • Retention: keep enough base backups plus continuous WAL to reach any point in your recovery window, and prune WAL only up to the oldest base backup you keep. Store copies off the primary host and in another failure domain.
  • Verify: restore regularly into a scratch server, run `pg_amcheck` or a few sanity queries, record how long it took (your real RTO), and alert when archiving falls behind (`pg_stat_archiver`).

Examples

Logical backups: one database in parallel directory format, plus the cluster-wide roles.

bash
pg_dump -h "<host>" -U "<user>" -Fd -j 4 -f "/backups/shop.dir" shop
pg_dumpall -h "<host>" -U "<user>" --globals-only > "/backups/globals.sql"
pg_restore -h "<scratch-host>" -U "<user>" -d shop_restored -j 4 "/backups/shop.dir"

postgresql.conf for continuous archiving; the test-then-copy form never overwrites a segment.

properties
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/wal_archive/%f && cp %p /mnt/wal_archive/%f'
archive_timeout = 300

Base backup, then PITR: restore into a fresh directory, set the target, signal recovery, start.

bash
pg_basebackup -h "<host>" -U "<repl-user>" -D "/backups/base" -X stream -c fast -P

# Restore: copy the base backup to a new, empty PGDATA, then:
cat >> "$PGDATA/postgresql.auto.conf" <<'CONF'
restore_command = 'cp /mnt/wal_archive/%f %p'
recovery_target_time = '2026-03-14 09:30:00+00'
recovery_target_action = 'pause'
CONF
touch "$PGDATA/recovery.signal"
pg_ctl -D "$PGDATA" start

In practice

  • pgBackRest — Parallel, incremental backups with retention, WAL archive management, backup verification and restore to a target time.
  • WAL-G — Archival and restoration tool that streams base backups and WAL to object storage with compression and encryption.
  • pg_verifybackup — Checks a `pg_basebackup` (plain format) against its backup manifest; available since PG 13. It does not replace a real restore test.
  • In practice — A common failure is an `archive_command` that has been failing for days while the WAL piles up on the primary. Alert on `pg_stat_archiver.failed_count` and `last_failed_time`.

Trade-offs

Pros

  • Base backup plus WAL archive restores to any moment in the window, including just before a mistaken `DELETE`.
  • `pg_dump` is portable across versions and can restore a single table or schema.

Cons

  • PITR restore time grows with the amount of WAL to replay.
  • Physical backups are not portable across major versions or architectures.
  • Archiving is another moving part; a broken archive silently erodes your recovery window.
  • `pg_dump` takes a consistent snapshot but runs long on big databases and holds back vacuum cleanup.

Which DDL takes ACCESS EXCLUSIVE and the patterns (NOT VALID, CONCURRENTLY, lock_timeout, batched backfill, expand/contract) that keep a live table available.

The problem

A migration that is trivial on a laptop can lock a production table for minutes. Many `ALTER TABLE` forms take `ACCESS EXCLUSIVE`, which blocks every read and write, and even a short one must wait behind running transactions while everything else waits behind it. The goal is to make each step either instant or non-blocking.

How it works

  • Know the lock: `ADD COLUMN`, `DROP COLUMN`, `RENAME`, `SET NOT NULL`, most `ALTER COLUMN TYPE` and plain `ADD CONSTRAINT` take `ACCESS EXCLUSIVE`. Plain `CREATE INDEX` takes `SHARE` and blocks writes. See the safe-ddl table.
  • Add a column that is nullable, or has a constant `DEFAULT`: metadata-only since PG 11, no rewrite. A volatile default (`random()`) still rewrites the table.
  • Constraints in two steps: `ADD CONSTRAINT ... NOT VALID` (new rows are checked, existing ones are not), then `VALIDATE CONSTRAINT`, which scans under a weaker lock that lets writes continue. Works for `CHECK` and `FOREIGN KEY`.
  • Indexes: `CREATE INDEX CONCURRENTLY` takes longer, cannot run in a transaction, and on failure leaves an `INVALID` index you must drop. `DROP INDEX CONCURRENTLY` and `REINDEX CONCURRENTLY` (PG 12+) are the matching forms.
  • `SET NOT NULL` trick (PG 12+): add `CHECK (col IS NOT NULL) NOT VALID`, validate it, run `SET NOT NULL` (the scan is skipped because the check proves it), then drop the check.
  • Backfill in batches: update by key range in short transactions (a few thousand rows), sleep between batches, and let vacuum keep up. One big `UPDATE` holds locks, bloats the table and floods replicas with WAL.
  • Rename and type changes use expand/contract: add the new column, dual-write from the app, backfill, switch reads, then drop the old column in a later release. Never rely on a rename that old and new code versions disagree on.
  • Always set `lock_timeout` (for example `2s`) in the migration session and retry on failure. It stops a waiting `ACCESS EXCLUSIVE` from stalling the whole app behind a long transaction. Check `pg_stat_activity` for old open transactions first.
  • Table rewrites (`ALTER COLUMN TYPE` that is not binary compatible, volatile defaults) lock the table for the whole copy and double the space needed temporarily. Prefer a new column. `ATTACH PARTITION` with a pre-validated `CHECK` is the online way to load bulk data.

Examples

Instant column adds and a foreign key added in two steps, all under a lock_timeout.

sql
SET lock_timeout = '3s';

-- 1. Nullable column or constant default: metadata-only on PG 11+.
ALTER TABLE orders ADD COLUMN currency text NOT NULL DEFAULT 'USD';
ALTER TABLE orders ADD COLUMN shipped_at timestamptz;

-- 2. Foreign key in two steps: ADD ... NOT VALID is quick, VALIDATE scans
--    the table while normal reads and writes continue.
CREATE TABLE refunds (
    id bigserial PRIMARY KEY,
    order_id bigint NOT NULL,
    amount numeric(12,2) NOT NULL
);
ALTER TABLE refunds
    ADD CONSTRAINT refunds_order_id_fkey
    FOREIGN KEY (order_id) REFERENCES orders (id) NOT VALID;
ALTER TABLE refunds VALIDATE CONSTRAINT refunds_order_id_fkey;

Concurrent index build, invalid-index check, and SET NOT NULL via a validated CHECK.

sql
-- Build the index without blocking writes (cannot run inside a transaction).
CREATE UNIQUE INDEX CONCURRENTLY idx_orders_user_created
    ON orders (user_id, created_at, id);

-- A failed concurrent build leaves an INVALID index: look for it, drop it, retry.
SELECT indexrelid::regclass AS index_name
FROM pg_index
WHERE NOT indisvalid;

-- SET NOT NULL is instant when a validated CHECK already proves it (PG 12+).
ALTER TABLE orders
    ADD CONSTRAINT orders_total_not_null CHECK (total IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_not_null;
ALTER TABLE orders ALTER COLUMN total SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_total_not_null;

One backfill batch; the application or a script repeats it over the next id range.

sql
ALTER TABLE orders ADD COLUMN total_cents bigint;

-- One batch: keyset range, short transaction. Repeat from the application
-- with the next id range until 0 rows are updated, pausing between batches.
UPDATE orders
SET total_cents = (total * 100)::bigint
WHERE id > 0
  AND id <= 5000
  AND total_cents IS NULL;

-- Progress check.
SELECT count(*) FILTER (WHERE total_cents IS NULL) AS remaining
FROM orders;

In practice

  • squawk — A linter for PostgreSQL migrations that flags risky statements (blocking index creation, adding NOT NULL, type changes) in CI.
  • pg_repack — Rebuilds a table or its indexes online using a trigger-based copy, useful for bloat removal without a long exclusive lock.
  • Migration tools — Flyway, Liquibase, Alembic, Rails and others wrap each migration in a transaction by default; `CREATE INDEX CONCURRENTLY` needs that wrapper turned off for the migration.
  • In practice — A typical outage is a harmless-looking `ALTER TABLE` queued behind an hours-old open transaction, with all later queries stacking up behind it. `lock_timeout` plus retry avoids it.

Trade-offs

Pros

  • Each step is short or non-blocking, so deploys need no maintenance window.
  • Expand/contract lets old and new application versions run side by side and makes rollbacks safe.

Cons

  • A single change becomes several deploys and more code (dual writes, backfill jobs, cleanup).
  • `CONCURRENTLY` builds are slower, need two table passes and can fail leaving invalid indexes.
  • `NOT VALID` constraints are not guaranteed for old rows until validated; the planner cannot use them for optimisation until then.

Declarative RANGE, LIST and HASH partitions, partition pruning, retention by detaching partitions, and when partitioning does not help.

The problem

A table with hundreds of millions of rows makes retention deletes slow, vacuum and index maintenance heavy, and every query scan more than it needs to. Declarative partitioning splits one logical table into physical child tables so queries touch only the relevant ones and old data can be dropped as a unit. It helps specific workloads and hurts others.

How it works

  • Declare the strategy on the parent: `PARTITION BY RANGE (col)` (time series, id ranges), `LIST (col)` (tenant, region) or `HASH (col)` with `MODULUS`/`REMAINDER` (even spread, no natural key).
  • Partition pruning: when the `WHERE` clause constrains the partition key, the planner skips other partitions (plan time) and, for parameters, the executor skips them at run time. `EXPLAIN` shows only the surviving partitions; with `enable_partition_pruning = on` (default).
  • The partition key must be part of every `PRIMARY KEY` and `UNIQUE` constraint, because uniqueness is enforced per partition. A global unique id on its own is not possible; use `(id, created_at)`.
  • Indexes: an index created on the parent becomes one index per partition and is created automatically for new partitions. `CREATE INDEX CONCURRENTLY` is not supported on the parent; use `CREATE INDEX ... ON ONLY`, build each partition's index concurrently, then attach them with `ALTER INDEX ... ATTACH PARTITION`.
  • A `DEFAULT` partition catches rows that fit no other partition. It avoids insert errors but scanning it is needed when adding a partition, and it can silently fill up if you forget to create new ones.
  • Retention: `DROP TABLE` on an old partition is far faster than a huge `DELETE` and leaves no bloat (it takes `ACCESS EXCLUSIVE` on the parent, briefly). To keep the data, `ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY` (PG 14+) needs only `SHARE UPDATE EXCLUSIVE` on the parent; it cannot run inside a transaction block and is not allowed when the table has a default partition.
  • Loading and creating partitions: create the table separately with a matching `CHECK`, then `ATTACH PARTITION` so the validation scan is skipped. Automate future partitions with `pg_partman` or a scheduled job; running out of partitions is a classic outage.
  • When not to partition: queries that do not filter on the key still scan every partition and pay more planning cost; small tables gain nothing; foreign keys referencing a partitioned table, cross-partition updates and unique constraints get awkward. Partitioning is not a general speed-up.
  • Too many partitions slow planning and use more memory per query and per session. The docs say a few thousand are handled fairly well when queries prune to a small number; planning cost and memory grow with the partitions that survive pruning. Prefer fewer, larger partitions that still let you drop or vacuum one quickly.

Examples

Range partitions by month with a default partition; the plan should touch only the January partition.

sql
CREATE TABLE measurements (
    id bigint GENERATED ALWAYS AS IDENTITY,
    device_id int NOT NULL,
    recorded_at timestamptz NOT NULL,
    value double precision,
    PRIMARY KEY (id, recorded_at)
) PARTITION BY RANGE (recorded_at);

CREATE TABLE measurements_2026_01 PARTITION OF measurements
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE measurements_2026_02 PARTITION OF measurements
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE measurements_default PARTITION OF measurements DEFAULT;

-- Created on the parent: every partition gets its own index.
CREATE INDEX idx_measurements_device ON measurements (device_id, recorded_at);

INSERT INTO measurements (device_id, recorded_at, value)
SELECT g % 50, timestamptz '2026-01-01' + g * interval '1 minute', random()
FROM generate_series(1, 60000) AS g;
ANALYZE measurements;

-- Only the January partition should appear in the plan.
EXPLAIN (COSTS OFF)
SELECT count(*) FROM measurements
WHERE recorded_at >= '2026-01-10' AND recorded_at < '2026-01-11';

Attach a prepared partition without a validation scan, then retire an old one.

sql
CREATE TABLE logs (
    id bigint GENERATED ALWAYS AS IDENTITY,
    logged_at timestamptz NOT NULL,
    message text,
    PRIMARY KEY (id, logged_at)
) PARTITION BY RANGE (logged_at);

CREATE TABLE logs_2026_01 PARTITION OF logs
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

-- Prepare next month offline, then attach: the CHECK lets ATTACH skip the scan.
CREATE TABLE logs_2026_02 (LIKE logs INCLUDING DEFAULTS INCLUDING INDEXES);
ALTER TABLE logs_2026_02 ADD CONSTRAINT logs_2026_02_range
    CHECK (logged_at >= '2026-02-01' AND logged_at < '2026-03-01');
ALTER TABLE logs ATTACH PARTITION logs_2026_02
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
ALTER TABLE logs_2026_02 DROP CONSTRAINT logs_2026_02_range;

-- Retention: detach without blocking readers (PG 14+), then drop or archive.
ALTER TABLE logs DETACH PARTITION logs_2026_01 CONCURRENTLY;
DROP TABLE logs_2026_01;

In practice

  • pg_partman — Extension that creates and maintains time-based and number-based partition sets, adding new child tables and applying a retention policy, with an optional background worker to run maintenance.
  • TimescaleDB — An extension built on PostgreSQL that manages time-based chunks automatically, if you want that without writing the partition lifecycle yourself.
  • Citus — Distributes tables across nodes by a distribution column, a sharding approach rather than single-server partitioning.
  • In practice — The usual first reason to partition is cheap retention (drop a partition instead of deleting millions of rows), not query speed.

Trade-offs

Pros

  • Pruning avoids scanning irrelevant data when queries filter on the key.
  • Retention becomes a metadata operation: drop or detach a partition.
  • Vacuum, reindex and backfills work on smaller units.

Cons

  • Primary and unique keys must include the partition key.
  • Queries without the key touch every partition and plan slower.
  • Partition lifecycle (creating future partitions) is now your job.
  • Many partitions raise planning time and memory use.

Troubleshooting

Find the culprit with pg_stat_statements and pg_stat_activity, read EXPLAIN (ANALYZE, BUFFERS), then branch: missing index, stale stats, spilling sort, N+1 or a lock wait.

The problem

An endpoint got slow, the database CPU is climbing, or someone pastes a query and asks why it takes eight seconds. Tuning the wrong query is the usual time sink, so the order matters: find which statements cost the most in total, look at what they do right now, read the actual plan, and only then pick the branch (index, statistics, memory, application pattern, or a lock).

How it works

  • Symptom: p95 latency rose, one query runs for seconds, CPU or disk reads are high, or sessions pile up in `pg_stat_activity`. First decide whether it is one slow statement, many medium ones, or waiting (locks, IO) rather than computing.
  • First look: `pg_stat_statements` (needs `shared_preload_libraries` plus `CREATE EXTENSION`) ordered by `total_exec_time` for the biggest consumers, by `mean_exec_time` for individually slow ones, and by `calls` for chatty ones. Then `pg_stat_activity` for what is running now: `state`, `wait_event_type`, `wait_event`, `now() - query_start`. Then `EXPLAIN (ANALYZE, BUFFERS)` on the suspect with real parameter values.
  • Common causes: Plan shape. A `Seq Scan` on a big table with a selective filter points to a missing index, or to one the planner cannot use (function on the column, implicit cast, leading `%` in `LIKE`). Estimated rows far from actual rows (`rows=1` versus `actual rows=50000`) points to stale or missing statistics, correlated columns, or skewed data; run `ANALYZE`, raise the statistics target, or add extended statistics.
  • Common causes: Memory and IO. `Sort Method: external merge Disk: ...` or `Hash Batches` above 1 mean the operation spilled to temp files because `work_mem` is too small for it; `Buffers: shared read` far above `hit` means the data is not cached and the query is IO bound. Check `temp_blks_written` in `pg_stat_statements` for spillers.
  • Common causes: Pattern and waiting. Thousands of calls at 1 ms each (the N+1 pattern) show up as huge `calls` with a small `mean_exec_time`; fix them in the application with joins or batching. A query that is fast in `EXPLAIN` but slow in production is often waiting: `wait_event_type = 'Lock'` sends you to the lock-wait playbook.
  • Fix: apply the smallest change that matches the evidence: create the index (`CREATE INDEX CONCURRENTLY`), `ANALYZE` the table, rewrite the predicate so it is sargable, raise `work_mem` for that role or transaction with `SET LOCAL` (it is per sort/hash node, per session), or batch the calls. Change one thing, then re-measure.
  • Verify: rerun `EXPLAIN (ANALYZE, BUFFERS)` and compare execution time, rows estimate versus actual, and buffer counts; then watch `mean_exec_time` of the same `queryid` after `pg_stat_statements_reset()` or over a fresh window, not just one lucky run.
  • Prevent: set `log_min_duration_statement` to log slow statements, load `auto_explain` (`auto_explain.log_min_duration`) to capture plans of slow runs in the log, keep `pg_stat_statements` installed everywhere, and review the top queries after every release. Index changes should be tested against production-sized data.

Examples

Top statements by total time (swap the ORDER BY for mean_exec_time or calls).

sql
-- skip-exec: needs pg_stat_statements in shared_preload_libraries
SELECT queryid,
    calls,
    round(total_exec_time::numeric, 1) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    rows,
    shared_blks_read,
    temp_blks_written,
    left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

What is running right now and for how long, with what it waits on.

sql
SELECT pid,
    now() - query_start AS runtime,
    state,
    wait_event_type,
    wait_event,
    left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
    AND backend_type = 'client backend'
    AND pid <> pg_backend_pid()
ORDER BY runtime DESC;

Read the plan, add the index the filter and sort need, refresh stats, compare.

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

CREATE INDEX idx_orders_user_id_created_at ON orders (user_id, created_at DESC);
ANALYZE orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

postgresql.conf: log slow statements and capture plans of slow runs.

properties
log_min_duration_statement = 500ms
shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = '1s'
auto_explain.log_analyze = off
# log_analyze = on adds actual rows and timings (and enables log_buffers),
# but instruments every statement, not only slow ones; see log_timing.

In practice

  • pg_stat_statements — The contrib extension that aggregates statistics per normalized statement. Loading it needs `shared_preload_libraries` and a restart, which is why it is best installed before you need it.
  • auto_explain — Contrib module that logs the plan of statements slower than a threshold. With `auto_explain.log_analyze` on, per-node timing runs for every statement, not only the slow ones, which can hurt performance badly; `auto_explain.log_timing = off` reduces the cost. Enable with care on busy systems.
  • pgBadger — Parses PostgreSQL logs into a report of slowest and most frequent queries; it works best when `log_min_duration_statement` and a structured `log_line_prefix` are set.
  • HypoPG — Lets you create hypothetical indexes and see whether the planner would use them in plain `EXPLAIN`, before paying for a real build.

Trade-offs

Pros

  • Ordering by total time targets the statements that actually cost the most, not just the ones that look scary.
  • Every step uses built-in views or contrib modules, so the playbook works on any self-managed server.

Cons

  • `pg_stat_statements` needs a restart to load (`auto_explain` is usually preloaded too, though it can also be loaded per session), so they are often missing during the incident.
  • `EXPLAIN ANALYZE` really executes the statement; on writes, wrap it in a transaction you roll back.
  • A plan captured with different parameters or cold caches can mislead.

Detect dead tuples and wasted space, find what holds back the xmin horizon, then tune autovacuum, vacuum, or rewrite online with pg_repack or REINDEX CONCURRENTLY.

The problem

A table or index is far larger than its live data, scans got slower, disk usage keeps growing, and `autovacuum` seems to run but does not shrink anything. Bloat is dead space that plain VACUUM can reuse but rarely returns to the operating system, so the work is to find why vacuum cannot clean up, then to decide how to reclaim the space.

How it works

  • Symptom: relation size grows faster than row count, sequential scans and index scans touch more pages than expected, `n_dead_tup` is large, `last_autovacuum` is old or null, or vacuum runs for hours and finishes with little reclaimed.
  • First look: `pg_stat_user_tables` for `n_live_tup`, `n_dead_tup`, the dead ratio, `last_autovacuum` and `autovacuum_count`. For measured rather than estimated numbers use the `pgstattuple` extension (`pgstattuple`, `pgstattuple_approx`, `pgstatindex`). While a vacuum runs, `pg_stat_progress_vacuum` shows its phase and progress.
  • Common causes: xmin held back. Vacuum can only remove tuples older than the oldest snapshot anyone still needs. Check for a long-running or `idle in transaction` session (`backend_xmin`, `xact_start`), an abandoned prepared transaction in `pg_prepared_xacts`, a replication slot with an old `xmin` or `catalog_xmin`, and a standby with `hot_standby_feedback = on` running long queries.
  • Common causes: Vacuum too slow. Autovacuum triggers at `autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples` (defaults 50 and 0.2), so a huge table waits for a huge number of dead rows. It can also be throttled by the cost limit (`autovacuum_vacuum_cost_limit`, `autovacuum_vacuum_cost_delay`), starved by too few `autovacuum_max_workers`, or repeatedly cancelled by conflicting locks.
  • Common causes: Workload. Heavy `UPDATE` or `DELETE` churn, update patterns that defeat HOT updates (indexed columns changing, no room because of `fillfactor = 100`), or one big bulk delete that leaves a lot of free space inside pages.
  • Fix: first remove the blocker (end the old transaction, drop or advance the slot, resolve the prepared transaction). Then tune that table with `ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = ..., autovacuum_vacuum_cost_limit = ...)` and run `VACUUM (VERBOSE)` by hand. Plain VACUUM makes space reusable inside the file; it only truncates empty pages at the end. To give space back, use `REINDEX INDEX CONCURRENTLY` (PG 12+) for a bloated index and `pg_repack` (extension plus client) to rebuild a bloated table online; `VACUUM FULL` is the last resort: it rewrites the table under `ACCESS EXCLUSIVE`, blocking reads and writes, and needs free disk for the new copy.
  • Verify: `n_dead_tup` drops and `last_autovacuum` advances after the blocker is gone; `pgstattuple` shows lower `dead_tuple_percent` and `free_percent`; `pg_total_relation_size` shrinks after a rewrite. Bloat that returns within days means the cause was not fixed.
  • Prevent: per-table autovacuum settings for large and hot tables, alerts on the age of the oldest transaction and on slot `xmin`, `idle_in_transaction_session_timeout`, a lower `fillfactor` on update-heavy tables, and partitioning so old data is dropped instead of deleted.

Examples

Dead-tuple ratio and autovacuum history per table.

sql
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,
    last_autoanalyze,
    autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Measured bloat with pgstattuple (contrib; scans the table, so use the approx variant on big ones).

sql
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT table_len, tuple_percent, dead_tuple_percent, free_percent
FROM pgstattuple('orders');

SELECT approx_tuple_percent, dead_tuple_percent, approx_free_percent
FROM pgstattuple_approx('orders');

SELECT avg_leaf_density, leaf_fragmentation
FROM pgstatindex('orders_pkey');

What holds the xmin horizon back: sessions, prepared transactions, slots.

sql
SELECT pid, usename, state,
    age(backend_xmin) AS xmin_age,
    now() - xact_start AS xact_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

SELECT gid, prepared, owner, age(transaction) AS xid_age
FROM pg_prepared_xacts
ORDER BY prepared;

SELECT slot_name, slot_type, active,
    age(xmin) AS xmin_age,
    age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots;

Tune one hot table, vacuum it by hand, then rebuild a bloated index without blocking writes.

sql
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_vacuum_cost_limit = 1000
);

VACUUM (VERBOSE, ANALYZE) orders;

REINDEX INDEX CONCURRENTLY orders_pkey;

In practice

  • pgstattuple — A contrib extension that reports dead tuples and free space exactly by reading the relation. `pgstattuple_approx` trades precision for a much cheaper scan and `pgstatindex` covers B-tree density.
  • pg_repack — Rebuilds a table and its indexes online using a trigger-based log table; it holds `ACCESS EXCLUSIVE` only briefly during setup and the final swap. It needs the extension installed, a primary key or a unique index on a NOT NULL column, a superuser or the table owner, and free disk of roughly twice the table and its indexes.
  • In practice — Many teams lower `autovacuum_vacuum_scale_factor` on the few largest tables instead of changing it globally, since the global default suits small tables well.

Trade-offs

Pros

  • Finding and removing the xmin blocker fixes the cause, so every later vacuum becomes effective again.
  • `REINDEX CONCURRENTLY` and `pg_repack` shrink objects without a long outage.

Cons

  • Aggressive per-table autovacuum uses more IO and can compete with foreground traffic.
  • `pg_repack` and `REINDEX CONCURRENTLY` need extra disk and take longer than their blocking counterparts.
  • `VACUUM FULL` is simple but takes `ACCESS EXCLUSIVE` for the whole rewrite.

Queries hang: find who blocks whom with pg_blocking_pids, locate the root blocker, choose cancel or terminate, and prevent it with timeouts and migration discipline.

The problem

Requests hang, the connection pool fills with waiting sessions, and CPU is suspiciously low. Almost always a few sessions are waiting for a lock that one other session holds, often from a transaction that is doing nothing at all. The job is to find the root of the waiting chain, decide whether it is safe to stop it, and make sure it cannot happen quietly again.

How it works

  • Symptom: queries run far longer than usual with `wait_event_type = 'Lock'`, an `UPDATE` or `ALTER TABLE` never returns, the pool is exhausted, and the database is otherwise idle. One waiting `ACCESS EXCLUSIVE` request can also queue every later reader behind it.
  • First look: `pg_stat_activity` filtered by `cardinality(pg_blocking_pids(pid)) > 0` gives each waiting session and the pids it waits for. Follow the blockers until you reach sessions that wait for nobody: those are the root blockers. `pg_locks` shows the lock `mode` and `granted` flag if you need the exact relation or row.
  • Common causes: Idle in transaction. An application opened a transaction, took row locks, then went off to do other work, crashed, or leaked the connection. The root blocker shows `state = 'idle in transaction'` and an old `xact_start`.
  • Common causes: DDL. A migration waiting for `ACCESS EXCLUSIVE` behind a long query makes every new query on that table queue behind the migration; a long `ALTER TABLE` that rewrites the table holds the lock the whole time.
  • Common causes: Forgotten state. A prepared transaction (`PREPARE TRANSACTION`) that nobody committed keeps its locks across restarts and shows in `pg_prepared_xacts`; also look for a manual `LOCK TABLE`, a long `SELECT ... FOR UPDATE`, or an advisory lock held by a stuck worker.
  • Fix: `pg_cancel_backend(pid)` cancels the current query but leaves the session and its transaction open, which is often not enough for an idle transaction. `pg_terminate_backend(pid)` ends the whole session and rolls back its transaction. Prefer cancel first, terminate the root blocker when it is idle in transaction or cancel does not work, and for a prepared transaction use `ROLLBACK PREPARED 'gid'` or `COMMIT PREPARED 'gid'`.
  • Verify: rerun the blocker query and expect zero rows; waiting sessions finish on their own; check the application logs for the error the terminated session produced, since its work was rolled back and may need a retry.
  • Prevent: `lock_timeout` for migrations and `statement_timeout` for app roles, `idle_in_transaction_session_timeout` to cap forgotten transactions, `log_lock_waits = on` to log waits longer than `deadlock_timeout`, short transactions, and migration discipline: retry with a short `lock_timeout`, avoid table rewrites, build indexes `CONCURRENTLY`.

Examples

Every waiting session with the pids that block it.

sql
SELECT a.pid AS blocked_pid,
    pg_blocking_pids(a.pid) AS blocked_by,
    now() - a.query_start AS waiting_for,
    a.wait_event_type,
    left(a.query, 60) AS query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY waiting_for DESC;

Root blockers (they wait for nobody) with how many sessions they hold up, plus forgotten prepared transactions.

sql
WITH waiting AS (
    SELECT pid, unnest(pg_blocking_pids(pid)) AS blocker
    FROM pg_stat_activity
    WHERE cardinality(pg_blocking_pids(pid)) > 0
)
SELECT b.pid AS root_blocker,
    b.usename,
    b.state,
    now() - b.xact_start AS xact_age,
    count(*) AS blocked_sessions,
    left(b.query, 60) AS last_query
FROM waiting w
JOIN pg_stat_activity b ON b.pid = w.blocker
WHERE cardinality(pg_blocking_pids(b.pid)) = 0
GROUP BY b.pid, b.usename, b.state, b.xact_start, b.query
ORDER BY blocked_sessions DESC;

SELECT gid, prepared, owner, database
FROM pg_prepared_xacts
ORDER BY prepared;

Cancel first; terminate only a confirmed culprit (here: idle in transaction for over 10 minutes).

sql
-- Step 1: cancel the running query of one pid (replace with the root blocker)
SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE pid = 0;

-- Step 2: end sessions stuck idle in transaction for a long time
SELECT pid, pg_terminate_backend(pid) AS terminated
FROM pg_stat_activity
WHERE state = 'idle in transaction'
    AND now() - state_change > interval '10 minutes'
    AND pid <> pg_backend_pid();

A migration that gives up quickly instead of queueing the whole table behind it.

sql
BEGIN;
SET LOCAL lock_timeout = '3s';
SET LOCAL statement_timeout = '30s';
ALTER TABLE orders ADD COLUMN note text;
COMMIT;

SET idle_in_transaction_session_timeout = '60s';
SHOW idle_in_transaction_session_timeout;

In practice

  • pg_blocking_pids() — Built-in function (PG 9.6+) that returns the pids blocking a session, including those that only queue ahead of it for a lock, so it already accounts for lock queues.
  • pgbouncer — Transaction pooling keeps server connections busy only inside a transaction, so a leaked idle transaction still pins one server connection and its locks.
  • In practice — Migration tools commonly set `lock_timeout` and retry the DDL a few times; this trades a failed attempt for never stalling production traffic.

Trade-offs

Pros

  • The root-blocker query turns a vague hang into one pid and one decision.
  • Timeouts convert silent pile-ups into fast, visible errors that retry logic can handle.

Cons

  • Terminating a session rolls back its work and can surface errors in the application.
  • A too-short `statement_timeout` also kills legitimate long jobs, so set it per role.
  • `idle_in_transaction_session_timeout` can break clients that hold transactions open on purpose.

Measure lag in bytes and time on primary and standby, then separate network, standby IO, replay conflicts, big transactions and unconsumed slots.

The problem

Reads from a replica return stale data, a failover would lose recent writes, or monitoring shows the standby falling behind. Lag is not one number: WAL may be slow to arrive, slow to be written, or slow to be replayed, and each points to a different cause. Measure all three stages before changing anything.

How it works

  • Symptom: stale reads on replicas, growing `replay_lag`, a standby that never catches up after a spike, or a primary whose `pg_wal` keeps growing because a standby or slot is not consuming.
  • First look: on the primary, `pg_stat_replication` gives `sent_lsn`, `write_lsn`, `flush_lsn`, `replay_lsn` and the interval columns `write_lag`, `flush_lag`, `replay_lag`. `pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)` converts the gap into bytes. Compare the stages: sent far ahead of write is network or standby receive; flush ahead of replay is a replay bottleneck. On the standby, `pg_last_xact_replay_timestamp()` is the time the last replayed transaction committed (or aborted) on the primary, and `pg_last_wal_receive_lsn()` against `pg_last_wal_replay_lsn()` shows received-but-unreplayed WAL. On a primary with no writes the replay timestamp stops advancing, so it can look like lag when there is none.
  • Common causes: Resources and network. Standby IO or CPU saturation, storage slower than the primary, or a slow or lossy network link; check `write_lag` and `flush_lag` first, because they rise when WAL arrives or persists slowly.
  • Common causes: Replay. Recovery replays WAL in a single process, so a burst of writes, a huge transaction, an index build or a big `VACUUM` can outrun it. Long queries on a hot standby conflict with replay and replay waits up to `max_standby_streaming_delay` (default 30s) before cancelling them; with `hot_standby_feedback = on` most cancellations caused by cleanup records are avoided (lock and buffer-pin conflicts can still occur) but the primary retains dead rows.
  • Common causes: Slots. A physical slot whose standby is down keeps retaining WAL on the primary (see the disk-full playbook); a standby that fell too far behind may no longer find the WAL it needs and has to be rebuilt or restored from archive.
  • Fix: remove the cause you measured: add IO or CPU to the standby, shorten or move long reporting queries to a dedicated replica, tune `max_standby_streaming_delay` or `hot_standby_feedback` knowingly, spread big transactions into batches, fix the network, and drop or revive a dead slot. Check `pg_stat_database_conflicts` on the standby for cancelled queries.
  • Verify: `replay_lag` and the byte gap fall steadily and stay low under normal traffic; `pg_stat_replication.state` is `streaming`; the standby sees recent data when you write a marker row on the primary.
  • Prevent: alert on byte lag and on time lag separately (and on slot retained WAL), keep standby hardware equal to the primary, put heavy analytics on a replica configured for it, batch bulk writes, and rehearse failover so lag limits are a known number.

Examples

On the primary: lag per standby in bytes and as intervals.

sql
SELECT application_name,
    client_addr,
    state,
    sync_state,
    pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS sent_gap_bytes,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_gap_bytes,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication
ORDER BY replay_gap_bytes DESC;

On the primary: WAL each slot still holds back, and whether it is at risk.

sql
SELECT slot_name,
    slot_type,
    active,
    wal_status,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC NULLS LAST;

On the standby: replay delay, backlog and cancelled queries.

sql
-- skip-exec: needs a standby
SELECT pg_is_in_recovery() AS in_recovery,
    now() - pg_last_xact_replay_timestamp() AS replay_delay,
    pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS unreplayed_bytes,
    pg_get_wal_replay_pause_state() AS pause_state;

SELECT datname, confl_snapshot, confl_lock, confl_bufferpin
FROM pg_stat_database_conflicts
WHERE datname = current_database();

Standby settings that decide who wins, replay or a long query.

properties
# on the standby (postgresql.conf)
hot_standby = on
max_standby_streaming_delay = 30s
max_standby_archive_delay = 30s
hot_standby_feedback = off

In practice

  • Patroni — Cluster manager that watches replica lag and can keep a lagging standby from being promoted; its `maximum_lag_on_failover` setting limits how far behind a failover candidate may be, measured in bytes.
  • pg_stat_replication — The primary-side view with `write_lag`, `flush_lag` and `replay_lag` (PG 10+). The interval columns are NULL when the standby is idle and nothing was recently written.
  • In practice — Reporting queries on a standby are a frequent source of both lag and 'canceling statement due to conflict with recovery' errors; a dedicated reporting replica avoids tuning the failover standby around them.

Trade-offs

Pros

  • Splitting lag into write, flush and replay stages narrows the cause before any change.
  • Byte lag works even when the primary is idle, unlike timestamp-based lag.

Cons

  • Time-based lag from `now() - pg_last_xact_replay_timestamp()` is misleading on an idle primary.
  • `hot_standby_feedback` protects standby queries but can bloat the primary.
  • Raising `max_standby_streaming_delay` makes replay wait, so lag grows while queries finish.

When the disk fills or pg_wal explodes: check replication slots, archive_command, checkpoints, temp files and bloat, and never delete WAL files by hand.

The problem

The data volume is filling up, the log says `No space left on device`, or the server PANICs because it cannot write WAL. The biggest consumers are usually the WAL directory, temporary files, and bloated relations, and the WAL case is the dangerous one because the instinct to delete files by hand corrupts the cluster.

How it works

  • Symptom: `df` shows the volume nearly full, `pg_wal` is far larger than `max_wal_size` suggests, writes fail with `No space left on device`, or the server crashes with a PANIC on a WAL write. Find out first which directory grew: `pg_wal`, `base` (data and temp files), or logs.
  • First look: `du -sh` on `pg_wal`, `base` and the log directory; `pg_ls_waldir()` for the WAL file count and size; `pg_replication_slots` (`active`, `wal_status`, `restart_lsn`, `safe_wal_size`); `pg_stat_archiver` (`failed_count`, `last_failed_wal`); `pg_stat_database` (`temp_files`, `temp_bytes`).
  • Common causes: Slots. An inactive or lagging replication slot makes the primary retain every WAL segment since its `restart_lsn`; this is the most common reason `pg_wal` grows without bound. `max_slot_wal_keep_size` (PG 13+, default `-1` meaning unlimited) caps this. `wal_status` is `reserved` while the WAL is within `max_wal_size`, `extended` beyond that but still retained, `unreserved` when the slot no longer holds the WAL it needs (typically with a cap set), and `lost` once the slot is unusable.
  • Common causes: Archiving and checkpoints. A failing `archive_command` (or `archive_library`) means segments are kept until they can be archived, so the directory grows while `failed_count` rises. Very large `max_wal_size` plus long checkpoints, or a large `wal_keep_size` on the primary, also retain more WAL than expected.
  • Common causes: Other files. Runaway sorts and hash joins writing temp files (`log_temp_files` shows each one; `temp_bytes` accumulates), table and index bloat, an unrotated server log, and a huge single transaction or bulk load that generates a lot of WAL.
  • Fix: free space safely first (remove logs or old backups outside the data directory, extend the volume). Then fix the cause: repair the archive target, bring the standby back, or drop an abandoned slot with `pg_drop_replication_slot()` only after confirming that nothing needs it. Never delete or move files in `pg_wal` by hand; WAL that is still needed for crash recovery or a replica cannot be recreated.
  • Verify: `pg_wal` shrinks after the next checkpoints, `wal_status` returns to `reserved`, `failed_count` stops rising, and free disk space stays stable over several `checkpoint_timeout` cycles.
  • Prevent: monitor free space and alert before 80 percent, alert on slot retained WAL and `failed_count`, set `max_slot_wal_keep_size` on primaries with slots so a dead consumer cannot fill the disk (accepting that the slot can then be invalidated), set `log_temp_files` and a sensible `temp_file_limit`, rotate logs, and keep `pg_wal` on a volume with headroom.

Examples

Slots, their health and how much WAL each one pins.

sql
SELECT slot_name,
    slot_type,
    active,
    wal_status,
    pg_size_pretty(safe_wal_size) AS safe_wal_size,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC NULLS LAST;

Is archiving failing, and how are the WAL limits set?

sql
SELECT archived_count, failed_count,
    last_archived_wal, last_failed_wal, last_failed_time
FROM pg_stat_archiver;

SELECT name, setting, unit
FROM pg_settings
WHERE name IN ('archive_mode', 'max_wal_size', 'wal_keep_size',
    'max_slot_wal_keep_size', 'checkpoint_timeout')
ORDER BY name;

WAL directory size and temp-file volume per database.

sql
SELECT count(*) AS wal_files,
    pg_size_pretty(sum(size)) AS wal_size
FROM pg_ls_waldir();

SELECT datname,
    temp_files,
    pg_size_pretty(temp_bytes) AS temp_size
FROM pg_stat_database
WHERE datname IS NOT NULL
ORDER BY temp_bytes DESC;

Drop a slot only after confirming its consumer is gone for good (replace the name).

sql
-- skip-exec: needs an existing replication slot
SELECT slot_name, active, wal_status
FROM pg_replication_slots
WHERE slot_name = 'old_standby_slot';

SELECT pg_drop_replication_slot('old_standby_slot');

In practice

  • pgBackRest — Backup tool whose `archive-push` is a common `archive_command`; a repository that is full or unreachable makes archiving fail and WAL pile up, so alert on repository space too.
  • Patroni — Creates physical replication slots for cluster members by default (`use_slots`); a slot whose replica is down keeps WAL on the primary, so alert on retained WAL and consider `max_slot_wal_keep_size`.
  • In practice — `pg_resetwal` exists for last-resort recovery of a cluster that cannot start; it can lose data and is not a way to free space, and it is not a substitute for monitoring.

Trade-offs

Pros

  • Checking slots and archiver first finds the most common WAL cause in two queries.
  • `max_slot_wal_keep_size` turns an outage of the primary into, at worst, a lost standby that can be rebuilt.

Cons

  • Dropping a slot or hitting `max_slot_wal_keep_size` forces the affected standby or logical consumer to be rebuilt.
  • Keeping extra WAL for slow standbys costs disk, and capping it costs resilience; there is no free setting.
  • Freeing space under pressure tempts risky shortcuts; the safe options are slower.