TopolearnNeglected vacuum is behind most PostgreSQL bloat, slowdowns and the dreaded transaction ID wraparound.
Because of MVCC, an UPDATE writes a new row version and a DELETE only marks the old one dead. VACUUM reclaims space from dead tuples that no running transaction can still see, so it can be reused; it doesn't normally shrink the file. It also updates the visibility map (enabling index-only scans) and freezes old rows.
Freezing matters: transaction IDs are 32-bit and wrap around. Rows must be frozen before their xid gets too old, or PostgreSQL will eventually refuse writes to protect data. Autovacuum runs anti-wraparound vacuums automatically; your job is not to block them.
Autovacuum launches workers per table when dead tuples exceed autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples (defaults 50 + 20%). For large tables 20% is far too lazy; lower the scale factor per table: ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.01). It also runs ANALYZE to refresh statistics.
What stops vacuum from cleaning: long-running transactions, sessions idle in transaction, abandoned replication slots and prepared transactions — they hold back the "oldest xmin". Monitor pg_stat_user_tables (n_dead_tup, last_autovacuum) and pg_stat_activity.
VACUUM FULL rewrites the table to give space back to the OS but takes an exclusive lock for the whole time; tools like pg_repack do it online.
Example
CREATE TABLE counter (id int PRIMARY KEY, n int) WITH (autovacuum_enabled = off);
INSERT INTO counter SELECT g, 0 FROM generate_series(1, 10000) g;
UPDATE counter SET n = n + 1; -- 10000 new versions, 10000 dead ones
UPDATE counter SET n = n + 1;
SELECT pg_stat_force_next_flush();
SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'counter';
-- 10000 | 20000
VACUUM (VERBOSE) counter; -- reports the dead tuples it removed
SELECT pg_size_pretty(pg_table_size('counter')); -- unchanged: space is reusable
-- tune a big, busy table instead of relying on the 20% default
ALTER TABLE counter SET (autovacuum_enabled = on,
autovacuum_vacuum_scale_factor = 0.01);Remember
- UPDATE and DELETE leave dead tuples that VACUUM makes reusable
- Lower autovacuum_vacuum_scale_factor for large tables
- Long transactions and stale replication slots block cleanup
Watch out for
- Disabling autovacuum to 'save resources' and later facing bloat or wraparound shutdown.