PostgreSQL WAL Commit Process
1. The Modification (In-Memory)
When a user executes a data-modifying query (like INSERT, UPDATE, or DELETE):
The change is made to the table or index data inside the Shared Buffers (RAM). The page in memory is now marked as “dirty.”
Simultaneously, a record of this exact change is constructed and written sequentially into the WAL Buffers (also in RAM).
2. The COMMIT Command Issued
When the client sends the COMMIT command, PostgreSQL must guarantee that this change will survive a sudden power outage or crash before it can tell the user “Success.”
3. Flushing to Disk (XLogFlush)
To ensure durability without the massive overhead of writing entire data pages to disk immediately, Postgres uses the Write-Ahead Logging protocol:
The internal function
XLogFlush()is called.It identifies the exact position (Log Sequence Number, or LSN) of the commit record in the WAL Buffer.
It issues a synchronous write to flush all WAL buffers up to that LSN out of RAM and into the current 16MB WAL segment file on permanent storage.
An
fsync()system call is issued to ensure the OS cache actually commits the data to physical disk platters or flash memory.
4. Acknowledgment to the Client
Once the operating system confirms that the WAL record is safely written to the physical storage, the transaction status is updated to “committed” in the commit log (CLOG), and PostgreSQL sends a success acknowledgment back to the client application.
Crucial Architectural Concepts
Write-Ahead Rule: The core rule of WAL is that changes to data pages must not be written to permanent database files on disk until the log records describing those changes have been flushed to stable storage. If the server crashes, Postgres reads the WAL from the last checkpoint forward and reapplies the changes (“redoes” them).
Asynchronous Commit Alternative: If you set the configuration parameter
synchronous_commit = off, Postgres will acknowledge the client’sCOMMITbefore the WAL buffer is flushed to disk (relying on the WAL Writer background process to flush it within roughly 3 timeswal_writer_delay). This massively increases write throughput but introduces a risk of losing up to a split-second of recent transactions if the server suddenly loses power.