Build Your Own PostgreSQL
Write-Ahead Logging and the Durability Contract
What You Will Learn
- Understand the commit promise and why the simple version is too slow in a PostgreSQL-like database
- Understand commit is a log position in a PostgreSQL-like database
- Understand commit path in a PostgreSQL-like database
- Understand the alternatives are painful in a PostgreSQL-like database
The commit promise and why the simple version is too slow
Durability sounds simple from the outside. After commit, the data should not vanish. The straightforward way to deliver that is too slow inside a database. Writing every changed data page to its final location before acknowledging commit would scatter random I/O across the data directory and make small transactions expensive. Write-ahead logging offers a better bargain. The database writes a compact, sequential record of changes first, then lets data pages reach disk later.
The write-ahead log, or WAL, is an ordered stream of records. Each record describes enough about a change to redo it during recovery. There are records for tuple inserts, updates, deletes, page changes, transaction commits, aborts, checkpoints, and many structural operations. The exact format can vary, but the central rule is stable. Before a dirty data page containing a change is flushed, the corresponding WAL must already be durable.
Commit is a log position
Commit is the moment the promise tightens. A transaction can modify buffers in memory, but those modifications are not enough on their own. To commit, the system must ensure that the commit record and all WAL needed by the transaction have reached durable storage according to the configured durability policy. Once that happens, the server can report success even if the data pages themselves remain dirty in memory. If a crash follows, recovery can replay the log.
Commit path
The alternatives are painful
Force-at-commit storage pays the durability bill immediately by writing every changed data page before success. That design makes recovery easier because committed data is already in place, but it makes commit latency depend on scattered page writes. At the other extreme, no logging with periodic snapshots can be fast until a crash loses recent commits or leaves pages half-updated. WAL falls between these two designs. It gives sequential durability now and page placement later.
The log has positions, often called log sequence numbers. A page can record the latest WAL position whose change is reflected in that page. When the buffer manager wants to flush the page, it checks whether WAL through that position has been flushed. This creates a precise dependency between page state and log durability. Without it, recovery might see a data page with a change that the log cannot explain, or it might miss a committed change whose page was not written.
WAL records have to be designed with care. Some records can describe logical actions, such as inserting a tuple. Others need lower-level physical detail, especially for page structure changes. Full-page images may be written after checkpoints so recovery can repair a page that was torn by a crash during write. The log is not a list of SQL statements. Replaying SQL would depend on the state of catalogs, functions, snapshots, and the data itself. Recovery needs records that can deterministically restore storage-level effects.
Throughput comes from grouping
Group commit is one of WAL's most useful performance properties. Many backends may commit at around the same time. Rather than each forcing a separate disk flush, the system can flush WAL once through a position that covers several commits. Every transaction whose commit record is included can then be acknowledged. This turns many small synchronous operations into fewer larger ones, which improves throughput while keeping the contract intact.
There are still tradeoffs. Synchronous commit waits for durable WAL before success, which gives stronger crash guarantees at the cost of latency. Asynchronous commit may acknowledge earlier, accepting that the last few transactions can be lost if the server crashes before WAL reaches disk. Replication can extend the commit path further by waiting for standbys to receive or apply WAL. Durability is not a single setting. It is a set of promises with different costs.
WAL also powers more than crash recovery. Replication streams WAL to standbys. Point-in-time recovery replays WAL to a chosen moment. Logical decoding can interpret changes for downstream systems. Backup tools rely on the relationship between base data files and later WAL. Once the database has an ordered change stream, many operational features become possible.
Into transactions
The write-ahead log is the database's record of what was intended and what followed. It lets the system tell users that a transaction is safe before the final pages settle. But a transaction is about more than durability. It is also about grouping changes so they appear atomic, isolated, and reversible. That is the next promise to cover.
This is why WAL sits below so many higher-level features. It does not know business meaning, yet it records the storage consequences that make business meaning survive failure. A commit message to the client depends on that lower promise being kept with precision.
When the user hears "done," the log has already made that word recoverable.