A 180 GB PostgreSQL 15 `ledger_entries` table (3,500 UPDATEs/s) bloated to 660 GB over 10 days, pushing primary key lookup p99 latency from 4 ms to 52 ms and threatening disk exhaustion. `autovacuum` is running continuously, yet `n_dead_tup` exceeds 420M rows and `pg_stat_all_tables` shows zero dead tuples being removed.
From patterns to decisions.
A 180 GB PostgreSQL 15 `ledger_entries` table (3,500 UPDATEs/s) bloated to 660 GB over 10 days, pushing primary key lookup p99 latency from 4 ms to 52 ms and threatening disk exhaustion. `autovacuum` is running continuously, yet `n_dead_tup` exceeds 420M rows and `pg_stat_all_tables` shows zero dead tuples being removed.
Implementation & Guardrails (1).
An open transaction, idle-in-transaction session, or replica with hot_standby_feedback=on is pinning OldestXmin.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Killing an active session aborts its in-flight work. |
Step 1
Inspect pg_stat_activity.backend_xmin and pg_stat_replication.backend_xmin.
Step 2
Check pg_replication_slots for inactive slots.
Alternative 1
Run VACUUM VERBOSE to see the exact 'removable vs dead yet not removable' counts.
Failure Mode 1
Disk full or Transaction ID wraparound shutdown.
SELECT pid, state, now() - xact_start AS age, backend_xmin FROM pg_stat_activity WHERE backend_xmin IS NOT NULL ORDER BY age(backend_xmin) DESC;
Implementation & Guardrails (2).
Unpin OldestXmin, run pg_repack --only-indexes to free ~190 GB, then run pg_repack on the heap to shrink to 180 GB online.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | pg_repack generates extra WAL during shadow copy. |
Step 1
Terminate idle-in-tx sessions and disable hot_standby_feedback on BI replica.
Step 2
Compact indexes first, then compact heap via pg_repack.
Alternative 1
Logical replication cutover to a fresh cluster.
Failure Mode 1
Running heap pg_repack first fails with No Space Left on Device.
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
-- pg_repack --only-indexes --table ledger_entries -d prod_db
One BI query must never.
One BI query must never be allowed to bloat the primary OLTP database and cause a production outage.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Long replica queries may see recovery conflict cancellations. |
Step 1
Set hot_standby_feedback = off and max_standby_streaming_delay = 1800s.
Step 2
Route >30m exports to a dedicated snapshot/logical replica.
Alternative 1
Use a separate logical replication subscriber or Snowflake/BigQuery CDC sink for BI.
Failure Mode 1
Setting max_standby_streaming_delay=-1 stalls replication lag indefinitely.
hot_standby_feedback: off
max_standby_streaming_delay: 1800s
PostgreSQL refuses all new write.
PostgreSQL refuses all new write transactions (database is not accepting commands to avoid wraparound data loss) until freeze vacuum completes.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Emergency single-user mode vacuum causes hours of downtime. |
Step 1
Monitor max(age(datfrozenxid)) in Prometheus/Grafana.
Step 2
Alert at 150M and auto-kill pinned transactions before 500M.
Alternative 1
Increase autovacuum_freeze_max_age only as a temporary buffer, never a fix.
Failure Mode 1
Complete database write lockup.
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) DESC;
Default autovacuum_vacuum_scale_factor=0.
Default autovacuum_vacuum_scale_factor=0.20 waits for 20% dead tuples (~36 GB) on large tables. Lower it to 0.01 and raise cost_limit.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | fillfactor=85 uses 15% more initial space to enable fast in-page HOT updates. |
Step 1
Set per-table autovacuum_vacuum_scale_factor = 0.01.
Step 2
Increase autovacuum_vacuum_cost_limit = 2000 and set fillfactor = 85 for HOT updates.
Alternative 1
Partition ledger_entries by month so old partitions are immutable.
Failure Mode 1
Default 0.20 scale factor leaves tens of GBs unvacuumed on 180 GB tables.
ALTER TABLE ledger_entries SET (fillfactor = 85, autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_cost_limit = 2000);
Theory & Tips and Tricks
Practical heuristics & interview traps
- 01Interview anchor: Enforce idle_in_transaction_session_timeout and statement_timeout, isolate BI reads with hot_standby_feedback=off, and reclaim bloat online via pg_repack.
- 02Trade-off tip: Long replica queries may see recovery conflict cancellations.
- 03Watch out for: Complete database write lockup.
Postgresql Vacuum And Table Bloat — Normal → Load / Fault → Recovery
Enforce idleintransactionsessiontimeout and statementtimeout, isolate BI reads with hotstandbyfeedback=off, and reclaim bloat online via pgrepack.
Putting Preventing PostgreSQL VACUUM Starvation & Bloat Together
Enforce idle_in_transaction_session_timeout and statement_timeout, isolate BI reads with hot_standby_feedback=off, and reclaim bloat online via pg_repack.
Putting Preventing PostgreSQL VACUUM Starvation & Bloat Together
- Terminate idle-in-tx sessions and disable hot_standby_feedback on BI replica.
- Compact indexes first, then compact heap via pg_repack.
Interview Readiness Check
PostgreSQL refuses all new write transactions (database is not accepting commands to avoid wraparound data loss) until freeze vacuum completes.
Walk through bottlenecks, back-of-the-envelope math, and failure recovery.
Make the trade-off yourself.
Practice Check
Test your understanding
When is ACID Transaction Fundamentals the right default for a production team?
Preventing PostgreSQL VACUUM Starvation & Bloat
Enforce idle_in_transaction_session_timeout and statement_timeout, isolate BI reads with hot_standby_feedback=off, and reclaim bloat online via pg_repack.
