PostgreSQL Internals: MVCC, WAL, Locking, and Reading Query Plans Like the Planner

PostgreSQL keeps old row versions in the table itself, makes changes durable through a write-ahead log before data files change, and picks plans from sampled statistics. Bloat, lock queues and a plan that was fine yesterday all follow from those three facts. This guide inspects each mechanism on a live PostgreSQL 17 server and ends with a full EXPLAIN (ANALYZE, BUFFERS) walkthrough.

Lab setup

Use a disposable instance. pageinspect needs superuser, and pg_walinspect needs superuser or pg_read_server_files. All transaction IDs, LSNs and timings shown are illustrative.

CREATE EXTENSION IF NOT EXISTS pageinspect;
CREATE EXTENSION IF NOT EXISTS pg_walinspect;

CREATE TABLE acct (id int PRIMARY KEY, balance numeric NOT NULL);
INSERT INTO acct VALUES (1, 100), (2, 200);

Tuple versioning: xmin, xmax and ctid

An UPDATE never overwrites a row. It writes a new tuple and marks the old one as superseded. Three header fields are exposed as hidden columns:

• xmin: the transaction that created this version.
• xmax: the transaction that deleted, superseded or locked it. Zero means none has.
• ctid: the physical location (block, line pointer). It changes on every update, so never store it.

UPDATE acct SET balance = 150 WHERE id = 1;

SELECT lp, t_xmin, t_xmax, t_ctid, raw_flags
FROM heap_page_items(get_raw_page('acct', 0)) AS p,
     LATERAL heap_tuple_infomask_flags(p.t_infomask, p.t_infomask2);
-- lp | t_xmin | t_xmax | t_ctid | raw_flags
--  1 |   1041 |   1042 | (0,3)  | {HEAP_XMIN_COMMITTED,HEAP_HOT_UPDATED,...}
--  2 |   1041 |      0 | (0,2)  | {HEAP_XMIN_COMMITTED,HEAP_XMAX_INVALID,...}
--  3 |   1042 |      0 | (0,3)  | {HEAP_UPDATED,HEAP_ONLY_TUPLE,HEAP_XMAX_INVALID,...}

Line pointer 1 is the old version: its t_xmax names the updater and its t_ctid forwards to the new tuple. heap_page_items skips visibility filtering, so both appear.

A non-zero xmax does not always mean deletion. SELECT ... FOR UPDATE writes the locker's ID there with a lock-only flag, several lockers share a multixact ID, and a rolled-back update also leaves xmax set. Visibility consults commit status, not the mere presence of the value.

Commit status and hint bits

Tuples store only transaction IDs; outcomes live in the commit log (pg_xact). The first reader to resolve a tuple caches the answer as hint bits such as HEAP_XMIN_COMMITTED. So a SELECT can dirty pages, which is why dirtied= appears in buffer statistics after a bulk load. With data checksums or wal_log_hints, the first hint-bit change after a checkpoint can also write a full-page image, so reads can generate WAL.

Visibility and snapshots

A snapshot records which transactions count as finished. pg_current_snapshot() prints it as xmin:xmax:xip_list:

SELECT pg_current_snapshot();
--  1043:1045:1043

• First number: the oldest transaction still in progress. Everything below it has finished.
• Second number: one past the highest completed transaction. Everything at or above it counts as in progress.
• The list: transactions between those bounds still running at snapshot time.

This snapshot treats 1043 as invisible and 1044 as finished. Simplified, a tuple is visible when both of these hold:

1. Its xmin committed and is finished according to the snapshot, or it is the current transaction's own earlier command.
2. Its xmax is unset, aborted, lock-only, in progress according to the snapshot, or the current transaction's own later command.

This explains why readers never block writers: a reader never waits on a row, it computes which version it should see. Read-only transactions get only a virtual ID, so pg_current_xact_id_if_assigned() returns NULL until the first write and read traffic does not consume XIDs.

Isolation levels are snapshot policies

READ UNCOMMITTED is accepted but behaves as READ COMMITTED, since no snapshot can expose an uncommitted tuple.

The Read Committed re-check deserves attention. A blocked UPDATE does not restart when the other transaction commits. It follows t_ctid to the new version and re-evaluates the qualifier on that row only, a mechanism called EvalPlanQual. Other rows are still judged by the original snapshot, so one statement can act on a mix of versions.

Write skew and SSI

Snapshot isolation does not stop two transactions from reading overlapping data and writing disjoint rows:

-- oncall(doctor, active) starts with two active doctors.
-- Run in two sessions, each updating a different doctor.
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM oncall WHERE active;      -- both see 2
UPDATE oncall SET active = false WHERE doctor = 'a';
COMMIT;
-- One session fails, often at COMMIT:
-- ERROR:  could not serialize access due to read/write dependencies among transactions

At Repeatable Read both commit and nobody remains on call. Serializable tracks reads with SIReadLock entries in pg_locks, which block nothing and only feed dependency detection. The cost is operational: any transaction may fail with 40001, so the application must retry the whole transaction, including the reads behind its decisions. Long transactions also raise the abort rate. Reports can use SERIALIZABLE READ ONLY DEFERRABLE, which waits for a safe snapshot and then cannot fail this way.

Why bloat happens

Space is reclaimed only once no snapshot can see the old version, so bloat is the rate of dead tuple creation combined with whatever delays cleanup.

HOT updates and pruning

When an update changes no indexed column and the new version fits on the same page, it is a heap-only tuple (HOT) update. No index entries are added, and any later page access can prune the chain without VACUUM. A non-HOT update adds an entry to every index, including those on unchanged columns. The usual causes of lost HOT updates are an index on a frequently updated column and full pages under the default fillfactor of 100. Compare n_tup_upd, n_tup_hot_upd and n_tup_newpage_upd in pg_stat_user_tables.

The xmin horizon

VACUUM removes a tuple only if it is dead to every possible snapshot, so one old snapshot stalls cleanup across the database. Autovacuum then does expensive work and removes little. VACUUM (VERBOSE) shows the symptom:

tuples: 0 removed, 120000 remain, 80000 are dead but not yet removable
removable cutoff: 1043, which was 52110 XIDs old when operation ended

Four things hold the horizon back. Check all of them, not just sessions:

SELECT 'backend' AS source, pid::text AS id, age(backend_xmin) AS xmin_age, state AS detail
FROM pg_stat_activity WHERE backend_xmin IS NOT NULL
UNION ALL
SELECT 'slot', slot_name::text, age(xmin), active::text
FROM pg_replication_slots WHERE xmin IS NOT NULL
UNION ALL
SELECT 'prepared', gid, age(transaction), owner::text
FROM pg_prepared_xacts
UNION ALL
SELECT 'standby', application_name, age(backend_xmin), state
FROM pg_stat_replication WHERE backend_xmin IS NOT NULL
ORDER BY xmin_age DESC NULLS LAST;

Typical culprits are a session stuck idle in transaction, an abandoned slot, a forgotten prepared transaction, and a standby with hot_standby_feedback = on running a long report. That setting is a trade-off: on, standby query cancellations become primary bloat; off, standby queries get cancelled on conflicts. Set idle_in_transaction_session_timeout and, new in PostgreSQL 17, transaction_timeout per role, because a global limit can kill legitimate maintenance.

What VACUUM does not do

Plain VACUUM marks space reusable but normally does not return it to the operating system, except by truncating empty pages at the end of the file. A table that bloated once stays that size until rewritten by VACUUM FULL, which blocks all access, or an online tool such as the third-party pg_repack. Autovacuum triggers at a threshold plus autovacuum_vacuum_scale_factor times the row count, so the default of 0.2 lets large tables gather millions of dead tuples before a run. Per-table storage parameters are the usual fix.

Freezing and wraparound

Transaction IDs are 32 bits and compared on a circle, so old tuples must be frozen. An aggressive autovacuum is forced once table age passes autovacuum_freeze_max_age. The horizon blocks freezing too, so a stuck slot, left long enough, ends with the server refusing new XIDs. Monitor age(datfrozenxid) in pg_database.

WAL and checkpoints

A change must be durable in the log before the data page holding it reaches disk. Commit forces the log, not the data files, which turns random data writes into sequential log writes. After a crash, recovery replays WAL from the last checkpoint's redo point.

Measuring WAL for a statement

SELECT pg_current_wal_lsn() AS before_lsn \gset
UPDATE acct SET balance = balance + 1;

SELECT resource_manager, count, record_size, fpi_size
FROM pg_get_wal_stats(:'before_lsn'::pg_lsn, pg_current_wal_lsn(), true)
WHERE count > 0
ORDER BY combined_size DESC;
-- resource_manager   | count | record_size | fpi_size
-- Heap/HOT_UPDATE    |     2 |         146 |     8056
-- Transaction/COMMIT |     1 |          34 |        0

Full-page images

A power failure can tear an 8 kB page write, leaving a page that WAL deltas cannot repair. With full_page_writes = on, the first change to each page after a checkpoint logs the whole page, which is the large fpi_size above. Later changes to that page log only deltas until the next checkpoint.

This couples checkpoint frequency to WAL volume. Short intervals mean more full-page images, and WAL volume jumps right after each checkpoint. Random-key inserts such as UUIDv4 primary keys touch more distinct pages per interval than sequential keys. wal_compression shrinks the images at some CPU cost.

Checkpoint trade-offs

A checkpoint writes and syncs dirty buffers and advances the redo point. One starts when checkpoint_timeout elapses or WAL approaches max_wal_size, and checkpoint_completion_target spreads the writes.

PostgreSQL 17 moved checkpoint counters out of pg_stat_bgwriter. Requested checkpoints outnumbering timed ones usually mean max_wal_size is too small:

SELECT num_timed, num_requested, buffers_written, write_time, sync_time
FROM pg_stat_checkpointer;

Failure mode: a full pg_wal stops the server with a PANIC. max_wal_size is a soft limit, and a failing archive_command or inactive replication slot retains WAL without bound. Check pg_stat_archiver.failed_count and wal_status in pg_replication_slots. max_slot_wal_keep_size caps retention, at the cost of invalidating a slot that falls too far behind.

Lock modes, queues and deadlocks

MVCC removes reader-writer conflicts on rows, but anything touching a table still takes a heavyweight lock until the transaction ends.

Row locks live in the tuple's xmax, not shared memory, so pg_locks shows them only through the waiter, which waits on the holder's transactionid. Foreign key checks take FOR KEY SHARE on the parent, and updates to non-key columns take FOR NO KEY UPDATE, so they do not block child inserts.

The lock queue trap

Lock requests queue, and a waiter blocks later conflicting requests. A one-line ALTER TABLE waiting behind a slow report therefore blocks every new SELECT, which looks like an outage caused by a harmless migration. Always bound the wait and retry:

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

Diagnosing blocking

SELECT a.pid, a.state, a.wait_event_type, a.wait_event,
       pg_blocking_pids(a.pid) AS blocked_by, left(a.query, 60) AS query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0;

SELECT pid, locktype, mode, relation::regclass, transactionid, now() - waitstart AS waiting
FROM pg_locks
WHERE NOT granted;

A blocker in state idle in transaction does nothing while holding everything, and its query column shows only its last statement. pg_cancel_backend has no effect on it, so use pg_terminate_backend. pg_blocking_pids briefly needs exclusive access to lock manager state, so do not poll it in a tight loop.

Deadlocks

-- Session A
BEGIN; UPDATE acct SET balance = balance - 1 WHERE id = 1;
-- Session B
BEGIN; UPDATE acct SET balance = balance - 1 WHERE id = 2;
UPDATE acct SET balance = balance + 1 WHERE id = 1;   -- blocks on A
-- Session A
UPDATE acct SET balance = balance + 1 WHERE id = 2;   -- closes the cycle

-- ERROR:  deadlock detected
-- DETAIL:  Process 4211 waits for ShareLock on transaction 1903; blocked by process 4188.
-- Process 4188 waits for ShareLock on transaction 1904; blocked by process 4211.

Each waiter sleeps for deadlock_timeout, then checks for a cycle, and the session that finds it aborts, so the victim is unpredictable. The server log has the statements. Lowering the timeout only detects deadlocks sooner. The fix is consistent ordering, taking locks by key first:

SELECT id FROM acct WHERE id IN (1, 2) ORDER BY id FOR NO KEY UPDATE;

A plain UPDATE ... WHERE id IN (1, 2) gives no order guarantee, because order depends on the plan. Deadlocks use SQLSTATE 40P01 and should be retried like serialization failures.

Planner statistics

The planner knows only what ANALYZE samples: 300 times default_statistics_target rows, 30,000 by default, summarized in pg_stats. Row estimates come from the density reltuples / relpages, multiplied by the current physical size and the predicate selectivity.

• Equality on a common value uses its entry in most_common_freqs.
• Equality on any other value spreads the remaining frequency across the remaining distinct values.
• Ranges interpolate within histogram_bounds, whose buckets hold equal row counts.
• correlation measures how closely physical order follows logical order, which drives plain index scan cost.

Correlated columns

By default selectivities are multiplied as if columns were independent. That underestimates, the dangerous direction, because it favours nested loops:

SET max_parallel_workers_per_gather = 0;
CREATE TABLE addr AS
SELECT i AS id, i % 100 AS city_id, i % 100 AS region_id
FROM generate_series(1, 1000000) AS i;
ANALYZE addr;

EXPLAIN SELECT * FROM addr WHERE city_id = 7 AND region_id = 7;
--  Seq Scan on addr  (cost=0.00..20406.00 rows=100 width=12)     -- true count: 10000

CREATE STATISTICS addr_city_region (dependencies, mcv) ON city_id, region_id FROM addr;
ANALYZE addr;

EXPLAIN SELECT * FROM addr WHERE city_id = 7 AND region_id = 7;
--  Seq Scan on addr  (cost=0.00..20406.00 rows=10000 width=12)

Extended statistics help predicates on one table, not join selectivity across tables. Other estimate killers are values beyond the histogram, such as today's date on a table analyzed last night, and expressions such as created_at::date, which get default selectivities unless an expression index or expression statistics exist.

EXPLAIN (ANALYZE, BUFFERS) walkthrough

The dataset has 5 million orders spread evenly over two years, 2% pending, with one customer in 25 on the gold tier.

CREATE TABLE customers (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    country char(2) NOT NULL, tier text NOT NULL
);
CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers,
    status text NOT NULL, created_at timestamptz NOT NULL, total_cents int NOT NULL
);
INSERT INTO customers (country, tier)
SELECT (ARRAY['DE','NL','US','FR'])[1 + i % 4],
       CASE WHEN i % 25 = 0 THEN 'gold' ELSE 'std' END
FROM generate_series(1, 100000) AS i;

INSERT INTO orders (customer_id, status, created_at, total_cents)
SELECT 1 + (random() * 99999)::bigint,
       CASE WHEN random() < 0.02 THEN 'pending' ELSE 'shipped' END,
       now() - random() * interval '730 days', (random() * 50000)::int
FROM generate_series(1, 5000000);

CREATE INDEX orders_created_at_idx ON orders (created_at);
VACUUM (ANALYZE) customers, orders;

SET max_parallel_workers_per_gather = 0;
SET track_io_timing = on;

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.country, count(*), sum(o.total_cents)
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= now() - interval '7 days'
  AND o.status = 'pending' AND c.tier = 'gold'
GROUP BY c.country;

Illustrative output from a cold cache, wrapped for width:

HashAggregate  (cost=45011.96..45012.00 rows=4 width=19)
               (actual time=1906.611..1906.615 rows=4 loops=1)
  Group Key: c.country
  Batches: 1  Memory Usage: 37kB
  Buffers: shared hit=415 read=28302
  I/O Timings: shared read=1775.029
  ->  Hash Join  (cost=43113.68..45011.10 rows=38 width=7)
                 (actual time=1885.312..1906.502 rows=41 loops=1)
        Hash Cond: (c.id = o.customer_id)
        Buffers: shared hit=415 read=28302
        ->  Seq Scan on customers c  (cost=0.00..1887.00 rows=3987 width=11)
                                     (actual time=0.021..19.733 rows=4000 loops=1)
              Filter: (tier = 'gold'::text)
              Rows Removed by Filter: 96000
              Buffers: shared read=637
        ->  Hash  (cost=43101.77..43101.77 rows=953 width=12)
                  (actual time=1884.902..1884.903 rows=962 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 50kB
              Buffers: shared hit=415 read=27665
              ->  Bitmap Heap Scan on orders o
                    (cost=918.38..43101.77 rows=953 width=12)
                    (actual time=33.118..1884.207 rows=962 loops=1)
                    Recheck Cond: (created_at >= (now() - '7 days'::interval))
                    Filter: (status = 'pending'::text)
                    Rows Removed by Filter: 46981
                    Heap Blocks: exact=27941
                    Buffers: shared hit=415 read=27665
                    I/O Timings: shared read=1766.118
                    ->  Bitmap Index Scan on orders_created_at_idx
                          (cost=0.00..918.14 rows=47810 width=0)
                          (actual time=31.402..31.402 rows=47943 loops=1)
                          Index Cond: (created_at >= (now() - '7 days'::interval))
                          Buffers: shared hit=3 read=136
Planning Time: 1.204 ms
Execution Time: 1907.244 ms

Reading it step by step

1. Start at the deepest node. The bitmap index scan collects row locations, the heap scan fetches them, the hash stores them, and the scan of customers probes the hash.
2. Compare estimates with actuals. 47,810 against 47,943, 953 against 962, 38 against 41. The planner was right, so this plan is well chosen and still slow. It needs a physical design change, not statistics.
3. Check the arithmetic. 953 is 47,810 times the pending frequency. The join estimate is 953 times 3,987 divided by 100,000 distinct customers, which gives 38.
4. Find the time. Parents include children. The heap scan took 1,884 of 1,907 ms, and 1,766 ms of that was reads.
5. Read the waste. Rows Removed by Filter shows 98% of fetched rows were discarded, and nearly 28,000 pages were visited to return 962 rows.
6. Do the buffer accounting. Buffers include children: 139 index pages plus 27,941 heap pages make 28,080, which matches 415 + 27665. Adding 637 for customers gives the top-node 28,717, about 224 MB.

A bitmap is used because created_at values landed in random heap positions, so correlation is near zero. The bitmap sorts page visits and reads each page once. A second run reports all shared hit and is far faster, so never compare runs without reading the buffer lines.

Acting on the plan

The filter is doing work the index should do, and a partial index matches the access pattern:

CREATE INDEX CONCURRENTLY orders_pending_created_idx
    ON orders (created_at) WHERE status = 'pending';

With data this scattered, expect the heap scan to fall from roughly 28,000 buffers to under 1,000, with the Filter line gone. The costs: status updates now lose HOT, because status appears in an index predicate, and only queries that imply status = 'pending' can use the index.

Signals in plan output

EXPLAIN ANALYZE executes the statement, triggers included. Wrap data-changing statements in BEGIN and ROLLBACK; the locks, WAL and dead tuples are still real. Result sets are not sent to the client, so a query that is slow only in the application may need the PostgreSQL 17 SERIALIZE option. From PostgreSQL 18, ANALYZE includes buffers by default.

Verifying it works

1. Versioning: update one row and confirm heap_page_items shows the old t_ctid pointing at the new tuple.
2. Snapshots: hold a Repeatable Read transaction open after one SELECT, update a row from a second session, and confirm the first still sees the old value. Updating that row from the first session must fail with 40001.
3. Horizon: with that transaction open, delete rows and run VACUUM (VERBOSE). Confirm "dead but not yet removable", then commit and repeat.
4. WAL: run CHECKPOINT, update one row twice, and confirm with pg_get_wal_records_info that only the first update carries a full-page image.
5. Locks: reproduce the deadlock, find its statements in the server log, and confirm the blocking query reports a held row lock.
6. Plans: run the walkthrough twice and explain every difference from the buffer lines alone.
7. Recurring checks: alert on these.

SELECT max(now() - xact_start) FROM pg_stat_activity WHERE state <> 'idle';

SELECT slot_name, active, wal_status,
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes
FROM pg_replication_slots;

SELECT max(age(datfrozenxid)) FROM pg_database;