VACUUM, autovacuum and bloat

conceptThe concept

Plain VACUUM scans pages that may contain dead tuples, removes those no snapshot can see, removes their index entries, records free space in the free space map, and sets visibility-map bits. It runs alongside normal reads and writes. It only gives space back to the operating system when empty pages sit at the end of the table.

VACUUM FULL rewrites the table and its indexes into new compact files. It needs an ACCESS EXCLUSIVE lock for the whole duration, blocking even reads, plus room for a full copy. In production, pg_repack does the same online.

Autovacuum vacuums a table when dead tuples exceed autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples (50 + 20%), and also after many inserts. For a 500 million row table, 20% is 100 million dead rows before it starts, so large tables get per-table settings such as autovacuum_vacuum_scale_factor = 0.01. Workers are throttled by autovacuum_vacuum_cost_limit so they do not saturate I/O.

whyWhy it matters

When autovacuum cannot keep up, tables and indexes bloat, queries read more pages, index-only scans degrade to heap fetches, and eventually transaction-ID wraparound protection forces aggressive vacuums at the worst possible time.

Use caseReal-world scenario

A nightly job updates every row of a 50 GB table. The table doubles in size every night. Batching the update, letting VACUUM run between batches and lowering the table's scale factor keeps it stable.

Try itExamples

Run and edit these in the interactive lesson.

DROP TABLE IF EXISTS bloat_lab;
CREATE TABLE bloat_lab WITH (autovacuum_enabled = off) AS
  SELECT g AS id, repeat('x', 100) AS filler FROM generate_series(1, 20000) g;
SELECT pg_size_pretty(pg_relation_size('bloat_lab')) AS after_load;

UPDATE bloat_lab SET filler = repeat('y', 100);
SELECT pg_stat_force_next_flush();
SELECT n_live_tup, n_dead_tup, pg_size_pretty(pg_relation_size('bloat_lab')) AS after_update
FROM pg_stat_user_tables WHERE relname = 'bloat_lab';

VACUUM bloat_lab;
SELECT pg_stat_force_next_flush();
SELECT n_dead_tup, pg_size_pretty(pg_relation_size('bloat_lab')) AS after_vacuum
FROM pg_stat_user_tables WHERE relname = 'bloat_lab';

VACUUM FULL bloat_lab;
SELECT pg_size_pretty(pg_relation_size('bloat_lab')) AS after_vacuum_full;
DROP TABLE bloat_lab;

SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / greatest(n_live_tup, 1), 1) AS dead_pct,
       last_vacuum, last_autovacuum, last_analyze, last_autoanalyze, vacuum_count, autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

SELECT c.relname,
       c.reltuples::bigint AS rows,
       current_setting('autovacuum_vacuum_threshold')::int
         + current_setting('autovacuum_vacuum_scale_factor')::float8 * c.reltuples AS dead_tuples_to_trigger
FROM pg_class c
WHERE c.relnamespace = 'public'::regnamespace AND c.relkind = 'r'
ORDER BY c.reltuples DESC;

BEGIN;
ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 1000, autovacuum_analyze_scale_factor = 0.02);
SELECT relname, reloptions FROM pg_class WHERE relname = 'events';
ROLLBACK;

internalsUnder the hood

Three things stop VACUUM from removing dead tuples: a long-running or idle-in-transaction session, a replication slot (or a standby with hot_standby_feedback) whose xmin is old, and orphaned prepared transactions. All three show up as an old backend_xmin or slot xmin, and VACUUM VERBOSE reports "dead but not yet removable" tuples.

Avoid / preferDo this, not that

VACUUM FULL orders;  -- weekly cron

-- tune autovacuum for the table; for real bloat, rebuild online with
-- pg_repack / pg_squeeze, or REPACK (CONCURRENTLY) orders; on PostgreSQL 19

ALTER TABLE events SET (autovacuum_enabled = false);

ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.02, autovacuum_vacuum_cost_limit = 2000);

PitfallsCommon mistakes

• Disabling autovacuum on a busy table because it "uses I/O". Bloat and wraparound protection will cost far more later.
• Running VACUUM FULL in business hours. It locks the table against reads for the whole rewrite; use pg_repack.
• Leaving the default 20% scale factor on huge tables, so autovacuum runs rarely and takes hours when it does.

LimitsWhen not to use it

Avoid VACUUM FULL and CLUSTER on busy tables unless you can afford the exclusive lock; they are for maintenance windows or for tables nobody is using. PostgreSQL 19 folds both into REPACK, whose CONCURRENTLY option holds the exclusive lock only for the final swap.

SecuritySecurity notes

Since PostgreSQL 17, the MAINTAIN privilege and the pg_maintain role let operators run VACUUM, ANALYZE and REINDEX without owning tables or being superuser.

ChallengeTry it yourself

SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY dead_pct DESC NULLS LAST;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Routine vacuuming
• Autovacuum settings