VACUUM & Autovacuum

PostgreSQL's MVCC model (see the MVCC page) never overwrites a row in place when you UPDATE or DELETE it — instead it keeps the old row version around so concurrent transactions that started before your change can still see a consistent snapshot. Those old row versions are called "dead tuples," and once nothing needs them anymore, something has to clean them up. That something is VACUUM.

VACUUM

VACUUM scans a table, identifies dead row versions that no active transaction can still see, and marks the space they occupied as free for PostgreSQL to reuse for future rows. It runs concurrently with normal reads and writes — it does not block the table.

vacuum-basic.sql

SQL

VACUUM orders;

-- Update planner statistics at the same time — very commonly run together
VACUUM ANALYZE orders;

VACUUM does not shrink the file on disk

In its normal mode, `VACUUM` reclaims space for reuse within the table's existing files — it does not return that space to the operating system, so the table's on-disk size will not shrink. The freed space just becomes available for future inserts/updates to reuse, which is normally exactly what you want.

VACUUM FULL

VACUUM FULL is the version that actually shrinks the table on disk. It does this by rewriting the entire table into a new, compact file and then swapping it in — which means it needs an ACCESS EXCLUSIVE lock on the table for the duration of the operation.

Avoid VACUUM FULL on busy production tables

Because `VACUUM FULL` holds an exclusive lock on the whole table, every query touching that table — reads included — blocks until it finishes. On a large, actively-used table this can mean minutes of total downtime for that table. Reach for it only during a maintenance window, and generally prefer addressing the root cause of excessive bloat (e.g. tuning autovacuum) over routinely running `VACUUM FULL`.

ANALYZE

ANALYZE collects statistics about the distribution of values in each column — how many distinct values, roughly how rows are spread out — and stores them for the query planner to use. Accurate statistics are what let the planner choose good query plans (e.g. deciding whether an index scan or a sequential scan will be faster). Stale statistics after a table changes significantly can lead to poor plan choices even though nothing is functionally wrong with the data.

Autovacuum

Running VACUUM/ANALYZE manually on every table forever isn't practical, so PostgreSQL runs a background process called autovacuum that watches tables for enough changes (inserts, updates, deletes) and automatically vacuums and analyzes them without any manual intervention.

autovacuum_vacuum_scale_factor / autovacuum_vacuum_threshold — control how many dead rows accumulate before autovacuum kicks in.
autovacuum_max_workers — how many autovacuum processes can run at once across the whole server.
Per-table overrides are possible via ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = ...) for tables with unusual write patterns.

Leave autovacuum enabled

Disabling autovacuum (or setting its thresholds so high it rarely runs) is a common but risky move on tables with heavy write/update traffic — dead rows accumulate unchecked, tables and indexes bloat, and query performance degrades over time until a large manual `VACUUM` (or worse, `VACUUM FULL`) is needed to recover. It is almost always better to leave autovacuum on and tune its settings for demanding tables than to turn it off.

Monitoring Bloat

Bloat — the accumulation of dead tuples and unused space — can be checked via pg_stat_user_tables (columns like n_dead_tup show dead row counts) or dedicated bloat-estimation queries commonly shared in the PostgreSQL community. Keeping an eye on it helps catch tables where autovacuum isn't keeping up before it becomes a performance problem.