PostgreSQL vacuum internals: fixing write-pattern bloat

Meta description: Learn how to tune PostgreSQL autovacuum for high-write mobile backends with per-table overrides, bloat detection queries, and visibility map leverage for index-only scans.

---

TL;DR

Default PostgreSQL autovacuum settings were designed for balanced read/write workloads — not the upsert storms, session tables, and event streams that mobile backends generate. At scale, this mismatch produces table bloat that invisibly degrades query performance while pg_stat_user_tables estimate counters lag behind reality. The fix: per-table autovacuum overrides, accurate bloat queries, and visibility map coverage for index-only scans.

---

The problem with mobile backend write patterns

Mobile write patterns strain PostgreSQL’s vacuum defaults harder than traditional OLTP. A delivery service backend sustains location pings every 2–5 seconds per active user, session state upserts, notification event queues, and order lifecycle transitions. A 500K-row session table receiving 2,000 writes per minute generates over 100K dead tuples before autovacuum’s default 20% threshold even triggers — and that single table is rarely the only high-churn relation in a production schema. These sustained, asymmetric write loads expose PostgreSQL’s vacuum defaults as dangerously conservative.

PostgreSQL’s MVCC model never overwrites rows in place. Every UPDATE creates a new row version; the old version becomes a dead tuple. Autovacuum is supposed to clean these up, but its cost-delay throttle means it cannot keep pace with high-frequency upsert tables.

---

How autovacuum’s defaults fail you

Two settings do the most damage:

• autovacuum_vacuum_scale_factor = 0.2 — vacuum triggers when 20% of rows are dead
• autovacuum_vacuum_cost_delay = 2ms — sleep between I/O cost units to limit disk impact

For a user_sessions table with 500K rows, 20% means 100,000 dead tuples accumulate before vacuum fires. On a write-heavy event table with millions of rows, you may never catch up.

Table type	Default trigger	Recommended override
Event stream (millions of rows)	20% = 2M dead tuples	scale_factor = 0.01, cost_delay = 0
Session state (high-churn)	20% = 100K dead tuples	scale_factor = 0.05, threshold = 100
Notification queue	20% of queue depth	scale_factor = 0.01, cost_delay = 0
Reference/lookup tables	Default is fine	Leave defaults alone

---

Per-table autovacuum overrides

Stop applying global autovacuum settings uniformly. PostgreSQL supports per-table storage parameters — use them:

ALTER TABLE user_events SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_cost_delay = 0,
  autovacuum_vacuum_threshold = 500,
  autovacuum_analyze_scale_factor = 0.005
);

ALTER TABLE user_sessions SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_cost_delay = 0,
  autovacuum_vacuum_threshold = 100
);

Setting cost_delay = 0 disables the I/O throttle for these tables — necessary when you need vacuum to outrun your write rate. Accept the disk pressure; the alternative is bloat that stalls your entire query path.

---

Detecting bloat: why pg_stat_user_tables lags under write load

pg_stat_user_tables reports n_dead_tup as an estimate updated by autovacuum — it lags reality badly under sustained write load. Use this instead:

SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
  n_dead_tup,
  n_live_tup,
  ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_live_tup > 1000
ORDER BY dead_pct DESC
LIMIT 20;

For accurate measurement, install pgstattuple and run:

SELECT * FROM pgstattuple('public.user_events');

The dead_tuple_percent field reads actual page data, not stats estimates. Alert on this number, not the estimate.

---

Visibility map and index-only scans

Most teams treat vacuum as a space-reclamation job. It’s that, but it’s also what makes index-only scans work. When vacuum confirms all tuples on a page are visible to all transactions, it marks that page in the visibility map. Once marked, PostgreSQL can satisfy index lookups without touching the heap at all.

On high-write tables that never get properly vacuumed, the visibility map stays perpetually dirty. Index-only scans fall back to heap fetches on every row. In practice on NVMe storage, index-only scans run in roughly 0.1–0.3ms per fetch versus 1–3ms for heap fetches — a 5–10x latency gap that compounds under concurrent read load. Push that far enough and the planner stops choosing your indexes.

Monitor visibility map health:

SELECT
  pg_stat_user_tables.schemaname,
  pg_stat_user_tables.tablename,
  pg_class.all_visible,
  pg_class.relpages,
  (pg_class.all_visible::float / NULLIF(pg_class.relpages, 0) * 100)::int AS pct_visible
FROM pg_class
JOIN pg_stat_user_tables
  ON pg_stat_user_tables.tablename = pg_class.relname
  AND pg_stat_user_tables.schemaname = (
    SELECT nspname FROM pg_namespace WHERE oid = pg_class.relnamespace
  )
WHERE pg_class.relkind = 'r' AND pg_class.relpages > 100
ORDER BY pct_visible ASC;

Tables below 70% visible are candidates for VACUUM ANALYZE and tighter autovacuum configuration. A table sitting at 40% visible is paying full heap I/O on every index scan you thought you’d already optimized. Keep this above 90% on any table where your query planner depends on index-only access paths.

---

3 actionable takeaways

1. Override autovacuum per-table for any table receiving more than 1,000 writes per minute. Set scale_factor = 0.01 and cost_delay = 0. Global defaults won’t save high-write tables.

2. Stop relying on pg_stat_user_tables alone. Install pgstattuple and schedule a job that alerts when dead_tuple_percent exceeds 5% on high-churn tables — catch bloat before it reaches your query planner.

3. Monitor visibility map coverage on every table you expect index-only scans from. Below 70% visible means you’re paying heap I/O costs you think you’ve already eliminated. Run VACUUM ANALYZE and tighten autovacuum thresholds until that number holds above 90%.

---

About the author

I run HealthyDesk between sessions like this one — it nudges me through a desk stretch after extended deep-focus debugging, which matters when you’re three hours into pg_stat pages and haven’t moved. Find it on Google Play.

---

Tags: backend mobile architecture devops