B-Tree Index Fragmentation
SEV-4, Near-MissIsolated propagation · storage · Affects 0 scenario(s)
Severity Classification
Classified as DEGRADED based on failure mode severity.
Propagation Chain
Origin component
B-Tree Index Fragmentation begins at the source component. Trigger: High-frequency deletes on an indexed column (soft-delete patterns updating status fields).
Immediate (T+0) · Signal: Disk Saturation
Blast Radius
Query latency degradation is isolated to the specific indexes and query patterns that read fragmented index pages. Other tables and unrelated query patterns are unaffected. However, if fragmented indexes occupy a large fraction of shared_buffers, cache efficiency degrades system-wide, causing a broader increase in physical I/O across all queries that reference the affected table.
Contributing Factors
This operational trigger enables B-Tree Index Fragmentation: High-frequency deletes on an indexed column (soft-delete patterns updating status fields)
This operational trigger enables B-Tree Index Fragmentation: Update-heavy workload on indexed columns where updated values span many different index pages
This operational trigger enables B-Tree Index Fragmentation: Bulk delete operations removing large fractions of a table without subsequent REINDEX
Mitigation Gaps
Identify and document at least one architectural change that prevents B-Tree Index Fragmentation from occurring (not just recovering from it).
Remediation Plan
Query pg_stat_user_indexes and pg_class to identify bloated indexes (ratio of pg_relation_size to expected size > 2x)
Effort: Minutes to hours (on-call response)
Run REINDEX CONCURRENTLY on the most bloated indexes during off-peak hours
Effort: Minutes to hours (on-call response)
Monitor query p99 latency during and after rebuild to confirm improvement
Effort: Minutes to hours (on-call response)
Run REINDEX CONCURRENTLY index_name to rebuild the index to full density without blocking reads or writes. The concurrent form builds a new index alongside the existing one and swaps them atomically. Duration depends on table size (estimate 1 hour per 50 GB of table data). Schedule during low-traffic windows and monitor index build progress via pg_stat_progress_create_index. Requires PostgreSQL 12+ for CONCURRENTLY support on REINDEX.
Effort: 1 day to 1 week
Create indexes on frequently-updated columns with a lower fill_factor (60–70 instead of default 90). This leaves reserved space on each page for future insertions from HOT (Heap Only Tuple) updates, reducing page splits and dead-entry accumulation. Apply when creating the index: CREATE INDEX idx_name ON table (col) WITH (fillfactor = 70). Rebuilding existing indexes with a new fill_factor requires REINDEX.
Effort: 1 day to 1 week
For status-column indexes where most queries filter on a small subset of values (WHERE status = active, which is 5% of rows), create a partial index: CREATE INDEX idx_active ON table (id) WHERE status = 'active'. The partial index is smaller, fragments more slowly, and is more cache-efficient. Rows with status != active are excluded entirely, reducing the write amplification from status updates.
Effort: 1 day to 1 week
Configure alerts for: disk saturation, latency spike, alert. Set thresholds to fire at 70% of critical level to allow response before full failure.
Effort: 1–3 days
Conduct a structured architecture review focused on preventing recurrence. Review topology for blast radius reduction, mitigation coverage, and observability gaps. Consider whether the current architecture scenario should evolve.
Effort: 1–2 sprints
This post-mortem framework is derived from structured architecture knowledge. It provides an evidence-grounded starting point, not a substitute for a live incident review conducted by the team closest to the system. Adjust remediation priorities based on actual runtime observations.