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.
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:
- Replication Slots (
pg_replication_slots): An inactive or lagging physical/logical replica keepsactive = falsewhile holdingrestart_lsnfixed. Every 16 MB segment generated sincerestart_lsnstays on disk. - WAL Archiving (
pg_stat_archiver): Ifarchive_mode = onandarchive_commandis failing (e.g., S3 credentials expired or slowscp),.readyfiles accumulate inpg_wal/archive_status/and block recycling. - Long-Running Transactions (
pg_stat_activity): Open transactions holdbackend_xminand prevent vacuum/checkpoint cleanup, though slots and failed archiving are the primary drivers of 100+ GBpg_walbloat.
WAL Segment Pinning Horizons
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.
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_targetis low (e.g.,0.5), the checkpointer dumps gigabytes of dirty 8 KB shared buffers into OS page cache in a short window, followed byfsyncstalls that saturate the 3,000-IOPS EBS volume. - Post-Checkpoint FPW Avalanche: Immediately after every checkpoint starts,
full_page_writes = onforces 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.
Frequent Checkpoints -> FPW Amplification -> IOPS Saturation
1.:
2.:
Caption: Undersized max_wal_size triggers frequent checkpoints that reset Full-Page Write bitmaps, amplifying WAL volume and saturating shared disk IOPS.
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.
Unbounded Slots vs max_slot_wal_keep_size
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.
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 socheckpoint_timeout—notmax_wal_size—drives checkpoints.checkpoint_completion_target = 0.9: Spreads dirty-page writes evenly across0.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 topg_wal, cutting FPW disk bandwidth by 40–60% for ~2–3% CPU.
Smooth 18-Minute Flush Window
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.
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 []Proactive Lag Alerting Before Slot Invalidation
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.
Isolating pg_wal IOPS and Linux Dirty-Page Writeback.
Even with tuned PostgreSQL checkpoints, two OS-level bottlenecks can still cause tail-latency spikes:
- Dedicated
pg_walVolume: On AWS EBS,gp3supports provisioning IOPS (up to 16,000) and throughput (up to 1,000 MB/s) independently of storage size. Movingpg_walto a dedicated volume ensures sequentialfsynccommits never queue behind random 8 KB checkpoint flushes. - 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!) beforepdflushwakes up and dumps gigabytes at once. Cappingvm.dirty_background_bytes = 268435456(256 MB) andvm.dirty_bytes = 1073741824(1 GB) forces smooth, continuous kernel writeback.
Shared Disk + Large Dirty Cache vs Isolated WAL + Capped Writeback
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.
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_reqvscheckpoints_timed(pg_stat_checkpointerin PG 17 /pg_stat_bgwriterin PG 16): Timed checkpoints should exceed 90% of total checkpoints, confirmingmax_wal_size = 32GBis no longer forcing premature flushes.- WAL Volume: Measure
pg_stat_walbytes before and afterwal_compression = zstdto 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).
Before vs After Remediation Metrics
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: Make the trade-off yourself.
Which PostgreSQL setting prevents a disconnected replica from filling the primary server's pg_wal disk?