---
title: "PostgreSQL Index Bloat: Fix B-Tree Fragmentation in Mobile Backends"
published: true
description: "Learn how to detect real PostgreSQL index bloat using pgstattuple, tune FILLFACTOR for mobile write patterns, and run REINDEX CONCURRENTLY without downtime."
tags: [postgresql, api, mobile, architecture]
canonical_url: https://mvpfactory.co/blog/postgresql-index-bloat-btree-fragmentation-mobile-backends
---
## What You Will Learn
By the end of this workshop you will know how to detect real B-Tree fragmentation in a PostgreSQL backend that ingests mobile telemetry, configure FILLFACTOR to match your write pattern, and rebuild bloated indexes in production — zero downtime, zero table locks. Here is a pattern I use in every mobile backend project before things quietly fall apart.
---
## Prerequisites
- PostgreSQL 12 or later (REINDEX CONCURRENTLY requires it)
- The `pgstattuple` extension installed (`CREATE EXTENSION pgstattuple;`)
- A backend handling insert-heavy writes: user events, session starts, tap logs, crash reports, heartbeats
---
## Step 1 — Stop Trusting `pg_stat_user_indexes`
This is the first thing most teams reach for:
sql
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE schemaname = 'public';
The docs do not mention this, but that view tells you *usage*, not *health*. An index can be sitting at 80% dead space and you will get back a clean row count and cheerful scan numbers. It has no concept of internal fragmentation.
## Step 2 — Measure Real Bloat with `pgstattuple`
Here is the minimal setup to get this working:
sql
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 at 60–70% free space — meaning over half the index is dead weight your disk head is crossing on every scan. Add this query to your observability stack and alert when any high-traffic index exceeds 30%.
## Step 3 — Set FILLFACTOR at Index Creation
FILLFACTOR controls how full PostgreSQL packs each B-Tree page on the initial write. The default is 90. For insert-heavy workloads, dropping it reserves space on existing pages and reduces page splits. Here is the table I use:
| 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 it at index creation:
sql
CREATE INDEX CONCURRENTLY idx_events_user_id
ON user_events(user_id)
WITH (fillfactor = 70);
One important thing: 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.
## Step 4 — The REINDEX CONCURRENTLY Playbook
Before PostgreSQL 12, a REINDEX acquired an `ACCESS EXCLUSIVE` lock — table offline, queries blocked. Since PostgreSQL 12, `REINDEX CONCURRENTLY` builds the new index in the background while traffic flows normally. Here is the safe production sequence:
sql
-- 1. Confirm the bloat threshold warrants a 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 the new size
SELECT pg_size_pretty(pg_relation_size('idx_events_user_id'));
Monitor progress without guessing using `pg_stat_progress_create_index` while the rebuild runs.
On a 50M-row event table with 62% bloat, here is what this looks like in production:
| 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. Zero downtime.
---
## Gotchas
Here is the gotcha that will save you hours:
- **`REINDEX CONCURRENTLY` cannot run inside a transaction block.** If you wrap it, it will fail immediately.
- It takes **2–3x longer** than a standard REINDEX. Do not kick this off expecting it to finish in minutes on a large table.
- If the operation fails midway, it leaves an **invalid index** behind. Clean it up immediately: `DROP INDEX CONCURRENTLY <invalid_index_name>;`
- FILLFACTOR only applies at creation or rebuild time. Changing it on an existing index does nothing until the next REINDEX.
---
## Conclusion
Your mobile backend's gradual slowdown — 5ms, then 12ms, then 40ms — is almost never the ORM or the network. It is silent B-Tree fragmentation accumulating under insert-heavy write patterns. The fix is straightforward: instrument `pgstattuple` into your observability pipeline, set FILLFACTOR correctly when building indexes for event and telemetry tables, and treat `REINDEX CONCURRENTLY` as scheduled maintenance rather than an emergency response.
**Relevant docs:**
- [pgstattuple — PostgreSQL docs](https://www.postgresql.org/docs/current/pgstattuple.html)
- [REINDEX CONCURRENTLY — PostgreSQL docs](https://www.postgresql.org/docs/current/sql-reindex.html)
- [pg_stat_progress_create_index — PostgreSQL docs](https://www.postgresql.org/docs/current/progress-reporting.html#CREATE-INDEX-PROGRESS-REPORTING)
Top comments (0)