Write-ahead logging lets a database make a transaction durable without immediately flushing every table and index page that transaction changed. In PostgreSQL, the log records needed to recover a page must be durable before that page is written to storage. Normal synchronous commit also waits for the commit record to be flushed. Buffered table changes and durable log records therefore advance through different paths.

When it breaks

WAL is not simply a promise to apply an SQL statement later. Execution already changes in-memory pages, and log records describe recovery actions. Some WAL records concern transactions that never commit. Recovery must combine redo with transaction status so an unfinished transaction does not become visible merely because some of its records or data pages reached disk.

Durability settings change the contract. With synchronous_commit=off, a successful reply can precede durable WAL, allowing recent acknowledged transactions to disappear after a crash. With fsync=off, the risks extend to inconsistent storage after a system failure. Full-page writes address another problem: reconstructing pages after partial page writes. None of these mechanisms compensates for storage that falsely acknowledges synchronization or for losing every durable copy.

A lost client reply creates a different uncertainty. The server can commit successfully before the connection fails. Recovery may preserve that transaction even though the client never saw success. Blindly retrying the business action can then duplicate it; use a stable operation identity and reconciliation where the application requires it.

Check it quickly

Run this script as a non-root Linux account with PostgreSQL 18 server and client programs in PATH. It was executed with PostgreSQL 18.6. It initializes an isolated temporary cluster, uses a private Unix socket, and enables normal durability settings explicitly. The immediate shutdown targets only the cluster created by the script. It intentionally exercises a database process interruption, so use the complete fixture rather than substituting a production data directory.

#!/bin/sh
set -eu
umask 077
lab=$(mktemp -d /tmp/xbcatm-crash.XXXXXX)
mkdir "$lab/socket"
cluster=$lab/data
writer_pid=''
cleanup() {
    if test -f "$cluster/postmaster.pid"; then
        pg_ctl -D "$cluster" -m fast -w stop >/dev/null
    fi
    if test -n "$writer_pid"; then wait "$writer_pid" || :; fi
}
trap cleanup EXIT
initdb -D "$cluster" --no-locale --encoding=UTF8 --auth-local=trust --auth-host=reject >/dev/null
cat >> "$cluster/postgresql.conf" <<CONF
listen_addresses = ''
unix_socket_directories = '$lab/socket'
port = 56432
fsync = on
synchronous_commit = on
full_page_writes = on
checkpoint_timeout = '1h'
CONF
pg_ctl -D "$cluster" -l "$lab/server.log" -w -t 15 start >/dev/null
export PGHOST=$lab/socket PGPORT=56432 PGDATABASE=postgres PGUSER=$(id -un)
export PGCONNECT_TIMEOUT=2
psql -X -v ON_ERROR_STOP=1 -qAt <<'SQL'
CREATE TABLE marker (id integer PRIMARY KEY,value text);
INSERT INTO marker VALUES (1,'base');
CHECKPOINT;
SQL
checkpoint_before=$(psql -X -v ON_ERROR_STOP=1 -qAt -c 'SELECT checkpoint_lsn FROM pg_control_checkpoint()')
psql -X -v ON_ERROR_STOP=1 -qAt -c "INSERT INTO marker VALUES (2,'committed')"
commit_end=$(psql -X -v ON_ERROR_STOP=1 -qAt -c 'SELECT pg_current_wal_lsn()')
test "$(psql -X -v ON_ERROR_STOP=1 -qAt -c "SELECT pg_current_wal_flush_lsn()>='$commit_end'::pg_lsn")" = t
PGAPPNAME=crash_uncommitted psql -X -v ON_ERROR_STOP=1 -qAt >"$lab/writer.log" 2>&1 <<'SQL' &
BEGIN;
UPDATE marker SET value='uncommitted' WHERE id=1;
SELECT pg_sleep(120);
COMMIT;
SQL
writer_pid=$!
attempt=0
while test "$(psql -X -v ON_ERROR_STOP=1 -qAt -c "SELECT EXISTS (SELECT FROM pg_stat_activity WHERE application_name='crash_uncommitted' AND wait_event='PgSleep')")" != t; do
    attempt=$((attempt+1))
    test "$attempt" -lt 100 || { echo 'uncommitted writer was not ready' >&2; exit 1; }
    sleep 0.1
done
test "$(psql -X -v ON_ERROR_STOP=1 -qAt -c "SELECT checkpoint_lsn='$checkpoint_before'::pg_lsn FROM pg_control_checkpoint()")" = t
echo committed_wal_flushed_without_new_checkpoint
pg_ctl -D "$cluster" -m immediate -w stop >/dev/null
if wait "$writer_pid"; then
    writer_pid=''
    echo 'interrupted writer unexpectedly succeeded' >&2
    exit 1
fi
writer_pid=''
pg_ctl -D "$cluster" -l "$lab/server.log" -w -t 15 start >/dev/null
grep -F 'redo starts at' "$lab/server.log"
psql -X -v ON_ERROR_STOP=1 -qAt <<'SQL'
SELECT id,value FROM marker ORDER BY id;
DO $$ BEGIN
  IF (SELECT string_agg(id||':'||value,',' ORDER BY id) FROM marker)
     IS DISTINCT FROM '1:base,2:committed' THEN
    RAISE EXCEPTION 'crash recovery result mismatch';
  END IF;
END $$;
SQL
printf 'lab_directory %s\n' "$lab"

The first row is checkpointed with value base. The script then commits a second row and verifies that the flushed WAL location covers the captured post-commit location. A separate backend changes the first row inside a transaction and enters a sleep before its commit. Observing that backend’s actual sleep state confirms that the unfinished update has executed.

Before interruption, the control-file checkpoint location must still match the baseline. Thus the acknowledged insert did not require another checkpoint. Immediate shutdown interrupts the sleeping backend without a normal shutdown checkpoint. The script requires that client’s operation to fail, restarts the same cluster, and checks the server log for redo starting.

Recovered output must be 1|base and 2|committed; a SQL assertion rejects missing, additional, or incorrectly valued rows. The committed transaction survives and the unfinished update is not visible. This does not prove which individual page was reconstructed by redo rather than already written by background activity. It verifies the final recovery contract across a real interrupted server with pending work.

The script stops its recovered cluster on exit and prints the directory containing the logs. It does not simulate a power cut, dishonest disk cache, lost volume, or remote replication failure. Those scenarios need their own recovery evidence.

Account for the complete write path

Appending WAL can avoid forcing scattered data pages at every commit, but it is not free. WAL still consumes bandwidth, synchronization latency, storage, and replication capacity. Full-page images can enlarge the stream. Dirty pages still need later writeback, and checkpoints establish recovery starting points rather than eliminating that work.

Measure commit latency, WAL generation, checkpoint behavior, and recovery duration together. A lower acknowledgment latency obtained by weakening synchronization is a changed durability promise, not an equivalent optimization. Keep local crash recovery separate from archived recovery and failover guarantees. The useful result is a documented relationship between acknowledged transactions, recoverable state, and the failure model actually rehearsed.

Sources