Catalog My learning Blog
PostgreSQL Course: Beginner to Production in 30 Days · Phase 4 · Lesson 24 · 7 min read

PostgreSQL MVCC, VACUUM and Autovacuum Explained

On this page
  1. Why did the table double in size?
  2. How MVCC works: UPDATE writes a new row
  3. VACUUM vs VACUUM FULL
  4. Autovacuum, and tuning it for big tables
  5. Transaction ID wraparound
  6. Check yourself

Why did the table double in size?

Try this. Create a table with 100,000 rows, check its size, then update every row once:

SQL
CREATE TABLE accounts (id int PRIMARY KEY, balance numeric NOT NULL)
  WITH (autovacuum_enabled = off);   -- only for this experiment
INSERT INTO accounts SELECT g, 100 FROM generate_series(1, 100000) AS g;

SELECT pg_size_pretty(pg_relation_size('accounts'));   -- 4328 kB
UPDATE accounts SET balance = balance + 1;
SELECT pg_size_pretty(pg_relation_size('accounts'));   -- 8656 kB

Same number of rows, twice the disk space. The cause is the design that lets PostgreSQL readers and writers work without blocking each other. You met it on Day 12, and now you'll see the cost.

How MVCC works: UPDATE writes a new row

PostgreSQL never changes a row in place. An UPDATE writes a new version of the row and marks the old one as replaced. A DELETE only marks the row. This is MVCC (multi-version concurrency control).

Every row version carries two hidden columns. xmin is the ID of the transaction that created it. xmax is the ID of the transaction that deleted or replaced it, or 0 if it's still current. You can look at them:

SQL
SELECT xmin, xmax, ctid, * FROM accounts WHERE id = 1;
-- 754 | 0 | (540,101) | 1 | 101
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
SELECT xmin, xmax, ctid, * FROM accounts WHERE id = 1;
-- 755 | 0 | (540,186) | 1 | 91

ctid is the row's physical location (page, slot). After the update, the current version sits in a new slot and was created by a newer transaction. The old version is still at (540,101), now with its xmax set. Your exact numbers will differ.

Why keep it? A transaction that started before your update may still need to see the old balance, because that's what its snapshot says is true. Each transaction ignores row versions outside its snapshot. That's how a long report reads a consistent picture while writes carry on, and nobody waits for a lock.

Once no running transaction can see an old version, it's a dead tuple: wasted space. Dead tuples that pile up are called bloat. They make tables and indexes bigger, push useful data out of the cache and slow down scans.

VACUUM vs VACUUM FULL

VACUUM finds dead tuples and marks their space as free for new rows in the same table:

SQL
VACUUM accounts;
SELECT pg_size_pretty(pg_relation_size('accounts'));   -- still 8656 kB
UPDATE accounts SET balance = balance + 1;
SELECT pg_size_pretty(pg_relation_size('accounts'));   -- still 8656 kB

The file didn't shrink, but the next full update reused the freed space instead of growing the table again. That's the normal steady state for a healthy table. Plain VACUUM doesn't block reads or writes. It also updates the visibility map that index-only scans rely on (Day 11).

VACUUM FULL is a different tool. It rewrites the table into a new, compact file and gives the space back to the operating system:

SQL
VACUUM FULL accounts;
SELECT pg_size_pretty(pg_relation_size('accounts'));   -- 4328 kB

The cost is an exclusive lock for the whole rewrite: nothing can read or write the table. Save it for a table that's badly bloated after a one-off event, like a huge DELETE, and run it in a maintenance window. The pg_repack extension can do the same job while holding the heavy lock only briefly. PostgreSQL 19 builds this in: a new REPACK command replaces VACUUM FULL and CLUSTER (both still work), and REPACK (CONCURRENTLY) rebuilds a table without blocking reads and writes. That's from the PostgreSQL 19 release notes, so check the final release before you rely on it.

There's also a way to make updates cheaper. If an UPDATE doesn't change any indexed column, and the new version fits on the same page, PostgreSQL makes a HOT update (heap-only tuple). It skips writing new index entries, and the old version can be cleaned up during normal page access. For update-heavy tables, leaving free space on each page helps:

SQL
ALTER TABLE accounts SET (fillfactor = 80);   -- new pages keep 20% free

Autovacuum, and tuning it for big tables

You rarely run VACUUM by hand. The autovacuum background workers (Day 2) vacuum and analyze tables when enough rows have changed. A table gets vacuumed once its dead tuples pass:

autovacuum_vacuum_threshold (50) + autovacuum_vacuum_scale_factor (0.2) × rows in the table

That 20% suits small tables. On a 500-million-row table it means waiting for 100 million dead tuples, followed by one enormous vacuum. PostgreSQL 18 added autovacuum_vacuum_max_threshold (default 100 million) to cap this, but for hot tables you'll usually want much smaller, more frequent runs. Set it per table rather than globally:

SQL
ALTER TABLE accounts SET (
  autovacuum_vacuum_scale_factor  = 0.01,
  autovacuum_analyze_scale_factor = 0.005
);

Check whether autovacuum is keeping up:

SQL
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Dead tuples climbing while last_autovacuum is old means it's falling behind. The fix is usually to make it run more often, or to let it work faster by raising autovacuum_vacuum_cost_limit.

Never fix this by turning autovacuum off. That leads straight to bloat, and then to the problem in the next section.

Transaction ID wraparound

Transaction IDs are 32-bit numbers, so there are about 4 billion of them, and PostgreSQL reuses them in a circle. To keep old rows from suddenly looking like they're "from the future", VACUUM periodically freezes old rows, marking them as visible to everyone forever.

If freezing falls too far behind, PostgreSQL protects your data the hard way. As the counter nears the wraparound point, it stops assigning new transaction IDs, which means no writes at all, until a VACUUM catches up. That's transaction ID wraparound, and it's the one MVCC failure that can halt a production database.

Autovacuum normally prevents it. It forces a freezing vacuum when a table's oldest unfrozen transaction is more than autovacuum_freeze_max_age (200 million) transactions old, and switches to an emergency mode at vacuum_failsafe_age (1.6 billion). Monitor how far along each database is:

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

Alert well before a billion. The usual reason freezing falls behind isn't autovacuum itself. It's something holding back the oldest snapshot: a session left idle in transaction for hours, a forgotten prepared transaction, or an abandoned replication slot (Day 27). Find long-open transactions here:

SQL
SELECT pid, state, xact_start, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 5;

Setting idle_in_transaction_session_timeout makes PostgreSQL end sessions that sit idle inside a transaction, which removes the most common culprit.

Check yourself

No. Plain VACUUM makes the space reusable inside the table but doesn't shrink the file. To return the space to the operating system, use VACUUM FULL in a maintenance window, or pg_repack to avoid the long lock.

Lower autovacuum_vacuum_scale_factor for that table, so autovacuum triggers after far fewer dead tuples. If runs still can't keep up, raise the cost limit so each run works faster.

Something holding back the oldest snapshot, such as a long idle-in-transaction session, an old prepared transaction or an inactive replication slot. Autovacuum can't freeze rows that such a transaction might still need.

VACUUM can only remove dead rows that no running transaction might still need to see. An idle session left open in a transaction for hours keeps every row version since its start alive across the whole database. Find them in pg_stat_activity and set idle_in_transaction_session_timeout.

It rewrites the whole table into a new file and holds an exclusive lock while it does, so nothing can read or write the table for the duration. Tools like pg_repack reclaim space while keeping the table available, and tuning autovacuum prevents the bloat in the first place.