Autovacuum Isn't Your Friend: A Field Guide to High-Write Postgres

TL;DR

• Autovacuum's default settings are tuned for low-write, small databases. At scale, they guarantee either massive bloat or disruptive I/O contention.
• Relying on global autovacuum_vacuum_scale_factor is a recipe for disaster. A 1-billion-row table and a 1000-row table do not need the same vacuum policy, yet that's what you get by default.
• The real emergency isn't bloat; it's the autovacuum_freeze_max_age. Hitting this threshold triggers an unthrottled, system-wide vacuum storm that will halt your application to prevent data corruption. This is your 3 AM page.

---

We've all been there. It’s 2 AM. A perfectly tuned query that was running in 50ms is now taking 3 seconds, or timing out entirely. APM charts are a sea of red. You check query plans, indexes, application code—nothing has changed. The culprit isn't your code. It's the database slowly eating itself alive from the inside. The janitor, autovacuum, who was supposed to be cleaning up quietly in the background, is either asleep on the job or has decided to use a leaf blower in the middle of a library.

I spent years on the Oracle query engine team, and I’ve seen every flavor of database self-sabotage. The Postgres autovacuum architecture, while elegant in theory, is one of the most common sources of production pain for high-throughput systems. Let's be clear: autovacuum is not an intelligent, adaptive system. It's a simple daemon running a simple loop with simple, global thresholds. At scale, simple breaks.

The Anatomy of Bloat

To understand the problem, you have to understand MVCC (Multi-Version Concurrency Control). In Postgres, an UPDATE is not an in-place modification. It’s a DELETE followed by an INSERT. The old version of the row, now a "dead tuple," is kept around in case other transactions still need to see it. VACUUM is the process that finally reclaims the space occupied by these dead tuples.

When does VACUUM run? The "auto" in autovacuum is controlled by two main settings:

• autovacuum_vacuum_threshold: A fixed number of dead tuples to trigger a vacuum.
• autovacuum_vacuum_scale_factor: A percentage of the table's size. A vacuum is triggered when dead_tuples > threshold + (scale_factor * table_size).

The default scale factor is 0.2 (20%). For a 10,000-row users table, that’s 2,000 dead tuples. Manageable. For your 1-billion-row events table? Autovacuum won't wake up until you have 200 million dead tuples. By then, your table is a bloated mess.

This bloat is poison:

1. Slower Sequential Scans: Any query that needs to read the whole table now has to scan through millions or billions of useless rows, wasting I/O and CPU.
2. Inefficient Indexes: Index-only scans become heap fetches because the visibility map marks pages as having dead tuples that need checking. Even when using an index, Postgres fetches entire pages (8KB blocks) from disk into the buffer cache. If that page is 90% dead tuples, you've wasted 90% of that I/O and precious cache space.
3. Buffer Cache Pollution: Those bloated pages loaded into memory displace more useful data, causing your cache hit rate to plummet and I/O to skyrocket.

You can see this happening with a simple query:

SELECT relname, n_live_tup, n_dead_tup, 
       (n_dead_tup * 100) / (n_live_tup + n_dead_tup + 1) as dead_tup_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_tup_pct DESC
LIMIT 10;

If you see large tables with dead_tup_pct in the double digits, you have a problem.

The Freeze Storm and the Wraparound Panic

Bloat is slow and painful. Transaction ID (TXID) wraparound is fast and fatal.

Every transaction in Postgres gets a 32-bit ID. These IDs are how Postgres determines which rows are visible to which transaction. But since it’s a 32-bit integer, it will eventually wrap around (after about 2 billion write transactions). If not handled, a transaction from the distant past could suddenly appear to be in the future, causing massive data corruption.

To prevent this, Postgres uses VACUUM FREEZE. This process scans the table and marks very old row versions as Frozen, meaning they are visible to all transactions, present and future. Autovacuum does this routinely in the background.

The danger zone is autovacuum_freeze_max_age (default: 200 million transactions). If any table in your database contains a row with a TXID older than this limit, Postgres panics. It launches an emergency, high-priority, un-throttled autovacuum process on that table. This isn't your friendly, cost-limited background vacuum. This is a five-alarm fire.

This "freeze storm" will consume as much I/O and CPU as it can get, starving your application. We saw a client where a freeze storm on a poorly-tuned 2TB table caused an I/O spike that saturated their io2 block store IOPS budget, increasing application p99 latency from 80ms to over 15,000ms for six hours. The application was effectively down.

If the freeze storm cannot complete its work before the TXID counter wraps fully, Postgres will take the ultimate step to protect your data integrity: it will stop accepting write commands and shut down.

PANIC: database is not accepting commands to avoid wraparound data loss
HINT: You may need to manual VACUUM FREEZE.

This is the database equivalent of a blue screen of death. It's the ultimate failure state that a DBA is paid to prevent, and ironically, it's often caused by the very mechanism designed to be automatic.

The Sisyphean Task of Manual Tuning

The standard advice is to tune autovacuum on a per-table basis.

-- For a large, high-write table
ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01, 
  autovacuum_vacuum_threshold = 10000,
  autovacuum_freeze_min_age = 10000000,
  autovacuum_freeze_table_age = 60000000
);

-- For a small, static table
ALTER TABLE countries SET (
  autovacuum_vacuum_scale_factor = 0.4,
  autovacuum_vacuum_threshold = 5000
);

This works. But it turns into a full-time job. You need to write custom monitoring scripts against pg_stat_user_tables and age(datfrozenxid). You have to know the write patterns of every single table. A new microservice comes online, adds a new high-churn audit_log table, and forgets to configure its vacuum settings. Three months later, you're back in the fire-fighting business. This is not scalable systems engineering.

You're essentially doing the work the database should be doing for you: observing its own workload and adapting. But out of the box, it can't.

What DeepSQL does about this

Blindly applying global settings is malpractice at scale. DeepSQL treats vacuum tuning as a continuous optimization problem. By analyzing pg_stat tables and query history, our system models the insert, update, and delete velocity for every significant table in your database. It then generates precise ALTER TABLE commands to set aggressive, non-default vacuum thresholds on high-churn tables and relaxed ones on static tables, ensuring vacuums are frequent, small, and non-disruptive. We monitor the age(datfrozenxid) for every table and database to forecast freeze pressure weeks in advance, flagging tables that are falling behind long before they trigger an emergency. When a table's bloat becomes unmanageable even with tuning, we recommend structural changes like partitioning and provide the DDL and application-level query changes to make it happen.