<aside> 💡 Generated by BigBrain. Repeated material was merged while distinct trade-offs, caveats, examples, and operational details were retained.

</aside>

06 - Databases, Storage, and Data Modeling

Overview

This reference page consolidates key concepts, mechanisms, and trade-offs in databases, storage, and data modeling. It covers database selection, internals, indexing, partitioning, sharding, storage engines, write-ahead logging (WAL), multiversion concurrency control (MVCC), NoSQL families, schemas, normalization, and data-model trade-offs. The material is organized from foundational principles to design choices and operational concerns.

Database Internals

Write-Ahead Logging (WAL)

WAL ensures durability and crash recovery by recording changes in a sequential log before applying them to the database. Key mechanisms include:

• Durability: Changes are logged before being written to data pages, enabling recovery after crashes.
• Efficiency: Sequential log writes reduce disk I/O compared to random writes.
• Checkpointing: Periodically writes all changes from the log to the database, clearing the log.
• Recovery: Uses the log to redo committed changes and undo incomplete transactions.

Example: PostgreSQL

PostgreSQL uses WAL for in-place updates, maintaining a dirty page table and transaction table for recovery. It employs ARIES-like algorithms with compensation log records (CLRs) for undo operations.

Multiversion Concurrency Control (MVCC)

MVCC provides non-blocking reads and writes by maintaining multiple versions of data:

• Snapshots: Readers access a consistent snapshot of the database.
• Isolation: Writers create new versions, visible only after commit.
• Trade-offs: MVCC avoids contention but requires periodic cleanup (e.g., PostgreSQL's autovacuum).