How databases actually work under the SQL — storage engines (B-tree vs LSM), pages and the buffer pool, the write-ahead log, indexes, transactions and isolation, MVCC, and query planning.
A database is not magic — it's a program that turns your rows into bytes on a disk and finds them again quickly, correctly, and without losing them when the power fails. Understanding the machine underneath the SQL is what separates someone who writes queries from someone who knows why they're slow.
A database is a program that turns your rows into bytes on a disk and finds them again quickly, correctly, and without losing them when the power fails. Understanding the machine underneath the SQL is what separates writing queries from knowing why they're slow.
Almost every database on earth stores its data in one of two structures: a B-tree that updates in place, or an LSM-tree that only ever appends. This one choice ripples through everything — read speed, write speed, space usage, and latency predictability — so knowing which your database uses tells you more about its behavior than almost anything else.
Almost every database stores data in one of two structures: a B-tree that updates in place, or an LSM-tree that only appends. This one choice ripples through read speed, write speed, space, and latency predictability.
The buffer pool is where a database spends most of its memory and wins or loses most of its performance. It's a cache of disk pages in RAM, and the difference between a query that hits it and one that misses is the difference between a microsecond and a millisecond — a thousandfold gap that decides whether your database feels fast.
The buffer pool is where a database spends most of its memory and wins or loses most of its performance — a cache of disk pages in RAM, where a hit versus a miss is a thousandfold latency gap.
Durability — the promise that a committed transaction survives a crash — comes down to one deceptively simple rule: write down what you're about to do before you do it. The write-ahead log is that rule made concrete, and it's the reason a database can be both fast and crash-safe, two goals that otherwise pull in opposite directions.
Durability comes down to one deceptively simple rule: write down what you're about to do before you do it. The write-ahead log is that rule made concrete — the reason a database can be both fast and crash-safe.
An index is a data structure that lets a database find rows without reading the whole table — the difference between flipping to a book's index and reading every page. It's the highest-leverage performance tool a database gives you, and also the most misused: every index you add speeds up reads and slows down writes, so the skill is knowing exactly which ones earn their cost.
An index lets a database find rows without reading the whole table — the highest-leverage performance tool a database gives you, and the most misused: every index speeds up reads and slows down writes.
A transaction is a promise that a group of operations happens all-or-nothing and doesn't get corrupted by everyone else doing the same thing at once. Most developers know the word ACID; far fewer know that the "I" — isolation — is a dial with several settings, and that the default setting in most databases allows anomalies they've never heard of.
Most developers know ACID; far fewer know that the 'I' — isolation — is a dial with several settings, and that the default in most databases allows anomalies they've never heard of.
The reason a long analytics query doesn't block every writer in your database — and vice versa — is a single elegant idea: never overwrite data, keep multiple versions, and give each transaction a consistent snapshot in time. MVCC is how nearly every modern database delivers isolation without readers and writers fighting over locks.
The reason a long analytics query doesn't block every writer — and vice versa — is a single elegant idea: never overwrite data, keep multiple versions, and give each transaction a consistent snapshot in time. That's MVCC.
SQL is a language where you say what you want, not how to get it — and the component that invents the "how" is the query planner, the closest thing a database has to a brain. When a query is mysteriously slow, the answer is almost always in the plan, which is why reading `EXPLAIN` is the single most valuable database skill you can learn.
SQL says what you want, not how to get it — and the component that invents the 'how' is the query planner, the closest thing a database has to a brain. Reading EXPLAIN is the single most valuable database skill you can learn.
This series is part of a larger body of work by Pratik Dhanave, an Agentic AI Architect writing about production AI systems, distributed systems, and cloud-native engineering. Explore all course series, browse every post, or find topics via the tag index.