Write-Ahead Logging

Write-ahead logging records recovery information before the corresponding changed data pages are written to durable storage. With synchronous durable commit, the required log is flushed before success is acknowledged; data pages can be written later. Group commit lets several transactions share a flush, while asynchronous settings provide different failure guarantees. WAL’s general role is durability and recovery, not a universal mechanism for snapshot reads. SQLite WAL mode gives a reader a fixed end mark and reads eligible page images from the WAL or database file. PostgreSQL instead uses tuple versions and snapshot visibility rules for MVCC and snapshots; its WAL records support recovery and replication.

SQLite WAL stores changed page images and checkpoints them into the database file. PostgreSQL uses its own recovery records and checkpoint machinery. A checkpoint here is a database-internal operation, unrelated to the stream-processing checkpoint that records a consumer’s position. It is neither a new business commit nor permission to discard every old log segment: readers, replication, and retention requirements can keep data or logs needed — a logical replication slot held by an absent consumer is the usual reason logs accumulate. A WAL file’s size is therefore not a direct measure of uncommitted work.

Positions in the log are what let other things point into it. Recovery needs to know where to resume, and so does anything else reading the log — a replica applying changes, or a capture process feeding a downstream system. Engines therefore expose a monotonically increasing position for each record: PostgreSQL calls it the log sequence number, and other systems use their own name for the same idea. A consumer’s progress is that position, not a timestamp, which is why resuming is exact rather than approximate.

What the log contains is not the same as what a consumer wants, and this catches teams moving from queries to log reading. The log is written in the order changes were made, so records from concurrent transactions are interleaved rather than grouped, and work that was later rolled back is present because the log was written before the outcome was known. A reader that treats every record as a committed fact will therefore see changes that never happened and see one transaction’s changes split across others. Capture tools handle this by reconstructing transaction boundaries, and by default PostgreSQL’s logical decoding hands a transaction to the output plugin only after it commits. That is a property of the decoding, not of the log, and it has an exception: for large transactions, logical decoding can stream changes while the transaction is still in progress and report its commit or abort afterward. A consumer of such a stream must hold the changes until the commit arrives and discard them on an abort; arrival alone does not make a change final.

Batching reduces per-commit overhead by changing the transaction boundary. If batches one and two commit and batch three fails, rolling back the third does not undo the first two. Define restart and duplicate-handling rules accordingly. WAL also does not replace backups or automatically provide a complete business audit. Change data capture must use the engine’s supported stream and restart-position protocol.

References

• SQLite Documentation, Write-Ahead Logging
• PostgreSQL Documentation, Write-Ahead Logging (WAL)
• PostgreSQL Documentation, Streaming of Large Transactions for Logical Decoding
• PostgreSQL 18 — MVCC introduction

See Relational Database Internals for the worked examples.