# Chapter 5: Durability & Transactions
## 5.0 - Write-Ahead Log (Safety Without Sweating)
<img src="assets/chap_4_safety.png" width="250" style="float: left; margin: 0 20px 20px 0;" />
Postgres is built on a simple, uncompromising trust: once a transaction is confirmed, the data must survive any system failure. This reliability is the engine's primary currency. If the system loses power or the operating system crashes, the promises made to the user must remain etched in reality.
### What You'll Learn
- Why direct page updates are an architectural catastrophe (Torn Writes, Random I/O, Atomicity failures)
- How the **Write-Ahead Log (WAL)** provides durability through sequential, append-only writes
- How **Transactions** and the WAL together guarantee **Atomicity** — all-or-nothing commit semantics
- How Postgres recovers from a crash by replaying WAL records from the last Checkpoint
To understand how Postgres maintains this trust, we must first look at the most intuitive—and most flawed—way to persist data.
### The Naive Model: Direct Page Updates
In a naive world, every time you update a **Tuple** (a record's tuple), Postgres would immediately find the relevant **8KB [[Manuscript/02 - Physical Storage & MVCC/2.3 - The Page (The Shipping Container)|Page]]** (page) on disk and overwrite it with the new data.
It is an obvious strategy, but in production, it is an architectural catastrophe.
### Why Direct Persistence Fails
There are three fundamental reasons why Postgres cannot update its data files directly for every change:
1. **The Random I/O Speed Trap**: Operating systems and storage hardware are significantly faster at writing one continuous stream of data (**Sequential I/O**) than jumping between isolated locations on disk (**Random I/O**). Erasing and re-writing an entire 8KB page just to change a single 10-byte tuple is like repainting an entire warehouse because of a smudge on a single crate.
2. **The Torn Write Risk**: If the system loses power mid-write, an 8KB page may only be partially updated. The result is a **Torn Write**, where half the page represents the old state and half represents the new. Without a backup, this results in permanent physical corruption.
3. **The Atomic Failure**: Complex operations (like money transfers) often span multiple rows in different pages. If the system crashes after the first page is written but before the second, the database is left in an impossible, inconsistent state.
This fundamental binary — the speed of the sequential log versus the permanence of the random heap — is the most important trade-off in database architecture.
> [!NOTE] Durable WAL Lets Data Pages Wait
> **Concept**: WAL is the durable recovery record; heap and index files are the materialized database state used for normal access.
> **Payoff**: A normal durable commit does not need to flush every changed heap and index page. PostgreSQL first makes the required WAL records durable. After a crash, REDO can reconstruct committed changes that had not yet reached the data files.
### The Solution: The Write-Ahead Log (WAL)
PostgreSQL solves this with one ordering rule: WAL describing a page change must become durable before the corresponding changed data page. A dirty page can still be written during the transaction—for example when buffer pressure evicts it—but its WAL must cross the durability boundary first.
WAL records describe changes in a form understood by PostgreSQL resource managers. They are often smaller than a page, although the first change to a page after a checkpoint may include a full-page image. Because WAL is predominantly appended, PostgreSQL can durably flush the transaction's recovery story instead of forcing every scattered heap and index page at COMMIT.
If the power dies, Postgres wakes up at its last known **Checkpoint** and reads the durable WAL forward, reapplying records that had not yet reached the main data files. The ordinary 8KB heap and index pages can catch up later.
### The Atomicity Guarantee (Transactions)
The WAL also supports the **[[Manuscript/05 - Durability & Transactions/5.4 - Transactions (The Atomic Seal)|Atomic Seal (Transaction)]]**. Atomicity applies to a transaction, not every action in an entire session: either the transaction commits as a unit or its changes remain invisible. Recovery redoes WAL as needed; PostgreSQL does not perform a conventional UNDO pass over heap changes from uncommitted transactions.
That is the bargain: make the compact recovery story durable now, and let the relation pages catch up later. The next section follows that story through the operating system and storage stack.
---
## 5.1 - WAL & fsync (The Durable Ledger)
<img src="assets/arch_wal_diary.png" width="250" style="float: left; margin: 0 20px 20px 0;" />
### The Write-Ahead Log (WAL)
When a database performs an update, it must ensure that the change is written to disk so that the data survives a sudden power loss or system crash. To achieve this, Postgres writes your update to two separate files on disk: it appends a record to the **Write-Ahead Log (WAL)**, and it eventually writes the updated page back to the table heap. Writing data to *two* physical locations should take twice as long. Yet, performing this dual write is orders of magnitude *faster* than writing the change directly to the table heap alone. Why does writing data twice make the database perform faster?
> [!IMPORTANT] Predict Why Sequential WAL Wins
> What physical characteristic of storage hardware (both SSDs and traditional spinning disks) makes appending to a log file faster than updating a table file? Pause and formulate a guess.
You might expect that Postgres simply delays writing the data to the disk, keeping it in memory and acknowledging the write immediately. But doing this would violate the durability promise of ACID transactions—if the server lost power, the unwritten memory would vanish, and the database would be corrupted.
Instead, Postgres resolves this by leveraging the huge performance gap between **Sequential I/O** and **Random I/O**.
Table and index files are divided into pages, normally 8KB. Updating a row dirties its heap page and can dirty index and visibility-related pages too. Forcing every scattered dirty page through the operating system and storage stack at each commit would make the commit path depend on many unrelated writes.
Postgres sidesteps this with the Write-Ahead Log (WAL) (think of it as a physical **Pocket Diary**):
1. **The WAL Record**: When a transaction changes WAL-logged state, PostgreSQL produces an `XLogRecord` interpreted by a resource manager. It can contain a compact change record and, when required, a full-page image.
2. **Mostly sequential append**: It appends the record to PostgreSQL's logical WAL byte stream, stored in fixed-size segment files. That access pattern is generally friendlier to storage and batching than forcing many scattered relation pages, although SSDs have no disk head and WAL still crosses files, caches, controllers, and synchronization barriers.
3. **The Commit Flush**: Under normal durable settings, `COMMIT` waits until its WAL is flushed through the configured synchronization method. The exact system call and which backend performs it depend on `wal_sync_method`, concurrency, and group commit.
4. **Deferred relation-page write**: Dirty table and index pages can be written before or after commit by backends, the background writer, or the checkpointer. Commit durability does not require each of those relation pages to be flushed first.
By writing WAL first, PostgreSQL avoids forcing scattered relation pages at each commit. Commit latency depends on storage, configuration, concurrency, and WAL volume—it is not guaranteed to be measured in microseconds. If the database crashes, recovery replays the WAL records required to bring data files forward to a consistent state.
Every change is encapsulated in an **`XLogRecord`**—a binary header followed by a physical delta.
### Anatomy of an `XLogRecord`
```text
┌───────────────────────────────────────────────────────────┐
│ XLogRecord │
│ │
│ xl_tot_len │ xl_xid │ xl_prev │ xl_rmid │
├──────────────┴─────────────┴─────────────┴─────────────────┤
│ PAYLOAD DATA │
└───────────────────────────────────────────────────────────┘
```
- **`xl_tot_len`**: Total record size.
- **`xl_xid`**: Transaction ID.
- **`xl_prev`**: Pointer to the previous record's LSN (a contiguous chain).
- **`xl_rmid`**: The **Resource Manager ID** (Heap, B-Tree, etc.) that interprets the payload.
- **Payload**: The raw binary "delta."
### The Log Sequence Number (`LSN`)
Every WAL record is identified by a **Log Sequence Number (LSN)**—a 64-bit coordinate representing its byte-offset in the database's log history. The LSN is a monotonically increasing value that serves as the engine's primary timeline.
```sql
SELECT pg_current_wal_lsn(); -- 0/16A5E88
INSERT INTO ingredients (name, category, base_cost_per_kg) VALUES ('Peanut', 'Nut', 15.00);
SELECT pg_current_wal_lsn(); -- 0/16A5FA0 (It moved!)
```
**STOP.**
Let's inspect a real WAL record on disk using `pg_waldump`.
In our terminal, we locate the WAL directory (`pg_wal`) and run the tool targeting our current LSN coordinate:
```bash
pg_waldump --start=0/16A5E88 --end=0/16A5FA0 --path=pg_wal
```
Look:
```text
rmgr: Heap len: 73, tx: 851, lsn: 0/16A5E88, prev 0/16A5E50, desc: INSERT off 5 flags 0x00
```
- **`rmgr: Heap`** = The Heap resource manager is parsing this record.
- **`len: 73`** = This record is exactly 73 bytes of binary data.
- **`tx: 851`** = Transaction 851 wrote this insert.
- **`desc: INSERT off 5`** = We inserted a tuple at page offset 5.
That's it. You can now read raw binary WAL records.
### Durability and the `fsync()` Syscall
Under the normal durable configuration, a transaction is acknowledged only after its commit record and preceding WAL are flushed to durable storage. PostgreSQL uses the synchronization method selected by `wal_sync_method`—commonly an `fdatasync()` or `fsync()`-style operation—and relies on the operating system and storage stack to honor that request.
> [!TIP]
> **Living Dangerously**: If you are willing to risk a fraction of a second of data for a massive throughput boost, you can perform an **Asynchronous Commit** by setting `synchronous_commit = off`. See **[[Manuscript/05 - Durability & Transactions/5.1.1 - Commit Tuning (The Loose Handshake)|5.1.1 Asynchronous Commit]]** for the technical details.
> [!TIP]
> **Group Commit**: To minimize the cost of `fsync()`, Postgres uses **Group Commit**. The WAL writer can aggregate multiple concurrent transactions into a single flush operation. You can tune this behavior using **`commit_delay`** and **`commit_siblings`**.
### ⚠️ Pathology: The WAL Commit Tail (Why In-Memory Speed Lies)
In an OLTP application, a single `UPDATE` statement modifying an indexed row in memory typically takes less than **0.8 milliseconds** of CPU execution time. However, application monitoring might report that the transaction takes **30 milliseconds**.
Where did the remaining 29 milliseconds go? They vanished into the **WAL Commit Tail**.
Below is a measured, nanosecond-precision trace capturing a single-row OLTP update:
Notice the stark contrast in the execution profile:
1. **In-Memory Compute (0.8ms, `CPU`)**: Parsing, index traversal, and modifying the tuple in `shared_buffers` is blazing fast.
2. **WAL Ring Buffer Append (0.6ms, `LWLock:WALWriteLock`)**: Acquiring the lightweight lock to copy the binary `XLogRecord` into the in-memory WAL buffer.
3. **The WAL flush stall (27.6ms, `IO:WALSync`)**: In this captured fixture, `COMMIT` waits while the configured WAL synchronization path asks the storage stack to make the WAL durable.
In this one measured capture, `IO:WALSync` accounts for **94% of the observed latency**. That signature makes the durability path the next thing to investigate for this incident; it is not a universal explanation for OLTP p99 latency.
### Persisting the Heap: Checkpoints and Background Writing
While the WAL ensures durability, the actual data files are updated asynchronously via two background processes:
1. **Shared Buffers (Dirty Pages)**: When a page is modified in memory, it becomes "Dirty."
2. **The Checkpointer**: PostgreSQL begins checkpoints on timeout, WAL-volume pressure, or explicit request. It spreads the required writes across the checkpoint interval and establishes a redo point after the necessary pages are safely synchronized. WAL older than that redo requirement may still be retained for archiving, backups, slots, or standbys.
3. **The Background Writer**: This process scans a limited number of buffers and writes reusable dirty buffers in batches. Backends can still have to write buffers themselves; the background writer does not guarantee an endless supply of clean slots.
```mermaid
graph TD
subgraph "The Durability Boundary (Synchronous)"
A[UPDATE Statement] -->|"1: Append"| B[(WAL Buffer)]
B -->|"2: fsync"| C[WAL File on Disk]
C -.->|"3: Atomic Seal"| A
end
A -->|"4: Dirty"| D[Shared Buffers]
subgraph "Deferred Persistence (Asynchronous)"
D -.->|"5: Flush"| E[(Data Files)]
end
style C fill:#f96,stroke:#333,stroke-width:4px
style E fill:#e3f2fd,stroke:#1565c0
linkStyle 0,1,2 stroke:#c62828,stroke-width:2px;
linkStyle 3,4 stroke:#2e7d32,stroke-width:2px,stroke-dasharray: 5 5;
```
### Full Page Writes (FPW)
There is a danger: a system crash mid-write could leave an 8KB Page half-updated. Because most hardware writes in 512-byte or 4KB chunks, this results in a **Torn Page**—a corrupted block that cannot be recovered by standard means.
With `full_page_writes` enabled, PostgreSQL normally logs a full image of a data page on its first change after a checkpoint. During redo, that image can replace a page whose on-disk write was incomplete before later WAL changes are applied. Full-page-image behavior is also affected by backups, hint-bit/checksum rules, and WAL-compression settings.
> [!IMPORTANT]
> **The Truth Hierarchy**:
> 1. **Durable WAL**: The redo history that protects changes not yet reflected safely in relation files.
> 2. **Shared Buffers**: The current in-memory page state used by PostgreSQL.
> 3. **Data Files**: The durable page state advanced asynchronously under the WAL-before-data rule.
#### The "No-Force, Steal" Storage Policy
To achieve high write performance while maintaining durability, Postgres implements a **No-Force, Steal** buffer management strategy:
- **No-Force (Deferred Writes)**: A normally durable commit does *not* have to force all of its dirty relation pages to disk. It ensures the required WAL position is durable; relation pages may already have been written or may be written later.
- **Steal (Early Eviction)**: If the Shared Buffer Pool is full, the engine can "steal" a page containing uncommitted data, write it to disk to free up buffer space, and reuse it.
If the database crashes after a dirty page containing an uncommitted tuple version reaches storage, PostgreSQL does not need a separate UNDO log to erase that tuple. Recovery redoes WAL as required, transaction status leaves uncommitted versions invisible, and later cleanup can reclaim their space. If a committed dirty page had not reached storage, REDO reconstructs it from WAL.
The whole architecture rests on a single principle: a sequential log entry is as durable as the physical data page, provided the log entry is persisted before the data file is updated.
### 🧪 Observation Lab: Tracking LSN Coordinates
To observe how transaction writes move the database's write position, we will query the current Log Sequence Number (LSN) before and after inserting data.
#### The Task
Query the database's active write position coordinates using the `pg_current_wal_lsn()` function:
```sql
SELECT pg_current_wal_lsn();
```
Output:
```
pg_current_wal_lsn
--------------------
0/6C556D0
```
This returns a hex address representing the logical byte offset from the start of the WAL system (a Log Sequence Number).
Now, execute a small transaction that inserts a single record, and query the LSN again:
```sql
-- Create a temporary table and insert a row
CREATE TEMP TABLE lsn_test (val INT);
INSERT INTO lsn_test VALUES (42);
SELECT pg_current_wal_lsn();
```
Output:
```
pg_current_wal_lsn
--------------------
0/6C5B178
```
#### The LSN Advances by 23,208 Bytes
Examine the two LSN values:
- Before write: `0/6C556D0`
- After write: `0/6C5B178`
The hex address incremented from `6C556D0` to `6C5B178`. If you subtract these hex coordinates:
`0x6C5B178 - 0x6C556D0 = 23,208 bytes`.
The observed WAL position advanced by 23,208 bytes during this particular interval. That interval can include catalog work and activity from other sessions; temporary-table row data is not itself WAL-logged like a permanent table. Treat the delta as an observation of a shared WAL stream, not as the exact WAL cost of one statement.
#### Every Write Gets a Coordinate in History
Every WAL-logged change occupies a position in the WAL stream. LSNs let PostgreSQL order recovery records, describe flush/replay progress, and coordinate replication.
---
## 5.1.1 - Commit Tuning (The Loose Handshake)
<img src="assets/arch_wal_tweak.png" width="250" style="float: left; margin: 0 20px 20px 0;" />
For a locally durable transaction, the commit path must ensure the commit record reaches durable WAL according to `wal_sync_method` and the storage stack. That synchronization is one common source of commit latency; CPU, WAL insertion/locking, synchronous standbys, and workload contention can dominate elsewhere.
For small, frequent write transactions, synchronization can become the bottleneck. Choose a commit strategy from measured latency and an explicit recovery-point objective rather than assuming every write-heavy system is sync-bound.
### 🧪 Trade Commit Latency for a Bounded Loss Window — Lab
**Sustain the Morning Ingest Rush**: "We need to ingest 10,000 supply deliveries per second during the morning rush. The disk is screaming, and we're hitting a wall."
#### The Naive Solution
By default, `synchronous_commit = on`: a commit waits for local WAL durability. If synchronous standbys are configured, `on` also waits for the selected standbys to flush according to that configuration.
```sql
-- Standard, safe transaction
BEGIN;
INSERT INTO supply_deliveries (...) VALUES (...);
COMMIT; -- The engine stalls here, waiting for the fsync() handshake.
```
#### The Fallout
Frequent commits expose storage synchronization latency, although PostgreSQL can group concurrent committers behind one flush. The observed throughput depends on concurrency, device and filesystem behavior, WAL volume, and group commit; measure rather than applying a fixed transactions-per-second ceiling.
#### The Lazy Fix
Enable **Asynchronous Commit**. For high-volume, non-critical data, you can instruct the engine to return success before the WAL record has reached the disk.
```sql
BEGIN;
SET LOCAL synchronous_commit = off; -- "Don't wait for the ledger."
INSERT INTO supply_deliveries (...) VALUES (...);
COMMIT; -- Returns without waiting for local WAL flush.
```
#### Lower Commit Latency
This can reduce commit latency when WAL synchronization is the limiter. PostgreSQL records the commit in WAL buffers and acknowledges without waiting for local durable flush; the WAL writer or another flushing backend makes it durable later. It does not make query execution or WAL generation free.
> [!WARNING]
> **The Data Loss Window**: If the system crashes *between* a COMMIT and the subsequent WAL flush, that data is lost. This trade-off is acceptable for telemetry or logs, but should be avoided for financial or critical transactions.
### The Group Rush: Tuning Group Commit
If you choose to keep safety `on`, you can still optimize performance by helping Postgres aggregate its work. This is managed by two settings:
#### 1. `commit_delay`
This setting can add a configured microsecond delay before a group-commit leader flushes WAL when enough sibling transactions are active. The goal is to let more commits join the same flush; an unnecessary delay directly harms latency.
#### 2. `commit_siblings`
This is a guardrail for `commit_delay`. It specifies the minimum number of concurrent transactions required to trigger the delay. If fewer than `commit_siblings` are active, the engine flushes immediately.
### The Write Buffer: `wal_buffers`
Before WAL records hit the disk, they sit in the **WAL Buffers** (the mental scratchpad).
By default, `wal_buffers = -1` selects roughly 1/32 of **`shared_buffers`**, bounded by documented minimum/maximum rules (the upper bound is normally one WAL segment, commonly 16 MB). The automatic choice is usually reasonable. Use the `wal_buffers_full` counter in `pg_stat_wal` to detect forced WAL-buffer writes; `WALBuffer-Full` is not a PostgreSQL wait-event name.
> [!IMPORTANT]
> **Summary of Commit Levels**:
> Remote levels below affect commit only when `synchronous_standby_names` selects synchronous standbys.
> - **`on`**: The default. Wait for local WAL durability and, when configured, selected synchronous standbys to flush.
> - **`off`**: Do not wait for disk. Best for performance where minor data loss is acceptable.
> - **`local`**: Used in replication (see Chapter 8). Wait for local flush but not replica flush.
> - **`remote_write`**: Wait for selected synchronous standbys to write the commit record to their operating-system filesystems, not necessarily durable storage.
> - **`remote_apply`**: Wait for selected synchronous standbys to replay the commit record so the transaction is visible to queries there.
---
## 5.2 - Crash Recovery (The Recovery Parade)
<img src="assets/arch_recovery_parade.png" width="250" style="float: left; margin: 0 20px 20px 0;" />
A power loss can stop execution with dirty memory, incomplete transactions, and partially written data pages. PostgreSQL restarts with durable control data and WAL that let it reconstruct a consistent state under its documented storage assumptions.
The architectural payoff of the **[[Manuscript/05 - Durability & Transactions/5.1 - WAL & fsync (The Durable Ledger)|Write-Ahead Log]]** is precisely this scenario: every change was recorded sequentially in the WAL *before* it was applied to the heap, so the recovery process is just a matter of replaying the log forward from a known good point. The architectural cost is the bookkeeping required to find that point and to know when it is safe to stop.
### `pg_control`: The Survival Blueprint
PostgreSQL startup reads `PGDATA/global/pg_control`. The physical file is 8192 bytes; its active `ControlFileData` structure is deliberately kept at or below 512 bytes for atomic-write reliability and protected by a CRC. Among many compatibility and recovery fields, it provides:
| Field | What it tells the engine |
| :------------------- | :-------------------------------------------------------------------------------------- |
| `state` | Was the previous shutdown clean (`DB_SHUTDOWNED`) or interrupted (`DB_IN_PRODUCTION`)? |
| `checkPoint` | The location of the latest checkpoint record, whose contents include the REDO start point |
| `minRecoveryPoint` | During archive recovery, the minimum WAL point recovery must reach before consistency can be declared |
The state distinguishes clean shutdown from production, crash recovery, archive recovery, and other transitions. An unclean primary startup enters crash recovery before accepting ordinary connections.
```mermaid
sequenceDiagram
participant PM as Postmaster
participant PC as pg_control
participant WAL as WAL
participant SB as shared_buffers
PM->>PC: read state + checkPoint LSN
alt state != DB_SHUTDOWNED
PM->>WAL: locate checkpoint and replay from REDO pointer
loop each XLogRecord
WAL->>SB: read page if pd_lsn behind record
SB->>SB: apply change, advance pd_lsn
end
PM->>PC: advance recovery/control state
PM->>PM: enter production and accept connections
else clean shutdown
PM->>PM: accept connections
end
```
> [!IMPORTANT]
> **The atomicity design**: PostgreSQL writes an 8192-byte control file, but constrains the active control structure to at most a commonly atomic 512-byte sector and checks it with a CRC. The design reduces and detects torn/corrupt control data; it is not a claim that every storage stack makes all 8192 bytes atomic or that the file cannot be lost.
### The Redo Loop
Recovery locates the checkpoint record referenced by `pg_control`, takes its REDO pointer, and reads WAL forward. Resource-manager REDO routines identify referenced blocks and apply a record when its effects are not already represented. Conceptually, page LSNs often provide the watermark:
1. Identifies the affected page using the record's `RelFileNode` and `BlockNumber`.
2. Reads the page from disk into shared buffers.
3. Compares the page's `pd_lsn` (the LSN of the last WAL record already reflected in this page) against the WAL record's LSN.
4. If `pd_lsn < record.lsn`, applies the change and updates `pd_lsn`. Otherwise, the change was already on disk before the crash, and the record is skipped.
Page LSN comparison is an important idempotence mechanism, while full-page images, record CRCs, resource-manager logic, transaction status, timelines, and recovery targets complete the recovery protocol. REDO is designed so already-applied effects need not be applied twice.
```text
WAL: ... [LSN 0/3A12] [LSN 0/3A48] [LSN 0/3A90] [LSN 0/3B04] ...
^ ^
pg_control.checkPoint end of WAL
\________________ replay ___________/
```
Crash recovery normally proceeds to the available end of WAL. Archive recovery and standbys also obey consistency, timeline, target, and promotion rules; `minRecoveryPoint` is a lower bound that must be reached, not a generic stopping point.
### What Happens to Uncommitted Work
Recovery replays *every* record in the WAL — including changes from transactions that never reached `COMMIT`. That sounds dangerous, but the **[[Manuscript/05 - Durability & Transactions/5.4 - Transactions (The Atomic Seal)|Commit Log (CLOG)]]** is the safety net.
If transaction 47291 has no durable commit record, recovery and transaction-status rules do not make its tuples committed. Their bytes may remain on a page but are not visible as committed rows; later cleanup can reclaim versions that are proven dead.
> [!NOTE]
> **No UNDO log required**: many databases (Oracle, MySQL/InnoDB) maintain a separate UNDO log to roll back uncommitted changes during recovery. Postgres avoids this entirely by leaning on MVCC — uncommitted writes don't need to be rolled back, just left invisible. The trade-off is the bloat that accumulates from aborted transactions.
### Torn Pages and Full Page Writes
There is one scenario the redo loop alone cannot handle: a page that was halfway written when the power died. Half of its 8KB has the new bytes, half has the old, and the page is no longer internally consistent — the line pointers may reference a garbage tuple, the checksum will not match.
With `full_page_writes = on` (the default), the first WAL-logged modification of a page after a checkpoint includes a full-page image (which may be compressed when configured). Because WAL is flushed before the corresponding data-page write, recovery can restore the image without relying on a potentially torn on-disk page, then replay later changes. (See **[[Manuscript/05 - Durability & Transactions/5.1 - WAL & fsync (The Durable Ledger)|5.1 WAL & fsync]]**.)
### What You'll See in the Log
When recovery runs, the server log records the journey:
```text
LOG: database system was interrupted; last known up at 2026-04-27 21:43:18 UTC
LOG: database system was not shut down cleanly; automatic recovery in progress
LOG: redo starts at 0/3A000028
LOG: redo done at 0/3B0479F8 system usage: CPU: user 0.04 s, system 0.01 s, elapsed 0.18 s
LOG: database system is ready to accept connections
```
These illustrative lines show detection, the reported REDO range, and readiness. Recovery duration depends on WAL volume, storage, full-page images, recovery prefetch, configuration, and hardware; frequent checkpoints trade a shorter potential REDO distance for more checkpoint and full-page-image pressure.
The bargain is that required WAL reaches durable storage before corresponding dirty data pages, allowing many data-page writes to happen later. Recovery also depends on valid control data, required WAL continuity, a crash-safe storage configuration, base data files, and correct durability settings; WAL is central, not sufficient in isolation.
---
### 🧪 Manipulation Lab: Container Crash Recovery
To see how Postgres performs crash recovery, we will run a write query, simulate a sudden power failure by killing the database container, restart it, and read the engine's boot logs to observe the WAL recovery phase.
#### The Task
1. Run a database container write query to ensure some write activity has occurred.
2. Force-kill the running Docker container using `docker kill` (which sends `SIGKILL` to bypass a clean database shutdown):
```bash
docker kill elephant_cafe_db
```
3. Restart the container:
```bash
docker compose up -d
```
4. Immediately read the container logs to inspect the engine's boot steps:
```bash
docker logs elephant_cafe_db 2>&1 | grep -E "database system was not properly shut down|redo starts at|redo done at|database system is ready to accept connections"
```
#### Postgres Replays WAL Before Accepting Connections
Notice the specific recovery sequence printed in the server logs:
```text
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 0/6C5C000
LOG: redo done at 0/6C5D230
LOG: database system is ready to accept connections
```
The database system detected that the data files were left in an inconsistent state due to the dirty buffers in memory not being flushed to disk. Instead of panicking, it read the WAL starting at LSN `0/6C5C000`, replayed (redid) all transaction modifications up to LSN `0/6C5D230`, and safely brought the system back to consistency before accepting connections.
#### A Crash Ends; Committed Data Does Not
Durability is not about preventing crashes; it is about meeting a defined recovery promise. With `fsync`-safe storage, the normal synchronous commit boundary, required WAL available, and no acknowledged hardware lies, crash recovery preserves acknowledged committed transactions. Settings such as `synchronous_commit = off`, missing WAL, or unsafe storage deliberately weaken that promise.
---
## 5.3 - Logical Replication (The Relay Chain)
<img src="assets/arch_logical_replication_crier.png" width="250" style="float: left; margin: 0 20px 20px 0;" />
Physical streaming replication sends PostgreSQL's storage-level WAL stream and maintains a standby copy of the cluster. It is designed for physically compatible systems, not selective table delivery. Logical replication decodes WAL into relation-level changes, which is the better fit when you need to publish selected tables or cross a major-version boundary.
**Logical Replication** solves these cases by shipping the **meaning** of each change rather than the physical delta. The architectural payoff is that the source and target can differ in version, in physical layout, even in extension set; the architectural cost is decoder CPU on the publisher and replication slot disk pressure on the source.
### Physical vs. Logical: the architectural difference
| Aspect | Physical Replication | Logical Replication |
| :------------------ | :-------------------------------- | :------------------------------------------- |
| What ships | Storage-level WAL change records, including full-page images when required | Decoded relation-level changes such as inserts, updates, deletes, and truncates |
| Granularity | Entire cluster | Per-table (via publications) |
| Cross-version | Requires compatible physical major/version rules | Supports selected major-version migrations subject to publisher/subscriber compatibility rules |
| Cross-architecture | Requires physical compatibility | Relation-level protocol avoids identical on-disk layout, subject to data-type and extension compatibility |
| Target writability | Read-only standby | Independent primary that may also accept writes |
| Network volume | Workload- and checkpoint-dependent WAL volume | Workload-, replica-identity-, protocol-, and transaction-dependent decoded volume |
### Logical Decoding
The engine produces logical change events through a four-stage pipeline running inside the **WAL Sender** process on the publisher:
1. The Sender reads each `XLogRecord` as it is appended to the WAL.
2. A **Decoding Plugin** (`pgoutput` is the in-tree default) translates the binary delta into a row-shaped event keyed by `(schema, table, action, before, after)`.
3. PostgreSQL can stream large in-progress transactions to capable subscribers. The subscriber applies them inside a transaction, preserving the rule that ordinary queries do not see uncommitted replicated changes.
4. A commit message completes the transaction on the subscriber; abort discards streamed work. Whether in-progress changes are streamed and whether parallel apply is used depends on protocol version and subscription settings.
> [!NOTE]
> **`pgoutput` and `wal2json`**: most subscribers use `pgoutput` because it speaks the Postgres-native binary protocol. Heterogeneous targets (Kafka, Debezium, DataDog, etc.) typically use `wal2json` or a custom plugin that converts the same event stream into JSON.
### Publications and Subscriptions
Logical replication is configured declaratively. The publisher declares a **Publication** — a named subset of tables to expose:
```sql
-- On the publisher (the London branch)
CREATE PUBLICATION cafe_menu FOR TABLE dishes, dish_ingredients;
```
The subscriber connects with a **Subscription**, which creates a background **Apply Worker** that consumes the change stream and re-executes each event locally:
```sql
-- On the subscriber (the New York branch)
CREATE SUBSCRIPTION ny_branch_sync
CONNECTION 'host=london-cafe port=5432 user=replicator dbname=elephant_cafe'
PUBLICATION cafe_menu;
```
The subscriber's apply worker maps decoded changes to local relations in transactional order. It uses replication-specific behavior—such as `session_replication_role = replica`—so it should not be modeled as indistinguishable from an ordinary client issuing the same SQL.
### Replication Slots: the durability promise
Without coordination, the publisher would not know which WAL the subscriber still needs. If that WAL disappears from local storage and every usable archive, the consumer loses its place and may need to be resynchronized.
A **Replication Slot** is the consumer's persistent bookmark, exposed through `pg_replication_slots`. It tracks required WAL and, for logical slots, a catalog visibility horizon. With the default unlimited `max_slot_wal_keep_size`, a stalled bookmark can retain WAL without a size cap. A configured cap or idle timeout can instead let PostgreSQL invalidate the slot—but the consumer may then need resynchronization.
```sql
-- Inspect slot state on the publisher
SELECT slot_name, slot_type, active, restart_lsn,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;
```
The retained-WAL distance tells you how much the bookmark is holding, not why. Check `wal_status`, `safe_wal_size`, activity, catalog horizons, configured limits, free disk, and subscriber lag before choosing a fix.
> [!CAUTION]
> **Stale slots are a production hazard.** `DROP SUBSCRIPTION` normally tries to remove its publisher slot, but network failures and manually managed or abandoned consumers can leave one behind. With unlimited retention it can fill `pg_wal`; with a cap PostgreSQL may invalidate it. Confirm that the consumer is truly gone before dropping its bookmark, because a live consumer may have to be rebuilt. Monitor slot state, retained WAL, catalog horizons, and free disk.
### The Cost of Decoding
Logical replication is not free CPU on either side:
- **Publisher CPU**: every committed transaction is decoded into row events. For write-heavy workloads with wide rows, decoder CPU can be a significant fraction of total backend time. Visible as `LogicalLauncherMain` and `WalSenderMain` activity in `pg_stat_activity`.
- **Publisher Memory and temporary storage**: `logical_decoding_work_mem` limits memory used by each logical decoding connection before decoded changes may spill. Large or concurrent transactions therefore need capacity planning.
- **Subscriber CPU & I/O**: apply throughput can lag the publisher. PostgreSQL can use parallel apply workers for qualifying in-progress transactions when configured, but apply behavior and bottlenecks depend on transaction shape and settings.
- **Network**: logical traffic may be smaller or larger than physical WAL for a given workload. Wide rows, replica identity, full-page images, batching, and protocol overhead all affect the comparison; cross-region behavior can be limited by bandwidth, latency, or apply throughput.
```sql
-- Subscriber-side wait events worth watching
SELECT pid, wait_event_type, wait_event, state
FROM pg_stat_activity
WHERE backend_type LIKE 'logical replication%';
```
A wait such as `IPC:WalReceiverMain` says the process is waiting for receiver activity; it does not by itself prove that the network is the bottleneck. Likewise, `IO:DataFileWrite` identifies a data-file write wait but not its root cause. Corroborate with lag, network, `pg_stat_io`, storage, and apply-worker evidence before choosing a fix.
### When to Use It
Logical replication is the right tool when you need any of the following:
1. **Low-downtime major-version upgrades**: replicate from a supported older publisher to PostgreSQL 18, validate sequences/schema/extensions and application compatibility, pause or quiesce writes for a controlled cutover, then retire the old path.
2. **Selective replication**: ship only the `orders` and `dishes` tables to a reporting cluster, leave the rest of the schema behind.
3. **Cross-cloud or cross-region warehouse feeds**: the subscriber is often a Kafka/Debezium relay that lands rows in S3, Snowflake, or BigQuery.
4. **Bidirectional designs**: specialized topology and conflict-avoidance rules can exchange changes in more than one direction; built-in logical replication is not a turnkey conflict-free multi-primary system.
It is the wrong tool when you need a strict binary clone (use physical streaming) or when you need synchronous durability (logical apply is asynchronous by design).
---
## 5.3.1 - Replication Slots (The Reserved Parking Space)
<img src="assets/arch_replication_slots.png" width="250" style="float: left; margin: 0 20px 20px 0;" />
Postgres recycles WAL segments aggressively. Once a checkpoint confirms that the data has been propagated to the heap and all standbys have acknowledged receipt, the engine deletes the old WAL files to reclaim disk space. This is correct behavior — but it creates a coordination problem for any consumer that is temporarily offline.
A **Replication Slot** is a persistent bookmark in `pg_replication_slots` that tells the engine: *"Do not recycle any WAL past this point — someone still needs it."* The slot pins WAL retention to the consumer's confirmed progress, measured by an LSN. As long as the slot exists, the publisher refuses to delete WAL segments that the consumer has not yet acknowledged, even if the consumer has been offline for days.
This is the mechanism that makes both physical streaming replication and logical replication (CDC, Debezium, Kafka connectors) reliable across network interruptions. It is also the mechanism that fills your disk when a consumer stops consuming.
### Physical vs. Logical Slots
Postgres supports two slot types, corresponding to the two replication modes:
| Attribute | Physical Slot | Logical Slot |
| :------------------- | :------------------------------------ | :---------------------------------------------- |
| **Created by** | `pg_create_physical_replication_slot` | `CREATE SUBSCRIPTION` or `pg_create_logical_replication_slot` |
| **What it retains** | Raw WAL segments | WAL segments + catalog snapshots for decoding |
| **Consumer** | Streaming replica (Hot Standby) | Logical subscriber, CDC tool, `wal2json` client |
| **Decoding plugin** | None (bytes are forwarded as-is) | Required (`pgoutput`, `wal2json`, etc.) |
| **xmin pinning** | Pins WAL only | Pins WAL **and** catalog `xmin` (prevents vacuum of system catalogs the decoder needs) |
> [!IMPORTANT]
> **Logical slots are heavier than physical slots.** A logical slot must also prevent vacuum from removing catalog rows that the decoding plugin needs to interpret WAL records. This means a stale logical slot can block both WAL recycling *and* system catalog cleanup — a double hazard.
### The Key Columns
The `pg_replication_slots` view is the single operational dashboard for slot health:
```sql
SELECT slot_name,
slot_type,
active,
restart_lsn,
confirmed_flush_lsn,
pg_size_pretty(
pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
) AS retained_wal
FROM pg_replication_slots;
```
| Column | What it means |
| :---------------------- | :--------------------------------------------------------------------------- |
| `slot_name` | The unique identifier for the slot. |
| `slot_type` | `physical` or `logical`. |
| `active` | `true` if a consumer is currently connected. **`false` is the danger signal.** |
| `restart_lsn` | The oldest WAL position the engine must retain. This is the low-water mark. |
| `confirmed_flush_lsn` | (Logical only) The position the consumer has confirmed processing up to. |
| `retained_wal` | Computed: the distance between `restart_lsn` and the current WAL position. This number must not grow unbounded. |
### The Slot Lifecycle
A healthy slot follows a predictable lifecycle:
1. **Creation**: a `CREATE SUBSCRIPTION` or manual `pg_create_*_replication_slot()` call registers the slot in shared memory and persists it to `pg_replslot/`.
2. **Active streaming**: the consumer connects, the slot transitions to `active = true`, and `restart_lsn` advances as the consumer acknowledges progress.
3. **Temporary disconnect**: the consumer goes offline. The slot transitions to `active = false`. WAL segments accumulate. This is normal — the slot is doing its job.
4. **Reconnection**: the consumer reconnects, replays the retained WAL, and `restart_lsn` catches up. The engine recycles the old segments.
5. **Removal**: when the consumer is permanently decommissioned, the slot is explicitly dropped via `SELECT pg_drop_replication_slot('slot_name')` or `DROP SUBSCRIPTION`.
The failure mode is when step 4 never happens.
### 🧪 Diagnose WAL Retention from an Orphaned Slot — Lab
**Stop the Abandoned Slot from Filling Disk**: "We set up a Debezium connector to stream `orders` changes to Kafka. The connector crashed last Thursday and nobody noticed. Now the database disk is 94% full and growing."
#### The Investigation
```sql
-- Step 1: Check slot health
SELECT slot_name, active, slot_type,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;
```
```text
slot_name | active | slot_type | retained_wal
--------------------+--------+-----------+--------------
debezium_orders | f | logical | 47 GB
standby_replica_1 | t | physical | 128 kB
```
#### The Diagnosis
The `debezium_orders` slot has been inactive (`active = f`) since the connector crashed. Postgres has dutifully retained **47 GB** of WAL because the slot promises that someone will eventually come back for it. The engine is not wrong — it is doing exactly what the slot asked. The problem is that nobody told the engine the consumer is gone.
#### The Immediate Fix
```sql
-- Drop the orphaned slot to release WAL
SELECT pg_drop_replication_slot('debezium_orders');
```
Within seconds, the engine begins recycling the retained WAL segments and disk usage drops.
#### The Structural Fix
Prevent this from happening again with **slot activity monitoring** and, on PG17+, automatic slot invalidation:
```sql
-- PG17+: automatically invalidate slots that fall behind
ALTER SYSTEM SET max_slot_wal_keep_size = '10GB';
SELECT pg_reload_conf();
```
When a slot's retained WAL exceeds `max_slot_wal_keep_size`, the engine **invalidates** the slot — marking it as permanently broken rather than allowing it to consume unbounded disk. The consumer must perform a full re-snapshot to recover.
> [!WARNING]
> **The Cascade of Consequences**: An orphaned slot doesn't just consume disk. As `pg_wal` grows, checkpoint duration increases, recovery time after a crash extends, and eventually the cluster refuses new writes entirely. Slot monitoring is not optional infrastructure — it is a production safety requirement.
### The Monitoring Checklist
Every production cluster with replication slots should monitor these metrics:
1. **`active = false` duration**: alert if any slot is inactive for more than your acceptable recovery window (e.g., 1 hour).
2. **`retained_wal` size**: alert if any slot retains more WAL than your disk headroom allows.
3. **`catalog_xmin` age** (logical slots): alert if `catalog_xmin` age exceeds `autovacuum_freeze_max_age`, as this blocks system catalog vacuum.
```sql
-- The production health query
SELECT slot_name,
slot_type,
active,
age(catalog_xmin) AS catalog_xmin_age,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;
```
> [!TIP]
> **The `max_slot_wal_keep_size` Safety Net**: Starting in PG13, you can set `max_slot_wal_keep_size` to cap the amount of WAL any single slot can retain. In PG17, this was hardened further with automatic slot invalidation. For any cluster running CDC or logical replication, setting this parameter is the single highest-value configuration change you can make.
Replication slots are the engine's way of being lazy about cleanup — it refuses to delete WAL because someone *might* still need it. That laziness is a feature when the consumer is healthy and a production incident when it isn't.
---
## 5.4 - Transactions (The Atomic Seal)
<img src="assets/arch_atomic_seal.png" width="250" style="float: left; margin: 0 20px 20px 0;" />
A power surge or logic error could interrupt a process halfway through, leaving data in an inconsistent state. To prevent this, Postgres uses **Transactions**—a logical abstraction that ensures a set of actions is an indivisible "Unit of Work."
This is the foundation of **Atomicity**: every action in the set succeeds, or the entire set is discarded.
> [!NOTE] Transactions Coordinate Visibility, Not Deferred Execution
> **Concept**: Transactions do not group queries to execute them together at commit time. They isolate execution.
> **Payoff**: The database executes statements as they arrive and creates tuple versions in shared buffers; dirty pages may reach storage before the transaction ends. A transaction coordinates atomicity, durability, locks, and visibility. `ROLLBACK` does not have to erase every heap version immediately: aborted transaction status keeps those versions invisible until cleanup reclaims them.
### 🧪 Roll Back the Failed Banquet — Lab
**Keep the Order and Its Items Atomic**: "Babu the Elephant wants to order a 'Peanut Extravaganza' and 100 sides of Hay. We need to record the order and the items together. If the items fail, we don't want a 'ghost' order sitting in the system."
#### The Naive Solution
Sending separate commands without a transaction block.
```sql
-- Step 1: Create the order (ID is generated automatically)
INSERT INTO orders (animal_id, order_time, status)
VALUES (1, NOW(), 'Pending');
-- Step 2: Add the items using the last generated ID in the session
INSERT INTO order_items (order_id, dish_id, quantity)
VALUES (currval('orders_id_seq'), 1, -5); -- ERROR: violates CHECK constraint (quantity must be > 0)
```
#### The Fallout
If Step 2 fails, Step 1 has already "happened." You now have an `order` record in the database but no corresponding items. The waiter thinks an order is coming, the kitchen sees nothing, and Babu is left hungry. Your data is **inconsistent**.
#### The Lazy Fix
Wrap the operations in a transaction block using **`BEGIN`** and **`COMMIT`**.
```sql
BEGIN;
INSERT INTO orders (animal_id, order_time, status)
VALUES (1, NOW(), 'Pending');
-- This will fail and trigger a ROLLBACK
INSERT INTO order_items (order_id, dish_id, quantity)
VALUES (currval('orders_id_seq'), 1, -5);
COMMIT;
```
#### No Ghost Order Survives
Postgres detects the failure in the second statement. Because the commands are in the same transaction, the engine performs a **ROLLBACK**. It updates the CLOG status for the active transaction to `10` (Aborted). Even though the first insert succeeded in memory, the engine treats those bytes as invisible. The transaction is fully discarded.
---
While the **WAL** records physical changes, the **Commit Log (CLOG)** records the logical status of every transaction. Stored in the `pg_xact` directory, the CLOG allocates exactly **2 bits** for every Transaction ID (XID):
* **`00`**: In-Progress
* **`01`**: Committed
* **`10`**: Aborted
* **`11`**: Sub-committed
> [!IMPORTANT]
> **The Atomicity Checkpoint**: If you remember one thing about transactions, let it be this: **COMMIT does not visit and finalize every tuple you changed.** A commit record and transaction-status transition make the transaction's tuple versions logically committed as one unit. Each observer's snapshot still decides whether that committed transaction is visible.
The engine does not finalize individual tuples during `COMMIT`. PostgreSQL records commit in WAL for durability and marks the top-level XID committed in `pg_xact`; visibility checks combine transaction status, tuple metadata, hint bits, and the reader's snapshot. A transaction that began earlier under Repeatable Read can therefore continue not to see a transaction that has already committed.
### The WAL Seal: Making it Real
The flip in the CLOG is the logical finality, but the **WAL Commit Record** (`XLOG_XACT_COMMIT`) is the physical authority. Before Postgres updates the CLOG, it must first write this commit record to the **[[Manuscript/05 - Durability & Transactions/5.1 - WAL & fsync (The Durable Ledger)|Write-Ahead Log]]** and ensure it is physically `fsync`'d to disk.
Once that single record is on disk, the transaction is durable. If the power fails before the CLOG is updated, Postgres will find the commit record in the WAL during recovery and finish the job.
Uncommitted tuple versions can reach data files before a transaction completes. This is safe because the write-ahead rule protects the physical page change and MVCC transaction status leaves an aborted or still-in-progress version invisible. PostgreSQL does not need to revert that heap version during crash recovery; vacuum or pruning can reclaim it later.
Checking the CLOG for every tuple in a large table scan would be a performance bottleneck. To avoid this, Postgres uses a specialized caching mechanism at the tuple level: **Hint Bits** (`HEAPTUPLE_HINT_BITS`).
The first time a process visits a tuple after its parent transaction finishes, it looks up the status in the CLOG and stamps the tuple header with a hint bit: `COMMITTED` or `ABORTED`. Subsequent visitors read the status directly from the tuple header, bypassing the CLOG lookup.
> [!NOTE]
> **The Lock Footprint**: this is why even a `SELECT` query can occasionally trigger a write to disk. If the engine needs to set a hint bit on a previously clean page, that page becomes Dirty, requiring an eventual flush.
The memorable part of the atomic seal is that PostgreSQL does not rewrite every affected row at commit. The durable WAL commit record, transaction-status machinery, snapshots, tuple headers, and hint bits cooperate so an arbitrarily large set of changes acquires one committed meaning without a tuple-by-tuple finalization pass.
---
## 5.5 - Isolation (The Looking Glass Windows)
<img src="assets/arch_isolation_windows.png" width="250" style="float: left; margin: 0 20px 20px 0;" />
Postgres allows dozens of transactions to execute concurrently on the same tables. The engine must decide, for every query, which modifications from concurrent transactions are visible.
This decision is governed by the **Isolation Level**, which is implemented through a structure called a **Snapshot**. Isolation levels provide the architectural trade-off between concurrency and consistency. The same table, read by two transactions simultaneously, can return different consistent results without compromising data integrity.
### The Anatomy of a Snapshot
A snapshot defines a visibility horizon without copying table pages. The simplified model has two boundaries plus a variable set of in-progress transaction IDs:
* **`xmin`**: The lowest XID that was still active when the snapshot was taken. All transactions older than `xmin` are guaranteed to be committed or aborted.
* **`xmax`**: The next XID to be assigned. All transactions with XIDs greater than or equal to `xmax` are invisible to this snapshot.
* **`xip_list`**: The list of XIDs that were active (in-progress) at the moment the snapshot was taken.
PostgreSQL combines this metadata with a tuple's creating and deleting transaction IDs, their transaction status, command ordering, and tuple hint bits. The important scaling property is that snapshot state follows concurrent transaction activity rather than database size; no fixed latency is guaranteed.
#### 1. Read Committed (The Default Level)
This is the default isolation level. In this mode, Postgres takes a **new snapshot for every query** within a transaction.
### 🧪 Watch Read Committed Take a New Snapshot — Lab
**Observe a Concurrent Saffron Price Change**: "Two waiters are looking at the price of 'Saffron'. We need to see what happens if one updates it while the other is still looking."
#### The Setup (Two Sessions)
Open two terminal windows (`psql`).
**Session A (Manager)**:
```sql
BEGIN;
-- Step 1: Check the base cost
SELECT base_cost_per_kg FROM ingredients WHERE name = 'Saffron';
-- Returns 50.00
```
**Session B (Supplier)**:
```sql
BEGIN;
-- Step 2: Update the base cost
UPDATE ingredients SET base_cost_per_kg = 99.00 WHERE name = 'Saffron';
COMMIT;
```
#### The Fallout
Go back to **Session A** and run the query again:
```sql
SELECT base_cost_per_kg FROM ingredients WHERE name = 'Saffron';
-- Returns 99.00!
```
In **Read Committed** mode, Session A saw the supplier's change mid-transaction because it took a fresh snapshot for the second query. This phenomenon is known as a **Non-repeatable Read**.
---
### 🧪 Hold a Stable Audit Snapshot — Lab
**Keep the Audit's Prices Frozen**: "Ensure that once a manager starts a stock audit, the prices they see remain frozen in time, even if a supplier updates them."
#### The Lazy Fix
Use the **Repeatable Read** isolation level.
**Session A (Manager)**:
```sql
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT base_cost_per_kg FROM ingredients WHERE name = 'Saffron';
-- Returns 50.00
```
**Session B (Supplier)**:
```sql
UPDATE ingredients SET base_cost_per_kg = 150.00 WHERE name = 'Saffron';
```
#### The Audit Keeps Its Original Price
Go back to **Session A** and check again:
```sql
SELECT base_cost_per_kg FROM ingredients WHERE name = 'Saffron';
-- Returns 50.00
```
Session A is now using a **Consistent Snapshot**. It uses the same snapshot from the start of the transaction, ignoring any modifications committed by concurrent transactions.
#### The Conflict
Now try to update the base cost in **Session A**:
```sql
UPDATE ingredients SET base_cost_per_kg = 125.00 WHERE name = 'Saffron';
-- ERROR: could not serialize access due to concurrent update
```
Postgres protects the integrity of the data. Since the record was changed by another session after Session A's snapshot was taken, the engine refuses to let Session A overwrite "invisible" history. It forces a rollback.
---
### 🧪 Preserve the Last Active Dish — Lab
**Prevent Two Managers from Emptying the Menu**: "At least one active dish must always be kept on the menu. We currently have two active dishes. If two managers try to deactivate different dishes at the same time, we must not end up with zero active dishes."
#### The Naive Solution (Repeatable Read)
In Repeatable Read, both managers would see `count = 2`. Both would update their respective dish's `is_active` to `false` (one deactivates 'Dish A', the other deactivates 'Dish B'). Since they are updating *different* rows, there is no row-level lock conflict. Both updates would succeed, deactivating both dishes and leaving zero active options on the menu. This is **Write Skew**.
#### The Lazy Fix
Use **Serializable**.
```sql
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM dishes WHERE is_active; -- Returns 2
UPDATE dishes SET is_active = false WHERE name = 'Dish A';
COMMIT;
```
#### One Transaction Must Retry
If another manager does the same for 'Dish B' at the same time, Postgres tracks the **Predicate Dependency**. The engine realizes that the second transaction's logic was based on data that the first transaction modified. Postgres throws a **Serialization Error** on the second commit to prevent the anomaly.
> [!NOTE]
> **Snapshot Persistence and Vacuum**: Long-running transactions in `REPEATABLE READ` or `SERIALIZABLE` mode prevent `VACUUM` from cleaning up old tuple versions. Because the snapshot must remain consistent, the engine cannot reclaim space until the transaction closes and the "xmin horizon" can advance.
---
### Choosing Your Window
You can tell the engine exactly which window you want for your next transaction:
```sql
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
```
Stricter isolation levels require more system resources for state tracking. They also increase the likelihood of serialization failures, which require the application to retry the transaction.
---
## 5.6 - Transaction ID Wraparound (The Infinite Calendar)
<img src="assets/arch_xid_wraparound.png" width="250" style="float: left; margin: 0 20px 20px 0;" />
PostgreSQL uses 32-bit normal **Transaction IDs (XIDs)** in tuple headers. The counter wraps through a circular space of roughly 4.2 billion values, so a long-lived cluster must keep aging information safe before old identifiers cross the visibility horizon.
Think of the XID space as an **Infinite Calendar**: a wheel of dates that loops back to January 1 every four billion transactions, where “old” and “new” are defined by modular distance rather than absolute position. Vacuum must mark sufficiently old committed tuple versions as frozen before they travel around the dangerous half of that wheel. PostgreSQL warns and eventually stops assigning new XIDs if that protection falls too far behind.
### The Visibility Math
To decide if transaction A came before or after transaction B, Postgres subtracts the two IDs and casts the result to a **signed 32-bit integer**:
- If the result is **negative**, A is in the past.
- If the result is **positive**, A is in the future.
This means PostgreSQL can distinguish about **2 billion** transactions on either side of a normal XID. An unfrozen tuple left across that boundary could appear to come from the future and become inaccessible. To prevent that catastrophic visibility failure, PostgreSQL forces anti-wraparound maintenance and stops new XID assignment before the boundary is reached.
> [!NOTE]
> **Why not just use 64-bit XIDs in every tuple?** Wider header fields would impose permanent per-row storage and cache costs and require a different on-disk format and transaction-status design. PostgreSQL exposes `xid8`-based values in some APIs, but PostgreSQL 18 heap tuple headers retain 32-bit `xmin`/`xmax` fields and solve long-term visibility through freezing.
### The FREEZE Ritual
To prevent the wheel from biting itself, the engine performs a ritual called **FREEZE**.
When a committed tuple version is old enough to be visible to all current and future normal transactions, **Vacuum** can mark its inserting XID as frozen through a tuple-header flag. PostgreSQL 18 preserves the original `xmin`; physically replacing it with `FrozenTransactionId` is behavior from releases before 9.4 (though upgraded clusters may contain such old tuples). Frozen insertion XIDs are treated as older than every normal XID for visibility purposes. Other header state—such as deletion—still matters, so “frozen” does not mean “cannot ever be deleted.”
The engine tracks freezing progress at three levels:
| Level | Catalog | What it records |
| :------- | :---------------------------- | :----------------------------------------------------------- |
| Cluster | `pg_control` → `oldestXid` | Globally oldest unfrozen XID across all databases. |
| Database | `pg_database.datfrozenxid` | Oldest unfrozen XID anywhere in the database. |
| Table | `pg_class.relfrozenxid` | Oldest unfrozen XID in this relation. Drives autovacuum priority. |
Each `VACUUM (FREEZE)` advances `relfrozenxid` for the table; the database's `datfrozenxid` is the minimum across its tables; the cluster's `oldestXid` is the minimum across databases. The wraparound horizon is measured against `oldestXid`.
#### Monitoring
```sql
-- Per-database distance to the wraparound horizon
SELECT datname,
age(datfrozenxid) AS xid_age,
2_000_000_000 - age(datfrozenxid) AS xids_remaining
FROM pg_database
ORDER BY xid_age DESC;
```
```sql
-- Per-table view: which relations does autovacuum need to chase?
SELECT n.nspname || '.' || c.relname AS relation,
age(c.relfrozenxid) AS xid_age,
pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','m','t')
ORDER BY age(c.relfrozenxid) DESC
LIMIT 10;
```
#### The Threshold Ladder
Postgres reacts at three escalating thresholds, each tunable via GUC:
| Threshold (default) | Engine behavior |
| :---------------------------------------- | :----------------------------------------------------------------------------------------------- |
| `vacuum_freeze_min_age` (50M) | Tuples older than this become eligible for freezing during ordinary autovacuum. |
| `vacuum_freeze_table_age` (150M) | Vacuum uses its aggressive strategy and scans all all-visible-but-not-all-frozen pages, while pages already all-frozen can still be skipped. |
| `autovacuum_freeze_max_age` (200M) | A wraparound-prevention vacuum is triggered, regardless of dead-tuple thresholds. Cannot be disabled per-table. |
| Final safety margin near wraparound | The affected database refuses commands that assign new XIDs when fewer than roughly three million XIDs remain; existing work and new read-only transactions can continue while the cause is removed and ordinary `VACUUM` advances the horizon. |
---
### The Operational Friction: When Safety Stalls
#### Why Wraparound Vacuums Get Stuck
The single most common reason a healthy autovacuum stops making progress on `relfrozenxid` is a **long-running transaction holding an old snapshot**. A transaction's `xmin` pins the freeze horizon: autovacuum will not freeze any tuple newer than the oldest live snapshot. If a session has been sitting idle in a transaction for hours, every table's `relfrozenxid` is stuck at that session's start.
Diagnose with:
```sql
SELECT pid, age(backend_xmin) AS xmin_age,
state, query_start, wait_event, query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xmin_age DESC
LIMIT 5;
```
The fix is rarely "tune autovacuum harder" — it is to find and terminate the transaction that is holding the snapshot. Common offenders are: idle-in-transaction app connections, abandoned `pg_dump` runs, and replication slots whose subscribers have stopped advancing.
#### Recovering From the Panic Zone
> [!CAUTION] Already in the Panic Zone?
> PostgreSQL 18 usually leaves an online escape.
>
> Resolve old prepared transactions, end sessions holding old horizons, and remove only replication slots proven obsolete. Then run an ordinary database-wide `VACUUM` as a superuser so system catalogs are included, targeting the oldest relations first if time is tight.
>
> Do **not** reach first for `VACUUM FULL`, `VACUUM (FREEZE)`, or single-user mode. Ordinary online vacuum is the preferred recovery path. Single-user mode is exceptional, chiefly when unneeded relations must be dropped or truncated.
Wraparound is rare in a well-monitored cluster, but at high transaction velocity it is the ultimate end boss. Keep autovacuum healthy, watch XID and multixact age, and evict transactions or slots that pin ancient history. The calendar keeps turning; WAL, backups, and replication handle the separate problem of surviving failure.
---
---
## 5.7 - Summary: The Crash Boundary
Before this chapter, `COMMIT` may have felt like a promise Postgres simply makes.
Now you know what backs the promise.
The Write-Ahead Log is the durable record of intent. Dirty pages may still be waiting in memory. Indexes and heap pages may not yet be in their final shape. But once the right WAL record is safely flushed, Postgres has enough information to survive the crash and reconstruct the committed truth.
You also gained a sharper distinction:
- **WAL** gives physical durability.
- **Transactions** give logical all-or-nothing meaning.
- **Isolation** defines which concurrent truths a session is allowed to observe.
- **Vacuum and freezing** keep the transaction timeline from turning into a liability.
The achievement is not memorizing WAL mechanics. It is being able to reason about failure without superstition: what survives, what rolls back, what becomes visible, and what must be cleaned up later.
> [!NOTE] The Log Carries the Promise
> **Concept**: Memory is temporary. The log is the promise.
### Sources & Further Reading
- [PostgreSQL 18: Write-Ahead Logging](https://www.postgresql.org/docs/18/wal-intro.html)
- [PostgreSQL 18: WAL Configuration](https://www.postgresql.org/docs/18/runtime-config-wal.html)
- [PostgreSQL 18: Transaction Isolation](https://www.postgresql.org/docs/18/transaction-iso.html)
- [PostgreSQL 18: Routine Vacuuming](https://www.postgresql.org/docs/18/routine-vacuuming.html)
- Source trail: `src/backend/access/transam/` and `src/backend/storage/lmgr/`.
<div style="page-break-after: always;"></div>