PostgreSQL Autovacuum and Bloat: MVCC, Freezing, Wraparound and Per-Table Tuning

A complete guide to the maintenance work that decides whether your PostgreSQL ages well or quietly degrades: why MVCC produces bloat by design, what VACUUM actually does (and why it does not shrink your table), the autovacuum trigger formula behind 80% of the problems, three levels of detection with pg_stat_user_tables, pgstattuple and pg_stat_progress_vacuum, per-table tuning instead of global, the cost budget that throttles your vacuum to 39 MB/s, HOT updates and fillfactor to prevent rather than clean up, and the full transaction ID wraparound runbook without dropping into single-user mode. Covers the four blockers that make VACUUM run without removing a single tuple, what changed in PostgreSQL 17 (TidStore) and 18 (eager freezing and autovacuum_vacuum_max_threshold), remediation with REINDEX CONCURRENTLY, pg_repack and pg_squeeze, a Python monitoring script, Prometheus alerts, eight recurring mistakes, a production checklist and an FAQ. With production-ready SQL, Python, YAML and configuration. Expanded edition: the invisible bloat of TOAST tables and their own toast.-prefixed parameters, replicas and the real cost of hot_standby_feedback with recovery conflicts, orphaned slots and PostgreSQL 18 idle_replication_slot_timeout, index bloat from the inside with bottom-up deletion, pgstatindex, REINDEX CONCURRENTLY and INDEX_CLEANUP OFF for emergencies, partitioned tables with cold-partition freezing and the parent ANALYZE autovacuum never runs, autovacuum on managed services (rds.adaptive_autovacuum, autovacuum_worker_slots and the worker memory arithmetic), and a full worked example going from 41 GB to 9 GB with no maintenance window. August 2026 expansion: the visibility map as VACUUM’s second job and the heap fetches that quietly degrade index-only scans, append-only tables with autovacuum_vacuum_insert_threshold and COPY FREEZE, catalog bloat from temp tables and DDL, parallel VACUUM and what it actually parallelizes, major upgrades with pg_upgrade and the loss of the cumulative statistics that trigger autovacuum, what is coming in PostgreSQL 19 (priority scoring with pg_stat_autovacuum_scores, autovacuum_max_parallel_workers and the new REPACK CONCURRENTLY command), and a pgbench test bench to validate your configuration before production does it for you.