PostgreSQL index bloat: fix B-Tree fragmentation in mobile backends

Meta description: Learn how to detect real PostgreSQL index bloat using pgstattuple, tune FILLFACTOR for mobile write patterns, and run REINDEX CONCURRENTLY without downtime.

Tags: backend api mobile architecture microservices

TL;DR

pg_stat_user_indexes will not tell you your indexes are rotting. Under insert-heavy mobile workloads — user events, telemetry, session writes — B-Tree indexes fragment silently. Use pgstattuple to measure real bloat. Set FILLFACTOR based on your access pattern. Run REINDEX CONCURRENTLY when bloat exceeds ~30%. Zero downtime required.

The problem most teams discover too late

In my experience building production systems that ingest mobile telemetry at scale, index bloat is the silent killer. Your queries slow down gradually — 5ms, then 12ms, then 40ms — and your team starts blaming the ORM, the network, the mobile client. Almost never do they look at index fragmentation first.

What most teams get wrong: PostgreSQL’s B-Tree indexes are optimized for read-heavy workloads with scattered updates. Mobile backends are the opposite. You have millions of devices firing insert-heavy streams — session starts, tap events, crash logs, heartbeats. Each insert splits B-Tree leaf pages. Dead tuples accumulate. Pages fill unevenly. Your index grows 3x its logical size, and now every index scan is dragging across fragmented pages on disk.

Why `pg_stat_user_indexes` lies to you

The first instinct is to query the built-in stats view:

SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE schemaname = 'public';

This tells you usage, not health. It has no concept of internal fragmentation. An index can have 80% dead space and pg_stat_user_indexes will show you a clean row count and cheerful scan numbers.

To measure actual bloat, reach for pgstattuple:

SELECT
  indexrelname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  (st.free_space::float / pg_relation_size(indexrelid) * 100)::int AS bloat_pct
FROM pg_stat_user_indexes ui
JOIN LATERAL pgstattuple(ui.indexrelid) st ON true
WHERE schemaname = 'public'
ORDER BY bloat_pct DESC;

The numbers can be ugly. I have seen mobile event tables with indexes reporting 60–70% free space — meaning over half the index is dead weight your disk head is traversing on every scan.

FILLFACTOR tuning by access pattern

FILLFACTOR controls how full PostgreSQL packs each B-Tree page on initial write. The default is 90. For insert-heavy workloads with no in-place updates, dropping this further reserves space for future insertions on existing pages, reducing page splits.

Workload Pattern	Recommended FILLFACTOR	Rationale
Append-only event log	70–75	Heavy inserts, no updates; reduce splits
Session/presence data	80	Mixed insert + update on active rows
User profile / config	90 (default)	Low write velocity, read-heavy
Time-series telemetry	70	Sequential inserts, high volume

Apply FILLFACTOR at index creation or rebuild time:

CREATE INDEX CONCURRENTLY idx_events_user_id
ON user_events(user_id)
WITH (fillfactor = 70);

FILLFACTOR does not retroactively compact existing bloat — it only governs page packing going forward. If your index is already fragmented, you need to rebuild it.

The REINDEX CONCURRENTLY playbook

Before PostgreSQL 12, a REINDEX acquired an ACCESS EXCLUSIVE lock — table offline, queries blocked, on-call pager lights up. Since PostgreSQL 12, REINDEX CONCURRENTLY builds the new index in the background while traffic flows normally.

Safe production sequence:

-- 1. Confirm bloat threshold is worth the rebuild
SELECT indexrelname,
       (st.free_space::float / pg_relation_size(indexrelid) * 100)::int AS bloat_pct
FROM pg_stat_user_indexes ui
JOIN LATERAL pgstattuple(ui.indexrelid) st ON true
WHERE schemaname = 'public' AND bloat_pct > 30;

-- 2. Rebuild concurrently (no table lock)
REINDEX INDEX CONCURRENTLY idx_events_user_id;

-- 3. Verify new size
SELECT pg_size_pretty(pg_relation_size('idx_events_user_id'));

A few things to know before running this in production:

REINDEX CONCURRENTLY cannot run inside a transaction block.
It takes 2–3x longer than a standard REINDEX — plan for it.
Monitor pg_stat_progress_create_index to track progress without guessing.
If the operation fails midway, it leaves an invalid index behind — clean it up with DROP INDEX CONCURRENTLY.

Benchmark reality check

On a 50M-row event table with a heavily fragmented B-Tree index (62% bloat), post-REINDEX results from a production backend:

Metric	Before REINDEX	After REINDEX
Index size	4.1 GB	1.6 GB
Median index scan	38ms	11ms
p99 index scan	140ms	34ms
Bloat (pgstattuple)	62%	4%

A 3x latency reduction with zero downtime. Worth the operational discipline to instrument this properly.

Three takeaways

Stop trusting pg_stat_user_indexes for health signals. Add a scheduled pgstattuple query to your observability stack and alert when bloat exceeds 30% on high-traffic indexes.
Set FILLFACTOR at index creation for insert-heavy tables. For mobile event and telemetry tables, start at 70–75 and adjust based on observed split rates in pg_stat_user_tables.n_tup_ins vs. page size growth.
Automate REINDEX CONCURRENTLY as routine maintenance, not an emergency measure. Schedule it off-peak on a bloat threshold, monitor via pg_stat_progress_create_index, and clean up invalid indexes immediately on failure. Treat index health like autovacuum — proactive, not reactive.