In a previous article, PostgreSQL Write-Ahead Log (WAL): Durability, Performance Tuning, and Recovery Explained, we explored how PostgreSQL ensures data durability and crash recovery through its Write-Ahead Log mechanism. That discussion focused on how every change in the database is first written to WAL before being applied to the data files, the foundation of PostgreSQL’s reliability.
PostgreSQL replication works like a delivery system between two databases, the primary (source) and the replica (destination). To understand how this system works, we need to learn about five key parts:
- WAL (Write-Ahead Log)
- Replication Slot
- Publication
- LSN (Log Sequence Number)
- Replication Lag
These five components work together to keep the replica up to date with the primary database.
WAL – The Change Log
WAL (Write-Ahead Log) is the log of every change made to the database.
Before PostgreSQL writes data to the main table files, it first records the action inside the WAL.
- WAL is stored in files in
$PGDATA/pg_wal/ - Every transaction (INSERT, UPDATE, DELETE) is written there
- WAL ensures data durability and is also used for replication
Instead of copying the full tables, PostgreSQL simply sends WAL entries to replicas.
This makes replication fast and efficient.
Analogy
Think of WAL like a journal or black box in an airplane. Every action that happens in the database is recorded there. Even if something breaks, you can replay the journal (WAL) to rebuild what happened.
LSN – The WAL Position Marker
LSN (Log Sequence Number) is a unique address or marker that tells you where you are in the WAL log.
- Each WAL record has an LSN (like a page number)
- The primary keeps writing new LSNs as new changes happen
- The replica reads WAL entries in order, following the LSNs
Example:
SELECT pg_current_wal_lsn(); -- On primary SELECT pg_last_wal_replay_lsn(); -- On replica
If both LSNs are close, replication is healthy. If they are far apart, the replica is falling behind.
Analogy
Imagine LSN as page numbers in the journal (WAL). The primary keeps writing new pages, and the replica keeps reading them to stay updated. If the replica is still on page 50 while the primary is on page 100, it’s lagging behind.
Replication Slot – The Bookmark
A Replication Slot is a bookmark on the primary that remembers how far each replica has read the WAL.
Without slots, the primary might delete old WAL files too soon, before the replica has finished reading them.
Why it’s important:
- Prevents data loss for slow replicas
- Ensures WAL files stay available until all replicas catch up
- Created automatically for logical replication, or manually for physical replication
Example:
SELECT * FROM pg_replication_slots;
This shows information such as:
- Slot name
- Slot type (physical or logical)
- Restart LSN (the oldest WAL still needed)
- Active status (whether the replica is connected)
Analogy
Think of the replication slot as a bookmark in the WAL journal. Each replica has its own bookmark telling the primary:
“Don’t throw away pages older than the one I last read.”
If the primary deletes old pages too soon, the replica will miss data and replication will break.
Understanding Slot Retention
“Slot retention” means PostgreSQL will keep old WAL files on disk
as long as one or more replication slots still need them.
For example, if you have three slots with different replay positions:
| Slot | Restart LSN |
|---|---|
| slot_A | 0/7000000 |
| slot_B | 0/6800000 |
| slot_C | 0/6500000 |
PostgreSQL must keep all WAL files since 0/6500000, because that’s the earliest point still required by slot_C. Even if slots A and B are already ahead, WAL files cannot be removed yet. That is slot retention, keeping old WAL segments until every slot has caught up.
Do Slots Store Duplicate WAL Files?
No, they don’t. There is only one copy of each WAL file in the pg_wal/ directory. Replication slots store only metadata — information about which part of the WAL each subscriber still needs.
You can think of it like this:
| Concept | Analogy |
|---|---|
| WAL files | A single pile of daily newspapers |
| Replication slot | A reader’s note saying, “I’ve read up to issue #50” |
| Slot retention | The publisher keeps old newspapers until all readers are done |
| Restart LSN | The edition number each reader still needs |
So PostgreSQL doesn’t duplicate WAL files, it simply delays deleting them until all slots are safe.
Why Slot Retention Can Be Dangerous
If a replica (or subscriber) is offline or inactive for too long,
its slot will continue to retain old WAL files indefinitely.
This can cause:
- Rapid growth of the
pg_wal/directory - Disk space exhaustion on the primary
- WAL write failures or replication stop
You can manage this by:
- Dropping inactive slots manually:
SELECT pg_drop_replication_slot('old_slot'); - Or limiting how much WAL is kept for slots:
max_slot_wal_keep_size = 10GB
Physical vs Logical Slots
| Type | Used For | Data Format | Slot Behavior |
|---|---|---|---|
| Physical Slot | Streaming replication | Binary WAL blocks | Keeps WAL until the standby reads it |
| Logical Slot | Logical replication / Change Data Capture | Decoded logical changes | Keeps WAL until all changes are decoded and consumed |
Logical slots can retain WAL even longer,
because decoding processes also depend on older WAL segments.