Database internals overview: query, concurrency, WAL, pages, and recovery

A database request crosses several contracts: parse/bind, plan, execute, concurrency control, buffer management, logging, storage, and recovery. Different engines combine them differently, so this lesson uses PostgreSQL, MySQL InnoDB, and RocksDB only as documented examples rather than pretending there is one universal implementation.

Write-ahead logging means recovery information is durable before dependent data pages. It does not require each modified page to flush at commit. Checkpoints bound recovery work; crash recovery starts from a checkpoint/redo location, reapplies needed records, and handles incomplete transactions according to the engine protocol.

Модель и предпосылки

• The optimizer selects a plan from estimates; the executor’s actual rows, reads, spills, and locks must be observed.
• MVCC visibility and blocking locks coexist. PostgreSQL Serializable is SSI, not strict two-phase locking.
• A buffer hit avoids a storage read; a dirty page can be written later because WAL/redo preserves crash recovery ordering.

Проверяемые утверждения

• C1. WAL permits data-page writes after the corresponding log record is durable and permits commit before every data page is flushed.
• C2. PostgreSQL crash recovery starts from the redo record identified by the latest checkpoint rather than replaying arbitrary history from the beginning.
• C3. PostgreSQL Serializable is SSI over MVCC and can abort on dangerous read/write dependencies; it is not 2PL.
• C4. PostgreSQL documents table and row lock modes but not automatic row-to-table lock escalation as a generic behavior.
• C5. InnoDB recovery rolls forward redo and rolls back incomplete transactions; RocksDB compaction is a different storage-engine concern with read/write/space trade-offs.

Исполняемые сценарии

Query lifecycle and cardinality error. A query is parsed, planned, executed, and compared with actual runtime evidence.

Buffer hit and miss. The same logical page access may use memory or storage; neither is a fixed I/O count.

WAL before data-page dependency. Commit durability is tied to required WAL flush; dirty data pages can reach storage later.

Checkpoint and crash recovery. Checkpoint metadata bounds redo start; recovery replays needed log and handles incomplete work.

SSI is not two-phase locking. MVCC snapshot reads, SSI dependency tracking, and blocking locks are separate mechanisms.

Ошибки проектирования

• Не называйте background WAL writer единственным процессом, который делает commit durable: foreground backend/group commit могут участвовать в flush path.
• Не считайте checkpoint полным flush всех данных или recovery «с нуля».
• Не приписывайте PostgreSQL automatic lock escalation по аналогии с другим DBMS.
• Не делайте вывод о query cost только из SQL текста; смотрите plan, actual rows, buffers, spills и waits.

Границы гарантии

• PostgreSQL heap+B-tree, InnoDB clustered index и RocksDB LSM имеют разные page/version/recovery layouts.
• Durability зависит от storage and fsync contract; WAL protocol не исправляет устройство, которое ложно подтверждает flush.
• Logical backup, physical backup, replication и crash recovery решают разные классы отказов.

Связанные материалы

Официальные и первичные источники

1. PostgreSQL: Write-Ahead Logging
2. PostgreSQL: WAL Configuration and Recovery Start
3. PostgreSQL: Transaction Isolation and SSI
4. PostgreSQL: Explicit Locking
5. MySQL 8.4: InnoDB Recovery
6. RocksDB: Compaction