How Databases Work
A database is not just a file. Under the hood, every database engine manages pages of data on disk, a buffer pool in memory, a write-ahead log for crash recovery, and a query planner that turns your SQL into an execution plan. Understanding these pieces tells you why certain queries are fast, why indexes matter, and why writes are durable even after a power failure.
Storage engines like InnoDB (MySQL) and the Postgres heap store rows in fixed-size pages (usually 8KB). Reads load pages into the buffer pool; writes go to the log first, then flush to disk asynchronously. The query planner evaluates multiple access strategies — sequential scan, index scan, hash join — and picks the cheapest one based on table statistics.
Check your understanding
What is the write-ahead log for?Show answerHide answer
Answer
Durability and crash recovery: changes are logged before (or as part of) making data durable so the engine can recover after a failure.What does the buffer pool hold?Show answerHide answer
Answer
Recently used pages in memory so reads avoid hitting disk on every access.What does the query planner choose?Show answerHide answer
Answer
An execution strategy (scan vs index, join method, etc.) based on statistics and estimated cost.