PostgreSQL MVCC, VACUUM, and Table Bloat Explained

PostgreSQL can keep reads and writes moving concurrently, but that behavior depends on storing multiple versions of a row and later cleaning up versions that no transaction needs. For database operators and developers, understanding PostgreSQL MVCC, VACUUM, and table bloat helps explain growing storage use, slower queries, and urgent transaction-ID warnings. This guide follows the lifecycle of a row version, shows what routine cleanup does, and provides practical checks before changing maintenance settings.

What Is PostgreSQL MVCC?

Multi-Version Concurrency Control (MVCC) is PostgreSQL’s method for presenting each transaction with a consistent view of database rows while other transactions make changes. Rather than overwrite a row in place for every update, PostgreSQL usually writes a new tuple version. A transaction’s snapshot determines which versions it can see.

This lets a reader continue using its snapshot while a writer commits a change, without readers taking ordinary row-level locks that block every other access. PostgreSQL describes this model in its MVCC introduction. MVCC is an implementation mechanism; it does not mean that every transaction sees the latest committed value at every statement. The selected transaction isolation level defines snapshot behavior and which anomalies are possible.

Why VACUUM Exists

An update or delete does not immediately make the previous tuple version reusable. A concurrent transaction may still have a snapshot that can see it. Once no active snapshot can see an obsolete version, PostgreSQL can mark its space reusable. Until cleanup happens, obsolete versions take up space and can add work to scans and index maintenance.

This cleanup is necessary because PostgreSQL’s MVCC design favors concurrency over overwriting tuples immediately. Routine VACUUM also updates visibility information used by index-only scans and freezes old transaction IDs so they cannot be confused with future transactions.

Table bloat is excess space occupied by dead or poorly packed tuples relative to the table’s useful data. Indexes can accumulate their own bloat. A high n_dead_tup estimate can point to cleanup pressure, but it is not an exact measurement of physical bloat; estimates, free space, page density, and index contents all affect actual disk use.

How MVCC and VACUUM Work

Each tuple carries transaction metadata that helps PostgreSQL determine when the tuple was inserted and when it was superseded or deleted. When a transaction reads a table, its snapshot is a boundary for which committed changes it may observe.

For example, suppose one transaction reads an account balance while another updates it. PostgreSQL can retain the earlier tuple for the reader and write the new tuple for later snapshots. When the reader finishes and no other snapshot needs the old version, that version becomes eligible for cleanup. If a transaction stays open for a long time, the old snapshot can prevent removal of rows that would otherwise be dead.

VACUUM scans eligible relations, removes dead tuple references from indexes where appropriate, and makes reusable space available inside the relation. Ordinary VACUUM generally does not shrink a table file and return all reclaimed space to the operating system. It can sometimes truncate empty pages at the end of a relation. VACUUM FULL takes a different approach: it rewrites the table to compact it and can return space to the operating system, but it requires an exclusive lock and additional disk space during the rewrite.

The VACUUM command reference documents options and restrictions. In particular, VACUUM must run outside an explicit transaction block.

Key Components and Sources of Cleanup Pressure

Autovacuum is PostgreSQL’s background maintenance system. It launches workers when table activity crosses configured thresholds, and it can also run ANALYZE. The autovacuum trigger for updates and deletes is based on a per-table threshold plus a scale factor multiplied by the table’s estimated row count. Large tables may therefore benefit from table-specific settings rather than a single global scale factor. See the current autovacuum configuration before changing defaults.

Long-running transactions can retain old snapshots. An application that leaves a transaction open while waiting on external work, an idle session in a transaction, or a reporting query that runs for hours can keep tuple versions visible for longer than expected. Fixing the transaction lifetime is often safer than forcing more aggressive vacuum settings.

Replication slots can retain WAL for consumers and, for logical slots, may also hold back removal of row versions through the slot’s xmin horizon. These are related but distinct forms of retention: retained WAL consumes disk, while a held-back cleanup horizon can contribute to table bloat. Monitor inactive slots and investigate before removing them; see the PostgreSQL logical replication and CDC explainer.

Transaction-ID freezing prevents wraparound. PostgreSQL marks old tuples as frozen so their transaction IDs no longer need to be compared as ordinary recent IDs. Autovacuum prioritizes this safety work as databases approach configured limits. Disabling autovacuum or allowing it to fall behind can eventually threaten database availability; freezing is not optional cosmetic cleanup.

Real-World Use Cases

For an update-heavy application, such as order processing or user profiles, routine vacuum helps keep obsolete row versions from accumulating after frequent updates and deletes. On append-heavy event tables, the main concerns may instead be maintaining visibility maps, refreshing planner statistics, and managing retention or partition removal.

Capacity investigations also need to distinguish table bloat from index bloat, retained WAL, and legitimate data growth. If a relation grows after a burst of updates, ordinary VACUUM may make space reusable without reducing its on-disk size. A later workload can reuse that space, so file size alone does not prove vacuum failed.

Maintenance diagnosis belongs alongside query and index analysis. The PostgreSQL performance guide covers broader performance tuning, and the database indexing guide explains index trade-offs that interact with write and cleanup costs.

Getting Started: Observe and Run VACUUM Safely

Use a role with access to the relevant statistics. Start by checking estimates and the last automatic or manual maintenance runs:

SELECT schemaname,
       relname,
       n_live_tup,
       n_dead_tup,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

These tuple counts are estimates, not an exact bloat report. Check for transactions that may be holding old snapshots:

SELECT pid,
       usename,
       state,
       now() - xact_start AS transaction_age,
       backend_xmin,
       left(query, 120) AS query_sample
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

Review the results in context before terminating a session; it may be a valid long-running job. You can also inspect active vacuum work with pg_stat_progress_vacuum and transaction-ID age with age(datfrozenxid) from pg_database. PostgreSQL’s monitoring statistics documentation describes these views.

To see the lifecycle on a disposable database, create a table, change and delete rows, inspect the estimates, and vacuum it:

CREATE TABLE mvcc_demo (
  id bigint PRIMARY KEY,
  value text NOT NULL
);

INSERT INTO mvcc_demo
SELECT id, 'before'
FROM generate_series(1, 100000) AS id;

UPDATE mvcc_demo
SET value = 'after'
WHERE id % 2 = 0;

DELETE FROM mvcc_demo
WHERE id % 5 = 0;

SELECT n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'mvcc_demo';

VACUUM (ANALYZE, VERBOSE) mvcc_demo;

Run these statements in a psql session with autocommit enabled, not inside an explicit BEGIN/COMMIT block. Statistics may take time to reflect activity, and a small test relation may not produce a dramatic disk-size change. In production, first establish a baseline, check for long-lived transactions and slot horizons, then observe autovacuum progress and logs. Tune per-table thresholds only when evidence shows the defaults are not keeping up with the workload.

Common Misconceptions

“VACUUM should make the table file smaller.”

Ordinary VACUUM generally makes dead-tuple space reusable by PostgreSQL rather than shrinking the file. If storage must be returned to the operating system, VACUUM FULL or another table-rewrite approach may be appropriate, but its lock and disk-space costs require planning.

“A high dead-tuple count proves the table is bloated.”

Statistics are estimates, not a precise physical measurement. Consider workload patterns, reusable free space, table and index size, and whether a long-running snapshot prevents cleanup before deciding to rewrite a relation.

“More aggressive autovacuum settings always solve bloat.”

Vacuuming harder cannot remove tuples that are still needed by a snapshot or replication horizon. More workers or lower thresholds can also add I/O and CPU load. Find the retention cause first, then adjust maintenance based on measurements.

Related Articles

• For broad tuning techniques, read the PostgreSQL performance optimization guide.
• To understand slot retention and change streams, see PostgreSQL logical replication and CDC.
• For write amplification and index maintenance context, explore database indexing strategies.

Changelog

• Initial publication. Last updated: October 3.