PostgreSQL Vacuum Is Concurrency Control, Not Housekeeping

Aug 31

By

postgresqldatabase engineeringmvccreliability

An UPDATE in PostgreSQL usually does not overwrite a row. It creates a new tuple version and leaves the old version behind until no active snapshot can need it. DELETE similarly marks a version obsolete without immediately removing its storage.

That behavior is the foundation of MVCC: readers and writers avoid blocking one another because they can observe different versions. Vacuum is the process that eventually proves an old version is no longer visible to anybody and makes its space reusable.

The invariant is:

A tuple may be removed only after every transaction that could legally observe it has ended, and every surviving tuple must remain comparable across transaction-ID wraparound.

The cleanup horizon is global

Suppose transaction 100 begins a report and holds its snapshot. Transactions 101 through 500 update the same account rows. Those old tuple versions cannot be removed if transaction 100 might still see them.

T100: snapshot opens ────────────────────────────────┐
T101..T500: updates create dead versions            │
VACUUM: sees versions, but cannot remove them        │
T100: commits ───────────────────────────────────────┘
next VACUUM: cleanup can progress

The long transaction may be “only reading,” yet it expands storage, index work, cache pressure, replica WAL, and future vacuum cost. Idle transactions are worse: they retain a snapshot while delivering no product value.

Old prepared transactions and replication slots can also hold horizons. A logical slot’s catalog_xmin protects catalog rows required to decode its stream. Dropping an abandoned slot may release cleanup, but a real consumer may then require rebuilding. This is an operational decision, not a routine delete.

Autovacuum thresholds are workload policy

Autovacuum decides whether a table needs vacuuming using a base threshold plus a scale factor derived from table size. A large table can therefore accumulate many dead tuples before the percentage threshold is crossed. A small, intensely updated table may need much more frequent vacuuming than global defaults provide.

Tune per table when the workload demands it:

ALTER TABLE job_queue SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 500,
  autovacuum_analyze_scale_factor = 0.02
);

Lower thresholds trade background I/O for a smaller dead-tuple backlog. Increasing workers without checking I/O capacity can turn maintenance into foreground latency. The target is not “vacuum constantly”; it is “complete useful cleanup faster than the workload creates debt.”

Vacuum and vacuum full solve different problems

Ordinary VACUUM marks space reusable inside the relation and can run alongside normal reads and writes. It generally does not return the file’s space to the operating system. VACUUM FULL rewrites the table into a compact file and requires an ACCESS EXCLUSIVE lock.

If the only plan for bloat is regular VACUUM FULL, the maintenance policy has already failed. Prefer preventing the backlog, controlling transaction horizons, and scheduling rewrites only when returning disk space justifies the lock and rewrite cost.

Transaction IDs create a safety deadline

PostgreSQL’s internal xid is 32 bits and wraps after roughly four billion assignments. Visibility comparisons treat about two billion IDs as the past and two billion as the future. A tuple left with an ancient normal XID can eventually appear to belong to the future.

Vacuum freezes sufficiently old tuple versions so they remain visible regardless of future XID movement. PostgreSQL tracks the oldest remaining unfrozen XID through relfrozenxid and datfrozenxid. Anti-wraparound vacuum is therefore correctness work, not storage optimization.

Near exhaustion, PostgreSQL protects data by refusing commands that assign new XIDs. The database becoming effectively read-only is a safety mechanism after maintenance has been ignored.

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

SELECT relname, n_live_tup, n_dead_tup,
       last_autovacuum, autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

What to monitor

  • oldest backend_xmin and transaction age;
  • oldest prepared transaction;
  • replication-slot xmin, catalog_xmin, and retained WAL;
  • age(datfrozenxid) and per-table age(relfrozenxid);
  • dead tuples created versus removed per interval;
  • autovacuum duration, cancellations, and worker saturation;
  • table and index growth relative to live rows;
  • commit-to-replica lag during heavy maintenance.

An alert on disk percentage alone arrives too late and explains too little.

The CTO decision

Vacuum policy belongs in capacity planning. Set limits for transaction age, prohibit idle-in-transaction sessions, size autovacuum for the write rate, and give high-churn tables explicit settings. Treat abandoned slots and prepared transactions as incidents because they can pin global cleanup.

MVCC moves contention out of the foreground path. Vacuum is where the deferred cost becomes visible.

References

>