Day 385: DBMS Architecture and Storage Hierarchy

The core idea: A DBMS is a machine for moving data across a steep latency hierarchy while preserving page-level, transactional, and durability invariants.

Today's "Aha!" Moment

In ../24/16.md, the curriculum closed by arguing that production systems only become trustworthy when their boundaries and handoffs are explicit. A database engine is the same kind of system compressed into one process. When Harbor Point reruns its municipal-bond risk screens after a storm warning, the hard part is not "running SQL." The hard part is moving the right state through parser, planner, executor, buffer manager, WAL, and data files without turning every request into a disk stall or every crash into lost money.

That is the first important shift for this module. A DBMS is not a flat box that somehow stores rows. It is a layered design built around a brutal fact: CPU caches, DRAM, SSDs, and remote backups have radically different latency and durability properties. If the engine treats those tiers as interchangeable, read latency explodes, commits serialize on random writes, and crash recovery becomes guesswork.

The common misconception is that database architecture is mostly about speed tricks. Speed matters, but the architecture exists because the engine must answer a stricter question: which copy of the data is authoritative right now, which copy is merely cached, and which writes are durable enough to survive power loss? Once that question is explicit, the rest of the storage hierarchy starts to make sense.

Why This Matters

Harbor Point's risk database has two competing jobs on the same morning. Traders are appending fresh municipal quote updates, and analysts are issuing selective reads over the most recent market slices. Those requests do not touch storage in the same way. The write path wants sequential, durable logging so commits stay cheap. The read path wants the engine to keep index roots, hot catalog entries, and recent leaf pages in memory so repeated lookups avoid device latency.

If the team sees the database as "just a service with SQL on top," these pressures are easy to misdiagnose. A sudden spike in latency might look like a bad query when the real problem is buffer churn. A slow commit path might look like CPU saturation when the engine is really waiting on WAL fsyncs. A crash that leaves data files looking stale might look like corruption when the committed state is actually preserved in the log and will be replayed during recovery.

Understanding the architecture changes the operator's question from "why is the database slow?" to "which layer is paying the latency bill, and which invariant is being protected?" That is the level you need before page layout, buffer replacement, and write-path design become concrete rather than memorized vocabulary.

Learning Objectives

By the end of this session, you will be able to:

1. Explain the major layers inside a DBMS - Trace how SQL execution hands work to the buffer manager, log manager, and storage layer.
2. Reason about the storage hierarchy mechanistically - Describe why hot state lives in memory, why durable state lands in WAL and data files, and how page movement dominates performance.
3. Evaluate production trade-offs - Distinguish read-path, write-path, and recovery bottlenecks instead of treating "the database" as one undifferentiated component.

Core Concepts Explained

Concept 1: The DBMS exposes a layered architecture because queries do not talk to disk directly

Return to Harbor Point's morning workload. An analyst asks for the last 30 minutes of quotes on a narrow set of municipal issuers, while the ingestion service keeps inserting new ticks. It is tempting to imagine each query as a direct trip to disk, but the engine does not work that way. The SQL layer parses text, the planner chooses an access path, the executor asks for tuples through pages, and the storage stack decides whether those pages are already resident or must be fetched.

One useful sketch is:

SQL text
   -> parser / optimizer
   -> executor
   -> buffer manager
   -> WAL + data files
   -> storage device

The critical boundary is between the executor and the storage subsystem. The executor thinks in terms of operators such as scans, joins, and index lookups. The storage layer thinks in terms of fixed-size pages, page identifiers, dirty bits, pin counts, and flush order. That mismatch is deliberate. Query processing needs logical operators; the storage engine needs stable physical units that match how devices and operating systems move data.

That is why a DBMS usually organizes work around pages rather than individual rows. A page is large enough to amortize I/O and metadata overhead, small enough to cache aggressively, and stable enough to use as the unit of locking, latching, eviction, and recovery bookkeeping. The engine therefore turns "find this row" into "locate the page that could contain it, bring that page into memory if needed, then inspect or modify bytes inside it."

The trade-off is that page-oriented design simplifies the contract between layers, but it also means tiny logical operations can still force page-sized work. The benefit is architectural clarity. The next lesson on 02.md builds directly on this point by opening the page itself and asking how tuples are arranged inside that physical unit.

Concept 2: The storage hierarchy is the real performance model of a database engine

Once you accept that the executor consumes pages, the storage hierarchy stops being background detail and becomes the main performance model. Harbor Point's hottest data structures, such as lock tables, buffer metadata, B-tree roots, and the most recently touched leaf pages, need to stay as close to the CPU as possible. Recent but not constantly reused pages belong in the buffer pool. Durable history lands on SSD-backed WAL segments and data files. Cold backups and snapshots can move to slower remote storage because they are optimized for retention, not immediate query response.

The latency gaps are orders of magnitude apart. CPU caches operate on the scale of nanoseconds. DRAM access is slower but still vastly cheaper than touching storage. NVMe or SSD reads are many thousands of times slower than a cache hit, and networked or archival storage is slower again. A DBMS architecture exists largely to avoid paying those slow tiers on the critical path unless the engine must.

For Harbor Point's read-heavy dashboard, that means repeated index probes should hit a small stable set of upper B-tree pages already resident in memory. If those pages churn out of the buffer pool, the same logical query suddenly performs extra device reads before it can even decide which leaf page holds the target rows. The query text did not change, but the storage hierarchy turned it into a different workload.

The same logic explains why a database keeps its own buffer manager instead of relying only on generic OS caching. The engine needs to know which pages are pinned by active operators, which pages are dirty, which flushes would violate write-ahead logging rules, and which eviction choices would punish a known hot index. Operating systems optimize for general file access. A DBMS needs policy that is tied to transactions, recovery, and page semantics.

The trade-off is control versus complexity. Owning the buffer pool and page metadata lets the DBMS make smarter decisions than a generic file cache, but it also means the engine must implement its own replacement policy, housekeeping, and recovery-aware flush discipline.

Concept 3: Durability comes from separating commit from data-page persistence

The most important write-path idea is that a committed transaction does not require every changed data page to reach its final home immediately. Suppose Harbor Point inserts a new quote, updates an aggregate table, and commits. If the engine had to synchronously rewrite every touched page in place before acknowledging success, commit latency would inherit the worst behavior of random storage I/O.

Instead, the engine uses write-ahead logging. The transaction changes pages in memory, appends redo information to the WAL, and only needs the relevant log records to become durable before the commit is acknowledged. The dirty data pages can be written later by background flushing and checkpoints because the WAL already contains the authoritative replay history needed after a crash.

You can summarize the write path like this:

transaction updates page in buffer pool
   -> append redo record to WAL
   -> flush WAL to durable media
   -> acknowledge commit
   -> flush dirty data pages later

This is the architectural hinge of the whole storage hierarchy. The WAL is optimized for ordered, durable append. Data files are optimized for eventual organization of pages into their long-term structure. Checkpoints then limit recovery time by ensuring the system does not have to replay an unbounded amount of log from the beginning of history.

The trade-off is explicit. Fast commits are purchased by accepting deferred work: dirty-page management, checkpoints, background writeback, and crash recovery logic. That is a good bargain in production because most systems prefer a short sequential log flush now over many scattered data writes now, even though it makes the engine internally more sophisticated.

Troubleshooting

Issue: Query latency spikes even though CPU utilization and average buffer hit ratio still look acceptable.

Why it happens / is confusing: A few hot pages may still hit in memory while another part of the workload causes eviction churn, extra device reads, or stalled WAL flushes. A single aggregate hit ratio hides which pages are cold, pinned, or repeatedly reread.

Clarification / Fix: Inspect page-level behavior by access path. Separate read misses, WAL sync time, checkpoint pressure, and buffer eviction counts. "The cache is mostly warm" is not enough if one hot index or catalog page keeps falling out of memory.

Issue: Operators inspect data files after a crash and conclude that committed writes were lost because the newest rows are not visible on disk.

Why it happens / is confusing: They are looking only at data pages and forgetting that committed state may live in WAL records waiting to be replayed.

Clarification / Fix: Check recovery status and log durability before declaring corruption. In a write-ahead design, the log becomes authoritative first; data pages may legitimately lag until redo is applied or background flushing catches up.

Issue: Adding more RAM improves performance for one workload but barely helps another.

Why it happens / is confusing: Memory helps only when the working set contains reusable hot pages. Large scans, poorly selective queries, or ingestion bursts that dirty many pages may still be limited by storage bandwidth and flush policy.

Clarification / Fix: Model the workload in page terms, not row counts or total table size. Ask whether the workload reuses a compact hot set, streams through cold data once, or creates heavy dirty-page churn that simply shifts pressure elsewhere.

Advanced Connections

Connection 1: DBMS architecture ↔ operating-system memory management

Both operating systems and database engines manage the movement of pages between fast and slow storage. The difference is that a DBMS cannot treat pages as anonymous memory. It must keep transaction semantics, WAL ordering, and buffer residency visible. Real engines therefore sit in constant negotiation with the OS: PostgreSQL leans on both shared buffers and the filesystem cache, while other engines prefer more direct I/O so they can control caching and writeback more tightly.

Connection 2: DBMS architecture ↔ storage-engine design choices

The architecture described here is the common ground beneath later choices such as B-trees, heap files, and log-structured merge trees. All of them are ultimately arguments about which pages should stay hot, which writes should become sequential, and how much future read or compaction work the engine is willing to buy. That is why this lesson has to come before detailed topics such as slotted pages, buffer replacement, and write-path variants.

Resources

Optional Deepening Resources

• [PAPER] Architecture of a Database System
  • Focus: Classic layered view of query processing, buffer management, recovery, and storage responsibilities inside a DBMS.
• [DOC] PostgreSQL Storage
  • Focus: Map pages, relation files, and storage metadata to a widely used production engine.
• [DOC] SQLite Architecture
  • Focus: See the pager layer directly and how it mediates between SQL execution and durable storage.

Key Insights

1. A DBMS is layered on purpose - Query processing works in logical operators, while storage works in pages and durability rules; the architecture is the contract between those views.
2. The storage hierarchy is the engine's real cost model - Page residency in CPU caches, DRAM, and storage tiers determines whether the same SQL text behaves like a memory lookup or a slow device-bound request.
3. Fast commits come from deferred page persistence, not from skipping durability - Write-ahead logging makes the log durable first so the engine can acknowledge success before every changed data page reaches its final location.