SoftwareDevs mvpfactory.io

Posted on Nov 2 Originally published at mvpfactory.io

PostgreSQL Autovacuum Tuning for High-Churn SaaS Tables

#webdev #programming

---
title: "PostgreSQL Autovacuum Tuning for High-Churn SaaS Tables"
published: true
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."
tags: postgresql, performance, architecture, cloud
canonical_url: https://mvpfactory.co/blog/postgresql-autovacuum-tuning-high-churn-saas
---

What We Will Build

By the end of this tutorial you will know how PostgreSQL's cost-based autovacuum throttle works internally, how to identify tables where the global defaults are actively causing bloat, and how to apply surgical per-table ALTER TABLE overrides that let your hottest tables vacuum aggressively — without starving I/O for the rest of the cluster.

This is a pattern I apply in every SaaS production PostgreSQL cluster I've worked on. The global defaults are a compromise. Your high-churn tables deserve better.

Prerequisites

PostgreSQL 12+ (storage parameters work on earlier versions, but pg_stat_user_tables columns vary)
psql access with at least SELECT on pg_stat_user_tables
ALTER TABLE privileges on tables you want to tune
A basic understanding of PostgreSQL MVCC (multi-version concurrency control)

Step 1 — Understand Why Dead Tuples Accumulate

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.

Most teams see n_dead_tup climbing in pg_stat_user_tables and assume autovacuum is broken. It is not — it is 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 have diagnosed in production.

Step 2 — Learn the Cost-Based Throttling Internals

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

Here is the gotcha that will save you hours: do the arithmetic on a high-churn table.

A worker hitting mostly dirty pages — the common case on an events or audit log 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.

Step 3 — Identify Your Offending Tables

Run this query against 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.

The docs do not mention this prominently, but 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 — hard to diagnose if you are not watching n_dead_tup.

Step 4 — Apply Per-Table Storage Overrides

ALTER TABLE accepts storage parameters that shadow global autovacuum settings at the table level. Here is the minimal setup to get this working on 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
);

Let me show you a pattern I use in every project: set autovacuum_vacuum_cost_delay = 0 to remove the sleep entirely for this table's worker. Each autovacuum worker targets a single table at a time, so disabling throttling on user_events does not affect the worker cleaning billing_invoices on the same cluster — they are independent processes with independent cost accumulators.

The scale factor matters just as much. The default 0.2 means autovacuum will not trigger until 20% of the table consists of dead tuples. On a 50-million-row table, that is 10 million dead rows before cleanup even starts. At 0.01, you trigger at 500,000 dead rows, keeping individual vacuum cycles fast and incremental.

Step 5 — Verify the Impact

After applying overrides, monitor the same query from Step 3. Here is what you should expect over the following 24–48 hours.

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

Gotchas

Do not change global vacuum_cost_delay instead. Dropping the delay globally risks I/O contention across the entire cluster. The per-table approach is surgical — it does not affect workers on other tables.

Do not skip the analyze_scale_factor. Vacuuming reclaims space, but without fresh ANALYZE statistics your planner estimates will still diverge on high-churn tables. Set autovacuum_analyze_scale_factor = 0.005 alongside the vacuum overrides.

Alert before query latency degrades. Set an alert on dead_ratio_pct > 10%. Waiting for latency regressions to surface bloat means you are already in a degraded state.

Large tables need lower scale factors. The 20% default was calibrated for small tables in the single-digit millions. At SaaS scale, 0.01 or lower is the right default for your highest-churn tables.

Conclusion

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 not to touch the globals. It is to reach for ALTER TABLE ... SET (autovacuum_*) overrides and give your hottest tables an aggressive vacuum budget that does not compete with the rest of the cluster.

Identify offenders with pg_stat_user_tables, apply the four-parameter override block above, and watch n_dead_tup stabilize. That is the entire strategy.

Further reading:

DEV Community