On this page

How a Database Works: A Gentle Tour

Most of us meet databases from the outside. We type SELECT * FROM users WHERE email = 'alice@example.com', and an answer comes back. Sometimes in 1 millisecond, sometimes in 10 seconds, and it is not obvious why.

This lesson opens the box. You will not learn every detail. The goal is a clear mental model of what happens between "query sent" and "rows returned". Once you have that picture, a lot of system design advice starts to make sense: why we add indexes, why we give databases lots of RAM, and why a "simple" query can take down production.

We will follow one idea the whole way through: reading from disk is slow, reading from memory is fast, and a database spends most of its effort avoiding disk reads.

Data Lives in Pages

A database does not store your table as one long file that it reads from top to bottom. It splits the data into small, fixed-size blocks called pages. PostgreSQL uses 8 KB pages and MySQL's InnoDB uses 16 KB pages. Each page holds a handful of rows, plus a small header that says what is inside.

graph LR
    subgraph File["users table on disk"]
        P0["Page 0<br/>rows 1-60"]
        P1["Page 1<br/>rows 61-120"]
        P2["Page 2<br/>rows 121-180"]
        P3["..."]
        P4["Page 16,666<br/>rows ~1,000,000"]
    end
    P0 --- P1 --- P2 --- P3 --- P4

Why pages and not individual rows?

  • Disks work in blocks. An SSD or hard drive never reads "just 50 bytes". It reads a whole block anyway, so the database might as well use that whole block.
  • Fixed size makes bookkeeping easy. "Page 1,204" is always at byte offset 1,204 × 8 KB in the file. No searching needed.
  • Memory can be managed in the same units. As we will see next, the database caches whole pages in RAM.

So a table with 1 million rows of ~130 bytes each is roughly 16,000 pages. Remember that number. Whenever someone says "this query reads a lot of data", what they really mean is "this query touches a lot of pages".

The unit of work is the page

When you reason about database speed, count pages touched, not rows returned. A query that returns one row but has to look through 16,000 pages to find it is a slow query.

Memory vs Disk: Why the Buffer Pool Exists

Here are rough access times every engineer should keep in their head:

Where the data isRough time to readRelative to RAM
RAM (memory)~100 nanoseconds1×
NVMe SSD~20-100 microseconds~200-1,000× slower
Network disk (e.g. cloud block storage)~0.5-2 milliseconds~5,000-20,000× slower
Spinning hard drive~5-10 milliseconds~50,000-100,000× slower

The gap is enormous. If reading a page from RAM took one second, reading it from a cloud disk would take a few hours.

So every serious database keeps a buffer pool (PostgreSQL calls it "shared buffers"): a large chunk of RAM that holds copies of recently used pages. When the database needs a page, it checks the buffer pool first.

graph TD
    Q["Executor needs page 1,204"] --> C{"Is page 1,204 in the buffer pool?"}
    C -->|"Yes (cache hit)"| H["Read it from RAM<br/>~0.1 microseconds"]
    C -->|"No (cache miss)"| D["Read page from disk<br/>~100 microseconds or more"]
    D --> E["Store a copy in the buffer pool<br/>(evict an old, unused page if full)"]
    E --> H

A few things follow from this simple picture:

  • A "warm" database is fast; a "cold" one is slow. Right after a restart the buffer pool is empty, so every query goes to disk. Engineers often see latency spike after a failover for exactly this reason.
  • Fitting your hot data in RAM matters more than almost any tuning knob. If the pages your app uses most (the "working set") fit in the buffer pool, most reads never touch the disk.
  • The cache hit ratio is a key metric. Healthy OLTP databases often run at 99% or higher. When it drops, latency climbs.

When the buffer pool is full and a new page needs to come in, the database evicts a page that has not been used recently (a variant of LRU, "least recently used"). That is the same idea you will see again in caching strategies.

The Path of a Query

Let us follow a single query through the database:

SELECT name FROM users WHERE email = 'alice@example.com';
graph TD
    A["Client sends SQL text"] --> B["1. Parser<br/>Check syntax, build a tree"]
    B --> C["2. Planner / Optimizer<br/>Pick the cheapest way to run it"]
    C --> D["3. Executor<br/>Run the chosen plan step by step"]
    D --> E["4. Buffer pool<br/>Hand over the pages the plan asks for"]
    E -->|"miss"| F["Disk"]
    F --> E
    E --> D
    D --> G["Rows sent back to client"]

1. Parse. The SQL arrives as plain text. The parser checks that it is valid SQL (no typos like SELCT), that the table users and column email exist, and turns it into a tree structure the database can work with.

2. Plan. This is the clever part. There are usually several ways to answer a query. The database could read every row in the table, or it could use an index on email if one exists. The planner (also called the optimizer) estimates the cost of each option using statistics about the table, such as how many rows it has and how many distinct emails there are, and picks the cheapest plan.

3. Execute. The executor runs the chosen plan. It does not read files directly. It asks the buffer pool for specific pages: "give me page 0", "give me page 1", and so on.

4. Read pages. The buffer pool returns pages from RAM if it has them, and fetches them from disk if it does not. The executor looks inside each page, finds matching rows, and sends them back.

You can see the plan the database chose with EXPLAIN:

EXPLAIN SELECT name FROM users WHERE email = 'alice@example.com';
-- Seq Scan on users  (cost=0.00..20834.00 rows=1 width=12)
--   Filter: (email = 'alice@example.com'::text)

"Seq Scan" means a sequential scan: read the whole table. That is the planner telling you it had no better option.

Why a Full Table Scan Is Slow

Without an index, the database has no idea where Alice's row lives. It might be on page 0 or page 16,000. The only safe approach is to read every page and check every row.

For our 1-million-row table:

  • 16,000 pages to read
  • If they are all on a cloud disk at ~1 ms each and not cached, that is many seconds of I/O in the worst case (real systems read ahead in big chunks, which helps, but you are still moving ~130 MB)
  • Even if they are all in RAM, the CPU still checks a million rows

And here is the part that hurts in production: a big scan also pushes other useful pages out of the buffer pool. The table scan's pages flood in, the pages other queries depend on get evicted, and suddenly everyone's queries get slower.

War Story (illustrative)

A team ships a new "find user by phone number" feature. It works fine in staging, which has 5,000 users. In production, with 40 million users and no index on phone, every call does a full table scan. At 20 requests per second, the database spends all its time reading pages, the buffer pool churns, and unrelated pages like checkout and login slow to a crawl. The fix is one line: CREATE INDEX ON users (phone);. Lookups drop from seconds to under a millisecond.

Full scans are not always bad. If you really need most of the rows (say, a nightly report over the whole table), reading pages in order is the most efficient way to do it. The problem is using a full scan to find one row.

Indexes: A Table of Contents for Your Data

An index is a separate data structure that maps a column's values to where the matching rows live. Think of the index at the back of a textbook: instead of reading all 600 pages to find "photosynthesis", you look it up in the index, which says "page 214", and you go straight there.

The most common index type in relational databases is the B-tree (strictly, a B+tree, but people usually just say B-tree). It is a tree of pages, kept sorted by the indexed value:

  • The root page at the top holds a few keys that split the whole range into sections.
  • Internal pages in the middle split each section further.
  • Leaf pages at the bottom hold every indexed value in sorted order, each with a pointer to the row's location in the table.

The key trick is that each page holds hundreds of keys, not two like the binary trees from algorithms class. That makes the tree very wide and very shallow.

How One Indexed Lookup Works

Let us look up email = 'alice@example.com' using a B-tree index on email:

graph TD
    R["Root page<br/>[ ... | 'f' | 'm' | 's' | ... ]"]
    I1["Internal page for a-f<br/>[ 'ab' | 'al' | 'b' | ... ]"]
    I2["Internal page for f-m"]
    I3["Internal page for m-s"]
    L1["Leaf page<br/>alex@... -> row at page 912<br/>alice@... -> row at page 4,021<br/>alicia@... -> row at page 77"]
    L2["Other leaf pages"]
    T["Table page 4,021<br/>Alice's full row"]

    R -->|"'alice' comes before 'f'"| I1
    R -.-> I2
    R -.-> I3
    I1 -->|"'alice' is between 'al' and 'b'"| L1
    I1 -.-> L2
    L1 -->|"follow pointer"| T

Step by step:

  1. Read the root page. Compare 'alice' with the keys. It is less than 'f', so follow the first child.
  2. Read the internal page. 'alice' sits between 'al' and 'b', so follow that child.
  3. Read the leaf page. Find the exact entry alice@example.com, which says "row is on table page 4,021".
  4. Read table page 4,021. Pull out Alice's name.

That is four page reads instead of 16,000. And it barely grows as the table grows:

Rows in tablePages in full scanLevels in B-tree (roughly)Page reads for one lookup
10,000~1702~3
1,000,000~16,0003~4
100,000,000~1,600,0004~5

Because each page fans out to hundreds of children, a tree only 3-4 levels deep can cover hundreds of millions of rows. Better still, the root and upper levels of busy indexes are almost always sitting in the buffer pool, so in practice only the last one or two reads might touch disk.

Because the leaves are sorted, a B-tree is also good at range queries (WHERE created_at > '2026-01-01') and ORDER BY. Find the starting leaf, then walk along the leaves in order.

Indexes are not free

Every index is another structure the database must update on every INSERT, UPDATE, and DELETE that touches the indexed column. It also takes disk space and buffer pool memory. Index the columns you actually filter, join, or sort on. Do not index everything "just in case".

Writes: Change in Memory, Log First

Reads are only half the story. What happens when you run this?

UPDATE users SET name = 'Alice Smith' WHERE email = 'alice@example.com';

At a high level:

  1. Find the row. Exactly like a read: use the index, bring the right page into the buffer pool.
  2. Change the page in memory. The database edits the copy of the page in RAM. That page is now dirty: it differs from what is on disk.
  3. Write a log record first. Before telling you "done", the database appends a short note describing the change ("on page 4,021, set name to 'Alice Smith'") to a separate file called the write-ahead log (WAL). This log write is flushed to disk.
  4. Reply "committed".
  5. Later, in the background, the dirty page itself is written back to its place in the table file.
sequenceDiagram
    participant App
    participant DB as Database
    participant BP as Buffer pool (RAM)
    participant WAL as WAL file (disk)
    participant Data as Table file (disk)

    App->>DB: UPDATE users SET name = ...
    DB->>BP: Load page 4,021 and change it (now dirty)
    DB->>WAL: Append log record and flush
    WAL-->>DB: Safely on disk
    DB-->>App: COMMIT OK
    Note over BP,Data: Later, in the background
    BP->>Data: Write dirty page 4,021 back

Why not just write the page straight to the table file? Two reasons:

  • Speed. Appending a small record to the end of one log file is much faster than writing full pages scattered all over the table file. Many changes to the same page can also be batched into a single page write later.
  • Durability. If the server crashes after step 4 but before step 5, the change only existed in RAM and RAM is gone. But the WAL is on disk. On restart, the database replays the log and reapplies every committed change that never made it into the table file. Nothing committed is lost.

That is the whole idea behind the "D" (durability) in ACID, which you will meet properly in the transactions lesson. The rule is simple: log first, apply later. The Write-Ahead Logging page goes into checkpoints, fsync, and how replication reuses the same log.

Putting It All Together

Here is the whole tour in one table:

ConceptOne-line summaryWhy you care in system design
PagesData is stored in fixed-size blocks (8-16 KB)Cost is measured in pages touched
Buffer poolRAM cache of pagesSize your database so the working set fits in memory
Parse, plan, executeSQL text becomes a tree, then a plan, then page readsThe planner can choose a bad plan; check with EXPLAIN
Full table scanRead every page to find matching rowsFine for reports, terrible for single-row lookups
B-tree indexShallow sorted tree pointing to rowsTurns 16,000 page reads into ~4
WALLog the change before applying itCommits are durable and fast, and crashes are recoverable

If you remember one sentence, make it this: a database is a machine for avoiding disk reads, and indexes plus the buffer pool are its two main tools.

Where to Go Next

This was the gentle version. When you are ready for more depth: