WAL Best Practices and Crash-and-Recover Testing

• Understanding what the WAL actually guarantees (ordering, batching, atomicity)
• Which sync method matches your risk profile: fsync, fdatasync, and O_DSYNC
• Checkpointing to bound recovery time and reduce WAL replay
• Automating crash-and-recover tests and fault injection at scale
• Monitoring recovery metrics and building an operational playbook
• Practical application: checklists, scripts, and test harnesses

Durability depends on one immutable rule: the write-ahead log (WAL) must reach durable storage before the system acknowledges a transaction. Get ordering, batching, and checkpoints right and your recovery window becomes predictable; get them wrong and you trade minutes of downtime for days of forensic work and lost trust.

The system-level symptoms you face are familiar: sub-second tail latencies that spike when fsync runs, unpredictable recovery time after a node crash, and rare-but-terrible incidents where acknowledged commits vanish after a storage controller reset. Those symptoms point to three core friction points — incorrect WAL ordering or flush semantics, poorly-tuned checkpointing that amplifies replay, and insufficient crash-and-recover testing that misses storage edge-cases. The rest of this piece walks through what the WAL actually guarantees, how to pick sync semantics, how to bound recovery time with checkpoints, how to automate crash-and-recover testing, and what to operationalize in monitoring and playbooks.

Understanding what the WAL actually guarantees (ordering, batching, atomicity)

• The fundamental promise of a write-ahead log is ordering: the log record describing a change must become durable before the corresponding data page is allowed to be considered durably updated. This is the core of WAL-based recovery: on restart, the system replays WAL records starting at the last checkpoint to reconstruct committed state.

• Atomicity at the transaction level is achieved by the commit record. A transaction becomes durable only when its commit record reaches the stable storage point you require; everything else (index/data page writes) can follow lazily. Implementations typically write a commit record (and possibly group multiple commits), flush it, then acknowledge the client. If that flush fails or is not awaited, the acknowledgement is meaningless.

• Batching and group commit are the performance levers. Instead of calling fsync() per-transaction, systems coalesce many commit records into one physical sync window (often a few hundred milliseconds or a tunable microsecond window) to amortize the sync cost. Postgres exposes knobs like commit_delay and commit_siblings that explicitly create a short leader-waits window to allow followers to piggyback on a single WAL flush. The WAL writer itself also flushes on a periodic cadence (wal_writer_delay) and can be configured to flush after a certain WAL volume (wal_writer_flush_after). Use these knobs to tradelatency for throughput with predictable bounds.

• Implementation detail that bites people: fsync()/fdatasync() guarantee that the OS received the write and (subject to device behavior) attempted to flush caches — but some devices (consumer SSDs, broken controller firmware) can report success even though volatile caches will be lost on power failure. That means a correct software protocol plus a lying device still produces data loss. Treat the storage layer as potentially lying unless you can verify non-volatile write caches or use battery-backed caches on the controller.

Important: The Log Is Law — every change that must survive a crash must be reflected in the WAL and the WAL must be durably persisted according to the durability contract you expose to clients. Any attempt to short-circuit that (no sync, or broken device caches) removes guarantees.

Example pseudo-code (conceptual):

/* simplified commit path */ write_wal_records(transaction_records); // buffered write lsn = current_wal_insert_lsn(); if (durable_commit_required) { flush_wal_to_storage(lsn); // fsync / fdatasync / O_SYNC } acknowledge_client(); apply_changes_to_data_files_asynchronously(); 

Cite the WAL checkpoints and recovery model when tuning this sequence.

Which sync method matches your risk profile: fsync, fdatasync, and O_DSYNC

What to choose for wal_sync_method (or equivalent in your engine) is a practical systems decision, not a religious one. Here’s a compact comparison and rules of thumb.

Practical rule-of-thumb from field experience:

• Prefer fdatasync/open_datasync for WAL files where you care about the sequence of WAL bytes but not the granularity of inode timestamps. This usually reduces metadata fsync overhead. Bench and validate with pg_test_fsync.
• Use fsync() (or fsync_writethrough) if your storage stack has flaky write-cache behavior or you must be conservative across diverse deployments.
• Measure: pg_test_fsync or your own microbenchmark gives the fastest safe option on that platform; do not assume SSD == fast fsync().

Example: choose an open flag in C:

int fd = open("pg_wal/00000001000000000000000A", O_WRONLY | O_CREAT | O_APPEND | O_DSYNC, 0644); 

If you use O_DSYNC/O_SYNC, be aware of kernel and filesystem differences: on some systems O_SYNC was historically implemented with O_DSYNC semantics, and support can evolve by kernel version. Verify with pg_test_fsync or your own test harness.

Checkpointing to bound recovery time and reduce WAL replay

Checkpointing is the lever that converts unbounded WAL replay into a bounded recovery window. The checkpointer writes all dirty buffers to data files and writes a checkpoint record into the WAL; crash recovery then begins at that checkpoint's redo LSN, meaning WAL replay only covers newer changes.

• Default tuning anchors (Postgres examples): checkpoint_timeout defaults to 5 minutes, and max_wal_size often defaults to 1 GB — these values directly influence how much WAL you may need to replay after a crash. Reducing checkpoint_timeout lowers potential replay volume but increases checkpoint IO and write amplification.

• Use pg_control_checkpoint() (or pg_controldata for offline inspection) to programmatically discover the last checkpoint LSN; combine that with pg_current_wal_lsn() and pg_wal_lsn_diff() to compute bytes of WAL to replay. This gives an operational estimate of what recovery would look like right now. Example SQL:
  

-- Get the last checkpoint LSN and redo LSN: SELECT (pg_control_checkpoint()).checkpoint_lsn, (pg_control_checkpoint()).redo_lsn; -- Estimate bytes to replay (from last checkpoint redo point to current WAL end): SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), (pg_control_checkpoint()).redo_lsn) AS bytes_to_replay; 

These functions let you put a numeric bound on recovery work.

• Checkpoint behavior trade-offs:

  • More frequent checkpoints → smaller WAL replay window → faster crash recovery, higher sustained IO and write amplification.
  • Less frequent checkpoints → lower steady-state IO but longer recovery time and larger WAL directories. Tune checkpoint_completion_target to smooth I/O during checkpoint windows.

• For LSM-tree engines (RocksDB, etc.) the same principle holds: they keep a WAL for durability until memtable flushes produce SST files; deleting WAL segments requires the SSTs to contain all updates from that WAL. RocksDB provides WAL configuration knobs and max_total_wal_size to bound WAL growth and force flushes. Make sure your ingestion, compaction and WAL retention policies match your recovery targets.

Automating crash-and-recover tests and fault injection at scale

Testing is the only way to validate assumptions about your entire stack: application code, database logic, OS, driver and device firmware. The objective: prove that an acknowledged commit survives real-world failure modes (process kill, kernel crash, storage controller reset, power loss, etc.).

• Use well-known frameworks where appropriate: Jepsen provides a methodology and tooling for verifying safety properties under crash and network faults; adopt Jepsen-style histories and checkers for sanity when testing distributed durability assumptions. For Kubernetes or cloud-native stacks, use Chaos Mesh or LitmusChaos to orchestrate pod/IO/network/node faults across clusters.

• Fault-injection levels:

  1. Application-level: kill the DB process with kill -9 during a high-volume WAL writer workload.
  2. OS-level: trigger immediate reboot (echo b > /proc/sysrq-trigger) or trigger kernel panic in a controlled lab.
  3. Device-level: use kernel fault-injection or SCSI scsi_debug to make specific BIOs fail or drop fsync() effects. The Linux kernel provides a fault-injection infrastructure for testing disk IO failures (/sys/kernel/debug/fault-injection and fail_make_request).
  4. Controller-level: simulate NVMe or RAID controller resets where possible (vendor tools, or physical power cycling in a lab).

• Example automation recipe (lightweight):

  1. Prepare a baseline dataset and a deterministic workload generator (e.g., pgbench with scripted transactions or a bespoke client that writes monotonically-increasing checksums).
  2. Start continuous write load at target QPS.
  3. Randomly choose one of the fault modes (process kill, node reboot, disk error injection).
  4. Restart the system and let recovery finish.
  5. Run verification queries that examine sequence counters, checksums or SELECT COUNT(*)/application-level invariants.
  6. Record recovery time (time from process restart to availability) and WAL replay volume/time. Log all evidence: pg_wal contents, pg_controldata, server logs, OS dmesg.

• LD_PRELOAD shims and syscall wrappers are useful test tools: build an LD_PRELOAD library that intercepts fsync()/fdatasync() and either delay, fail, or drop calls to simulate faulty devices — this isolates software resilience from device behavior. Use with great caution and only in test environments. Example concept (C, sketch):
  

#define _GNU_SOURCE #include <dlfcn.h> #include <unistd.h> #include <stdio.h> #include <errno.h> static int (*real_fsync)(int) = NULL; int fsync(int fd) { if (!real_fsync) real_fsync = dlsym(RTLD_NEXT, "fsync"); if (getenv("INJECT_FSYNC_DROP")) { // simulate a device that ACKs but loses data on power loss return 0; // return success but do not actually flush in test harness } return real_fsync(fd); } 

• Record pass/fail criteria automatically: on recovery, your verification script should assert exact match against a golden dataset hash or application-level invariants. If any assertion fails, record the pre-crash WAL segments and produce a minimal reproduction script for developers.

• Learn from Jepsen-style reports: real-world distributed engine failures often come from hidden assumptions (e.g., multiple logical logs per physical disk causing thundering fsync patterns), so aim to cover concurrency and storage edge-cases.

Monitoring recovery metrics and building an operational playbook

You need SRL — signals, runbooks and limits — for recovery.

Key metrics to emit and monitor:

• WAL backlog (bytes): use pg_wal_lsn_diff(pg_current_wal_lsn(), pg_last_wal_replay_lsn()) on replicas or pg_wal_lsn_diff(pg_current_wal_lsn(), (pg_control_checkpoint()).redo_lsn) to instrument potential replay. High backlog predicts longer recovery.
• Checkpoint health: pg_stat_bgwriter exposes checkpoint_write_time, checkpoint_sync_time, buffers_checkpoint, and checkpoint counts; alert on rising checkpoint_write_time or checkpoint_sync_time. This indicates checkpoint stalls that will elongate recovery.
• WAL IO timing: if you enable track_wal_io_timing/track_io_timing, pg_stat_io (object = wal) exposes write_time and fsync_time so you can detect slow fsyncs in production. Use these signals to correlate latency spikes with fsync events.
• Recovery time / MTTR post-crash: measure time from process start to readiness to accept writes, as well as time until replicas catch up; track trends and SLO violations.

Operational playbook (abbreviated, actionable steps):

1. Detect crash: pager alert + automated runbook window opens.
2. Gather facts (automated script):
   • Is the node on the right timeline? pg_is_in_recovery(), pg_control_checkpoint() output.
   • How many WAL bytes need replay? compute pg_wal_lsn_diff(...).
   • Check disk/SMART/RAID controller logs, dmesg for I/O errors, and controller battery state.
3. If fast recovery expected (small WAL replay), restart DB and monitor recovery logs until database system is ready to accept connections.
4. If WAL backlog or storage errors indicate deeper trouble, escalate to storage team and fail over to pre-warmed standby (if available), promote standby only when its pg_last_wal_replay_lsn() is close enough or you can replay archived WALs.
5. After recovery, run integrity checks: application-level invariant validators, pg_checksums or pg_verify_checksums (offline) where applicable, and replay the test harness to confirm expected data.

A short runbook snippet you can codify into a PagerDuty workflow:

• Step A: Run pg_controldata $PGDATA and capture Latest checkpoint location.
• Step B: Run SELECT (pg_control_checkpoint()).redo_lsn, pg_current_wal_lsn() and compute pg_wal_lsn_diff.
• Step C: If bytes_to_replay < X (your SLA-derived threshold), restart and monitor; else route to storage and SRE on-call for deeper analysis.

Practical application: checklists, scripts, and test harnesses

Use these templates to get started immediately.

Checklist: WAL and Sync Hardening (pre-deploy)

• [ ] Verify wal_sync_method on target OS with pg_test_fsync.
• [ ] Ensure storage controller write cache is non-volatile or disabled; verify with vendor tools and hdparm/sdparm.
• [ ] Choose commit_delay/commit_siblings settings consistent with your latency SLOs.
• [ ] Configure checkpoint targets (checkpoint_timeout, max_wal_size, checkpoint_completion_target) to bound recovery time by business SLA.
• [ ] Add automated crash-and-recover test to CI (see script below).

Crash-and-recover test harness (bash sketch):

#!/usr/bin/env bash # quick harness: run workload, kill DB, restart, verify. set -euo pipefail PGDATA=/var/lib/postgresql/data WORKLOAD_DURATION=60 # seconds PGCTL=/usr/bin/pg_ctl PG_USER=postgres start_db() { sudo -u "$PG_USER" $PGCTL -D "$PGDATA" -w start; } stop_db() { sudo -u "$PG_USER" $PGCTL -D "$PGDATA" -m immediate stop; } run_workload() { # replace with your deterministic workload; pgbench example: sudo -u "$PG_USER" pgbench -c 10 -j 2 -T $WORKLOAD_DURATION mydb } verify() { # implement application-specific invariants; placeholder: sudo -u "$PG_USER" psql -d mydb -c "SELECT COUNT(*) FROM important_table;" } # Flow start_db run_workload & WB_PID=$! sleep 5 # inject fault: kill the server process to simulate crash sudo pkill -9 -f postgres wait $WB_PID || true # restart and measure recovery START=$(date +%s) start_db END=$(date +%s) echo "Recovery time: $((END-START)) seconds" verify 

LD_PRELOAD injection (testing only) — conceptual C snippet already shown above — load with LD_PRELOAD=./libfsync_inject.so INJECT_FSYNC_DROP=1 ./your-workload.

Monitoring queries (Postgres):

-- WAL bytes to replay (primary perspective) SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), (pg_control_checkpoint()).redo_lsn) AS bytes_to_replay; -- Replica lag in bytes (per replication slot) SELECT pid, application_name, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS total_lag_bytes FROM pg_stat_replication; 

Key observability rules:

• Emit checkpoint_sync_time and checkpoint_write_time as per-minute rates and fire alerts if they steadily grow above historical baselines.
• Export pg_stat_io wal object metrics (with track_wal_io_timing) to detect slow fsync events.
• Capture WAL file counts and total size in pg_wal directory, and alert if growth exceeds retention policy.

Sources

PostgreSQL: WAL Configuration - WAL semantics, checkpoint behavior, defaults for checkpoint_timeout and max_wal_size, explanation of checkpoints and recovery start points.

PostgreSQL: Runtime Configuration — WAL - commit_delay, commit_siblings, wal_writer_delay, and wal_writer_flush_after configuration details that implement group commit and WAL writer behavior.

fsync(2) — Linux manual page (man7) - fsync() and fdatasync() semantics and caveats about metadata and device caches.

open(2) — Linux manual page (man7) - O_SYNC and O_DSYNC semantics and historical behavior across kernels.

Linux Kernel Documentation — Fault injection capabilities infrastructure - kernel-level fault injection methods, including IO fail paths and debugfs-based injection.

Jepsen — analyses and methodology - methodology and case studies for durability and consistency testing under faults; example findings and test patterns.

Red Hat — Storage Administration Guide (Write cache / write barrier guidance) - guidance on disabling drive write caches, battery-backed write caches, and when write barriers matter.

PostgreSQL: pg_test_fsync - utility to measure sync-method performance on your platform and inform wal_sync_method choices.

RocksDB: Write-Ahead Log (WAL) — RocksDB Wiki - WAL lifecycle for a write-optimized LSM engine, WAL archival, and deletion conditions tied to SST flushes.

Chaos Mesh — Chaos Engineering for Kubernetes (official site) - tooling and workflows for orchestrating fault-injection experiments in Kubernetes environments.

PostgreSQL: System Information Functions — pg_control_checkpoint() - pg_control_checkpoint() and related functions to query control-file checkpoint and redo LSNs from SQL.

PostgreSQL: The Statistics Collector — pg_stat_bgwriter - pg_stat_bgwriter columns such as checkpoint_write_time and checkpoint_sync_time for checkpoint monitoring.

A well-tuned WAL + sync strategy turns an otherwise risky crash into an operationally-manageable restart. Run the simple harness above against representative disks and controller firmware, capture pg_control_checkpoint() snapshots before and after tests, and bake those checks into your monitoring and runbooks to hold recovery time within your SLA.