Every UPDATE in PostgreSQL leaves a dead row behind. Here's how MVCC works under the hood, what VACUUM cleans up, and how to keep autovacuum ahead of your workload.
PostgreSQL lets readers and writers work at the same time without blocking each other. The mechanism behind that is Multi-Version Concurrency Control (MVCC) — and the price you pay for it is VACUUM.
Every row has a birth and death certificate
Each row version (a tuple) in a PostgreSQL table carries hidden system columns:
xmin — the ID of the transaction that created this version
xmax — the ID of the transaction that deleted or replaced it (0 if still live)
You can see them yourself:
CREATE TABLE accounts (id intPRIMARY KEY, balance numeric);
INSERT INTO accounts VALUES (1, 100);
SELECT xmin, xmax, *FROM accounts;
When a transaction reads a table, it uses its snapshot to decide which tuples are visible: a tuple is visible if its xmin committed before the snapshot and its xmax did not.
UPDATE is really INSERT + DELETE
PostgreSQL never overwrites a row in place. An UPDATE:
writes a new tuple with the new values (new xmin), and
sets xmax on the old tuple.
UPDATE accounts SET balance =150WHERE id =1;
The old version stays on disk so that transactions with older snapshots can still see it. Once no running transaction can possibly need it, it becomes a — wasted space.
MVCC means Postgres doesn't store a row count. Make exact counts cheaper, get instant estimates from the planner, and keep exact counters when you really need them.
marks space held by dead tuples as reusable for future inserts and updates,
updates the visibility map, which enables index-only scans,
freezes old tuples to protect against transaction ID wraparound.
Plain VACUUM does not usually shrink the file on disk — it makes space reusable inside the table. VACUUM FULL rewrites the whole table compactly but takes an ACCESS EXCLUSIVE lock, blocking all reads and writes. Treat it as a last resort.
Autovacuum and its thresholds
The autovacuum launcher vacuums a table when its dead tuples exceed:
With the defaults (50 and 0.2), a 100-million-row table accumulates about 20 million dead tuples before autovacuum starts. For large, busy tables, lower the scale factor per table:
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor =0.01,
autovacuum_analyze_scale_factor =0.02
);
Watching for bloat
pg_stat_user_tables tells you whether autovacuum is keeping up:
Old prepared transactions — check pg_prepared_xacts.
SELECT pid, state, now() - xact_start AS xact_age, query
FROM pg_stat_activity
WHERE xact_start ISNOT NULLORDERBY xact_start
LIMIT 5;
Transaction ID wraparound
Transaction IDs are 32-bit and compared using modular arithmetic, so PostgreSQL must freeze old tuples before their IDs get too old. If freezing falls too far behind, PostgreSQL will eventually refuse new write transactions to protect your data. Monitor age(datfrozenxid):
SELECT datname, age(datfrozenxid) FROM pg_database ORDERBY2DESC;
HOT updates: the cheap kind
If an update doesn't modify any indexed column and there's free space on the same page, PostgreSQL performs a Heap-Only Tuple (HOT) update: no new index entries are needed. Leaving free space with a lower fillfactor on update-heavy tables increases the HOT ratio:
ALTER TABLE sessions SET (fillfactor =80);
Key takeaways
Updates and deletes create dead tuples; VACUUM makes their space reusable.
Tune autovacuum per table for large, busy tables.
Kill long transactions and stale replication slots — they stall cleanup.
Monitor dead tuples and datfrozenxid age before they become an incident.
PostgreSQL Global Development Group has announced the first release candidate of version 10 of its popular database PostgreSQL. The PostgreSQL 10 RC1 is now available for download.
