Databases / storage deep dive

PostgreSQL WAL: why COMMIT is a log flush, not a data-page write

A database can acknowledge a committed transaction while the table page containing its rows is still only in memory. PostgreSQL makes that safe with write-ahead logging: flush the description of the change first, then let dirty data pages reach disk later.

Databases / storage deep dive · write-ahead logging, commit durability, checkpoints, crash recovery, and torn-page protection

2026-08-23

The concrete problem

Suppose an application transfers $10 from Alice to Bob. It sends two UPDATEs, receives COMMIT, and tells the customer the transfer succeeded. A power loss arrives before PostgreSQL has written the modified table pages from RAM to storage. If those pages were the only record of the change, the committed transfer would vanish—or, worse, one update might survive while the other does not.

Writing every changed table page synchronously at commit would make small transactions wait on random data-page I/O. PostgreSQL instead separates two jobs:

• WAL records what must be redone, in mostly sequential log order.
• Data pages in shared_buffers can be written later by the background writer, checkpointer, or a backend needing a clean buffer.

The shape of the solution

PostgreSQL keeps a log sequence number (LSN) for positions in the WAL stream. A modified page stores the LSN of the WAL record that first made its current contents necessary. When PostgreSQL is about to write that page, it makes sure the WAL has reached at least that LSN.

The log is not a SQL audit trail. Most WAL records are physical or physiological descriptions such as “insert this tuple into this heap page” or “update these index bytes.” Their purpose is fast crash recovery and replication, not human-readable history.

What happens during an update and commit

1. Find a buffer. The access method pins a relation page in shared_buffers, reading it from storage if necessary.
2. Change memory. The heap or index code modifies the page, marks the buffer dirty, and creates a WAL description. The page header receives the resulting record's LSN through the buffer/page APIs.
3. Insert WAL. PostgreSQL assembles record fragments in WAL insertion machinery and reserves a position in the shared WAL buffers. The record can contain a resource-manager ID, block references, redo info, and backup-image data when needed.
4. Write the commit record. Transaction code records the transaction's commit metadata and obtains a commit-record LSN. The commit path remembers the latest WAL position it needs.
5. Flush to the requested durability level. With the usual synchronous_commit=on, PostgreSQL waits for WAL through that LSN to be written and synchronized by the WAL writer/backend before returning success.
6. Write data later. A checkpointer or buffer writer eventually writes the dirty heap/index page. The page does not need to be written again at every commit because recovery can reconstruct it from WAL.

Group commit is the important performance trick hiding inside step 5. Concurrent transactions can share one WAL flush: once the flush reaches the furthest requested LSN, earlier committers are durable too. This turns many small sync operations into fewer batched ones, at the cost of a little queueing.

Source-level landmarks

Names move across PostgreSQL releases, but these open-source modules expose the durable path clearly. Read them as a map of responsibilities rather than as a stable extension API.

The central types are deliberately low-level. XLogRecPtr is an LSN into the WAL stream. XLogRecord carries record length, previous-record linkage, transaction ID, and resource-manager info. XLogReaderState holds the reader's segment/buffer state while recovery or inspection walks records. Page headers carry a page LSN, so a page write can compare its required WAL position with the WAL flush position.

Checkpoints and recovery time

A checkpoint writes enough dirty pages and records a checkpoint position so recovery does not need to scan WAL from the beginning of time. After a crash, startup begins near the last valid checkpoint and replays WAL records forward. A clean shutdown can avoid crash recovery; an unclean one leaves a signal that recovery is required.

Checkpoints trade recovery time against foreground I/O. Frequent checkpoints reduce the amount of WAL to replay but can create write bursts. max_wal_size, checkpoint_timeout, checkpoint_completion_target, and the workload's dirty-page rate shape that behavior. The checkpointer is not a commit barrier: commit durability comes from WAL flush, while checkpoints bound recovery work and make old WAL removable.

Why a page LSN prevents the wrong write order

Assume a dirty page contains a change whose WAL record ends at LSN 500. If the page reaches disk while durable WAL ends at LSN 400, a crash could leave a page that claims an update the log cannot redo. Before writing the page, PostgreSQL waits until WAL is flushed through at least 500. If the page write tears or is lost afterward, recovery still has the record needed to repair it.

Important settings and their actual guarantees

There are two separate questions that are often collapsed into “is it durable?”: has PostgreSQL issued a successful synchronization request, and does the storage stack honor that request across power loss? A database cannot repair a cloud volume, RAID controller, filesystem, or VM hypervisor that falsely acknowledges flushes. Validate the whole stack, not only the SQL setting.

Failure modes and debugging

• WAL disk fills. WAL cannot be recycled while a replication slot, archival command, or lagging standby still needs it. Writes eventually fail even when the table volume has free space. Watch pg_stat_wal, replication slots, archive failures, and the filesystem containing pg_wal.
• WAL flush latency spikes. Commit latency follows storage sync latency. Inspect pg_stat_wal.wal_sync_time and wal_sync_method, then correlate with device latency and checkpoint activity. More CPU will not fix a saturated flush path.
• Checkpoint write storms. A checkpoint forced too quickly can make the checkpointer dirty the device faster than it can absorb. Look for checkpoint log messages, writeback latency, and a sawtooth in WAL generation.
• Replication is mistaken for durability. An asynchronous standby may receive WAL later; a local commit does not mean another host has applied it. Configure synchronous replication and the right synchronous_commit mode when the remote copy is part of the business guarantee.
• Disabling fsync or full_page_writes in production. A clean benchmark can hide the exact crash that makes the setting dangerous. Treat these as controlled recovery experiments, not tuning knobs.
• “The row disappeared after restart.” Check whether the transaction used synchronous_commit=off, whether the client received success before a crash, and whether the server log shows recovery reaching the expected end of WAL. A lost acknowledged transaction under asynchronous commit is different from torn or inconsistent pages.

Useful observability includes pg_current_wal_lsn(), pg_wal_lsn_diff(), pg_stat_wal, pg_stat_bgwriter, checkpoint log lines, and replication views such as pg_stat_replication. For a forensic view, the PostgreSQL distribution's pg_waldump can decode records from a WAL directory. Preserve the original WAL and server logs before attempting cleanup.

Security and operational implications

WAL is part of the database's confidentiality and recovery boundary. It can contain old row images, index keys, full-page images, and changes that an application later “deletes.” Protect pg_wal, archived WAL, base-backup storage, replication connections, and diagnostic copies with the same access controls as the primary data directory.

WAL archiving also changes the incident story: an attacker who gains write access to an archive location may be able to corrupt the recovery chain or exhaust storage, while an attacker who reads it may recover historical data. Validate archive continuity, restrict restore commands, encrypt archives in transit and at rest, and test point-in-time recovery. Do not treat a successful COMMIT as proof that the only copy of sensitive data is in the current table.

Compact reproducible lab

Goal: observe a commit advance the WAL position, inspect the WAL directory, force a checkpoint, and see that a commit is not the same operation as writing every table page.

Prerequisites: Docker or Podman with the official PostgreSQL 18 image available, about 1 GB of disposable disk, and a shell. The commands use a temporary host port and remove the container at the end.

LAB="pg-wal-lab-$$"
docker run --name "$LAB" \
  -e POSTGRES_PASSWORD=lab \
  -e POSTGRES_DB=lab \
  -d postgres:18

until docker exec "$LAB" pg_isready -U postgres -d lab >/dev/null 2>&1; do sleep 1; done

docker exec -i "$LAB" psql -U postgres -d lab <<'SQL'
CREATE TABLE ledger(id integer PRIMARY KEY, amount integer NOT NULL);
SELECT pg_current_wal_lsn() AS before_insert;
BEGIN;
INSERT INTO ledger VALUES (1, 100), (2, -100);
COMMIT;
SELECT pg_current_wal_lsn() AS after_commit,
       pg_walfile_name(pg_current_wal_lsn()) AS wal_segment;
SELECT name, setting FROM pg_settings
 WHERE name IN ('synchronous_commit', 'fsync', 'full_page_writes');
CHECKPOINT;
SQL

docker exec "$LAB" sh -lc 'du -h "$PGDATA"/pg_wal; pg_waldump -p "$PGDATA"/pg_wal -n 12 | tail -40'
docker rm -f "$LAB" >/dev/null

Expected result: the second LSN is later than the first, the default settings show local synchronous commit, and pg_waldump prints transaction, heap, and checkpoint records. The checkpoint may write dirty pages, but it is a separate event after the commit.

Variation 1: run a second container with -c synchronous_commit=off, insert a row, and repeatedly stop it immediately after the client receives success. On restart, the row may be absent because the commit was acknowledged before the WAL flush; the database should still recover to a consistent state. This is probabilistic, so do not use it as a correctness test.

Variation 2: run docker exec "$LAB" psql -U postgres -d lab -c "SELECT * FROM pg_stat_wal;" before and after a bulk insert. Compare wal_bytes, wal_records, and sync timing. Then repeat with full_page_writes=off only in a disposable experiment; the lower WAL volume is not evidence that the setting is safe for your storage stack.

Production checklist

1. Keep fsync=on, full_page_writes=on, and a tested wal_sync_method unless a storage specialist has documented a stronger equivalent.
2. Choose synchronous_commit from the business loss budget: local durability, remote flush, or remote apply are different promises.
3. Monitor WAL generation, sync time, checkpoint behavior, archive failures, replication lag, slots, and free space on the WAL filesystem.
4. Test crash recovery and point-in-time restore with the same storage and archive path used in production.
5. Protect current and archived WAL as sensitive database data; limit restore and replication privileges.

The mental model to keep is simple: COMMIT makes a log record durable at the configured level. It does not synchronously paint every modified table page onto storage. That separation is why PostgreSQL can make small commits durable without turning every transaction into a random-write storm—and why the WAL, its flush path, and its recovery tests deserve first-class operational attention.

Primary sources

• PostgreSQL documentation: Write-Ahead Logging — WAL ordering, recovery, and the page-LSN rule.
• PostgreSQL documentation: WAL Configuration — checkpoints, WAL resource sizing, and performance tradeoffs.
• PostgreSQL documentation: Write Ahead Log settings — fsync, full_page_writes, synchronous_commit, and sync methods.
• PostgreSQL source: transaction and WAL access methods — commit, insertion, flushing, reading, and recovery code.
• PostgreSQL documentation: pg_waldump — inspecting WAL records from a segment directory.
• PostgreSQL documentation: High Availability, Load Balancing, and Replication — synchronous versus asynchronous standby guarantees.