MVCC, Vacuum and the Postgres Bargain
MVCC with tuple versions in the heap, snapshots and the visibility rules, why UPDATE is a delete plus an insert, bloat and how to measure and fix it, VACUUM lazy and full with the freeze map, autovacuum tuning, transaction ID wraparound, and the long transactions and replication slots that block vacuum.
Snapshots, visibility and the cost of UPDATE
InnoDB updates rows in place and keeps old versions in undo logs. Postgres writes a new tuple version into the heap and leaves the old one where it was, stamped with the deleting txid. Readers pick the version their snapshot can see. That makes rollback instant and reads lock-free, and makes cleanup (vacuum) a permanent part of the engine.
-- two sessions, watch versions appear BEGIN; UPDATE accounts SET balance_kobo = balance_kobo + 100 WHERE id = 1; SELECT ctid, xmin, xmax, balance_kobo FROM accounts WHERE id = 1; -- new ctid, xmin = my txid -- in another session, before COMMIT: the old version (old ctid) is still what it sees COMMIT;
Bloat, VACUUM and autovacuum
-- dead tuples and the last time autovacuum ran
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;
-- an estimate of bloat: pgstattuple gives exact numbers (and reads the whole table)
CREATE EXTENSION pgstattuple;
SELECT * FROM pgstattuple('accounts'); -- dead_tuple_percent, free_percent
-- autovacuum triggers when dead tuples > threshold + scale_factor × rows
-- defaults: 50 + 0.2 × rows → a 100M-row table waits for 20M dead tuples
ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_cost_limit = 2000);| operation | what it does | lock |
|---|---|---|
VACUUM | removes dead tuples, makes space reusable (does not shrink files except trailing empty pages), updates FSM and VM, freezes | SHARE UPDATE EXCLUSIVE (reads and writes continue) |
VACUUM FULL | rewrites the table compactly, returning space to the OS | ACCESS EXCLUSIVE (blocks everything) |
pg_repack | rebuilds online with a trigger-copied shadow table | brief exclusive lock at the swap |
ANALYZE | refreshes planner statistics | light |
The most common production failure is autovacuum that cannot keep up: defaults tuned for small tables, cost limits throttling it, or something holding back the xmin horizon so vacuum cannot remove anything at all.
Wraparound, and what blocks vacuum
-- what holds the xmin horizon back (vacuum cannot remove tuples newer than this) SELECT 'backend' AS kind, pid::text, backend_xmin, now() - xact_start AS age FROM pg_stat_activity WHERE backend_xmin IS NOT NULL UNION ALL SELECT 'slot', slot_name, xmin, NULL FROM pg_replication_slots WHERE xmin IS NOT NULL OR catalog_xmin IS NOT NULL UNION ALL SELECT 'prepared', gid, transaction, now() - prepared FROM pg_prepared_xacts ORDER BY 3 NULLS LAST;
Three things block vacuum: a long-running or idle-in-transaction session (set idle_in_transaction_session_timeout), an inactive replication slot (and hot_standby_feedback from a replica running long queries), and forgotten prepared transactions. Alert on age(datfrozenxid) above about 500 million and on the oldest transaction age.