Postgres vacuum and bloat: MVCC, autovacuum, wraparound

PostgreSQL never overwrites a row in place. An UPDATE writes a new version and leaves the old one on the page; a DELETE only marks a version as gone. Those dead versions stay until VACUUM reclaims the space, and that is the whole story behind table bloat, the mysterious growth of a table whose row count is flat, and the alarming log message about transaction wraparound.

The good news is that autovacuum does this for you and, on a healthy database, you will never think about it. The bad news is that it can be blocked by something entirely unrelated - a forgotten psql session with an open transaction, an abandoned replication slot - and when it is blocked, nothing warns you until the table is three times the size it should be. This post is how to notice, and what to do.

Why Postgres leaves dead rows behind#

PostgreSQL uses multi-version concurrency control. Every row version carries two hidden columns: xmin, the transaction that created it, and xmax, the transaction that deleted or replaced it. A transaction sees a version if xmin committed before its snapshot began and xmax is either empty or from a transaction it cannot see.

SELECT xmin, xmax, id, status FROM app.orders WHERE id = 42;

That design buys something valuable: readers never block writers and writers never block readers. A long report can run for ten minutes against a consistent snapshot while the application keeps writing, because the old versions it needs are still there.

The cost is that they are still there. A table where every row is updated ten times a day holds eleven versions of each row by evening unless something removes the other ten. That something is vacuum.

What VACUUM does, and what it does not#

A plain VACUUM scans a table, finds row versions that no running transaction can still see, and marks that space as reusable. Specifically it:

• Removes dead row versions and their index entries.
• Records the freed space in the free space map so new rows can be written into it.
• Updates the visibility map, which is what allows index-only scans.
• Freezes old row versions so their transaction ids can be recycled.
• Truncates trailing empty pages at the end of the table, taking a brief exclusive lock to do it.

What it does not do is give space back to the operating system. A table that grew to 4 GB and is vacuumed down to 1 GB of live data still occupies 4 GB on disk; the difference is free space inside the file that PostgreSQL will reuse. For a table that churns at a steady rate that is exactly right - the space is recycled forever and nothing needs to be rewritten. It is only a problem after a one-off event, such as deleting 80% of a table, where the space will never be needed again.

ANALYZE is a separate job that samples the table and updates the statistics the planner uses. Autovacuum runs both, on separate thresholds. VACUUM (VERBOSE, ANALYZE) app.orders; does them together by hand and prints what it found, which is the first command to run when you suspect something.

VACUUM FULL is a different operation despite the name. It rewrites the entire table into a new file, which does return space to the operating system, and it holds an ACCESS EXCLUSIVE lock for the duration - no reads, no writes, nothing. It also needs enough free disk for a second copy of the table and its indexes. It is a maintenance-window tool, not a routine one.

Autovacuum's thresholds, in real numbers#

Autovacuum wakes every autovacuum_naptime (one minute by default), looks at each table, and starts a worker if the table is over its threshold. There are three thresholds and they are arithmetic, not magic:

The insert-triggered vacuum was added in PostgreSQL 13 and matters for append-only tables, which have no dead rows but still need freezing and visibility-map maintenance.

Three more settings shape how hard a worker runs once it starts: autovacuum_max_workers (3), autovacuum_vacuum_cost_limit (inherits vacuum_cost_limit, 200) and autovacuum_vacuum_cost_delay (2 ms since PostgreSQL 12, and 20 ms before that). Together they throttle vacuum so it does not saturate the disk. On modern storage the default throttle is conservative; raising the cost limit is the usual first change, and PostgreSQL tuning for small servers has it in context with the rest of the configuration.

The default 20% scale factor is the setting worth arguing with. On a 10-million-row table it means two million dead rows before anything happens, and then one enormous vacuum. Smaller and more often is better on a small server.

Why autovacuum falls behind#

Vacuum can only remove a row version that no running transaction could still need. That single rule is behind every case of "autovacuum is running constantly and the table keeps growing".

A long-running transaction. A session that ran BEGIN an hour ago holds a snapshot, and every dead row created since then must be kept in case it looks. This includes a session sitting idle in transaction, which is usually an application that opened a transaction, made a request to something else, and forgot.

An abandoned replication slot. A slot holds back the xmin horizon for a replica that may never return. An inactive slot will happily stop vacuum across the whole cluster.

A stale prepared transaction. Two-phase commit that was prepared and never committed. Rare, and completely invisible until you look for it.

Standby feedback. With hot_standby_feedback = on, a long query on a replica delays cleanup on the primary. That is what it is for, but it means a report on the replica can bloat the primary.

Find all four in three queries:

-- Sessions holding back cleanup, oldest firstSELECT pid, state, age(backend_xmin) AS xmin_age,       now() - xact_start AS transaction_age, left(query, 60) AS queryFROM pg_stat_activityWHERE backend_xmin IS NOT NULLORDER BY age(backend_xmin) DESC;-- Replication slots, active or notSELECT slot_name, active, age(xmin) AS xmin_age, wal_statusFROM pg_replication_slots;-- Forgotten prepared transactionsSELECT gid, prepared, owner, database FROM pg_prepared_xacts;

The fix for the first is idle_in_transaction_session_timeout, set per role, so a forgotten session closes itself after a minute rather than after a weekend. The fix for the second is dropping the slot, once you are sure the replica is gone. Two other causes are worth naming: a table so large that one worker cannot finish before the next cycle starts, and locks - VACUUM yields to DDL, so an autovacuum can be cancelled repeatedly by a migration that keeps taking a conflicting lock.

Measuring dead rows and bloat#

The statistics views give you dead-row counts for free. This is the query to keep:

SELECT relname AS table,       n_live_tup AS live, n_dead_tup AS dead,       round(100 * n_dead_tup / GREATEST(n_live_tup + n_dead_tup, 1)) AS dead_pct,       last_autovacuum, last_autoanalyze, autovacuum_countFROM pg_stat_user_tablesWHERE n_dead_tup > 1000ORDER BY n_dead_tup DESCLIMIT 20;

A table over about 20% dead with a last_autovacuum of null or days ago is the signal. For a precise answer rather than an estimate, the pgstattuple extension reads the table and tells you exactly:

CREATE EXTENSION IF NOT EXISTS pgstattuple;SELECT * FROM pgstattuple('app.orders');          -- exact, reads everythingSELECT * FROM pgstattuple_approx('app.orders');   -- fast estimate

dead_tuple_percent and free_percent are the two columns that matter. A table with 5% free space is healthy; one with 60% free space has been through something.

While a vacuum is running, pg_stat_progress_vacuum shows which phase it is in and how far through the table it has got, which answers "is it stuck or just slow". And if your server logs to standard output, setting log_autovacuum_min_duration = 1s puts a line in the log every time a vacuum takes more than a second - the cheapest early warning there is, and it lands where you already read the console. The general principle is in monitoring that tells you something: a number nobody looks at is not monitoring.

Tuning vacuum per table#

Global settings are a blunt instrument, because the table that needs attention is almost never the average one. Per-table storage parameters override them:

-- A hot table: clean at 1% dead, and do not throttleALTER TABLE app.sessions SET (  autovacuum_vacuum_scale_factor = 0.01,  autovacuum_vacuum_threshold = 100,  autovacuum_vacuum_cost_delay = 0);-- A big append-only table: analyze more often, vacuum on insertsALTER TABLE app.events SET (  autovacuum_analyze_scale_factor = 0.02,  autovacuum_vacuum_insert_scale_factor = 0.05);

The other parameter worth knowing is fillfactor. It defaults to 100, meaning pages are filled completely. Set it to 90 on a table whose rows are updated frequently and PostgreSQL leaves room on each page for the new version of a row to live beside the old one. That enables a heap-only tuple update, which does not have to touch the indexes at all - much cheaper, and it leaves less for vacuum to clean. It only applies to pages written after the change, so it takes effect gradually.

ALTER TABLE app.sessions SET (fillfactor = 90);

Note the interaction with indexes: a HOT update is only possible when no indexed column changed. An index on a column your application updates constantly costs you twice, which is one of the reasons PostgreSQL indexes explained argues for fewer indexes rather than more.

Transaction ID wraparound#

Transaction ids are 32-bit numbers. PostgreSQL can see about two billion transactions into the past; beyond that, an id would appear to be in the future and old rows would become invisible - silent, catastrophic data loss. To prevent it, vacuum freezes old row versions, marking them as visible to everyone regardless of transaction id.

This is why vacuum is not optional, and why PostgreSQL will run an anti-wraparound vacuum on a table whose oldest unfrozen transaction reaches autovacuum_freeze_max_age (200 million) even if autovacuum is switched off entirely. Check where you stand:

SELECT datname, age(datfrozenxid) AS xid_ageFROM pg_database ORDER BY xid_age DESC;SELECT relname, age(relfrozenxid) AS xid_ageFROM pg_class WHERE relkind IN ('r','m','t')ORDER BY xid_age DESC LIMIT 10;

Under 200 million is normal. Growing steadily past it means an anti-wraparound vacuum is being blocked by one of the causes in the section above - go and find it now, because the clock does not stop. As the limit approaches, the server logs increasingly stern warnings, and before the point of actual danger it refuses to start transactions that need a new id. VACUUM itself still runs at that point, which is how you recover; the log hint about single-user mode is a last resort rather than a first step.

Two mitigations exist on modern versions. PostgreSQL 14 added a failsafe that makes vacuum skip index cleanup and ignore cost delays once a table gets dangerously old, so it finishes as fast as the disk allows. And PostgreSQL 17 reworked how vacuum tracks dead row pointers in memory, removing the old effective ceiling of 1 GB on the memory a single vacuum could use, which shortens the worst cases considerably. Neither replaces finding out what blocked cleanup in the first place.

Multixact ids have their own, parallel version of this problem, governed by autovacuum_multixact_freeze_max_age (400 million). They are consumed by row-level locks such as SELECT ... FOR SHARE, so a workload with heavy locking can reach that limit first.

Fixing bloat that has already happened#

Once a table is bloated, plain vacuum will not shrink the file. The options, worst case to best:

1. Leave it. If the table will reuse the space at its normal rate, do nothing. This is the right answer more often than people believe.
2. `VACUUM FULL app.orders;` Rewrites the table, returns the space, takes an exclusive lock and needs disk for a second copy. Fine for a 200 MB table at 3 a.m., not for a 40 GB one during the day.
3. `pg_repack`. An extension that does the same rewrite while keeping the table available, taking a brief lock only at the swap. It still needs the disk space for the copy, and it has to be installed on the server, which not every host allows.
4. Dump and restore. For a whole database that has gone badly wrong, pg_dump and restore into a fresh one produces the most compact possible result. It is also downtime. The pg_dump and pg_restore guide covers the formats and the parallel options.
5. Partition, then drop. If the bloat comes from deleting old rows, partition by time instead. Dropping a partition is instant and leaves nothing to vacuum.

Indexes bloat too, and separately. REINDEX INDEX CONCURRENTLY orders_created_idx; (PostgreSQL 12 and later) rebuilds one without blocking writes, and is usually a better first move than a full table rewrite, because index bloat is more common and the rebuild is cheaper.

Two practical notes on disk. VACUUM FULL and pg_repack both need roughly twice the size of the table free at once, which on a 20 GB plan is a real constraint - check pg_total_relation_size before you start. And take a backup first. On RE:NODE, backup slots come with every database plan, backups are stored off the machine they protect, and restoring is a button rather than a support ticket; the panel also graphs disk against the plan's limit, so you can see whether the rewrite will fit. The argument for pressing restore once on purpose is in backing up a database and proving it restores.

One last shortcut worth knowing: to empty a table completely, TRUNCATE is instant and reclaims the space immediately, because it creates a new empty file rather than marking rows dead. DELETE FROM table on a million rows creates a million dead rows and then a very long vacuum.

FAQ#

What is table bloat in PostgreSQL?

Space inside a table's files occupied by row versions nothing can see any more. It comes from updates and deletes, which never overwrite in place. Vacuum makes that space reusable; it does not shrink the file unless you rewrite the table.

Should I run VACUUM manually?

Usually no - autovacuum handles the routine work better than a cron job can, because it reacts to actual churn. Run VACUUM (ANALYZE) by hand after a bulk load or a mass delete, when you want the statistics updated immediately rather than at the next threshold.

Is VACUUM FULL safe to run on production?

It is safe for your data and hostile to your availability: it takes an exclusive lock for the whole rewrite and needs disk space for a second copy of the table. Schedule it, or use pg_repack if your host allows the extension.

Why is autovacuum running constantly but the table keeps growing?

Something is holding back the xmin horizon, so vacuum finds nothing it is allowed to remove. Look for an old transaction in pg_stat_activity, an inactive replication slot, or a prepared transaction that was never committed.

What happens if transaction wraparound actually arrives?

The server stops issuing new transaction ids to protect the data rather than losing it. Vacuum still runs, so the recovery is to let the anti-wraparound vacuum finish after removing whatever was blocking it. It is avoidable entirely by watching age(datfrozenxid).

Does a high dead-row count slow queries down?

Yes, in two ways. The table occupies more pages, so scans read more and cache holds proportionally less real data. And a table vacuum cannot keep up with loses its index-only scans, because the visibility map is never marked current.