Table and Index Bloat
SEV-3, Limited ImpactLinear propagation · capacity · Affects 3 scenario(s)
Severity Classification
Classified as PARTIAL based on failure mode severity. This failure mode appears in 3 known architecture scenarios, indicating widespread relevance.
Propagation Chain
Origin component
Table and Index Bloat begins at the source component. Trigger: High UPDATE or DELETE rate generating dead tuples faster than autovacuum can reclaim.
Immediate (T+0) · Signal: Disk Saturation
Downstream dependents
Failure propagates to directly dependent components via synchronous calls or shared resources. Latency increases and error rates rise on affected dependencies.
Days to weeks for bloat to reach a level that degrades performance noticeably. With regular bloat queries (pg_bloat monitoring), detectable within days. Without proactive monitoring, typically detected through unexplained storage growth or gradual query performance degradation over weeks. · Signal: Latency spike, connection timeout, or error rate increase on dependents
Blast Radius
Bloat is table-specific but its effects are broad. Storage growth affects all tables in the database. Index scan degradation affects all queries using affected indexes. Buffer pool pollution with dead pages degrades cache hit rate for all workloads. I/O amplification from bloated sequential scans affects all concurrent database operations sharing the I/O subsystem.
Contributing Factors
Write-heavy transactional workloads cause index bloat over time: dead tuples from updates and deletes leave stale entries in B-tree indexes that are not immediately reclaimed, causing indexes to grow larger than their live data size and degrading read performance.
PostgreSQL is known to be susceptible to Table and Index Bloat under medium operational burden. Not running PgBouncer: PostgreSQL connections are expensive; >500 direct connections degrade performance
This operational trigger enables Table and Index Bloat: High UPDATE or DELETE rate generating dead tuples faster than autovacuum can reclaim
This operational trigger enables Table and Index Bloat: Long-running transactions (analytics queries, ETL jobs) holding snapshots and blocking vacuum horizon advance
This operational trigger enables Table and Index Bloat: Autovacuum configuration too conservative for the table's update rate (default scale_factor unsuitable for large tables)
Remediation Plan
Measure current bloat: run pgstattuple or pg_bloat query on top-N tables by size
Effort: Minutes to hours (on-call response)
Check pg_stat_activity for long-running transactions (>1 hour) and terminate safely
Effort: Minutes to hours (on-call response)
Check pg_replication_slots for inactive or lagging slots with held xmin
Effort: Minutes to hours (on-call response)
Override per-table autovacuum settings for high-update tables: ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 1000). This triggers vacuum after 1% dead tuples instead of 20%, keeping bloat bounded for large tables.
Effort: 1 day to 1 week
Query pg_stat_activity for transactions older than 1 hour: SELECT pid, now() - xact_start AS duration, query FROM pg_stat_activity WHERE xact_start < now() - interval '1 hour'. Terminate long-running analytics queries or move them to replicas.
Effort: 1 day to 1 week
Lagging replication slots prevent vacuum from reclaiming dead tuples. SELECT slot_name, xmin, catalog_xmin FROM pg_replication_slots WHERE xmin IS NOT NULL AND age(xmin) > 1000000. Drop or pause consumer-inactive slots immediately.
Effort: 1 day to 1 week
Configure alerts for: disk saturation, alert, queue depth. Set thresholds to fire at 70% of critical level to allow response before full failure.
Effort: 1–3 days
Table and Index Bloat affects 3 architecture scenarios (AI Retrieval-Augmented Generation Platform, Content Management Platform, Search-Heavy Content Platform). Design a shared mitigation strategy or a platform-level safeguard that prevents this failure mode from manifesting across all affected services.
Effort: 1–3 months
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.