How MVCC and Dead Tuples Trigger PostgreSQL VACUUM

In PostgreSQL, an UPDATE creates a new row version, and a DELETE marks a row version as deleted. The old versions stay in the table while a transaction might still need to see them. PostgreSQL calls versions that are no longer visible to any relevant transaction dead tuples. VACUUM removes those versions and makes their space available for reuse.

Vacuum keeps repeated updates and deletes from steadily using more storage. A long-running transaction can keep an old snapshot open, delaying cleanup even while vacuum runs. A snapshot is a view of committed changes available to a transaction at a particular point. Knowing this helps you tell the difference between a rising dead-tuple estimate and a vacuum problem, and find what is holding cleanup back. PostgreSQL’s MVCC overview explains how multiversion concurrency control (MVCC) uses snapshots to let transactions see consistent data while changes occur.

How row versions become dead

When PostgreSQL updates a row, it stores a successor version and marks the previous version as superseded. A delete marks the current version as deleted. The database keeps those older versions because a transaction using an earlier snapshot might still need to read them.

Once no relevant snapshot can see an old version, PostgreSQL can treat it as dead and remove it during vacuum. A modifying transaction’s commit alone does not guarantee immediate removal: another transaction may still hold a snapshot from before that change. The routine vacuuming documentation explains how PostgreSQL retains versions for visibility and reclaims them after they are no longer needed.

Standard VACUUM removes dead row versions and cleans up associated index entries. It makes the freed space available for later rows, but generally leaves the table file at its current size. PostgreSQL can sometimes truncate empty pages at the end of a table, but that requires an ACCESS EXCLUSIVE lock. VACUUM FULL rewrites the table to compact it, also requires that lock, and needs extra disk space during the rewrite. Use regular vacuuming for routine cleanup; consider a rewrite only when returning space to the operating system is an explicit requirement. See the VACUUM command reference for the locking and space details.

Vacuum also updates the visibility map, which records whether table pages contain rows visible to all transactions or rows frozen to prevent transaction-ID aging. PostgreSQL uses that map to skip eligible pages in later vacuums and to avoid some heap reads during index-only scans. Vacuum also freezes old row versions to help prevent transaction-ID wraparound, work that can be necessary even when a table has few dead tuples.

Why long transactions hold back cleanup

A transaction that keeps an old snapshot open can prevent vacuum from removing row versions that the snapshot might still see. To diagnose the delay, check whether the transaction's snapshot is old enough to make PostgreSQL keep those versions. The connection's age alone does not answer that question. An idle in transaction session deserves attention because it can remain open after its query finishes while still holding transaction state.

Replication slots can also affect cleanup and transaction-ID maintenance. When old transaction IDs cannot be cleared, PostgreSQL’s guidance recommends checking both long-running transactions and old replication slots. Treat both as candidates to investigate; checking them does not establish either as the cause.

Autovacuum schedules vacuum and analyze work based on how much each table changes. In PostgreSQL 18, the default update/delete trigger is a base threshold of 50 tuples plus 0.2 times the table’s estimated row count, capped at 100 million tuples. The insert-based trigger uses a default base threshold of 1,000 tuples plus 0.2 times the number of unfrozen pages. These are version-specific defaults, and table settings can override them. Autovacuum also runs when needed to prevent transaction-ID wraparound, regardless of the ordinary autovacuum setting. Check the PostgreSQL 18 vacuum settings against the version and table configuration you run.

How to spot cleanup pressure

Start with table-level trends. PostgreSQL’s pg_stat_user_tables view reports estimated live and dead tuple counts, along with vacuum history. These estimates do not measure wasted disk space exactly. Compare them over time and with the table’s update and delete activity.

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

A rising dead-tuple estimate on a frequently modified table, especially when autovacuum history is stale, warrants investigation. It does not by itself prove that vacuum is failing or that the table is bloated. PostgreSQL documents these estimates and related fields in its statistics reference.

Next, look for sessions that hold an old transaction horizon. The backend_xmin column in pg_stat_activity reports a backend’s current xmin horizon, which helps identify sessions to examine:

SELECT pid,
       usename,
       state,
       xact_start,
       backend_xmin,
       age(backend_xmin) AS backend_xmin_age,
       query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

Use this query to investigate sessions. Do not use it to generate an automatic list of sessions to terminate. Before ending a session, check what it is doing and whether its transaction horizon is blocking cleanup. For wraparound risk, check the age of the oldest unfrozen transaction IDs as well; PostgreSQL’s wraparound guidance describes the relevant indicators and corrective maintenance.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com