MVCC, vacuum and table bloat from long-running transactions: an interactive simulator

Databases such as Postgres implement isolation with multi-version concurrency control: an update writes a new version of the row and leaves the old one in place, so transactions that started earlier can keep reading the version they saw. Vacuum later removes versions nobody can see any more. One long-running or forgotten transaction keeps every newer dead version alive, and on a busy table that means millions of them and a table file many times larger than its data.

The playground runs six hours of a table with a million rows and 2,000 updates a second, with only short transactions, with a two-hour report in a single transaction, or with a session left idle in a transaction.

What the playground shows

• Short transactions keep it tidy. Vacuum frees a minute’s dead versions a minute later, the space is reused, and 200 MB of data lives in a 248 MB file.
• A two-hour report holds everything back. Its snapshot could still need any version that died after it began, so 14.4 million dead versions pile up. When it finishes they are freed, but the file stays at 3.1 GB, over 15 times the live data, and a full scan takes 3.1 s instead of a quarter of a second until the table is rewritten.
• An idle session is worse. A connection that ran BEGIN and never committed holds its snapshot for as long as it lives: 36 million dead versions and 7.4 GB after five hours, and still growing.
• Timeouts bound the damage. Ending transactions open for more than 10 minutes caps the backlog at 1.2 million versions and the file at 464 MB. The report is killed too, so long analytics belong on a replica, a snapshot or an analytics store, and a replica with hot_standby_feedback on holds back the primary in exactly the same way.

Finding and fixing it

-- find what is holding vacuum back: the oldest open transactions
SELECT pid, state, now() - xact_start AS open_for, left(query, 60)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start LIMIT 5;

-- stop it happening again
ALTER DATABASE app SET idle_in_transaction_session_timeout = '10min';
ALTER ROLE app_web SET statement_timeout = '30s';

-- dead versions per table, and when autovacuum last ran
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

-- give the space back once it is bloated (pg_repack does it without a long lock)
VACUUM (FULL) orders;

Indexes bloat the same way, since each new row version needs index entries too unless the update qualifies as a heap-only update. Watching the age of the oldest transaction is a better early warning than watching disk space.

In a system design interview

When a design puts heavy writes and long queries on the same Postgres database, mention the interaction: “The orders table takes thousands of updates a second, so long transactions on the primary would stop vacuum and bloat it. Reporting runs on a separate replica or on the warehouse fed by change data capture, and we set idle-in-transaction and statement timeouts so a stuck client cannot hold a snapshot for hours.” Expect the follow-ups: how autovacuum is tuned for hot tables, what transaction ID wraparound is, and how to reclaim space without downtime.

Read the guide to scaling Postgres or transactions and isolation, see isolation levels and online schema migration, or watch a strong candidate design a payment system.

Questions people ask