Database Autovacuum Falling Behind

When ghost rows eat all your disk space and slow down queries.

The idea

In databases like PostgreSQL, when you UPDATE or DELETE a row, the old data isn't immediately erased. It is marked as "dead" (a ghost row). A background process called Autovacuum periodically sweeps through the disk to delete these ghosts and reclaim space. But if you have a massive spike in updates, Autovacuum might "fall behind." The ghosts accumulate, bloating the tables, filling up the hard drive, and making every SELECT query scan through millions of dead rows.

Step 1: Normal operations. Dead rows are created, and Autovacuum easily cleans them up.

How it works (MVCC and Vacuuming)

Postgres uses Multi-Version Concurrency Control (MVCC). It keeps old rows around so that long-running SELECT queries can read the "past" while other transactions update the present. Autovacuum is tuned by default to be very gentle so it doesn't hurt CPU performance. But on a write-heavy database, "gentle" means it loses the race against your application.

-- Checking for Table Bloat in Postgres
SELECT relname, n_dead_tup, n_live_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 100000;

-- Tuning Autovacuum to be more aggressive
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.05;
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 2000;
SELECT pg_reload_conf();

Cost

Tuning Autovacuum is a trade-off. If you make it too aggressive, it steals I/O bandwidth and CPU from your actual user queries, slowing down the app. If you make it too weak, table bloat causes sequential scans to take 10x longer. You have to find the sweet spot.

Watch out for