PostgreSQL VACUUM — table bloat and autovacuum tuning

PostgreSQL table grows much larger than the actual data. Queries slow down over time. pg_stat_user_tables shows high dead tuple count. Disk usage doesn't decrease after deleting rows.

(1) PostgreSQL never removes dead rows in-place — VACUUM marks space as reusable but doesn't return it to OS. VACUUM FULL rewrites the table (locks it). (2) Autovacuum settings: autovacuum_vacuum_threshold (default 50) + autovacuum_vacuum_scale_factor (default 0.2) determine when vacuum runs. For large tables: lower scale_factor to 0.01-0.05. (3) Per-table tuning: ALTER TABLE big_table SET (autovacuum_vacuum_scale_factor = 0.01). (4) Monitor: SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables. (5) Transaction ID wraparound: if autovacuum can't keep up, Postgres eventually shuts down to prevent data corruption. Monitor xid age. (6) Long-running transactions prevent vacuum — check pg_stat_activity for old transactions.

PostgreSQL uses MVCC (Multi-Version Concurrency Control). UPDATE creates a new row version; DELETE marks the old one as dead. Dead rows are invisible but take space until VACUUM reclaims it.