WAL, Checkpoints, and fsync

Postgres’ durability story. The write-ahead log is the source of truth; checkpoints flush data; fsync makes it survive a crash. Mis-tune these and you either lose data or spend all your IO on housekeeping.

WAL (Write-Ahead Log)

Before modifying a data page, Postgres writes a description of the change to the WAL. On crash, replay the WAL from the last checkpoint and the database is consistent.

The “log first, data later” property — Postgres can commit a transaction by fsync-ing the WAL record, without fsync-ing the changed data pages. Data pages are written by background processes much later (the bgwriter / checkpointer).

WAL files (~16MB each by default) accumulate in pg_wal/. Files older than the last checkpoint and not needed by replication slots can be recycled or removed.

What fsync = on does

When you COMMIT, Postgres writes the commit record to the OS, then calls fsync to flush it to disk. Only after fsync returns does the COMMIT return success.

fsync = on              # default — durability
synchronous_commit = on # also default

fsync = off returns “committed” immediately, without waiting for disk. Massively faster, completely unsafe — a power loss can lose minutes of data. Some teams run this on dev / disposable data only. Don’t run it in prod for anything you care about.

synchronous_commit = off is the safer middle: WAL is fsync’d in batches asynchronously. Slight data-loss window on crash (a few hundred ms of transactions) but no corruption. Useful for high-throughput logging workloads where losing 200ms is acceptable.

full_page_writes

full_page_writes = on   # default

Postgres writes the full page to WAL the first time a page is modified after a checkpoint. Why: an OS-level page write isn’t atomic — a torn write (half old, half new) leaves the page corrupt. Replay then restores from WAL.

Cost: WAL volume balloons right after each checkpoint. Tune checkpoint frequency carefully. Turning full_page_writes = off is unsafe unless your storage guarantees atomic page writes (some enterprise SANs).

Checkpoints

A checkpoint flushes all dirty buffers to disk and marks a point in the WAL: “everything before this is on disk.”

Why: WAL grows forever otherwise; recovery time after crash is proportional to WAL from last checkpoint.

Triggered by:

• checkpoint_timeout (default 5 min) — periodic.
• max_wal_size (default 1GB) — threshold.
• Manual CHECKPOINT command.

checkpoint_timeout = 15min
max_wal_size = 4GB
checkpoint_completion_target = 0.9     # spread the checkpoint over 90% of the interval

checkpoint_completion_target is the throttle — Postgres spreads writes across most of the interval rather than all at once, smoothing IO load.

Checkpoint storm

If checkpoints happen too often (small max_wal_size, busy DB), every checkpoint causes a wave of page writes + every-page-after gets a full-page WAL entry. Symptoms: periodic latency spikes, high write IO bursts.

Diagnostic:

SELECT
  checkpoints_timed, checkpoints_req,
  checkpoint_write_time, checkpoint_sync_time,
  buffers_checkpoint, buffers_clean, buffers_backend
FROM pg_stat_bgwriter;

checkpoints_req (request-triggered, by max_wal_size) much higher than checkpoints_timed (timer-triggered) = your max_wal_size is too small for your write rate. Increase it.

Bgwriter and backend writes

Three sources of page writes:

1. Checkpointer — flushes everything at checkpoint time.
2. bgwriter — background writer that opportunistically flushes dirty pages between checkpoints.
3. Backend — when a backend can’t find a clean buffer, it writes one itself (worst case — your query latency includes a page write).

buffers_backend should be very small. If it’s large, your bgwriter isn’t keeping up. Tune bgwriter_lru_maxpages, bgwriter_delay.

WAL settings checklist

wal_level = replica                  # or logical for CDC
max_wal_size = 4GB                   # bigger = less frequent checkpoints
min_wal_size = 1GB                   # don't keep more than this preallocated
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
wal_compression = on                 # compress WAL records, lighter on disk
wal_buffers = 16MB                   # default is auto-tuned, rarely needs change

For high-write OLTP, raising max_wal_size to 8-16GB and checkpoint_timeout to 30 min smooths IO and reduces full-page write overhead, at the cost of slightly longer crash recovery.

fsync gotcha: the famous postgres-fsync issue

In 2018, the Postgres community discovered Linux fsync doesn’t necessarily report I/O errors to the caller that asked. A write fails on disk, fsync returns success on the next call, Postgres thinks the data is safe but it isn’t.

Fixed in modern kernels (5.x+) and Postgres now panics on fsync error rather than masking it. Practically: don’t run Postgres on old kernels for critical workloads.

crash recovery

On startup after crash, Postgres:

1. Reads the control file → finds last checkpoint LSN.
2. Replays WAL from that LSN forward.
3. Marks itself ready.

Recovery time ≈ time-to-replay-the-WAL since last checkpoint. With checkpoint_timeout = 30min and 1 GB/min of write rate, you might be replaying 30 GB → minutes of downtime.

For HA, replicas avoid this — they replay continuously, so failover is “promote, accept connections” without replay.

Archive_mode (for PITR)

archive_mode = on
archive_command = 'aws s3 cp %p s3://my-wal-archive/%f'

Each WAL segment is shipped to durable storage (S3, NAS) when filled. Point-In-Time Recovery (PITR) restores from a base backup + replays archive WAL to a chosen timestamp.

RDS does this automatically (snapshot + WAL retention window). Self-managed Postgres needs the archive_command + a tool like pgBackRest or wal-g.

Common production issues

• fsync = off in prod — someone tried it for benchmarking and forgot.
• max_wal_size too small — checkpoint storms, latency spikes.
• checkpoint_completion_target at 0.5 — checkpoint writes squeezed into half the interval, IO spikes.
• Disk full from pg_wal/ — replication slot stuck, WAL retained forever. Drop the slot.
• Long crash recovery — too few checkpoints + huge write rate = lots to replay. Raise checkpoint frequency or use a replica for HA.
• archive_command failing silently — WAL piles up, pg_wal/ fills, primary stops. Always monitor archive lag.

Interview angle

• “What’s WAL and why does Postgres have it?” — Write-Ahead Log. Before modifying a data page, Postgres writes the change to WAL. WAL is durably fsync’d on commit; data pages can be written later. Crash recovery = replay WAL since last checkpoint. Allows fast commits (one fsync of small WAL record) without fsync’ing every modified page.
• “What does a checkpoint do?” — flushes all dirty buffers to disk and marks a point in the WAL beyond which no recovery is needed. Controls crash recovery time and WAL file recycling.
• “What happens if you set fsync = off?” — commits return before disk write completes. ~10× faster; an OS crash or power loss can corrupt the database (lose minutes of data, possible un-recoverable state). Don’t use in prod. synchronous_commit = off is the safer middle — batches fsync but stays corruption-safe.
• “What’s full_page_writes and why is it on by default?” — first time a page is modified after a checkpoint, the full page is logged to WAL. Why: OS page writes aren’t atomic; a torn write would leave the page corrupt; WAL has the full pre-image to restore from on recovery.
• “What’s a checkpoint storm and how do you diagnose it?” — too-frequent checkpoints cause IO spikes (every page write + post-checkpoint full-page WAL). pg_stat_bgwriter.checkpoints_req much greater than checkpoints_timed means max_wal_size is too small. Raise it.
• “How long does crash recovery take?” — proportional to WAL between last checkpoint and crash. Lots of WAL → minutes of replay. Mitigate with more frequent checkpoints (more IO during normal operation) or use a hot standby for HA (no replay during failover).