--- title: "PostgreSQL Internals - Module 4: Write-Ahead Logging, Checkpoints and Crash Recovery" description: "Deep dive into PostgreSQL's Write-Ahead Logging - how WAL records are written, what checkpoints do, and how crash recovery uses REDO to restore state." author: "Samba Siva" published: 2026-04-12T07:25:05.653Z updated: 2026-11-03T13:02:15.230Z tags: - "PostgreSQL" - "Technical" - "PGInternals" url: "https://sambasivareddy.in/blog/postgresql-internals-module-4-write-ahead-logging-checkpoints-and-crash-recovery" --- # PostgreSQL Internals - Module 4: Write-Ahead Logging, Checkpoints and Crash Recovery In the previous blogs, we explored the MVCC and Transaction Isolations in the PostgreSQL. This module 4 is about all about the **WAL**. WAL is the backbone of PostgreSQL's durability and replication. Every write (e.g., INSERT, UPDATE, DELETE, COMMIT) goes through the WAL before it touches a data page. This module covers the core mechanics of WAL from its physical layout on disk to crash recovery and replication with enough depth to understand what actually happens on each write. * * * ## Part 1: Why WAL Exists? **The Problem:** Writing an 8KB page to disk is not atomic. If the system crashes mid-write, the page is corrupted. Without WAL, a crash leaves the database in an inconsistent state with no way to recover. **The Solution:** With WAL, before any change is applied to a data page, a record describing that change is written to the WAL log. If the system crashes, PostgreSQL just replays the WAL to reconstruct any data page that weren't fully flushed (safely written on disk) before. ``` Without WAL: modify page in memory crash page is half-written The database is corrupt With WAL: write WAL record first flush WAL to disk modify page in memory crash replay WAL on restart The database is consistent ``` > This is the write-ahead rule: WAL must hit disk before the data page. The WAL writer enforces this and it is never violated. * * * ## Part 2: WAL Physical Layout on Disk WAL files live in `$PGDATA/pg_wal/`**.** Each file is exactly 16MB by default. And file size can be configurable with the flag `--wal-segsize` at `initdb` time. ### WAL Segment Naming ``` $PGDATA/pg_wal/ 000000010000000000000001 ← segment 1, timeline 1 000000010000000000000002 ← segment 2 000000010000000000000002 ← segment 3 ... and many more ``` Each filename is a 24-character hex string broken into three 8-character groups: ``` 00000001 00000000 00000001 -------- -------- -------- timeline log ID segment ID ``` - Timeline: increments every time you do point-in-time recovery (PITR). Keeps recovered branches separate. - Log ID + Segment ID - together form the position in the WAL stream. ### Log Sequence Number (LSN) An LSN is a **64-bit byte offset** into the WAL stream. It uniquely identifies a byte position across all WAL segments. ``` LSN = 0/1A3F2C0 0 - Log ID 1A3F2C0 - byte offset within the log -- Convert LSN to segment + offset segment = LSN / wal_segment_size(=16MB) offset = LSN % wal_segment_size -- Queries to get WAL Information SELECT pg_current_wal_lsn(); -- current write position -- 0/2278360 SELECT pg_current_wal_insert_lsn(); -- current insert position -- 0/2278360 SELECT pg_walfile_name(pg_current_wal_lsn()); -- which file we're in -- 000000010000000000000002 SELECT pg_walfile_name_offset(pg_current_wal_lsn()); -- file + offset -- (000000010000000000000002,2589536) ``` ### Internal Structure of a WAL Segment Each 16MB segment is divided into 8KB pages (same size as heap pages). Each page has a small header, followed by WAL records. ``` WAL Segment (16 MB): ┌──────────────────────────────────┐ │ Page 0 (8 KB) │ │ XLogPageHeaderData (20 bytes) │ │ xlp_magic (2B) — 0xD116 identifies WAL │ │ xlp_info (2B) — flags │ │ xlp_tli (4B) — timeline ID │ │ xlp_pageaddr (8B) — LSN of this page's start │ │ xlp_rem_len (4B) — bytes of record continued │ │ [WAL record 1] │ │ [WAL record 2] │ │ [WAL record 3 — starts here, continues on page 1] │ ├──────────────────────────────────┤ │ Page 1 (8 KB) │ │ XLogPageHeaderData │ │ [continuation of WAL record 3] │ │ [WAL record 4] │ │ ... │ └──────────────────────────────────┘ ``` WAL records can span pages, **xlp\_rem\_len** tells the reader how many bytes of the previous record continue on this page. Now that we know where WAL lives on disk, let's look at what a single WAL record actually contains. * * * ## Part 3: The WAL Record: Anatomy A WAL record has two parts: a fixed header and a variable body. The header (`XLogRecord`) identifies the record; the body carries the actual change data. ### Full Record Layout ``` ───────────────────────── XLogRecord (24 bytes) ← fixed header xl_tot_len, xl_xid, xl_prev, xl_info, xl_rmid, xl_crc ───────────────────────── XLogRecData — main data block ← rmgr-specific payload e.g. xl_heap_insert struct for INSERT (offset on page, flags) ───────────────────────── Block Reference(s) [0..MAX_BLOCKS] ← one per modified buffer RelFileLocator (tablespace/db/rel) BlockNumber fork number (main/fsm/vm) [optional: Full-Page Write image] ───────────────────────── ``` How a resource manager (discussed below) registers this at write time ``` /* How Heap rmgr builds an INSERT record — simplified from heapam.c */ XLogBeginInsert(); /* 1. Register main payload — the rmgr-specific struct */ XLogRegisterData((char *) &xlrec, sizeof(xl_heap_insert)); /* 2. Register the modified buffer — triggers Full-Page Write (Discussed below) if needed */ XLogRegisterBuffer(0, buffer, REGBUF_STANDARD); /* 3. Register the tuple data itself */ XLogRegisterBufData(0, (char *) tupledata, tuple_len); /* 4. Finalize — returns the LSN of this record */ recptr = XLogInsert(RM_HEAP_ID, XLOG_HEAP_INSERT); ``` ### Resource Managers (rmgr) The **xl\_rmid** field in the above structure (XLogRecord) identifies which subsystem generated this WAL record. Each subsystem is a **"resource manager"** responsible for replaying its own records. Some resource managers and what they log: > XLOG (Id = 0): Checkpoint, switch, backup, full-page writes > > Transaction (Id = 1): COMMIT, ABORT, PREPARE, sub transaction > > Storage (Id = 2): Relation files creation/deletion > > CLOG (Id = 3): Commit log page updates > > Database (Id = 4): CREATE/DROP DATABASE > > Tablespace (Id = 5): CREATE/DROP TABLESPACE > > MultiXact (Id = 6): Multi-transactions, mainly row lock sharing > > RelMap (Id = 7): Catalog relation mapping updates > > Standby (Id = 8): Standby snapshot, lock info for hot standby > > Heap2 (Id = 9): VACUUM, FREEZE, VISIBLE flag changes > > Heap (Id = 10): INSERT, UPDATE, DELETE, HOT\_UPDATE, LOCK > > Btree (Id = 11): Index insert, split, delete, newroot > > Hash (Id = 12): Hash Index operations > > Gin (Id = 13): GIN index operations > > Gist (Id = 14): GiST Index operations > > Sequence (Id = 15): Sequence increment > > SPGist (Id = 16): SP-GiST Index operations > > BRIN (Id = 17): BRIN Index operations ### Full-Page Writes The first time a page is modified after a checkpoint, PostgreSQL writes the **entire 8KB page** into the WAL record (not just the change). This is called a **Full-Page Write**. **Why?** Because during crash recovery, a page might be half-written when the crash occurs. Replaying just the delta record (like only changes after crash) onto a corrupt page would produce garbage. The Full-Page Writes ensures recovery always starts from a known-good page. ``` SHOW full_page_writes; -- should be 'on' ``` After the first modification post-checkpoint, subsequent changes to the same page within the same checkpoint interval write only the **delta** (the specific bytes changed). Much smaller. * * * ## Part 4: The COMMIT Path This is what happens, step by step, when user issues "COMMIT": ``` 1. Backend calls RecordTransactionCommit() 2. Builds a COMMIT WAL record (rmgr=Transaction): - xl_xid = transaction ID - commit timestamp - list of subtransaction IDs - list of invalidation messages (for catalog cache) 3. Calls XLogInsert() - copies record into WAL buffers -- WAL buffers: circular buffer in shared memory" 4. Calls XLogFlush(commit_lsn): - if synchronous_commit = on: WAL writer calls write() + fsync() blocks until OS confirms data on physical disk - if synchronous_commit = off: returns immediately - WAL may still be in OS buffer 5. Updates CLOG: marks XID as COMMITTED 6. Sends "command complete" to client ``` This is why COMMIT is fast even for large transactions because only the WAL record is fsynced (written to disk), not the data pages. **The data pages are written lazily by the background writer and checkpointer.** ### synchronous\_commit levels ``` off = return after WAL in local memory local = return after WAL fsynced locally remote_write = return after WAL written (not fsynced) on replica remote_apply = return after WAL applied on replica on (default) = return after WAL fsynced locally ``` * * * ## Part 5: Checkpoints A checkpoint is **a point in time where PostgreSQL guarantees all dirty data pages have been flushed to disk**. After a checkpoint, crash recovery only needs to replay WAL from that checkpoint forward, not again from the beginning of time. This is what happens, step by step, during a checkpoint: ``` 1. Checkpointer wakes up (timeout or WAL size trigger) 2. Writes a CHECKPOINT BEGIN WAL record with: - redo_lsn: the LSN from which recovery must start - this_lsn: the LSN of the checkpoint record itself 3. Scans the buffer pool for dirty pages For each dirty page: a. If page's pd_lsn > redo_lsn and no Full-Page Write (see Part 3: Full-Page Writes) yet: write Full-Page Write WAL record for this page b. write() the page to its data file (spread over time to avoid I/O spike i.e write happens in over several intervals) 4. Calls fsync() on all data files - Now the pages are guaranteed durable 5. Writes a CHECKPOINT END WAL record 6. Updates pg_control file with new checkpoint location ``` ### pg\_control pg\_control is the "source of truth" for recovery and important file in the postgresql. ``` # Read the pg_control file (run in bash, not in psql) pg_controldata $PGDATA # Key fields: # Latest checkpoint location: 0/1A3F058 # Prior checkpoint location: 0/1A2F000 # Latest checkpoint's REDO lsn: 0/1A3F000 # Database cluster state: in production # Latest checkpoint's TimeLineID: 1 ``` During crash recovery, PostgreSQL reads pg\_control to find where the last checkpoint was, seeks to that LSN in the WAL, and replays forward. ### Checkpoint Tuning ``` SHOW checkpoint_timeout; -- default 5min: time between checkpoints SHOW max_wal_size; -- default 1GB: WAL size that triggers checkpoint SHOW checkpoint_completion_target; -- default 0.9: spread I/O over 90% of interval -- Monitor checkpoint frequency and duration: SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time, buffers_checkpoint FROM pg_stat_bgwriter; ``` If **checkpoints\_req** is high, your **max\_wal\_size** is too small, checkpoints are being forced by WAL volume rather than schedule, so try to **increase max\_wal\_size**. With checkpoints in place, let's see how PostgreSQL actually uses them during crash recovery. * * * ## Part 6: Crash Recovery What happens when PostgreSQL starts after a crash: ``` 1. Read pg_control - Find last checkpoint LSN and redo_lsn 2. Open WAL segment containing redo_lsn 3. For each WAL record from redo_lsn to end of WAL: a. Read record, validate checksum using CRC (Cyclic Redundancy Check) b. Look up resource manager by xl_rmid c. Call rmgr->redo(record) For a Heap INSERT record: - load the target page into shared buffers - check page's pd_lsn v/s record's LSN - if page pd_lsn >= record LSN: SKIP (page already has this change) - else: apply the change to the page update pd_lsn = record LSN For a COMMIT record: - mark XID committed in CLOG 4. When end of WAL reached: recovery complete 5. Write new checkpoint 6. PostgreSQL starts listening.. ``` The `pd_lsn >= record LSN` check is the **idempotency guard**, it makes recovery safe to run multiple times. If a page was already written to disk with a change before the crash, replaying that WAL record does nothing. * * * ## Part 7: WAL and Replication WAL is the replication medium. ### Physical/Streaming Replication Streaming replication works by shipping WAL records from primary to standby in real time. ``` Primary Standby │ │ │── WAL record written ────────│ │ │── WAL receiver writes to pg_wal │ │── startup process replays record │ │── standby's pd_lsn advances │◄─ acknowledgement (replay_lsn) ─│ ``` ### Logical Replication Logical replication decodes WAL records into SQL-level changes (INSERT/UPDATE/DELETE) using **logical decoding**. The `pg_logical` output plugin (PostgreSQL's built-in logical decoder) reads WAL and emits row-level changes, which subscribers apply to their own tables. ``` -- Create a logical replication slot SELECT pg_create_logical_replication_slot('my_slot', 'pgoutput'); -- Peek at decoded changes SELECT * FROM pg_logical_slot_get_changes('my_slot', NULL, NULL); ``` ### Replication Slots and WAL Retention Every replication slot (physical or logical) holds a `restart_lsn` the oldest WAL position the slot still needs. PostgreSQL will **never recycle WAL segments** below that LSN, regardless of `max_wal_size`. ``` Primary WAL stream: ─────────────────────────────────────► seg 1 seg 2 seg 3 seg 4 seg 5 (current) └── slot restart_lsn stuck here segs 2, 3, 4 cannot be recycled WAL accumulates on disk ``` If a standby goes down or a logical subscriber stops consuming, its slot's `restart_lsn` freezes. WAL accumulates. When disk fills up, PostgreSQL either stops WAL archiving or crashes. ### Monitor all slots and how much WAL they are retaining ``` SELECT slot_name, slot_type, active, restart_lsn, pg_size_pretty( pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) ) AS retained_wal FROM pg_replication_slots ORDER BY retained_wal DESC; -- Example output: -- slot_name | slot_type | active | restart_lsn | retained_wal -- ─────────────────────────────────────── -- my_slot | logical | f | 0/5A000000 | 2147 MB ← danger -- standby_1 | physical | t | 0/8F320000 | 12 MB ← fine ``` Prevent unbounded WAL retention with a safety limit: ``` -- postgresql.conf max_slot_wal_keep_size = 10GB -- PostgreSQL 13+ -- If a slot falls behind by more than this, PostgreSQL -- invalidates the slot automatically rather than filling disk -- Check if any slot has been auto-invalidated SELECT slot_name, invalidation_reason FROM pg_replication_slots WHERE invalidation_reason IS NOT NULL; ``` This is one of the most common production WAL crises — a forgotten logical replication slot silently accumulating gigabytes until the server runs out of disk. Monitoring `retained_wal` per slot should be part of any PostgreSQL alerting setup. * * * ## Part 8: WAL Compression and Tuning ``` -- Key WAL parameters in postgresql.conf: -- wal_level = minimal | replica | logical wal_level = minimal -- fastest, no replication possible -- skips WAL for COPY/CREATE TABLE AS on new tables wal_level = replica -- default, enables physical standby + pg_basebackup wal_level = logical -- enables logical decoding, logs full row identity wal_compression = on -- compress Full-Page Write images wal_buffers = 16MB -- in-memory WAL buffer wal_writer_delay = 200ms -- how often WAL writer flushes wal_writer_flush_after = 1MB -- flush when this much WAL accumulated min_wal_size = 80MB -- keep at least this much WAL on disk max_wal_size = 1GB -- trigger checkpoint at this WAL volume archive_mode = on -- enable WAL archiving archive_command = 'cp %p /mnt/wal_archive/%f' -- copy to archive location -- Monitoring -- ------------------------------------------------------------ -- WAL generation rate SELECT pg_size_pretty( pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0') ) AS total_wal_generated; -- WAL files currently on disk SELECT count(*) AS wal_files, pg_size_pretty(count(*) * 16 * 1024 * 1024) AS total_size FROM pg_ls_waldir(); ``` * * * ## Part 9: Hands-On Lab ``` create table orders (id int primary key, name text); -- 1. Watch your LSN advance with each operation SELECT pg_current_wal_lsn(); -- 0/22A62F0 INSERT INTO orders VALUES (999, 'test'); -- Inserts 1 row SELECT pg_current_wal_lsn() ; -- 0/22A6400 -- Here, Difference show how many bytes of WAL produced by INSERT -- 2. Measure WAL bytes per operation SELECT pg_wal_lsn_diff('0/22A6400', '0/22A62F0'); -- 272 bytes -- 3. See current WAL files SELECT name, size, modification FROM pg_ls_waldir() ORDER BY modification DESC LIMIT 5; -- name | size | modification -- 000000010000000000000002 | 16777216 | 2026-04-17 19:08:49+05:30 -- 000000010000000000000003 | 16777216 | 2026-03-28 11:06:35+05:30 -- 4. Find what WAL file your current LSN is in SELECT pg_walfile_name(pg_current_wal_lsn()); -- 000000010000000000000002 -- 5. Manually force a checkpoint and time it \timing on CHECKPOINT; \timing off -- 104.841 ms -- 6. Read checkpoint info SELECT * FROM pg_control_checkpoint(); -- checkpoint_lsn | 0/22A66D8 -- redo_lsn | 0/22A66A0 -- redo_wal_file | 000000010000000000000002 -- timeline_id | 1 -- prev_timeline_id | 1 -- full_page_writes | t -- next_xid | 0:9144 -- next_oid | 34964 -- next_multixact_id | 1 -- next_multi_offset | 0 -- oldest_xid | 561 -- oldest_xid_dbid | 1 -- oldest_active_xid | 9144 -- oldest_multi_xid | 1 -- oldest_multi_dbid | 1 -- oldest_commit_ts_xid | 0 -- newest_commit_ts_xid | 0 -- checkpoint_time | 2026-04-18 08:36:30+05:30 -- 7. Decode actual WAL records with pg_waldump -- First, note your LSN range from step 1 and 2 -- Start LSN: 0/22A62F0 | End LSN: 0/22A6400 -- (run in shell, not psql) pg_waldump -p $PGDATA/pg_wal \ -s 0/22A62F0 \ -e 0/22A6400 -- Expected output: -- rmgr: Heap len (rec/tot): 59/272, tx: 9143, -- lsn: 0/022A62F0, prev 0/022A6278, -- desc: INSERT off 1, blkref #0: rel 1663/16384/16385 blk 0 FPW ``` ### Understanding pg\_waldump result - rmgr: Heap - Resource manager is Heap rmgr with ID: 10 (check Resource Managers part) - len (rec/tot): 59/272 - 59 bytes is the rmgr payload - 272 is total with Full-Page writes image - tx: 494 - Transaction ID that produced this record - lsn: 0/022A62F0 - this record's position in the WAL stream - prev: 0/022A6278 - LSN of the previous record (forms a linked list) - blkref #0: ... FPW - block reference, FPW present (first write after checkpoint) * * * ## Conclusion In this module explored how WAL (Write-Ahead Logging) works in PostgreSQL in detail, starting from: 1. Why WAL exists: the write-ahead rule and the durability guarantee it provides 2. How WAL is physically laid out on disk: segments, pages, and the LSN addressing system 3. The exact anatomy of a WAL record: `XLogRecord`, resource managers, and full-page writes 4. What happens byte-by-byte when you `COMMIT` : from `XLogInsert()` to `fsync()` to the client success response 5. How checkpoints bound recovery time and what `pg_control` stores as the source of truth 6. The crash recovery sequence : reading `redo_lsn`, replaying records, and the `pd_lsn` idempotency guard 7. How WAL powers both physical streaming replication and logical decoding 8. And many more * * * ## **What's Next → Module 5:** Query pipeline (Parser → Planner → Executor) You now understand how every write is made durable and how PostgreSQL survives crashes. Module 5 goes into the query execution layer. We'll cover how the parser turns raw SQL text into a parse tree, how the rewriter expands views and applies rules, how the planner generates candidate execution plans and picks the cheapest one using cost estimates and table statistics, and how the executor drives the chosen plan node by node to produce your result rows.