How WAL Works Internally in postgress
The Write-Ahead Log (WAL) in PostgreSQL is the database's most critical component for ensuring data durability (making sure your data is never lost) and enabling data recovery.
Think of the WAL as a highly detailed, continuous journal of every single change that happens in your database.
How WAL Works Internally 📝
1. The Core Principle: Write First, Then Data
The fundamental rule of WAL is simple: Any change must be recorded in the WAL file first, before it's applied to the actual data files.
Here’s the step-by-step process when you run an UPDATE command:
- Record the Intent: When you run
UPDATE products SET price = 100 WHERE id = 5;, PostgreSQL doesn't change the main data files immediately. Instead, it creates a small, binary record of that change and writes it to the WAL journal file on disk. - Acknowledge and Commit: Once that WAL record is safely on disk, PostgreSQL confirms to you that the transaction is successful (the
COMMIT). At this point, the data is durable, even if the server immediately crashes. - Apply to Data (Eventually): The actual change to the main data files happens later, in the background, which is a slower operation.
2. The Role of Checkpoints
Because the main data files are updated later, the data on the disk is often "behind" the live state. PostgreSQL periodically performs a checkpoint, which is a process where it ensures all WAL changes up to that point have been fully written to the main data files.
Example: Preventing Data Loss (Durability)
Imagine this scenario:
| Time | Action | WAL State | Data File State |
|---|---|---|---|
| 1:00 PM | START: You change a product's name. | Writes the change to the WAL. | Data files are not updated yet. |
| 1:01 PM | COMMIT: The database tells you the transaction is complete. | WAL record is safely on disk. | Data files are still not updated. |
| 1:02 PM | CRASH: The power goes out and the server dies. | WAL file is intact on disk. | Data files are missing the change. |
Recovery Process (When the Server Restarts):
- PostgreSQL starts up and notices the last checkpoint was at 12:55 PM.
- It reads the WAL files starting from 12:55 PM and replays every transaction.
- When it finds the 1:00 PM WAL record, it reapplies the product name change to the main data file.
- The product name change is recovered, and no data is lost.
Without the WAL, the change wouldn't have made it to the data files before the crash, and the committed data would be gone forever.
Key Purposes of WAL
- Durability: The primary goal, as shown above, is to guarantee that committed transactions survive a crash.
- Point-in-Time Recovery (PITR): Since the WAL is a full history of all changes, you can use it to restore a database to any exact second in the past (by restoring a base backup and replaying the WAL).
- Replication: When setting up a Standby server (for High Availability), the primary server sends a copy of the WAL over the network. The Standby then replays the WAL, keeping its data perfectly synchronized with the Primary. This is the foundation of Streaming Replication.