Storage Bloat Without Archiving or Retention Policy
A write-heavy table: audit logs, events, time-series metrics: grows unbounded without a retention or archiving policy. Dead tuples from high-churn UPDATE and DELETE operations accumulate faster than autovacuum can reclaim them. Table bloat causes sequential scans to read tens of millions of dead pages, the query planner degrades on stale statistics, and autovacuum becomes the dominant I/O consumer on the host.
PostgreSQL primary with a high-churn table growing 5-15GB/month and no partitioning or archiving
Degradation Replay
Nominal: Table Growing but Vacuum Keeping Pace
Table under 10GB; autovacuum triggering regularly and reclaiming dead tuples efficiently
- ·n_dead_tup < 5% of n_live_tup for all tables
- ·Table size growth rate matches expected business data volume
- ·pg_stat_user_tables.last_autovacuum within last 24 hours for high-churn tables
- ·Sequential scan times under 50ms for typical business queries
- ·Autovacuum running on small tables completes in minutes: low I/O overhead
- ·Table bloat ratio (actual_size / live_data_size) below 1.3×
- !Normal operation: autovacuum keeping pace with churn rate
Operational simulation model only, not a production forecast. Degradation stages are derived from structured operational knowledge, not measured telemetry. Do not use for capacity planning or incident response.
Run With Your Parameters
Adjust the parameters below to see how metric values shift across degradation stages. Formulas are deterministic: same inputs always produce the same output.
Simulation Parameters
Computed Degradation Stages
Table under 10GB; autovacuum triggering regularly and reclaiming dead tuples efficiently
Table reaches 20-50GB; autovacuum duration extends; dead tuple ratio rising between runs
Table exceeds 50GB; autovacuum runs take 3-4 hours; dead tuple accumulation rate exceeds vacuum rate
Table bloat consumes available disk; query planner degradation causes application query regressions
Retention DELETE job runs; archiving pipeline removes old data to object storage; table size stabilizing
Interpretation
4 secondary indexes create 4.2× write amplification. Peak disk utilization: 1% of 200 MB/s bandwidth.
Secondary index write amplification exhausting disk write bandwidth
Reduce index count from 4 to essential indexes only. Use partial indexes on hot write paths. Upgrade disk to 400 MB/s write bandwidth for headroom.
Parameterized simulation: not a production forecast. Values derived from deterministic formulas applied to your parameters. Do not use for capacity planning or operational decisions without validation.
Propagation Model
Table size grows monotonically; sequential scans must read all allocated pages including those holding only dead tuples; scan time grows O(N) with table size
Stabilizes: No stabilization without manual intervention: grows indefinitely
Default autovacuum_vacuum_scale_factor=0.2 means autovacuum triggers only when 20% of 50M rows (10M rows) are dead: at 500 updates/second this takes 20,000 seconds (5.5 hours) before vacuum even starts
Stabilizes: Lowering scale factor to 0.02 triggers autovacuum 10× earlier: requires explicit tuning
Large autovacuum job consumes disk I/O bandwidth; OLTP query latency rises; if autovacuum exceeds cost_delay budget or is manually cancelled by ops, vacuum aborts mid-run and dead tuples re-accumulate
Stabilizes: Stabilizes when autovacuum completes a full pass: but on a 100GB bloated table this can take 4-8 hours
Recovery Patterns
Bulk delete with retention window + VACUUM ANALYZE
Deletion: 1-4 hours depending on data volume; vacuum: 2-6 hours after deletion- ·Large DELETE generates significant WAL and may cause replication lag on standbys
- ·Bulk deletion creates additional dead tuples: temporary bloat spike before vacuum reclaims them
- !VACUUM reclaims pages for PostgreSQL reuse but does not shrink table file on disk: need VACUUM FULL or pg_repack to return disk space to OS
Migrate to partitioned table with time-range partitions
1-2 days for table reconstruction; ongoing management via pg_partman- ·Partition DDL migration requires application changes and potentially downtime
- ·Partition pruning must be verified for all query patterns
- !Queries that previously used btree index on timestamp may need query plan validation after partitioning
Operational Summary
Storage bloat without archiving is a slow-moving incident that arrives months after the engineering decision that caused it. The default PostgreSQL autovacuum settings are calibrated for small-to-medium tables with moderate churn. A high-write table growing at 10GB/month will outpace default autovacuum within a year.
The first symptom is often noticed by a reporting query that suddenly starts taking 60 seconds instead of 200ms. The query plan changed because statistics are stale and the planner no longer knows the table's shape. The root cause is 60% dead tuples that have never been reclaimed.
The structural fix is partitioning with time-range partition drops: instead of vacuuming dead tuples out of a monolithic table, you drop entire old partitions in milliseconds. This is the correct architecture for any table where data has a natural time-based retention policy.