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

WAL Segment Bloat &
Checkpoint I/O Stalls.

Separate WAL retention pinners (stuck replication slots and idle-in-transaction sessions) from checkpoint I/O burstiness by bounding slot retention and pacing dirty-page flushes across 90% of the checkpoint interval.
Start the journey7 stages
FIRST PRINCIPLES. PRODUCTION SCALE.
The idea to keep with you

Your primary PostgreSQL 16 OLTP database (16 vCPU, 64 GB RAM, 3,000-IOPS EBS gp3 volume) handles 4,500 TPS during peak hours. Every 12–15 minutes, p99 write latency spikes from 4 ms to 850 ms for 45 seconds, and `/pg_wal` disk usage has crept from 4 GB to 110 GB despite `max_wal_size = 8GB`. Diagnose the root causes of both the WAL segment bloat and the periodic checkpoint I/O storms, and design a zero-downtime remediation plan.

From patterns to decisions.

Separate WAL retention pinners (stuck replication slots and idle-in-transaction sessions) from checkpoint I/O burstiness by bounding slot retention and pacing dirty-page flushes across 90% of the checkpoint interval.

Full walkthrough · 7 stages
01
Stage 1 • Triagefoundation

Pinpointing Why WAL Exceeds max_wal_size.

In PostgreSQL 16, max_wal_size is a soft target that triggers checkpoints, not a hard disk quota. If any internal horizon pins an old LSN, the checkpointer cannot recycle or delete segments newer than that LSN. Check these three views immediately:

  1. Replication Slots (pg_replication_slots): An inactive or lagging physical/logical replica keeps active = false while holding restart_lsn fixed. Every 16 MB segment generated since restart_lsn stays on disk.
  2. WAL Archiving (pg_stat_archiver): If archive_mode = on and archive_command is failing (e.g., S3 credentials expired or slow scp), .ready files accumulate in pg_wal/archive_status/ and block recycling.
  3. Long-Running Transactions (pg_stat_activity): Open transactions hold backend_xmin and prevent vacuum/checkpoint cleanup, though slots and failed archiving are the primary drivers of 100+ GB pg_wal bloat.
Root Cause Triage

WAL Segment Pinning Horizons

Dead ReplicationSlotFailingarchive_command110 GB pg_walStoreBlocks segment reuse

1.:

2.:

Caption: Inactive replication slots and failing archive commands freeze the minimum required LSN horizon, forcing pg_wal to grow indefinitely past max_wal_size.

02
Stage 2 • Root Causefoundation

Anatomy of a 45-Second Checkpoint I/O Storm.

When max_wal_size = 8GB under 4,500 TPS, the database fills 8 GB of WAL roughly every 12–15 minutes, forcing frequent WAL-driven checkpoints (checkpoint_requested) instead of scheduled checkpoint_timeout checkpoints.

Two compounding effects create the 850 ms p99 latency spike:

  • Burst Dirty-Page Flush: If checkpoint_completion_target is low (e.g., 0.5), the checkpointer dumps gigabytes of dirty 8 KB shared buffers into OS page cache in a short window, followed by fsync stalls that saturate the 3,000-IOPS EBS volume.
  • Post-Checkpoint FPW Avalanche: Immediately after every checkpoint starts, full_page_writes = on forces PostgreSQL to log the entire 8 KB image of every page on its first modification. Frequent checkpoints multiply WAL bytes written per transaction by 3x–5x, accelerating the next checkpoint in a vicious cycle.
Feedback Loop

Frequent Checkpoints -> FPW Amplification -> IOPS Saturation

Small max_wal_size(8GB)FPW Burst3,000 IOPSSaturatedStoreRandom flush + WAL write contention

1.:

2.:

Caption: Undersized max_wal_size triggers frequent checkpoints that reset Full-Page Write bitmaps, amplifying WAL volume and saturating shared disk IOPS.

03
Stage 3 • Mitigationintermediate

Zero-Downtime Slot Cleanup & Bounded Retention Guards.

Before tuning checkpoints, stop the bleeding by inspecting and guarding WAL retention in live SQL (zero downtime, pg_reload_conf() applies parameter updates):

-- 1. Inspect lagging or dead replication slots
SELECT slot_name, active, slot_type,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_retained
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

-- 2. Drop only confirmed dead/decommissioned slots (irreversible for that replica)
-- SELECT pg_drop_replication_slot('decommissioned_analytics_slot');

-- 3. Enforce hard safety caps without restart
ALTER SYSTEM SET max_slot_wal_keep_size = '16GB';
ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
SELECT pg_reload_conf();

Setting max_slot_wal_keep_size = '16GB' guarantees that even if a replica dies over a weekend, PostgreSQL invalidates the dead slot rather than filling /pg_wal and crashing the primary.

Safety Guards

Unbounded Slots vs max_slot_wal_keep_size

Default (-1)max_slot_wal_keep_s…StateStore

1.:

2.:

Caption: Configuring max_slot_wal_keep_size and idle_in_transaction_session_timeout protects primary availability by invalidating dead consumers before disk exhaustion.

04
Stage 4 • Pacing & Sizingintermediate

Tuning max_wal_size, checkpoint_completion_target, and bgwriter.

Next, shift checkpoints from chaotic WAL-size triggers to smooth, time-paced intervals:

  • checkpoint_timeout = '20min': Extends the interval between FPW resets.
  • max_wal_size = '32GB': Sized above the ~12 GB of WAL generated in 20 minutes so checkpoint_timeout—not max_wal_size—drives checkpoints.
  • checkpoint_completion_target = 0.9: Spreads dirty-page writes evenly across 0.9 * 20 min = 18 minutes, cutting peak checkpointer IOPS by ~5x.
  • bgwriter_lru_maxpages = 400 & bgwriter_delay = 50ms: Continuously trickles cleanable buffers to disk between checkpoints.
  • wal_compression = zstd (PostgreSQL 15+): Compresses Full-Page Writes in memory before writing to pg_wal, cutting FPW disk bandwidth by 40–60% for ~2–3% CPU.
Paced Checkpointing

Smooth 18-Minute Flush Window

wal_compression=zs…completion_target=0…Steady Disk IOPSStoreZero I/O saturation

1.:

2.:

Caption: Combining zstd WAL compression, larger max_wal_size, and checkpoint_completion_target=0.9 spreads dirty-page flushes smoothly across 18 minutes and keeps p99 latency flat.

05
Stage 5 • Automationintermediate

Automated WAL Lag Watchdog with Bounded Retry & Rollback.

An automated observability watchdog should alert on pg_wal retention before max_slot_wal_keep_size invalidates a replica, using bounded retries with jitter and read-only fallback:

import time, random
import psycopg

WARN_BYTES = 8 * 1024 ** 3  # 8 GB warning threshold

def check_wal_horizon(dsn: str, max_retries: int = 3) -> list[dict]:
    query = '''
        SELECT slot_name, active,
               pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes
        FROM pg_replication_slots
        WHERE restart_lsn IS NOT NULL;
    '''
    for attempt in range(max_retries):
        try:
            with psycopg.connect(dsn, connect_timeout=3) as conn:
                with conn.cursor() as cur:
                    cur.execute('SET statement_timeout = 2000')
                    cur.execute(query)
                    rows = cur.fetchall()
                    return [
                        {'slot': r[0], 'active': r[1], 'lag_gb': round(r[2] / 1e9, 2)}
                        for r in rows if r[2] and r[2] > WARN_BYTES
                    ]
        except psycopg.OperationalError as exc:
            if attempt == max_retries - 1:
                raise RuntimeError(f'WAL watchdog failed after {max_retries} attempts: {exc}') from exc
            time.sleep((0.5 * (2 ** attempt)) + random.uniform(0, 0.25))
    return []
SRE Watchdog

Proactive Lag Alerting Before Slot Invalidation

Pollpg_replication_slo…lag_bytes > 8 GB?Page On-CallStorePrevent full re-seed

1.:

2.:

Caption: A lightweight watchdog with statement_timeout and exponential backoff alerts on WAL lag at 8 GB, giving operators runway before the 16 GB slot cap triggers.

06
Stage 6 • Infrastructureadvanced

Isolating pg_wal IOPS and Linux Dirty-Page Writeback.

Even with tuned PostgreSQL checkpoints, two OS-level bottlenecks can still cause tail-latency spikes:

  1. Dedicated pg_wal Volume: On AWS EBS, gp3 supports provisioning IOPS (up to 16,000) and throughput (up to 1,000 MB/s) independently of storage size. Moving pg_wal to a dedicated volume ensures sequential fsync commits never queue behind random 8 KB checkpoint flushes.
  2. Linux vm.dirty_background_bytes & vm.dirty_bytes: By default, Linux allows dirty OS page cache to accumulate up to 10–20% of 64 GB RAM (6–12 GB!) before pdflush wakes up and dumps gigabytes at once. Capping vm.dirty_background_bytes = 268435456 (256 MB) and vm.dirty_bytes = 1073741824 (1 GB) forces smooth, continuous kernel writeback.
OS & Volume Isolation

Shared Disk + Large Dirty Cache vs Isolated WAL + Capped Writeback

Single 3K IOPSVolumeDedicated WAL Volume+ 256MB dirty capStateStore

1.:

2.:

Caption: Placing pg_wal on a dedicated volume and capping Linux vm.dirty_background_bytes prevents OS-level writeback bursts and random heap I/O from starving commit fsyncs.

07
Stage 7 • Verificationadvanced

Validating Recovery Time Objective (RTO) and Metrics.

After rolling out the changes, verify both I/O smoothness and crash-recovery bounds over a 24-hour peak cycle:

  • checkpoints_req vs checkpoints_timed (pg_stat_checkpointer in PG 17 / pg_stat_bgwriter in PG 16): Timed checkpoints should exceed 90% of total checkpoints, confirming max_wal_size = 32GB is no longer forcing premature flushes.
  • WAL Volume: Measure pg_stat_wal bytes before and after wal_compression = zstd to confirm a 40%+ reduction in FPW bytes.
  • RTO Verification: Test crash recovery on a staging clone under peak load to confirm ARIES Redo completes within your SLA (typically < 30–45 seconds).
Post-Fix Verification

Before vs After Remediation Metrics

BeforeAfterStateStore

1.:

2.:

Caption: Tracking timed-to-requested checkpoint ratios and WAL byte volume confirms that checkpoint storms are eliminated while keeping crash RTO within SLA.

Practice · Explain the consequences

Practice: Make the trade-off yourself.

Question 1 of 3
Score: 0/3

Which PostgreSQL setting prevents a disconnected replica from filling the primary server's pg_wal disk?

Recommended Learning Flow