Write-ahead log (WAL)
What is the write-ahead log (WAL) in PostgreSQL?
The write-ahead log records every change before it reaches the data files, so PostgreSQL can recover after a crash. WAL also feeds replication and point-in-time recovery.
The central idea of write-ahead logging is that changes to data files may be written only after those changes have been logged and the log has been flushed to permanent storage. After a crash, PostgreSQL replays the log from the last checkpoint to bring the data files back to a consistent state.
Because only the sequential log has to be flushed when a transaction commits, not every data page it changed, WAL also reduces disk writes. The same stream of WAL records is what streaming replication sends to standby servers and what continuous archiving stores for point-in-time recovery. Positions in the log are called log sequence numbers (LSNs).
Example
The example shows the configured wal_level and the current WAL position before and after creating a table with 10,000 rows. The second position is further along because the new table was written to the log.
SHOW wal_level;
SELECT pg_current_wal_lsn();
CREATE TABLE wal_demo AS SELECT g FROM generate_series(1, 10000) AS g;
SELECT pg_current_wal_lsn();Plain SQL for stock PostgreSQL 16 or 17, no extensions needed. Run it in an empty database you can throw away.
Sources
Official PostgreSQL documentation (in English).