PostgreSQL Index Bloat in High-Churn Mobile Backends: VACUUM Tuning, pg_repack, and the Dead Tuple Ceiling That Kills Your P99

---
title: "PostgreSQL Index Bloat: Fix P99 Latency in Mobile Backends"
published: true
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: postgresql, mobile, api, architecture
canonical_url: https://mvpfactory.co/blog/postgresql-index-bloat-mobile-backends
---

What You Will Learn

By the end of this workshop you will know how to detect PostgreSQL index bloat before it degrades production, tune autovacuum for mobile traffic patterns, and recover from existing bloat without taking downtime. The failure mode we are fixing is silent — no error logs, no obvious symptoms — until your P99 doubles overnight.

Prerequisites

• A running PostgreSQL 14+ instance
• The pgstattuple extension available (CREATE EXTENSION pgstattuple)
• pg_repack installed on the server
• Basic familiarity with ALTER TABLE and EXPLAIN ANALYZE

---

Step 1 — Understand Why Mobile Backends Are Pathological

Mobile traffic creates a specific pattern PostgreSQL's defaults were not designed for. A presence or session table in production looks like this:

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

At 500k daily active users that is millions of dead tuples per hour. PostgreSQL's MVCC model keeps old row versions around until VACUUM reclaims them. When VACUUM does not keep up, index pages fill with pointers to dead tuples. That is where your P99 goes to die.

Step 2 — Measure Before You Touch Anything

Let me show you a pattern I use in every project. Never tune what you have not measured.

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 index-level density:

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');

An avg_leaf_density below 60% is a warning. Below 50% and your query planner is actively making wrong decisions — choosing sequential scans on tables that should be using the index.

Step 3 — Tune Autovacuum Per-Table

Here is the gotcha that will save you hours: the global autovacuum_vacuum_scale_factor default is 0.2. On a 2 million row sessions table, that means 400,000 dead tuples accumulate before autovacuum fires. At 10k upserts per minute, you have a 40-minute bloat window per cycle — and autovacuum may not finish before the next wave arrives.

Do not change global settings. Apply surgical per-table tuning:

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 triggers VACUUM at 1% dead rows — 20,000 on a 2M table instead of 400,000. Zero cost delay lets autovacuum run at full speed on this table without throttling other workloads.

Step 4 — Recover Existing Bloat With pg_repack

The docs do not mention this clearly enough, but VACUUM FULL holds an ACCESS EXCLUSIVE lock for its entire duration. On a live mobile backend, that is downtime. Use pg_repack instead:

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

pg_repack builds a shadow copy of the table and its indexes while the original stays live, then performs a fast swap. The lock window is seconds, not minutes.

After repacking, set fillfactor to leave room for HOT updates:

ALTER TABLE sessions SET (fillfactor = 75);

This reduces index churn on future updates by allowing in-place row updates when a page has space.

---

Gotchas

The planner regression fires before your alert does. When bloat crosses roughly 30–40% dead tuples in an index, PostgreSQL's cost estimator starts preferring sequential scans. This shows up as P99 spikes on read endpoints before your dead tuple monitoring alert fires. Set your alert threshold at 10% on n_dead_tup / n_live_tup from pg_stat_user_tables — not at 20%.

Never run VACUUM FULL on production. It sounds thorough. It is thorough, and it will lock your table for minutes.

Per-table tuning does not survive a CREATE TABLE AS SELECT. If you migrate or re-create a table, reapply these settings in your migration script.

---

Conclusion

Three changes will protect your mobile backend from this failure mode:

1. Set autovacuum_vacuum_scale_factor = 0.01 on every high-churn table. The global default of 0.2 will hurt you.
2. Use pg_repack for existing bloat, then set fillfactor = 75–80. Never reach for VACUUM FULL on live traffic.
3. Alert on dead tuple ratio at 10%, not at the autovacuum trigger threshold of 20%. By the time autovacuum fires, your query planner may already be choosing sequential scans.

Further reading: pgstattuple docs · pg_repack · autovacuum tuning — PostgreSQL wiki