Physiological Logging: Physical-to-a-Page, Logical-Within

Introduction

Write-ahead logging (WAL) is the backbone of crash recovery in virtually every modern database system.
The design of log records, specifically what information they capture and how they describe changes, has profound implications for recovery correctness, performance, and complexity.
Three broad strategies exist: physical logging, logical logging, and a hybrid that the database community calls physiological logging.
The term, coined by Gray and Reuter, is a portmanteau of "physical" and "logical," and it describes a scheme that is physical with respect to page identity but logical with respect to the operation performed within that page.

This hybrid approach is the dominant logging strategy in production systems including PostgreSQL, MySQL/InnoDB, SQL Server, and Oracle.
Understanding why requires examining the tradeoffs of the two purer approaches and seeing how physiological logging threads the needle between them.

Pure Physical Logging

In pure physical logging, every log record contains a byte-level before-image and after-image of the modified data.
At the extreme, a log record might capture the entire page before and after modification.
A less extreme variant captures only the changed bytes (a "byte-diff"), together with the page identifier and offset.

Advantages. Recovery is trivially correct.
Redo applies the after-image; undo applies the before-image.
No interpretation of the operation is needed.
Idempotency is straightforward: you can re-apply the same image repeatedly and converge to the same state.

Disadvantages. Log volume is high.
A single logical operation, such as inserting a row into a B-tree, might touch multiple pages (the leaf, possibly an internal node during a split, and a free-space map).
Each page modification produces a separate, potentially large, log record.
Worse, even a one-byte change on a 16 KB page can force you to record substantial context.
The byte-diff variant helps, but internal page reorganizations (compaction, slot reordering) produce large diffs for semantically trivial changes.

Pure Logical Logging

Logical logging records the high-level operation: "INSERT row (id=42, name='Alice') INTO table T." The log contains no reference to specific pages or byte offsets.

Advantages. Extremely compact.
A single log record can describe an operation that touches many pages.
Index maintenance, page splits, free-space bookkeeping are all implicit.

Disadvantages. Recovery is deeply problematic.
Redo of a logical operation requires the database to be in a transaction-consistent state because the operation's code path may traverse indexes, allocate pages, and make complex decisions.
If the system crashed mid-operation, the database is in a structurally inconsistent state and re-executing the logical operation from scratch may not be possible or may produce different physical results.
Logical undo has the same problem.
Concurrency control interactions become far more complex, and achieving idempotent redo is difficult because logical operations are not guaranteed to produce identical physical outcomes on repeated execution.

The Physiological Compromise

Physiological logging combines the strengths of both approaches:

• Physical addressing of the page. Each log record identifies the exact page (by page ID or file offset) that was modified. This eliminates the need to "find" the right page during recovery.
• Logical description within the page. Instead of recording byte-level diffs, the record describes the operation performed on that page in logical terms: "insert key X at page P" or "delete slot 3 from page P."

The key insight is that the unit of recovery is the page.
During redo, the system reads the page from disk, checks whether the log record's change has already been applied (using the page's LSN), and, if not, interprets the logical operation against the current page contents.
Because the log record targets a single, identified page, the operation's scope is bounded and well-defined.

Why This Works

A single page is a small, self-contained structure.
Logical operations within a page (insert a record into a slot array, update a field, delete a slot, and compact free space) are deterministic given the page's current state.
The page either has or has not been modified by this log record, and the page LSN provides a reliable arbitrator.

This eliminates the two core problems of pure logical logging:

1. No need for a structurally consistent database. Redo only needs the single target page, not a consistent multi-page structure.
2. Deterministic replay. Given the same page state, the same logical intra-page operation always produces the same result.

It also eliminates the primary problem of pure physical logging:

1. Tolerance of page reorganization. If a page internally compacts its free space, reorders its slot array, or shifts records around, a physiological log record like "insert record R into page P" remains valid. A byte-level diff would be invalidated by these internal rearrangements.

Walkthrough

Consider a B-tree leaf page insert in a system using physiological logging.
The page has ID 4471, the current page LSN is 1050, and we are inserting key K=37, with value V.

Normal Operation

1. Latch page 4471 in buffer pool (exclusive latch).
2. Determine insertion point within page's sorted slot array.
3. Write WAL log record:
       LSN: 1085
       Type: BTREE_LEAF_INSERT
       PageID: 4471
       PrevLSN: (previous LSN for this transaction)
       Data: key=37, value=V, slot_position=5
4. Perform the insert on the in-memory page:
       - Shift slots 5..N right by one.
       - Write record bytes into page free space.
       - Update slot array, record count, free space offset.
5. Set page LSN = 1085.
6. Unlatch page.

Redo During Recovery

procedure REDO(log_record):
    page = read_page(log_record.page_id)

    if page.LSN >= log_record.LSN:
        // Change already applied; skip (idempotent).
        return

    // Page LSN is less than log record LSN, so apply.
    switch log_record.type:
        case BTREE_LEAF_INSERT:
            apply_insert(page, log_record.key, log_record.value, log_record.slot_position)
            // apply_insert uses the logged slot_position as the authoritative
            // insertion point. The slot_position was determined at the time of
            // the original operation and is recorded in the log record so that
            // redo is deterministic regardless of any intra-page reorganization
            // that may have occurred. The page structure is still interpreted
            // logically (shift slots, write record bytes, update metadata)
            // rather than as a raw byte diff.

    page.LSN = log_record.LSN
    mark_page_dirty()

The critical detail: apply_insert is a logical operation on the page.
It uses the recorded slot_position as the authoritative insertion point (as determined at the time of the original write) but interprets the page structure rather than blindly writing bytes at fixed offsets.
This is safe because the page is self-consistent — either the old version from disk or a version partially updated by earlier redo records, each of which also left the page in a structurally valid state.

Undo During Recovery

For undo, a compensation log record (CLR) is written that also targets the specific page, and the log record contains sufficient information (e.g., the key) to perform the logical inverse operation:

procedure UNDO(log_record):
    page = read_page(log_record.page_id)
    latch_exclusive(page)

    // Perform logical inverse on this page.
    switch log_record.type:
        case BTREE_LEAF_INSERT:
            apply_delete(page, log_record.key)

    // Write CLR so this undo is not repeated if we crash again.
    clr = create_CLR(log_record)
    write_WAL(clr)
    page.LSN = clr.LSN
    mark_page_dirty()
    unlatch(page)

Interaction with ARIES

Physiological logging is a foundational component of the ARIES recovery algorithm.
ARIES mandates WAL with physiological log records, page-level LSN tracking, and a three-phase recovery protocol (analysis, redo, and undo).
Several ARIES design decisions depend directly on the physiological logging model:

• Page LSN comparison for redo. The redo phase scans forward through the log and conditionally applies each record by comparing the log record's LSN with the target page's on-disk LSN. This comparison is only meaningful because each log record addresses exactly one page.
• CLRs are redo-only. Compensation log records written during undo are themselves physiological. They are redone if needed but never undone, preventing infinite undo loops.
• No-force, steal buffer management. Physiological logging enables a no-force (dirty pages need not be flushed to disk at commit) and steal (dirty pages can be written to disk before the transaction commits) buffer management policy. No-force is safe because the WAL log contains sufficient information to redo committed changes. Steal is safe because the WAL log (combined with CLRs) provides sufficient information to undo uncommitted changes that may have been written to disk prematurely.
• Fine-grained locking compatibility. Because the logging is physical-to-a-page, it does not conflict with concurrent modifications to other pages in the same index structure. Logical logging of a full "B-tree insert" would create complex ordering dependencies across pages.

Subtleties and Edge Cases

Page splits. A B-tree page split is a multi-page operation.
Physiological logging handles this by writing multiple log records, one per affected page (the original page, the new sibling, and the parent for the new separator key).
Each record is independently redoable.
In ARIES, page splits and other structural modifications to index trees are treated as structure modification operations (SMOs), which are executed as nested top-level actions.
An SMO acquires its own locks and commits independently, meaning the physical restructuring (the split) persists even if the originating transaction later aborts.
This is correct because a split is a physical reorganization with no semantic effect on the data visible to other transactions; the originating insert or delete will be undone separately on the now-restructured pages.

Page image logging (FPI). After a checkpoint, the first modification to a page often includes a full page image (sometimes called a backup block in PostgreSQL) alongside the physiological record.
This bounds the recovery window: redo for that page never needs to look further back than the FPI.
This is a physical technique layered on top of the physiological scheme.

Consistency of logical operations. The correctness of physiological redo depends on each log record leaving the page in a structurally valid state.
A bug in the logical page-level operation can corrupt the page in a way that subsequent redo records cannot interpret.
This is a meaningful correctness concern in practice.

Key Points

• Physiological logging uses physical page addressing to locate the target of each log record and logical descriptions to express the change within that page.
• This approach avoids the log bloat of pure physical logging and the recovery complexity of pure logical logging.
• Page LSN comparison enables idempotent redo: each record is applied only if the page has not yet reflected that change.
• Multi-page operations (such as B-tree splits) are decomposed into one physiological log record per page, each independently recoverable. Structural modifications such as splits are handled as independently committing nested top-level actions.
• Tolerance of intra-page reorganization (compaction, slot reordering) is a direct benefit of describing changes logically rather than as byte diffs.
• ARIES relies on physiological logging as a foundational assumption, enabling its steal/no-force buffer management and three-phase recovery.
• Full page images (FPIs) complement physiological logging by bounding the redo horizon for each page.

References

C. Mohan, D. Haderle, B. Lindsay, H. Pirahesh, and P. Schwarz. "ARIES: A Transaction Recovery Method Supporting Fine-Granularity Locking and Partial Rollbacks Using Write-Ahead Logging." ACM Transactions on Database Systems, 17(1):94-162, March 1992.

Jim Gray and Andreas Reuter. "Transaction Processing: Concepts and Techniques." Morgan Kaufmann Publishers, 1993.

Raghu Ramakrishnan and Johannes Gehrke. "Database Management Systems." 3rd Edition, McGraw-Hill, 2003.

Goetz Graefe. "A Survey of B-Tree Locking Techniques." ACM Transactions on Database Systems, 35(3):Article 16, July 2010.