Commit log, hint bits and subtransactions

For DBAs and operators

pg_xact

Status values: 00 in progress, 01 committed, 10 aborted, 11 sub-committed (a short intermediate state, used only while a commit whose subtransactions lie on other pg_xact pages is being recorded; a finished subtransaction otherwise stays 00 until its top-level transaction ends). The file is accessed through an SLRU cache in shared memory sized by transaction_buffers (PostgreSQL 17+, default 0 = auto, scaled from shared_buffers; wait events LWLock:XactSLRU, XactBuffer). Segments are 256 kB (32 pages, about 1 million transactions) named 0000, 0001, … and are truncated after datfrozenxid advances, see wraparound. Commit status is written to WAL as the commit record; pg_xact pages themselves are flushed at checkpoints. See SLRU caches and pg_stat_slru.

Hint bits

Infomask bits HEAP_XMIN_COMMITTED, HEAP_XMIN_INVALID, HEAP_XMAX_COMMITTED, HEAP_XMAX_INVALID cache the commit-log answer. They are set by any reader, even on a standby, and only once the transaction is known to be finished (and, for commits, its commit record flushed). Setting them marks the buffer dirty but is not WAL-logged by itself, unless data_checksums or wal_log_hints is on: then the first such change after a checkpoint writes a full-page image, because a torn page would otherwise fail its checksum. PostgreSQL 18 initdb enables checksums by default (--no-data-checksums to opt out). Measured on PostgreSQL 18.6 with checksums on, 200,000 freshly loaded rows, after a CHECKPOINT:

EXPLAIN (ANALYZE, BUFFERS, WAL) SELECT count(*) FROM hb;
  Buffers: shared hit=885 dirtied=885
  WAL: records=885 fpi=885 bytes=7264605 buffers full=380
-- the same query again:
  Buffers: shared hit=885

So the first read of new data costs a page write (and here 7 MB of WAL); a VACUUM after bulk loads sets the hint bits and the visibility map in one go.

Subtransactions

A subtransaction gets its own XID only when it writes. Its parent is recorded in pg_subtrans (another SLRU, subtransaction_buffers, PostgreSQL 17+). Each backend's PGPROC entry caches up to 64 subtransaction XIDs, so snapshot code can check "is this XID running?" from shared memory alone. With a 65th, the cache is marked overflowed and every snapshot taken by any session while that transaction is open must map unknown XIDs to their top-level parent via pg_subtrans. On busy systems this shows as many sessions waiting on LWLock:SubtransSLRU / SubtransBuffer; standbys are affected in the same way.

-- PostgreSQL 16+: per-backend subxact count and overflow flag
SELECT a.pid, s.subxact_count, s.subxact_overflowed, left(a.query, 50)
  FROM pg_stat_get_backend_idset() b
  JOIN pg_stat_activity a ON a.pid = pg_stat_get_backend_pid(b),
       pg_stat_get_backend_subxact(b) s
 WHERE s.subxact_count > 0 OR s.subxact_overflowed;

Checked on 18.6: 64 savepoints with writes show 64 | f, 65 show 64 | t. Common sources: ORMs or drivers that wrap every statement in a savepoint (for example "autosave" settings), PL/pgSQL loops with an EXCEPTION block, and long batch transactions. Avoid them in hot paths, or keep such transactions short. Very long transactions with subxacts also make pg_subtrans lookups go to disk.

Multixacts (shared row locks) have their own pair of SLRUs, pg_multixact/offsets and members, with the same kind of contention and wraparound concerns.