VACUUM, autovacuum and table bloat in PostgreSQL

PostgreSQL uses multiversion concurrency control (MVCC): an UPDATE writes a new version of the row and marks the old one as dead, and a DELETE only marks the row dead. Readers that started earlier can still see the old versions. Once no transaction needs them any more, someone must clean them up. That someone is VACUUM.

What VACUUM does

• Marks the space of dead rows as reusable for new rows in the same table.
• Updates the visibility map, which makes index-only scans possible.
• Freezes old rows to protect against transaction ID wraparound.
• With ANALYZE, refreshes the statistics the query planner uses.

Plain VACUUM runs alongside normal reads and writes. It usually does not shrink the file on disk; it makes space reusable inside it (it can only give back completely empty pages at the end of the table).

VACUUM (VERBOSE, ANALYZE) orders;
ANALYZE orders;

VACUUM cannot run inside a transaction block.

VACUUM FULL is not routine maintenance

VACUUM FULL rewrites the whole table into a new file and returns the freed space to the operating system. It holds an ACCESS EXCLUSIVE lock for the whole run, which blocks every read and write on the table, and it needs enough free disk space for a full copy. The documentation recommends regular plain vacuuming instead. Keep VACUUM FULL for one-off cases, such as after deleting most of a large table, and plan a maintenance window for it.

Autovacuum

The autovacuum launcher (on by default) checks each table about once per autovacuum_naptime (default 1 minute) and starts workers, up to autovacuum_max_workers (default 3). A table is vacuumed when its dead rows exceed:

autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples
-- defaults: 50 + 0.2 * number of rows

There are similar thresholds for analyze (defaults 50 rows plus 10%) and for insert-only tables, and recent versions cap the update/delete trigger with autovacuum_vacuum_max_threshold. With the default 20% scale factor, a 100-million-row table collects about 20 million dead rows before autovacuum starts. For large, busy tables lower the factor per table:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

Watching dead rows

SELECT relname,
       n_live_tup,
       n_dead_tup,
       last_autovacuum,
       last_autoanalyze,
       autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 15;

A high n_dead_tup with an old or empty last_autovacuum means autovacuum is not keeping up or is being blocked.

What blocks cleanup

VACUUM can only remove rows that no running transaction might still need. These keep old rows alive, and the table grows (bloats) even while autovacuum runs:

• Long-running transactions, including sessions left idle in transaction. Find them in pg_stat_activity by old xact_start or by age(backend_xmin).
• Forgotten prepared transactions (pg_prepared_xacts).
• Replication slots that nobody consumes (pg_replication_slots), and standbys with hot_standby_feedback running long queries.
• Frequent commands that take locks conflicting with autovacuum, which can keep cancelling it.

SELECT pid, usename, state, xact_start, age(backend_xmin) AS xmin_age, left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 10;

Setting idle_in_transaction_session_timeout ends sessions that sit idle inside a transaction for too long, which prevents the most common cause.

Transaction ID wraparound

Transaction IDs are 32-bit counters. To stay safe, every row must be frozen before its transaction ID gets too old. Autovacuum does this automatically and will vacuum any table whose age passes autovacuum_freeze_max_age, even if autovacuum is otherwise turned off for it. If freezing cannot keep up, the server first warns and, very close to the limit, refuses to assign new transaction IDs. Check the age per database:

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

Values steadily climbing toward autovacuum_freeze_max_age (and beyond) mean something is holding back freezing; look for the blockers listed above.

Official documentation

• Routine vacuuming
• VACUUM
• Vacuuming settings