PostgreSQL index bloat: fix P99 latency in mobile backends

Meta description: Mobile backends accumulate silent PostgreSQL index bloat from high-churn upserts and deletes. Learn to measure bloat with pgstattuple, tune autovacuum, and deploy pg_repack before P99 craters.

Tags: backend mobile api architecture microservices

---

TL;DR

Autovacuum defaults are calibrated for general OLTP workloads. Mobile backends, with their aggressive upsert/delete patterns, accumulate index bloat silently until the query planner starts making wrong choices and your P99 doubles overnight. Measure first with pgstattuple, tune autovacuum_vacuum_scale_factor aggressively, and reach for pg_repack before you ever consider VACUUM FULL.

---

The silent killer in your mobile backend

In my experience building production systems that serve mobile clients, the failure mode nobody talks about is index bloat. Not slow queries. Not missing indexes. Bloat — the dead tuple accumulation that makes your perfectly-designed schema gradually rot under load.

Mobile traffic patterns are pathological for PostgreSQL’s default assumptions. Consider what a typical session tracking or presence table looks like in production:

• User opens app → upsert session row
• Heartbeat every 30 seconds → update last_seen
• App backgrounds → delete or soft-delete row

At 500k daily active users, that’s millions of dead tuples accumulating per hour. PostgreSQL’s MVCC model keeps old row versions visible until VACUUM reclaims them. When VACUUM doesn’t keep up, index pages fill with pointers to dead tuples. That’s where the trouble starts.

---

Measuring bloat before it bites you

Don’t guess at this. Use pgstattuple to get ground truth:

SELECT
  relname,
  pg_size_pretty(pg_relation_size(oid)) AS table_size,
  dead_tuple_percent,
  free_percent
FROM pgstattuple_approx('sessions')
JOIN pg_class ON relname = 'sessions';

For indexes specifically:

SELECT
  indexrelname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  round(avg_leaf_density::numeric, 2) AS fill_density
FROM pgstatindex('sessions_user_id_idx');

A avg_leaf_density below 60% is a warning sign. Below 50% and your query planner is likely underestimating index scan costs, triggering sequential scans on tables that absolutely should be using the index.

---

Why autovacuum defaults fail mobile workloads

PostgreSQL ships with autovacuum tuned conservatively. The critical defaults:

Parameter	Default	Problem
autovacuum_vacuum_scale_factor	0.2 (20%)	Waits for 20% of rows to be dead before triggering
autovacuum_vacuum_threshold	50 rows	Minimum rows before scale_factor applies
autovacuum_vacuum_cost_delay	2ms	Throttles I/O to avoid impacting OLTP
autovacuum_max_workers	3	Serializes cleanup on busy schemas

For a sessions table with 2 million rows, 20% means 400,000 dead tuples accumulate before autovacuum fires. On a mobile backend doing 10k upserts/minute, that’s a 40-minute bloat window per cycle — and autovacuum may not finish before the next wave arrives.

The tuning prescription

Apply these at the table level for high-churn tables, not globally:

ALTER TABLE sessions SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 100,
  autovacuum_vacuum_cost_delay = 0,
  autovacuum_analyze_scale_factor = 0.005
);

A scale_factor of 0.01 means VACUUM triggers when 1% of rows are dead — 20,000 rows on a 2M table rather than 400,000. Combined with zero cost delay, autovacuum runs at full speed on this specific table without throttling your other workloads.

---

pg_repack vs VACUUM FULL vs fillfactor: choosing your weapon

Most teams reach for VACUUM FULL because it sounds thorough. It is thorough, and it holds an ACCESS EXCLUSIVE lock for the entire duration. On a table serving mobile traffic, that means downtime.

Approach	Downtime	Reclaims Bloat	Rebuilds Indexes	When to Use
VACUUM	None	Dead tuples only	No	Ongoing maintenance
VACUUM FULL	Full table lock	Yes, fully	Yes	Never in production
fillfactor tuning	None	Prevents future bloat	No (on next rebuild)	New tables or after repack
pg_repack	None (online)	Yes, fully	Yes	Production bloat recovery

pg_repack works by building a shadow copy of the table and its indexes while the original remains live, then performing a fast swap. The lock window is seconds, not minutes.

pg_repack --no-superuser-check -t sessions -d mydb

Pair this with a fillfactor of 70–80 on high-update tables to leave room for in-place HOT updates, reducing future index churn:

ALTER TABLE sessions SET (fillfactor = 75);

---

The dead tuple threshold that breaks your planner

The query planner regression is the insidious part. PostgreSQL’s cost estimator uses pg_statistic, but statistics don’t account for index page density. When bloat crosses roughly 30–40% dead tuples in an index, the planner’s effective row estimates drift enough to prefer sequential scans. This shows up as P99 spikes on read-heavy endpoints before your dead tuple monitoring alert even fires.

Monitor n_dead_tup / n_live_tup ratio via pg_stat_user_tables and alert at 10%, not the default autovacuum threshold of 20%.

---

3 actionable takeaways

1. Tune autovacuum per-table, not globally. Set autovacuum_vacuum_scale_factor = 0.01 on every high-churn table in your mobile backend. The global default of 0.2 will hurt you.

2. Use pg_repack for existing bloat, then set fillfactor = 75–80. VACUUM FULL on live traffic means downtime. pg_repack gives you a clean slate without it.

3. Alert on dead tuple ratio at 10%, not when autovacuum triggers. By the time autovacuum fires at 20%, your query planner may already be making bad choices. Instrument pg_stat_user_tables.