PostgreSQL Autovacuum Tuning for High-Churn SaaS Tables

Meta description: Learn how to tune PostgreSQL autovacuum cost-delay knobs and apply per-table storage overrides to eliminate table bloat in high-churn SaaS workloads at scale.

---

TL;DR

Global autovacuum settings are a deliberate compromise — they protect I/O for the whole cluster while leaving high-churn tables chronically under-vacuumed. The fix is per-table ALTER TABLE ... SET (autovacuum_*) overrides that give hot tables an aggressive vacuum budget without starving the rest of the cluster. This post covers the cost-throttling internals, how to identify offenders, and the exact parameters to tune.

---

The problem: dead tuples accumulate faster than autovacuum can drain them

PostgreSQL’s MVCC model never updates rows in-place. Every UPDATE writes a new row version and marks the old one dead. DELETE does the same. Dead tuples stay on-disk until VACUUM reclaims them. For a SaaS events or audit table processing tens of thousands of writes per second, the backlog compounds fast.

Most teams see n_dead_tup climbing in pg_stat_user_tables and assume autovacuum is broken. It isn’t — it’s throttled by design. PostgreSQL deliberately slows down autovacuum workers to avoid saturating disk I/O during normal operation. The knobs controlling that throttle are the root cause of most table bloat incidents I’ve diagnosed in production.

---

How cost-based throttling works internally

Autovacuum accumulates a “cost” for every page it touches, then sleeps once it hits a budget ceiling:

Parameter	Default	Meaning
vacuum_cost_page_hit	1	Page found in shared_buffers
vacuum_cost_page_miss	2	Page fetched from disk
vacuum_cost_page_dirty	20	Page modified (requires write-back)
vacuum_cost_limit	200	Budget accumulated before sleeping
vacuum_cost_delay	2ms	Sleep duration after hitting the limit

Do the arithmetic. A worker hitting mostly dirty pages — the common case on a high-churn table — burns 20 cost units per page. At a limit of 200, it processes 10 dirty pages per cycle, then sleeps 2ms. That caps throughput at roughly 5,000 dirty pages per second under ideal conditions. A table with 10GB of dead-tuple bloat at that rate takes hours to fully vacuum, during which new dead tuples keep arriving.

---

Identifying tables where global settings cause lag

Query pg_stat_user_tables to surface the offenders before they become a production incident:

SELECT
  schemaname,
  relname,
  n_dead_tup,
  n_live_tup,
  round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_ratio_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY dead_ratio_pct DESC;

Any table with dead_ratio_pct consistently above 5–10% and a last_autovacuum timestamp that lags several hours is a candidate for per-table intervention. From what I’ve seen in production, a dead ratio above 15% on a frequently-queried table also degrades planner estimates — bloated statistics cause index scan regressions that show up as unexplained query latency spikes.

---

The per-table override strategy

ALTER TABLE accepts storage parameters that shadow global autovacuum settings at the table level. Most teams never reach for this.

-- Override for a high-churn events or audit log table
ALTER TABLE user_events SET (
  autovacuum_vacuum_cost_delay   = 0,     -- disable sleep between cycles
  autovacuum_vacuum_cost_limit   = 800,   -- 4x the global budget per cycle
  autovacuum_vacuum_scale_factor = 0.01,  -- trigger at 1% dead tuples (default: 20%)
  autovacuum_analyze_scale_factor = 0.005 -- keep statistics fresh
);

Setting autovacuum_vacuum_cost_delay = 0 removes the sleep entirely for this table’s worker. Each autovacuum worker targets a single table at a time, so disabling throttling on user_events doesn’t affect the worker cleaning billing_invoices on the same cluster — they’re independent processes with independent cost accumulators.

The scale factor matters just as much. The default of 0.2 means autovacuum won’t trigger until 20% of the table consists of dead tuples. On a 50-million-row table, that’s 10 million dead rows before cleanup even starts — a massive working set that makes each cycle expensive. At 0.01, you trigger at 500,000 dead rows, keeping individual vacuum cycles fast and incremental.

---

Impact: before and after per-table overrides

Metric	Default global settings	With per-table overrides
Steady-state dead tuple ratio	15–25%	< 2%
Table size growth over 30 days	+30–40% bloat	Stable
Autovacuum cycle duration	Hours	Minutes
Query planner regressions	Frequent	Rare
I/O impact on other tables	Baseline	Unchanged

---

What to actually do

1. Alert on dead_ratio_pct > 10%. Waiting for query latency regressions to surface bloat means you’re already in a degraded state.

2. Use per-table ALTER TABLE overrides, not global parameter changes. Dropping vacuum_cost_delay globally risks I/O contention across the entire cluster. Targeting individual tables with autovacuum_vacuum_cost_delay = 0 is surgical.

3. Lower autovacuum_vacuum_scale_factor for large tables. The 20% default was calibrated for small tables in the single-digit millions. At SaaS scale, set it to 0.01 or lower on your highest-churn tables — smaller triggers mean smaller working sets and faster, more frequent cycles that never fall behind.

---

Tags: backend saas architecture microservices api