53 VACUUM and ANALYZE

PostgreSQL uses multi-version concurrency control (MVCC): an UPDATE does not overwrite a row, it writes a new version and marks the old one dead. A DELETE only marks rows dead — they stay on disk. VACUUM reclaims that space and prevents transaction-ID wraparound. ANALYZE updates the planner’s statistics so it makes good decisions. PostgreSQL runs both automatically via autovacuum, but understanding what they do — and when to nudge them — is part of being a competent operator.

What you’ll learn

• Why MVCC produces dead rows and what VACUUM does about them
• The difference between VACUUM, VACUUM FULL, and VACUUM ANALYZE
• What ANALYZE actually computes
• Reading pg_stat_user_tables to see autovacuum activity
• The transaction-ID-wraparound horizon and why you cannot ignore it

Example

-- Inspect the per-table autovacuum stats
SELECT relname, n_live_tup, n_dead_tup,
       last_vacuum, last_autovacuum,
       last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY n_dead_tup DESC
LIMIT 5;

-- Manually vacuum and analyze a table
VACUUM (VERBOSE) fruits;
ANALYZE VERBOSE fruits;

-- Combined: VACUUM ANALYZE (most common manual invocation)
VACUUM ANALYZE fruits;

-- See the planner's stored statistics for a column
SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'fruits';

-- Bloat estimate (approximation): pg_stat_user_tables gives live vs dead row counts
-- pgstattuple extension gives exact numbers if you need them.

-- VACUUM FULL: rewrites the whole table, fully reclaims space, takes an exclusive lock.
-- Use sparingly.
-- VACUUM FULL fruits;

MVCC is what lets PostgreSQL achieve high concurrency: writers do not block readers and vice versa, because each transaction sees a consistent snapshot. The cost is dead rows. VACUUM walks the table, marks dead rows’ space as free for reuse, and updates the visibility map (a per-page bitmap that lets index-only scans avoid heap visibility checks on all-visible pages).

Plain VACUUM normally makes space reusable within the table. It can also truncate empty pages at the end of the table and return that space to the operating system. VACUUM FULL rewrites the entire table in a new file, releasing the old one — it reclaims space but takes an ACCESS EXCLUSIVE lock and is slow. Use it rarely; the routine maintenance is autovacuum.

ANALYZE samples the table to update pg_statistic. The planner uses these statistics — number of distinct values, most common values, histograms — to estimate row counts and pick plans. Stale statistics produce bad plans.

PostgreSQL’s autovacuum daemon runs VACUUM and ANALYZE automatically based on per-table thresholds (autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor). Autovacuum handles routine maintenance, but its progress and thresholds need monitoring, especially after bulk changes and on large or frequently updated tables.

Run the example

To run:

$ psql -f source/vacuum-and-analyze.sql postgres
 relname | n_live_tup | n_dead_tup | last_vacuum | ...
---------+------------+------------+-------------+----
 ...
INFO:  vacuuming "public.fruits"
INFO:  ...
VACUUM
 ...

Common pitfalls

• Disabling autovacuum to “save resources” — almost always a mistake. The dead rows pile up, queries slow down, and the eventual catch-up is much worse than steady-state autovacuum overhead.
• Running VACUUM FULL on a busy table — it blocks all reads and writes for the duration. Use pg_repack (extension) for online compaction.
• Forgetting to ANALYZE after a bulk import — the planner has no statistics for the new rows and may pick terrible plans until autovacuum catches up. Run ANALYZE schema.table; manually after big imports.
• Long-running transactions block autovacuum from cleaning up rows visible to them. A forgotten BEGIN; in psql can prevent vacuuming across the whole database — watch pg_stat_activity for state = 'idle in transaction'.

Downloads

SQL file: vacuum-and-analyze.sql · All lesson files · Setup guide