PostgreSQL WAL, Checkpoints, and Crash Recovery Explained

PostgreSQL can confirm a transaction before every changed table page has been written to its data files. That is possible because it first records the change in its write-ahead log (WAL). For developers and database operators, following WAL through commit, checkpoints, and restart recovery explains both how PostgreSQL protects committed work and how backups can be restored to an earlier point. The same mechanics also affect disk capacity, write latency, and recovery time.

What Is PostgreSQL WAL?

WAL is an ordered record of changes to a PostgreSQL cluster. It lives in the pg_wal directory and describes operations that modify database pages. PostgreSQL’s WAL introduction explains the central rule: the log describing a page change must reach durable storage before the changed page itself is written.

This ordering lets PostgreSQL avoid synchronously writing every touched table and index page for every transaction. At commit, it can flush the relevant WAL records, acknowledge the transaction, and write the data pages later. If the server stops unexpectedly, startup recovery replays the WAL records needed to bring the files forward to a consistent state. WAL is therefore a recovery mechanism, not a second copy of the database that an operator can query like a table.

PostgreSQL is the open-source relational database maintained by the PostgreSQL Global Development Group. Its durability depends on the complete storage path, not just the database setting: the operating system, controller, virtualized storage, and hardware must honor flush requests. The POSIX fsync() specification describes the interface for requesting that file data be synchronized, while noting that the nature of the transfer is implementation-defined.

Why WAL Exists

Without a log-first design, a transaction that updates several pages would need to coordinate durable writes across all of them before returning success. A crash partway through could leave only some changes on disk. WAL reduces that coordination: PostgreSQL writes a sequential log record and makes it durable before acknowledging a commit, then data pages can be written in the background or during a checkpoint.

That design provides atomic recovery. After a crash, PostgreSQL redoes logged page changes from an appropriate recovery point. Transactions that had not committed do not become visible as completed work. PostgreSQL does not maintain a physical undo log for this process; transaction state and its multi-version concurrency control determine which row versions are visible. For how those row versions are cleaned up later, see the PostgreSQL MVCC, VACUUM, and table bloat explainer.

WAL also enables physical replication and point-in-time recovery (PITR). A standby can receive WAL from a primary, while an archived sequence of WAL files can be replayed on top of a physical base backup. Those uses share the same record stream but have separate configuration and retention requirements.

How WAL and Checkpoints Work

The write path begins when a backend changes a page in PostgreSQL’s shared buffers. PostgreSQL creates WAL records that describe the change. Before the corresponding data page is allowed to reach disk, the WAL covering that change must be flushed. With fsync enabled and the usual synchronous_commit setting, a successful commit waits for the required local WAL flush; settings that relax this behavior can trade recent-commit durability for lower latency. Synchronous replication settings can add a wait for a standby, but are a separate policy.

A checkpoint is a recovery boundary. PostgreSQL writes dirty buffers to data files and records a checkpoint in WAL. It guarantees that pages modified before the checkpoint have been written, so after an unclean shutdown recovery can start from the checkpoint’s redo location instead of scanning the entire log history. Checkpoints happen automatically based on time and WAL volume; a manual CHECKPOINT is available but can cause a burst of I/O.

During crash recovery, PostgreSQL locates its latest usable checkpoint, replays records from the redo location, and brings the cluster to a consistent state. This is redo, not a user-selectable rollback of each incomplete transaction. The exact startup work depends on how much WAL was generated since that recovery point and whether the required records are available.

Checkpoint frequency is a trade-off. Shorter intervals can reduce the amount of redo after a crash, but flush dirty pages more often and can increase full-page images in WAL when full_page_writes is enabled. Longer intervals reduce checkpoint overhead but can increase recovery work and the amount of WAL PostgreSQL needs to retain. The WAL configuration documentation describes the related settings and their trade-offs.

Key Components and Terms

• WAL record and LSN: A record describes a database change. Its log sequence number (LSN) identifies a position in the ordered WAL stream, useful for comparing write and replication progress.
• WAL segment: PostgreSQL stores the stream in numbered segment files under pg_wal. The server recycles or removes segments when they are no longer needed locally.
• Redo location: The checkpoint’s starting position for redo. Recovery replays later records to restore consistency.
• Full-page image: With full_page_writes enabled, PostgreSQL logs a full copy of a page the first time it is modified after a checkpoint. This helps recover from a torn page write, at the cost of additional WAL.
• WAL archiver: When archiving is configured, PostgreSQL passes completed segments to an archive command or library. Archive failures are retried; they do not mean the segment has been safely preserved elsewhere.
• Replication slot: A slot can prevent WAL needed by a standby or logical consumer from being recycled. A stalled consumer can therefore fill pg_wal, even when checkpoints are succeeding.
• Timeline: A recovery or promotion can create a new history branch. Timeline history files help PostgreSQL identify the correct WAL sequence when restoring or following a standby.

WAL retention has several causes. A checkpoint determines when old records are no longer needed for local crash recovery, but an archive that has not succeeded or a replication slot that still needs a segment can hold it back. max_wal_size influences checkpoint scheduling; it is not a strict cap on disk use when other retention requirements apply.

Real-World Uses

Crash recovery is the automatic startup path after an unexpected restart. PostgreSQL replays WAL since its last checkpoint so the database can return to a consistent state without requiring a full restore.

High availability uses streaming or archived WAL to keep a standby close to a primary. Replication can reduce downtime, but it does not replace backups: an accidental deletion can also reach the standby.

Point-in-time recovery combines a physical base backup with an uninterrupted archive of the WAL needed after that backup. Recovery can stop at a selected time or before an unwanted operation. PostgreSQL’s continuous archiving and PITR guide emphasizes setting up and testing WAL archiving before relying on it for a restore.

Getting Started: Inspect WAL in a Local Database

The following disposable PostgreSQL instance uses Docker. It has a local development password and is not configured for production, backups, or external access. Ensure Docker is installed and that port 5432 is available:

docker run --name pg-wal-demo \
  -e POSTGRES_PASSWORD=localdev \
  -e POSTGRES_DB=app \
  -p 5432:5432 \
  -d postgres:17

Wait until PostgreSQL accepts connections, then open a psql session:

docker exec pg-wal-demo pg_isready -U postgres -d app
docker exec -it pg-wal-demo psql -U postgres -d app

In the session, record the current LSN, make a small change, and inspect WAL and checkpoint statistics:

SELECT pg_current_wal_lsn() AS before_change;

CREATE TABLE wal_demo (
  id bigint PRIMARY KEY,
  note text NOT NULL
);

INSERT INTO wal_demo VALUES (1, 'first committed row');
UPDATE wal_demo SET note = 'updated row' WHERE id = 1;

SELECT pg_current_wal_lsn() AS after_change;

SELECT wal_records, wal_fpi, wal_bytes, wal_sync
FROM pg_stat_wal;

SELECT num_timed, num_requested, write_time, sync_time, buffers_written
FROM pg_stat_checkpointer;

These counters are cumulative and may not change immediately for every query; compare them over an interval and interpret them alongside the workload. wal_fpi counts full-page images, and wal_sync counts WAL synchronization requests. To inspect archive health on a system where archiving is configured, query:

SELECT archived_count, failed_count, last_archived_wal, last_failed_wal
FROM pg_stat_archiver;

For a disposable experiment, run CHECKPOINT; and query pg_stat_checkpointer again to see the requested-checkpoint count increase. Do not schedule frequent manual checkpoints as a general tuning fix. The PostgreSQL monitoring documentation describes these statistics and their collection behavior.

Exit with \q and remove the test container when finished:

docker rm -f pg-wal-demo

Removing this container deletes its local database files. A clean Docker stop is not a crash-recovery test: PostgreSQL shuts down normally and can checkpoint before exiting. Do not simulate a crash against a production database. To validate a real recovery plan, restore a recent base backup to an isolated environment, provide the required WAL archive, replay to a chosen target, and verify application data before relying on that procedure.

For production PITR, configure an archive destination that is protected, monitored, and separate from the primary database’s failure domain. Use a backup tool or archive integration designed for the storage provider rather than copying the example for archive_command blindly. Alert on archive failures, retained WAL growth, filesystem capacity, and replication lag; then exercise restores regularly. A backup that has never been restored is an unverified recovery assumption.

Common Misconceptions

“A successful commit means table pages were all written.”

Not necessarily. PostgreSQL can acknowledge a commit after its WAL is durable while data pages remain dirty in memory. That is safe only when the storage stack honors durability requests and PostgreSQL can access the WAL during recovery.

“A checkpoint is the same thing as a backup.”

A checkpoint helps bound crash recovery on the existing cluster. It does not create an independent copy, protect against disk loss, or provide a historical restore point. Backups and archived WAL must be stored and tested separately.

“max_wal_size prevents WAL disk usage from growing past that size.”

It is a checkpoint scheduling target, not a hard storage limit. An unavailable archive destination or an inactive replication slot can prevent old segments from being recycled. Monitor both the PostgreSQL retention causes and filesystem usage.

Related Articles

• For practical tuning context, see the PostgreSQL performance optimization guide.
• To distinguish row-level CDC from physical WAL replay, read PostgreSQL logical replication and CDC.
• For backups and restore objectives, explore the database backup and recovery strategies guide.
• Learn how snapshots and cleanup interact in the PostgreSQL MVCC and VACUUM explainer.

Changelog

• Initial publication. Last updated: November 11.