Every change in PostgreSQL is written twice.
First to WAL.
Then later to the actual table files.
This mechanism is called Write-Ahead Logging (WAL) and it’s one of the most important parts of PostgreSQL’s architecture.
Without WAL, PostgreSQL would not be able to safely recover from crashes, replicate data, or support tools like CDC pipelines and backup systems.
In this Post we’ll break down:
- how PostgreSQL stores data internally
- what WAL is and why it exists
- what WAL files actually contain
- how PostgreSQL recovers from crashes
- why replication, CDC, and backups depend on WAL
How Data Is Stored in PostgreSQL (Quick Glance)
Before understanding WAL, it helps to know how PostgreSQL actually stores data on disk.
When you create a table in PostgreSQL:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT
);
PostgreSQL creates a table file on disk inside its data directory.
But rows are not stored randomly inside that file.
Instead, PostgreSQL organizes table storage into fixed-size pages.
Table
│
▼
Pages (8 KB each)
│
▼
Rows stored inside pages
Each page has a default size of 8 KB.
Inside a page PostgreSQL stores multiple rows (also called tuples).
Example simplified view:
Page (8 KB)
+---------------------+
| Row: (1, Alice) |
| Row: (2, Bob) |
| Row: (3, Charlie) |
| Row: (4, David) |
+---------------------+
When new data is inserted, PostgreSQL finds a page with enough free space and places the row there.
Why PostgreSQL Writes to WAL Before Writing Data
PostgreSQL does not directly write changes to the table files.
Instead it first records the change in WAL (Write-Ahead Log).
The rule PostgreSQL follows is simple:
Write change to WAL
↓
Flush WAL to disk
↓
Apply change to table pages later
This guarantees that if the database crashes, PostgreSQL still knows what changes were supposed to happen.
WAL acts as a durable record of database operations.
The Problem Databases Need to Solve
Consider a simple query:
INSERT INTO users(name) VALUES ('Alice');
For PostgreSQL to execute this, it must:
- modify a page inside the table file
- write the updated page to disk
But what happens if the database crashes mid-write?
For example:
write page to disk
↓
power failure
↓
page partially written
↓
database becomes corrupted
Databases must guarantee durability.
Once a transaction commits, the data should survive crashes.
This is exactly the problem Write-Ahead Logging solves.
What WAL Actually Contains
A common misconception is that WAL stores full rows.
It doesn’t.
Instead, WAL stores instructions describing what changed.
A simplified idea of a WAL record might look like:
page: 123
offset: 45
action: insert
tuple: ('Alice')
In other words:
WAL records describe how to reproduce the change, not the entire dataset.
This makes WAL much smaller and faster to write than copying full data pages.
Where WAL Is Stored
All WAL files are stored in the PostgreSQL directory:
pg_wal/
Inside this directory you’ll see files like:
000000010000000000000001
000000010000000000000002
000000010000000000000003
These files are called WAL segments.
Each segment has a fixed size.
Default size:
16 MB
When a segment fills up, PostgreSQL automatically creates a new one.
segment fills
↓
new WAL segment created
↓
logging continues
This process happens continuously while the database is running.
When Table Pages Are Actually Written
Once WAL is safely flushed to disk, PostgreSQL can update the actual table pages.
These writes happen through background processes such as:
- background writer
- checkpoints
This allows PostgreSQL to batch writes efficiently and reduce disk I/O.
Even if the database crashes before the page write finishes, WAL already contains enough information to recover.
Crash Recovery
When PostgreSQL starts after a crash, it performs WAL replay.
The process looks like this:
database crashes
↓
postgres starts again
↓
read WAL records
↓
replay changes
↓
database becomes consistent
This ensures that committed transactions are not lost.
WAL acts as the source of truth for rebuilding the database state.
Why WAL Is More Than Crash Recovery
Although WAL was originally designed for crash recovery, it powers several important PostgreSQL capabilities today.
Replication
Standby servers replicate data by replaying WAL records from the primary server.
This allows replicas to stay synchronized with the primary database.
Change Data Capture (CDC)
Tools like Debezium read WAL using logical decoding.
Instead of scanning database tables, they stream changes directly from WAL.
This enables architectures like:
Postgres
│
▼
Debezium
│
▼
Kafka
│
▼
Consumers
Applications can react to database changes in near real time.
Backups and Point-in-Time Recovery
Backup tools such as pgBackRest continuously archive WAL segments.
This enables Point-in-Time Recovery (PITR).
For example:
base backup taken
↓
WAL archived continuously
↓
restore database
↓
replay WAL until target timestamp
Without WAL archiving, restoring a database to an exact moment in time would not be possible.
A Simple Mental Model
One way to think about PostgreSQL is:
Tables store current state
WAL stores history of changes
Whenever PostgreSQL needs to recover, replicate, or stream changes, it uses that history.
Final Thoughts
Write-Ahead Logging is one of the core mechanisms that makes PostgreSQL reliable.
It enables:
- crash recovery
- replication
- change data capture
- backup systems
- point-in-time recovery
In many ways, WAL is the foundation that many PostgreSQL features are built on.
Understanding WAL makes it much easier to understand how the rest of the PostgreSQL ecosystem works.
Got it — a short hint works better anyway. Just replace the ending with something like this:
Next Article in This Series
In the next article, we’ll look at how tools like Debezium read PostgreSQL WAL to stream database changes in real time using logical decoding.
Later in the series, we’ll also explore how WAL is used for database backups and point-in-time recovery with tools like pgBackRest.
Top comments (0)