Skip to main content
← PROBLEMS / ARCHITECTURE
An end-to-end domain learning journey

Preventing PostgreSQL VACUUM
Starvation & Bloat.

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.
Start the journey5 stages
Loading diagram…
Preventing PostgreSQL VACUUM Starvation & Bloat architecture sketch diagram
FIRST PRINCIPLES. PRODUCTION SCALE.
The idea to keep with you

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.

01
ARCHITECTURE · STAGE 01L4 · FUNDAMENTALS

Implementation & Guardrails (1).

An open transaction, idle-in-transaction session, or replica with hot_standby_feedback=on is pinning OldestXmin.

ChoiceWhat it buysWhat it costs
Trade-off 1Killing 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;
02
ARCHITECTURE · STAGE 02L5 · APPLIED DESIGN

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.

ChoiceWhat it buysWhat it costs
Trade-off 1pg_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
03
ARCHITECTURE · STAGE 03L6 · SCALE & TRADE-OFFS

One BI query must never.

One BI query must never be allowed to bloat the primary OLTP database and cause a production outage.

ChoiceWhat it buysWhat it costs
Trade-off 1Long 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
04
ARCHITECTURE · STAGE 04L6 · SCALE & TRADE-OFFS

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.

ChoiceWhat it buysWhat it costs
Trade-off 1Emergency 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;
05
ARCHITECTURE · STAGE 05L7 · STAFF / PRINCIPAL

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.

ChoiceWhat it buysWhat it costs
Trade-off 1fillfactor=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.
Behavioral Progression · Postgresql Vacuum And Table Bloat

Postgresql Vacuum And Table Bloat — Normal → Load / Fault → Recovery

Send Request
1. Normal Request Ingress
Traffic: 1x baseline · Latency (p99): 4 ms
Process State
2. Core Execution & State Lookup
Traffic: 1x baseline · Latency (p99): 9 ms
Inject 10x Spike
3. Scale Spike / Failure Injection
Traffic: 10x burst · Latency (p99): 185 ms

Enforce idleintransactionsessiontimeout and statementtimeout, isolate BI reads with hotstandbyfeedback=off, and reclaim bloat online via pgrepack.

Synthesis / One connected design

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.

A candidate architecture, not a universal answer

Putting Preventing PostgreSQL VACUUM Starvation & Bloat Together

Step 1
Step 2
  1. Terminate idle-in-tx sessions and disable hot_standby_feedback on BI replica.
  2. Compact indexes first, then compact heap via pg_repack.
PostgreSQL refuses all new write transactions (`database is not accepting commands to avoid wraparound data loss`) until freeze vacuum completes.
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.

Practice / Explain the consequences

Make the trade-off yourself.

Practice Check

Test your understanding

Question 1 of 3

When is ACID Transaction Fundamentals the right default for a production team?

Key takeaway

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.