Case study
The Autovacuum That Couldn't Keep Up
A client disabled PostgreSQL autovacuum on their biggest tables because it caused I/O spikes. Six months later, the database was bloated, queries were crawling, and they were 62% of the way to a forced shutdown.
A logistics company called me in last month because their API response times had been creeping up for weeks. The pattern was steady: everything was fine back in spring, but by October, p95 latency had gone from 120ms to 900ms. They'd already thrown hardware at it — more memory, faster disks, an extra read replica. Each upgrade bought them a week of relief before the slowdown resumed.
Their DBA had left two months earlier. Nobody had touched the database configuration since.
The first thing I checked
When a PostgreSQL database gets slower over time without any obvious application changes, I start with one query:
SELECT
schemaname,
relname,
n_live_tup,
n_dead_tup,
round(n_dead_tup::numeric / greatest(n_live_tup, 1) * 100, 1) AS dead_pct,
last_autovacuum,
last_vacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;The results made me sit up straight. Their orders table had 48 million live rows and 112 million dead tuples. The order_items table was worse — 180 million dead rows sitting alongside 95 million live ones. Neither table had been vacuumed in over six months.
The dead-to-live ratio was over 2:1. For every real row in the orders table, there were two ghost rows that PostgreSQL was forced to wade through on every sequential scan.
Why the ghosts were there
PostgreSQL doesn't delete rows in place. When you UPDATE or DELETE a row, the old version stays on disk — that's a "dead tuple." Autovacuum is the background process that cleans them up, reclaims space, and updates the visibility map so index-only scans can skip heap fetches. Without it, dead tuples pile up, tables balloon, and indexes fill with pointers to data that no longer exists.
I checked the table-level settings:
SELECT relname, reloptions
FROM pg_class
WHERE relname IN ('orders', 'order_items', 'shipments');There it was: {autovacuum_enabled=false} on all three tables.
The former DBA had disabled autovacuum six months ago. I found the Slack message explaining why: "autovacuum is causing I/O spikes during business hours, disabling on large tables until we can schedule maintenance windows." The maintenance windows never happened. Then he left. The tables kept accumulating dead rows, one transaction at a time.
The part that scared me
Dead tuples and bloated tables are bad enough. But there was a bigger problem underneath.
PostgreSQL uses a 32-bit transaction ID counter that wraps around at roughly 2.1 billion. When the counter approaches that limit, the database enters emergency autovacuum mode. If that doesn't free up transaction IDs fast enough, PostgreSQL refuses all writes — a hard shutdown to prevent data corruption. There's no override. No flag to skip it. The database protects itself whether you like it or not.
I ran the check:
SELECT
datname,
age(datfrozenxid) AS xid_age,
round(age(datfrozenxid)::numeric / 2100000000 * 100, 1) AS pct_to_wraparound
FROM pg_database
WHERE datname = 'production';They were at 1.3 billion — 62% of the way to wraparound. At their transaction rate, roughly four months from a forced shutdown.
That's the kind of number that makes you close your laptop, take a breath, and then calmly explain to the VP of Engineering that this needs to happen now, not next quarter.
Warning
age(datfrozenxid) is above 500 million on any of your PostgreSQL databases, check your autovacuum health today. Above 1 billion is a red flag. Above 1.5 billion means you're running out of time.The fix wasn't a single command
You can't just flip autovacuum back on when the backlog is this deep. Running VACUUM on a 180GB bloated table during business hours would saturate the disks and make the latency problem worse before it got better.
We worked through it in phases over two weeks.
Week one was manual VACUUM during the 2 AM–6 AM low-traffic window, one table at a time, with I/O throttling to avoid slamming production:
SET vacuum_cost_delay = 20;
SET vacuum_cost_limit = 200;
VACUUM VERBOSE orders;We watched n_dead_tup drop daily and tracked disk I/O to make sure we weren't trading one problem for another.
Week two, once the backlog was manageable, we re-enabled autovacuum with tuned settings instead of the defaults:
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_vacuum_cost_delay = 10,
autovacuum_vacuum_cost_limit = 1000
);The default scale factor is 0.2, meaning autovacuum kicks in after 20% of the table is dead tuples. For a 50-million-row table, that's 10 million dead rows before cleanup even starts. We lowered it to 0.01 — cleanup begins after 500,000 dead rows. Still plenty of buffer, but it keeps the problem from compounding.
What changed
Within three days of the first manual vacuum, p95 latency dropped from 900ms back to 180ms. The orders table shrank from 180GB to 52GB on disk. Index-only scans started working again because the visibility map was finally accurate.
By the end of week two, the transaction ID age had fallen from 1.3 billion to under 200 million.
The whole engagement took twelve days. Most of that time was waiting for vacuum to chew through the backlog during maintenance windows. The diagnosis itself took about two hours.
The actual lesson
Disabling autovacuum is like disconnecting a smoke alarm because it beeps when you cook. The I/O spikes the DBA was trying to avoid were the database doing necessary work — cleaning up after every write operation. The correct fix was to tune the aggressiveness, schedule around peak hours, or add I/O capacity. Not to turn it off.
If you manage a PostgreSQL database and you're not periodically checking pg_stat_user_tables for dead tuple buildup and pg_database for transaction ID age, add those queries to your monitoring dashboard. They take seconds to run and can flag problems months before they bite you.
The former DBA didn't make a reckless call. He made a reasonable short-term fix and left a note about the follow-up. The follow-up just never happened, and nobody knew to watch for it. That's how most of the incidents I get called into work. Not bad engineering — good engineering with a shelf life that expired quietly.