Section 3 of 3 · 7 minutes
Follow a change into durable storage
Your goal
What you will be able to do
Explain why PostgreSQL writes WAL before changed table pages must reach durable storage.
Why this matters at work
The practical reason
Recovery depends on knowing which record makes a committed change safe after a crash.
Learn
The idea in plain English
When a transaction changes data, PostgreSQL records the change in the write-ahead log, usually called WAL. A commit becomes durable when the required WAL record is flushed according to the durability settings.
The changed table page may be written later. Checkpoints move dirty pages toward durable storage and set a recovery starting point. After a crash, PostgreSQL replays WAL records that were not yet reflected in table files.
Remember these points
- WAL describes changes before their table pages must be written.
- A checkpoint does not mean every future change is already in a table file.
- Crash recovery replays WAL from a safe checkpoint position.
See it in SQL
Read the current WAL position
Record the server's current write-ahead log location without changing data.
SELECT
pg_current_wal_lsn() AS current_wal_position,
pg_walfile_name(pg_current_wal_lsn()) AS current_wal_file;An LSN is a position in the WAL stream. It increases as PostgreSQL generates new WAL records.
The file name helps an operator connect the logical WAL position to archiving, replication, and recovery evidence.
One row with the current WAL sequence position and the WAL segment file that contains it.
Useful words
Important terms
- WAL
- The ordered record of changes PostgreSQL uses for durability and recovery.
- LSN
- A location in the PostgreSQL WAL stream.
Quick check · Not graded
Check your understanding
Why can PostgreSQL commit before the changed table page is written?
Your progress is saved to your signed-in account.