VACUUM removes dead row versions, the old copies that UPDATE and DELETE leave in a table, and marks their space as free for new rows. VACUUM ANALYZE also refreshes the statistics the planner uses. VACUUM FULL rewrites the whole table to give space back to the operating system. Autovacuum, on by default, runs VACUUM and ANALYZE for you in the background.
After the DELETE, the table holds 700 live rows and 300 dead ones; after VACUUM, 0 dead rows. The statistics views update with a delay; pg_stat_force_next_flush() (PostgreSQL 15+) makes them update right away for this demo.
Why PostgreSQL leaves dead rows
PostgreSQL keeps old row versions so that every transaction sees a consistent snapshot (MVCC). An UPDATE writes a new version of the row and marks the old one as expired; a DELETE only marks it. The old version stays until no running transaction can need it, and then VACUUM can remove it. ctid, the physical location of a row, shows the move:
Row 1 starts at (0,1), page 0 slot 1, and after the update it lives at (0,3). Slot 1 still holds the old version until it is cleaned up. A table that is updated often keeps producing dead rows, so autovacuum keeps coming back to it.
VACUUM vs VACUUM FULL
Plain VACUUM leaves the file at the same size: the free space stays in the table for future inserts. VACUUM FULL writes a new, compact copy and drops the old file.
VACUUM | VACUUM FULL | |
|---|---|---|
| Lock | allows reads and writes | blocks all access |
| Space | reused by the same table | returned to the operating system |
| Extra disk needed | no | room for a full copy of the table |
| When | routinely (autovacuum does it) | rarely, after deleting most of a big table |
Options go in parentheses:
VACUUM (VERBOSE, ANALYZE) items;prints what was done and refreshes statistics.VACUUM (PARALLEL 4)(PostgreSQL 13+) vacuums indexes with parallel workers.VACUUM (SKIP_LOCKED)skips tables it cannot lock right away.
How autovacuum decides when to run
Autovacuum checks each table every autovacuum_naptime. It vacuums the table when its dead rows exceed autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * table rows: with the defaults, 50 rows plus 20 percent of the table. It analyzes the table when the rows changed since the last analyze pass 50 plus 10 percent. Since PostgreSQL 13, inserts alone also trigger a vacuum. The defaults in postgresql.conf:
autovacuum = on
autovacuum_naptime = 1min
autovacuum_max_workers = 3
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
autovacuum_vacuum_insert_threshold = 1000
autovacuum_vacuum_insert_scale_factor = 0.2
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1
On a table with 100 million rows, 20 percent means 20 million dead rows before a vacuum starts. Lower the scale factor for big, busy tables only:
ALTER TABLE events RESET (autovacuum_vacuum_scale_factor); returns the table to the server-wide setting. The routine vacuuming chapter covers the remaining settings.
Check what autovacuum has done
pg_stat_user_tables shows dead rows and the last manual and automatic vacuum of each table:
SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
If dead rows keep growing although autovacuum runs, look for old open transactions, which stop it from removing anything newer than their snapshot:
SELECT pid, state, xact_start, query
FROM pg_stat_activity
WHERE xact_start < now() - interval '10 minutes'
ORDER BY xact_start;
Freezing and transaction ID wraparound
Transaction IDs are 32-bit counters that wrap around. To keep old rows visible, vacuum freezes them, marking them as older than every transaction. When a table's oldest unfrozen ID passes autovacuum_freeze_max_age (200 million transactions by default), autovacuum runs on it even if autovacuum is off.
If freezing still falls far behind, PostgreSQL stops assigning new transaction IDs, and writes fail until a vacuum catches up. This query shows how close each database is:
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;
Frequently Asked Questions
Does VACUUM lock the table?
Plain VACUUM takes a lock that still allows SELECT, INSERT, UPDATE and DELETE; it conflicts only with schema changes, index builds, ANALYZE and other vacuums. VACUUM FULL takes an ACCESS EXCLUSIVE lock, which blocks every query on the table until it finishes.
Should I turn off autovacuum?
No. Without it, dead rows pile up, statistics go stale and the database eventually has to stop accepting writes to prevent transaction ID wraparound. Tune its thresholds per table instead.
Why does my table not get smaller after VACUUM?
Plain VACUUM marks the space of dead rows as free for new rows in the same table; it returns space to the operating system only when the empty pages are at the end of the file. VACUUM FULL rewrites the table and gives the space back.
Why is VACUUM not removing dead rows?
A row can be removed only when no running transaction might still need it. A long-open transaction (often an idle session that ran BEGIN), an old replication slot or an abandoned prepared transaction holds them back.