PostgreSQL VACUUM: Autovacuum and bloated tables

In short: PostgreSQL does not delete rows immediately but marks them; VACUUM cleans up, frees space for reuse and keeps statistics current, usually automatically through autovacuum.

In more detail: Because of MVCC (several versions of a row for parallel transactions) UPDATE and DELETE produce dead tuples. They remain until no running access needs them any more. VACUUM removes them from the point of view of reuse.

In Depth

What VACUUM does

• Marks the space of dead rows as reusable.
• Updates the visibility map (speeds up index-only scans).
• Protects against transaction ID wraparound (freezing old rows).
• VACUUM FULL rewrites the table and returns space to the operating system but locks exclusively; rarely needed (alternative: pg_repack).
• ANALYZE collects statistics for the query planner.

Autovacuum

A background process starts VACUUM/ANALYZE when enough changes have accumulated (default: 20 % of the table + 50 rows). Settings: autovacuum_vacuum_scale_factor, autovacuum_naptime, per table ALTER TABLE … SET (autovacuum_vacuum_scale_factor = 0.02). Never switch it off. For large, heavily changed tables configure more aggressively.

Detecting bloat

Symptoms: table/index grows despite the same amount of data, queries get slower. Check: pg_stat_user_tables (n_dead_tup, last_autovacuum), size with pg_total_relation_size, extension pgstattuple. Causes: long transactions or forgotten open connections (prevent cleanup), too infrequent autovacuum, frequent mass updates. Solution: keep transactions short, tune autovacuum finer, manually VACUUM (VERBOSE, ANALYZE) table;.

Practice and managed services

On Neon, Supabase, RDS autovacuum mostly runs automatically; still observe patterns (tables with many updates such as sessions, counters, queues: better insert + delete old rows or partitions). Delete large amounts in batches. Back up before VACUUM FULL. Read pg_stat_progress_vacuum. Connections stuck in idle in transaction are the enemy of every vacuum: limit with idle_in_transaction_session_timeout.

See also: PostgreSQL, MVCC and isolation levels, PostgreSQL indexes, Connection pooling