The Life of a Row — PostgreSQL's Write Path, in Order
One UPDATE statement followed second by second, in strict chronological order: new row versions, WAL-first writes, LSNs and segments, CLOG, the single synchronous commit fsync, MVCC visibility, checkpoints, VACUUM and freezing, crash recovery, and isolation levels.
One UPDATE statement, followed second by second from the moment it's issued to the moment PostgreSQL could survive losing power. Nothing out of order, nothing skipped.
To keep every example straight, one naming convention holds for the rest of this page: T1 is whichever transaction created the row long ago; T2 is the UPDATE we're tracing; T3 is a second, concurrent transaction that shows up later to read the row mid-flight. Any transaction that appears only inside an isolation-level example gets its own label (Session A / Session B), since those are self-contained and unrelated to T1–T3.
t0 — What happens when we try to update a row
Picking up right where the read path left off — the row is already sitting in a page inside shared_buffers. Now a client wants to change it:
UPDATE accounts SET balance = 250 WHERE id = 7;
From the application's side, this looks just as simple as a read:
flowchart LR
APP["Application"]:::svc -->|"UPDATE accounts SET balance = 250 WHERE id = 7;"| DB[(Postgres DB)]:::dbBut underneath, PostgreSQL now has several jobs it didn't have during a read:
- Never overwrite the row in place — write a new version instead.
- Record the change somewhere durable, before touching the page.
- Track whether the transaction making the change actually committed.
- Make the change visible to other transactions only once it's actually committed.
- Eventually, lazily, catch the on-disk table file up to what's in memory.
- Eventually, lazily, reclaim the old row version once nobody could possibly need it anymore.
Everything below is that list, worked through in the order it actually happens.
t1 — A new row version is born
This is the idea everything else in this timeline rests on:
An
UPDATEnever modifies bytes in an existing row. It writes a brand-new version and marks the old one as expiring — both sitting in the same page, side by side.
flowchart LR
subgraph PG["Postgres Server"]
direction TB
subgraph RAM["shared_buffers (RAM)"]
direction LR
subgraph PAGE["Heap Page 2"]
direction LR
OLD["OLD version<br/>id=7, balance=100<br/>xmin=T1, xmax=T2"]:::pageBuffer
NEW["NEW version<br/>id=7, balance=250<br/>xmin=T2, xmax=null"]:::pageYellow
end
B1["Index page"]:::pageBuffer
B2["page 0"]:::pageBuffer
B3["page 1"]:::pageBuffer
end
end
PG --> DISK
subgraph DISK["Disk"]
direction LR
D0["Page 0"]:::pageDisk
D1["Page 1"]:::pageDisk
D2["Page 2 OLD version<br/>id=7, balance=100<br/>xmin=T1, xmax=null"]:::pageDisk
D3["Page 3"]:::pageDisk
end
classDef pageYellow fill:#fef3c7,stroke:#fbbf24,color:#111;
classDef pageBuffer fill:#e0f2fe,stroke:#0284c7,color:#111;
classDef pageDisk fill:#f3f4f6,stroke:#6b7280,color:#111;
style PG fill:#a78bfa,fill-opacity:0.12,stroke:#a78bfa,stroke-width:2px,color:#e2e8f0
style DISK fill:#94a3b8,fill-opacity:0.06,stroke:#94a3b8,stroke-width:2px,color:#e2e8f0
style RAM fill:#22d3ee,fill-opacity:0.06,stroke:#22d3ee,stroke-width:2px,color:#e2e8f0
style PAGE fill:transparent,stroke:#22d3ee,stroke-width:1px,color:#e2e8f0Note the disk copy of page 2 still shows the old bytes at this instant — the in-memory page has already been updated, but nothing has been flushed to the table file yet. That gap between memory and disk is normal, and t7 explains exactly how it gets closed.
Every row version carries two hidden columns that make all of this possible:
A transaction that started reading a moment earlier could see a half-changed row, or lose track of what it was reading entirely. Keeping both versions lets every transaction get a consistent answer to "what did this row look like for me?" — that mechanism is MVCC (Multi-Version Concurrency Control), and the rest of this timeline is really just its implementation.
Note what t1 has not done yet: nothing here is durable, and nothing here says whether T2 actually succeeds. Both of those are the next two sections' jobs.
t2 — Before the page changes, the change gets logged
Before PostgreSQL is allowed to keep the modified page in memory, it needs somewhere durable to describe that change first — the Write-Ahead Log (WAL).
WAL is not a list of SQL queries. It's a low-level, sequential record of exactly which bytes changed, on which page — the minimum information needed to redo the change later.
Two properties define it entirely. It lives in files under pg_wal, and it is append-only: records are never edited, reordered, or slotted into the middle. Every new change is written strictly after everything that came before it.
flowchart LR
subgraph WAL["WAL file — append-only"]
direction TB
R1["LSN 100 — UPDATE id=7"]:::pageYellow
R2["LSN 120 — INSERT id=44"]:::pageYellow
R3["LSN 140 — UPDATE id=7"]:::pageYellow
R4["LSN 160 — COMMIT"]:::pageYellow
R1 --> R2 --> R3 --> R4
R4 -.-> NEXT["new records enter here"]:::pageBuffer
end
classDef pageYellow fill:#fef3c7,stroke:#fbbf24,color:#111;
classDef pageBuffer fill:#e0f2fe,stroke:#0284c7,color:#111,stroke-dasharray: 4 3;Every position in that sequence has an address: an LSN (Log Sequence Number) — a byte offset into an ever-growing stream. PostgreSQL doesn't keep that stream in one giant file; it divides it into WAL segments, normally 16 MB each. An LSN is really two numbers glued together: which segment, and how far into that segment.
flowchart LR
subgraph WAL["WAL address space on disk"]
direction LR
S1["Segment 1<br/>16 MB"]:::segment
S2["Segment 2<br/>16 MB"]:::segment
subgraph S3["Segment 3 — 16 MB"]
direction TB
O0["offset 0 MB"]:::position
O5["offset 5 MB"]:::position
O10["offset 10 MB"]:::position
O0 --> O5 --> O10
end
S1 --> S2 --> S3
end
classDef segment fill:#dff3ff,stroke:#159ed0,color:#111;
classDef position fill:#fef3c7,stroke:#fbbf24,color:#111;Written out, a real LSN looks like 0/16B3748 — a 64-bit address shown as two hex halves, higher bits and lower bits, together naming one exact position in the stream:
0 / 16B3748
│ │
│ └── lower 32 bits
└───────── higher 32 bits
Because WAL only ever grows forward, replaying it after a crash is unambiguous: start at a known-good LSN and apply every record after it, in order, exactly once. The physical layout on disk is the chronological order of every change PostgreSQL has ever made.
A brand-new WAL record doesn't land straight in a file, either — it first lands in a small RAM area called wal_buffers, kept separate from the shared_buffers holding the actual data pages. Only when wal_buffers is flushed does it become the durable file under pg_wal.
WAL answers exactly one question — "what database change happened?" It has nothing to say about whether the transaction behind that change actually finished successfully. That's a separate piece of machinery, and it's the last concept we need before we can walk through the UPDATE end to end.
t3 — And a second log tracks what happened to the transaction
The row itself only stores transaction IDs in xmin and xmax — it never stores "committed" or "aborted" directly. Something else has to answer that question, and that something is the Commit Log, or CLOG, stored on disk under pg_xact.
WAL
│
└── "What database change happened?"
UPDATE id=7 → balance=250
CLOG
│
└── "What happened to the transaction?"
T2 → COMMITTED
So when anything — a reader, a recovery process, PostgreSQL itself — needs to know whether a row version is really visible, it's always a two-step lookup:
Heap page
┌─────────────────────────────┐
│ id=7 │
│ balance=250 │
│ xmin = T2 │
│ xmax = — │
└─────────────────────────────┘
│
│ "Who created this version, and did they finish?"
▼
Transaction T2
│
▼
CLOG / pg_xact
│
└── T2 → IN_PROGRESS / COMMITTED / ABORTED
- Read the stamp on the row (
xminorxmax) — this only names a transaction ID. - Look that transaction ID up in CLOG to find out what actually happened to it.
xmin/xmax and CLOG are two independent pieces of state, cached and persisted separately from everything else:
flowchart LR
APP["Client"]:::pageBuffer --> PG
subgraph PG["Postgres server"]
direction TB
subgraph RAMBOX["RAM"]
direction LR
WB["wal_buffers<br/>staged WAL records"]:::pageYellow
SB["shared_buffers<br/>cached data pages"]:::pageBuffer
CC["CLOG cache<br/>transaction status"]:::clog
end
end
subgraph DISK["Disk"]
direction LR
WALF["WAL segment files<br/>pg_wal/..."]:::pageYellow
HEAP["Heap files<br/>base/..."]:::pageDisk
CLOGF["CLOG / pg_xact<br/>XID → COMMITTED / ABORTED"]:::clog
end
WB -->|"1. WAL flush on COMMIT"| WALF
SB -->|"2. dirty page flushed later, at a checkpoint"| HEAP
CC <-->|"transaction status"| CLOGF
classDef pageYellow fill:#fef3c7,stroke:#fbbf24,color:#111;
classDef pageBuffer fill:#e0f2fe,stroke:#0284c7,color:#111;
classDef pageDisk fill:#f3f4f6,stroke:#6b7280,color:#111;
classDef clog fill:#ede9fe,stroke:#8b5cf6,color:#111;Keep this diagram in mind — it's the map the rest of the timeline plays out on. From here on, "log it to WAL" and "check CLOG" both mean exactly this.
t4 — Lock, log, write, stamp, unlock
With all three pieces of state on the table, here's exactly what the backend process does, in order, to carry out T2's UPDATE:
sequenceDiagram
participant App as Backend process
participant CLOG as CLOG cache
participant Page as shared_buffers
participant WAL as wal_buffers
App->>CLOG: 1. Mark T2 as IN_PROGRESS (on its first write)
App->>Page: 2. Lock page for writing
App->>Page: 3. Write the new row version (xmin=T2), expire the old one (xmax=T2)
App->>WAL: 4. Build the WAL record describing this exact change
WAL-->>App: Return LSN
App->>Page: 5. Record that LSN in the page header — pd_lsn = LSN
App->>Page: 6. Release page lockSteps 2 through 6 all happen inside one locked, all-or-nothing section — no one else can see a half-applied version of this update. Step 5 is the hinge the rest of this story turns on: the page remembers, in its own header, the LSN of the last WAL record that touched it. That field — pd_lsn — is what lets recovery later ask "has this specific page already absorbed this WAL record, or not?" without guessing.
Note what "WAL-first" actually means here — it's easy to misread it as "the WAL record must be written before the page bytes change in memory," but that's not it. In-memory, the page is modified first (step 3), and the WAL record describing that modification is built right after (step 4) — both happen back-to-back inside the same lock, before anything is durable. The real WAL-first rule is about the order things hit disk, not RAM: this page is never allowed to be flushed to the heap file until the WAL record describing it has been flushed first. pd_lsn is exactly what makes that rule enforceable — t7 shows the checkpoint logic that checks it.
t5 — Commit: the one synchronous write in the whole path
Everything up to this point has been RAM-only and invisible to anyone else. Committing is where disk finally enters the picture — and only one write in it is synchronous:
sequenceDiagram
participant App as Backend process
participant WAL as wal_buffers
participant Disk as pg_wal (disk)
participant CLOG as CLOG / pg_xact
App->>WAL: Append COMMIT record for T2
App->>Disk: Flush wal_buffers, fsync
Disk-->>App: WAL durable
App->>CLOG: Flip T2 to COMMITTED
App-->>App: Return success to clientNotice what's absent: the heap file — the actual table data on disk — is not touched at commit time. It doesn't need to be. WAL is what protects it, so the real flush to the heap file can happen whenever is convenient, later (t7 covers exactly when).
The client gets its acknowledgment after exactly one synchronous disk operation: the WAL fsync. Everything before it was RAM-only and reversible; everything after it (the CLOG flip, the eventual heap flush) is bookkeeping that WAL replay could always reconstruct on its own.
t6 — Meanwhile, a second transaction looks at the same row
The full path, with a concurrent reader — call it T3 — dropped in right where it matters most: mid-commit.
sequenceDiagram
participant C2 as Client (T2, writer)
participant BE as Backend process
participant WB as wal_buffers
participant SB as shared_buffers
participant WAL as WAL file (disk)
participant CLOG as CLOG (pg_xact)
participant C3 as Client (T3, reader)
C2->>BE: UPDATE accounts SET balance=250
BE->>CLOG: mark T2 as in-progress
BE->>SB: lock page, write new row version (xmin=T2), expire old (xmax=T2)
BE->>WB: build WAL record for the change, assign LSN
BE->>SB: stamp page with LSN (pd_lsn), release lock
C2->>BE: COMMIT
BE->>WB: append commit record
BE->>WAL: flush + fsync up to commit LSN
WAL-->>BE: fsync success
BE->>CLOG: flip T2 to committed
BE-->>C2: success
Note over C3: T3 can read at any point above,<br/>even mid-commit
C3->>BE: SELECT * FROM accounts WHERE id=7
BE->>SB: read page — already cached
BE->>CLOG: check T2's status
alt T2 still in-progress
BE-->>C3: OLD row (balance=100)
else T2 committed
BE-->>C3: NEW row (balance=250)
endThis is t3's two-step lookup, playing out live: T3 reads xmin=T2 off the row, then asks CLOG what happened to T2. The row's bytes never change when T2 commits — only CLOG does.
flowchart LR
ROW["Read xmin/xmax<br/>on the row version"]:::pageYellow --> LOOKUP["Look up that txn<br/>in CLOG"]:::clog --> DECIDE{"Committed, and<br/>before my snapshot?"}
DECIDE -->|yes| VIS["Visible"]:::pageBuffer
DECIDE -->|no| HID["Not visible"]:::pageDisk
classDef pageYellow fill:#fef3c7,stroke:#fbbf24,color:#111;
classDef clog fill:#ede9fe,stroke:#8b5cf6,color:#111;
classDef pageBuffer fill:#e0f2fe,stroke:#0284c7,color:#111;
classDef pageDisk fill:#f3f4f6,stroke:#6b7280,color:#111;Checking CLOG on every single read would be wasteful once a transaction's outcome is permanently decided. The first reader to resolve a row's status caches that result directly on the row as a hint bit, so future readers can skip the CLOG lookup entirely. This is a pure performance optimization — the logical outcome is identical either way, and nothing about the two-step lookup in t3 actually changes.
Even inside an uncommitted transaction, and even under isolation levels where the snapshot is otherwise frozen, a transaction always sees its own uncommitted row versions — xmin matching your own transaction ID skips the CLOG check entirely. This is why a transaction can build on its own earlier statements within the same transaction.
t7 — Much later: the heap file quietly catches up
shared_buffers and the heap file are allowed to drift apart — that's expected, not a bug. A checkpoint is what periodically reconciles them, and it's triggered by time or WAL volume, never by the buffer pool filling up:
A checkpoint is an all-or-nothing sweep: capture the current WAL position as the REDO pointer, sweep every buffer slot in the pool — not FIFO, not LSN-ordered, just a full scan — and flush every dirty page found, then only once the entire sweep completes, write a checkpoint record to WAL referencing that pointer. WAL segments older than the REDO pointer are now safe to trim.
Writing the checkpoint record last is deliberate. If data gets flushed but the checkpoint record itself fails to write, recovery just redoes some already-applied work — harmless, because replay checks each page's own pd_lsn and skips no-ops. But if it were the other way around — checkpoint record written before every page is actually flushed — a crash could permanently lose a change with no WAL left to reconstruct it from. The asymmetry between "safe to redo" and "impossible to undo" is exactly why the order is fixed.
t8 — Eventually: VACUUM reclaims the dead row version
Checkpoints keep the heap file in sync with memory, but they never remove anything — a page with a dead tuple on it gets flushed to disk exactly as it is, dead tuple and all. Getting rid of the old row version from t1 is a separate job entirely: VACUUM.
Recall from t1: the UPDATE never deleted the old row (xmin=T1, xmax=T2) — it just marked it expired and left the bytes sitting on the page. A row version becomes truly dead, and safe to remove, once its expirer's commit (T2) is visible to absolutely everyone — meaning no transaction anywhere still holds a snapshot old enough to need the old version. Postgres tracks this as a single moving watermark, often called the vacuum horizon: the oldest snapshot any currently-running transaction could still be using.
flowchart LR
subgraph BEFORE["Heap Page 2 — before VACUUM"]
direction LR
OLD1["OLD version<br/>xmin=T1, xmax=T2<br/>(dead — no snapshot needs it)"]:::pageDisk
NEW1["NEW version<br/>xmin=T2, xmax=null<br/>(live)"]:::pageYellow
end
BEFORE -->|"VACUUM runs"| AFTER
subgraph AFTER["Heap Page 2 — after VACUUM"]
direction LR
FREE["free space<br/>(line pointer removed,<br/>reusable by future inserts)"]:::pageBuffer
NEW2["NEW version<br/>xmin=T2, xmax=null<br/>(untouched)"]:::pageYellow
end
classDef pageYellow fill:#fef3c7,stroke:#fbbf24,color:#111;
classDef pageBuffer fill:#e0f2fe,stroke:#0284c7,color:#111;
classDef pageDisk fill:#f3f4f6,stroke:#6b7280,color:#111;Plain VACUUM reclaims space inside the table's existing files for future rows — it does not shrink the file or hand space back to the OS. Only VACUUM FULL does that, and it pays for it by rewriting the entire table under an exclusive lock, blocking every reader and writer for as long as it runs. Ordinary VACUUM never blocks concurrent reads or writes.
VACUUM has a second, easy-to-miss job: freezing. Transaction IDs are 32-bit and eventually wrap around — reused for a new transaction after roughly two billion transactions. A row whose xmin is old enough gets stamped with a special sentinel meaning "visible to everyone, permanently" — a frozen row — so its visibility never again depends on looking an XID up in CLOG (t3's two-step lookup) at all. If a table goes unvacuumed for too long, autovacuum escalates into a mandatory, non-skippable freeze pass, and in the worst case PostgreSQL will refuse new transactions entirely rather than risk XID wraparound corrupting visibility.
Without freezing, an XID from billions of transactions ago could eventually be reused by a brand-new transaction — and an old, un-frozen row would suddenly look like it was written by a future transaction, corrupting visibility. Freezing sidesteps the problem entirely by taking a row out of the XID/CLOG system once it's permanently, safely visible.
None of this usually runs by hand — autovacuum is a background process that watches each table's count of dead tuples and launches a VACUUM automatically once it crosses a threshold (autovacuum_vacuum_threshold plus a fraction of the table's row count, autovacuum_vacuum_scale_factor).
t9 — If the lights go out
Every crash scenario reduces to one asymmetry, repeated:
A change may never reach disk before its WAL record is durable — but the reverse is always safe, because replay is idempotent and simply catches the data file up.
Recovery, once you know this, is simple: read pg_control for the last checkpoint's REDO pointer, jump straight there in WAL, and replay forward until the first invalid record — never from the beginning of time, never backward from the newest file.
The whole cycle in one diagram: checkpoint, logging, crash, recovery
Everything from t2, t7, and this section, played out end to end against one running example — three writes, one checkpoint in the middle, then a crash:
sequenceDiagram
participant App as Backend process
participant WB as wal_buffers
participant WAL as WAL files (pg_wal)
participant SB as shared_buffers
participant CKPT as Checkpointer
participant CTRL as pg_control
participant HEAP as Heap files
participant REC as Startup process (recovery)
Note over App,HEAP: 1. Normal operation
App->>WB: WAL record — LSN 100
App->>SB: Modify page<br/>pd_lsn = 100
WB->>WAL: Flush WAL + fsync
Note over CKPT,CTRL: 2. Checkpoint
CKPT->>SB: Scan dirty buffers
CKPT->>HEAP: Flush dirty page<br/>pd_lsn = 100
CKPT->>WAL: Write checkpoint record<br/>REDO pointer = 100
CKPT->>CTRL: Update pg_control<br/>with checkpoint location
Note over App,HEAP: 3. Writes after checkpoint
App->>WB: WAL record — LSN 140
App->>SB: Modify page<br/>pd_lsn = 140
WB->>WAL: Flush WAL + fsync
App->>WB: WAL record — LSN 160
App->>SB: Modify new page<br/>pd_lsn = 160
WB->>WAL: Flush WAL + fsync
Note over App,REC: ⚡ 4. Crash — RAM is lost, disk survives
REC->>CTRL: Read last checkpoint location
CTRL-->>REC: Checkpoint location
REC->>WAL: Read checkpoint record
WAL-->>REC: REDO pointer = 100
Note over REC,WAL: 5. Recovery starts from REDO pointer
REC->>WAL: Read WAL record LSN 100
REC->>HEAP: Check page pd_lsn
Note right of REC: pd_lsn = 100<br/>100 >= 100 → already applied<br/>Skip
REC->>WAL: Read WAL record LSN 140
REC->>HEAP: Check page pd_lsn
Note right of REC: pd_lsn = 100<br/>100 < 140 → replay<br/>set pd_lsn = 140
REC->>WAL: Read WAL record LSN 160
REC->>HEAP: Check target page
Note right of REC: Change not on disk<br/>replay INSERT<br/>set pd_lsn = 160
REC->>WAL: Continue through valid WAL
REC-->>App: Recovery completeA few things this diagram makes concrete:
t10 — Isolation levels: how much of this a reader is allowed to see
Every transaction sits on top of the same shared reality — one set of row versions, one CLOG. Isolation level only controls when a transaction's snapshot of that reality gets frozen. (The examples below use fresh labels, Session A / Session B, since they're independent of the T1–T3 walkthrough above.)
Repeatable Read — the anomaly it prevents:
-- Session A (Repeatable Read) -- Session B
BEGIN;
SELECT balance FROM accounts
WHERE id = 7; -- 100
BEGIN;
UPDATE accounts SET balance = 250
WHERE id = 7;
COMMIT;
SELECT balance FROM accounts
WHERE id = 7; -- still 100
COMMIT;
Under Read Committed instead, that second SELECT would return 250 — each statement re-checks what's currently committed.
Serializable — catching write skew that Repeatable Read misses:
-- Session A (Serializable) -- Session B (Serializable)
BEGIN;
SELECT SUM(balance) FROM accounts
WHERE branch = 'A'; -- 1000
BEGIN;
SELECT SUM(balance) FROM accounts
WHERE branch = 'A'; -- 1000
UPDATE accounts SET balance -= 500
WHERE id = 1;
COMMIT; -- succeeds
UPDATE accounts SET balance -= 500
WHERE id = 2;
COMMIT; -- ERROR 40001: could not
-- serialize access
PostgreSQL detects the dependency cycle and aborts one transaction — it does not retry automatically. The application must catch SQLSTATE 40001 and re-run the transaction from BEGIN. This retry loop is a required part of using Serializable correctly, not an edge case to handle "just in case."
Different isolation levels coexist freely — many transactions at different levels can run concurrently against the same tables at the same time, each applying its own lens over the same shared row versions and CLOG. The one caveat: Serializable's conflict detection only tracks dependencies among other Serializable transactions — mixing levels on the same invariant can quietly defeat the protection.
The manual alternative: explicit locking
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
FOR UPDATE takes a row-level exclusive lock: other transactions trying to UPDATE, DELETE, or SELECT ... FOR UPDATE the same row must wait. Plain SELECTs are never blocked by row-level locks, regardless of type — MVCC readers just look at the last-committed version and move on.
Row-level locks only protect the exact rows you lock. A branch-total write-skew scenario isn't fixed by locking individual account rows if two transactions touch different rows — you'd need to lock something representing the invariant itself, like an advisory lock or a dedicated "branch total" row.
The whole story, once more, in order
1. STAMP
Write the new row version, mark the old one as expiring.
(in shared_buffers, RAM — immediate, unconditional)
│
▼
2. LOG
Describe the change in wal_buffers, get an LSN.
(RAM, before the page is stamped with that LSN)
│
▼
3. COMMIT
fsync wal_buffers → WAL file on disk. Flip CLOG.
(the only synchronous disk write — client gets "success" here)
│
▼
4. CATCH UP (lazy, later)
Checkpoint sweeps shared_buffers, flushes dirty
pages to the heap file, only then records itself.
│
▼
5. CLEAN UP (lazy, even later)
VACUUM removes the old row version once no
snapshot anywhere could still need it.
Every reader, at every isolation level, just answers one question against this shared timeline: as of my snapshot, is this row version's creator committed, and is its expirer not?
Key takeaways
The core mental model: stamp the page → log it to WAL → fsync on commit → let the heap file catch up later.
Frequently Asked Questions
Why does PostgreSQL need both WAL and CLOG?
WAL and CLOG solve different problems. WAL records the database changes needed for durability and crash recovery, while CLOG records the status of the transactions that made those changes. In short, WAL answers “what changed?” and CLOG answers “did the transaction that made the change commit or abort?”
Refer: t2 — Before the page changes, the change gets logged and t3 — And a second log tracks what happened to the transaction.
Does WAL contain the entire row?
Not necessarily. WAL contains low-level records describing database operations in a form PostgreSQL can use during recovery. It is not simply a copy of every complete row after every UPDATE.
Refer: t2 — Before the page changes, the change gets logged.
Why can PostgreSQL modify a page before the WAL is durable?
The write-ahead rule applies to the order in which data reaches durable storage, not to the order of operations in RAM. PostgreSQL can modify a page in shared_buffers and generate its WAL record before either is durable. The critical rule is that the WAL describing a dirty page must reach durable storage before that page itself is flushed to the heap file.
Why does PostgreSQL store an LSN in pd_lsn?
pd_lsn lets PostgreSQL know how far a page has progressed through the WAL stream. During crash recovery, PostgreSQL compares the WAL record’s LSN with the page’s pd_lsn. If the page already contains that change, recovery can skip replaying it.
Refer: t4 — Lock, log, write, stamp, unlock and t9 — If the lights go out.
What happens if PostgreSQL crashes before COMMIT?
If the transaction has not committed and the required commit durability has not occurred, the transaction does not become visible to others. Any WAL that was already written may still be present, but crash recovery will not turn an uncommitted transaction into a committed one.
Refer: t9 — If the lights go out.
What happens if PostgreSQL crashes after COMMIT but before the heap page is written?
The change can still be recovered. The WAL has already been made durable, while the modified heap page may still exist only in shared_buffers. During recovery, PostgreSQL replays the WAL and brings the heap page up to date.
Refer: t9 — If the lights go out.
Why doesn't COMMIT immediately write the modified table page to disk?
Because PostgreSQL does not need to synchronously write every modified heap page at commit time. Once the required WAL is durable, the heap page can safely remain dirty in memory and be flushed later. This reduces random I/O during commits.
Refer: t5 — Commit: the one synchronous write in the whole path and t7 — Much later: the heap file quietly catches up.
Why does a checkpoint not remove old row versions?
A checkpoint and VACUUM have different jobs. A checkpoint makes dirty pages durable in the heap files. It does not decide which row versions are obsolete. VACUUM later identifies obsolete row versions and reclaims their space when no active transaction can still need them.
Refer: t7 — Much later: the heap file quietly catches up and t8 — Eventually: VACUUM reclaims the dead row version.
Why doesn't VACUUM run as part of a checkpoint?
Checkpointing is about making dirty pages durable and establishing a recovery point. VACUUM is about MVCC cleanup, dead tuples, free space, visibility information, and transaction-ID freezing. Keeping these responsibilities separate allows each process to perform its own job independently.
Refer: t7 — Much later: the heap file quietly catches up and t8 — Eventually: VACUUM reclaims the dead row version.
Why can a SELECT see the old row while an UPDATE is happening?
PostgreSQL’s MVCC keeps the old row version available while the UPDATE creates a new version. A reader uses its snapshot together with the row’s transaction IDs and CLOG to determine which version is visible. This allows ordinary reads to continue without waiting for the writer to finish.
Refer: t6 — Meanwhile, a second transaction looks at the same row.
The core mental model: stamp the page → log it to WAL → fsync on commit → let the heap file catch up later.
This completes the write path. Together with the read path, you now have the full lifecycle of a row in PostgreSQL — from a query arriving, to the page being found and cached, to a change being stamped, logged, committed, and eventually made durable on disk.