autovacuum is a boolean. Default on, context sighup. Set it to off for any meaningful length of time and you have purchased a tour of every PostgreSQL failure mode worth knowing about, in escalating order, at no extra charge. Let me describe the tour.
The autovacuum launcher and its workers run VACUUM and ANALYZE against tables based on accumulated activity. It is the single most important background process in the database. People nevertheless turn it off, usually because vacuum is “causing performance problems,” because the table is “write-only,” or because they read a forum post written by someone who lost an argument with PostgreSQL in 2008. None of these reasons survive contact with the consequences.
The parade:
Heap and index bloat. Updates and deletes leave dead tuples that no transaction can see but the disk still has to hold. Your 10GB table becomes 40GB on disk with the same 10GB of live data in it. Sequential scans slow down proportionally. Indexes bloat too; B-tree pages don’t reclaim space without vacuum’s help.
Stale statistics. Autovacuum also runs ANALYZE. Without it, the planner’s row counts and column distributions drift from reality. Queries that were fast last month start picking different — worse — plans. The change is gradual, which makes it harder to attribute and easier to misdiagnose.
Index-only scans stop working. The visibility map is maintained by vacuum. Without an accurate visibility map, the planner cannot prove that the heap visit is unnecessary, and the index-only-scan optimization becomes an ordinary index scan plus heap fetches. Hot read paths get slower without anyone changing a query.
The free space map goes stale. INSERTs spend more cycles looking for free pages and end up extending the relation when they didn’t need to. Tables grow faster than they should, which makes the bloat worse, which makes everything else worse.
TOAST tables bloat invisibly. Every long text or bytea column has a TOAST table behind it that you never see in \d. It accumulates dead tuples too. It will not get vacuumed. Eventually you run out of disk and have to figure out where the space went.
MultiXact wraparound risk. Workloads that use row-level locks — SELECT FOR UPDATE, foreign keys, certain DDL — generate MultiXact IDs. They are 32-bit. They wrap. Without vacuum freezing them, you can hit the wraparound limit and the database will refuse the relevant operations until you’ve recovered. I have personally diagnosed an outage where the proximate cause was MultiXact SLRU contention; it is not a theoretical failure mode.
Then the end: transaction ID wraparound. PostgreSQL uses 32-bit transaction IDs. Vacuum’s most critical job is freezing old tuples so the ID space can be reused. About 200 million transactions in, autovacuum begins emergency anti-wraparound work and the warnings start. About two billion in, PostgreSQL refuses new write transactions to protect itself, and the only path back is VACUUM FREEZE — possibly in single-user mode if you ran the runway all the way out. There is no graceful recovery. There is only a long, embarrassed VACUUM while your customers wait.
Recommendation: Leave it on. If autovacuum is causing performance problems, the answer is to tune it — autovacuum_vacuum_cost_limit, autovacuum_naptime, per-table storage parameters — not to disable it. Each of those knobs gets its own post in this series. They are how you make autovacuum the right size for your workload, not how you turn it off. The only legitimate reason to set autovacuum = off globally is “I am about to bulk-load several billion rows and will turn it back on the moment I’m done.” For one specific append-only table, ALTER TABLE ... SET (autovacuum_enabled = false) is the right tool. The global switch is not for you.