Part 1 — The relational foundation › Chapter 01

What a database engine actually is

Almost every performance conversation you will have is really a conversation about disk. If you know what a page is, what the buffer pool is for, and why the log is written before the data, you can reason about problems other people can only guess at.

From the advert

“Solida conoscenza di MS SQL Server (versione ≥ 2008)” — nobody is checking that you have memorised a version's feature list. They are checking that you have used an engine deeply enough to know it has moving parts.

A database is not a file you read

🧒 Simple

Imagine a library where the books are far away in a cold warehouse, and there is one small warm room with a desk. Fetching a book from the warehouse takes ages, so the librarian keeps the books people ask for most on the desk. When you write in a book, the librarian first scribbles “I am about to change page 40 of this book” in a notebook by the door — because if the lights go out mid-sentence, that notebook is how she knows what she was doing.

🎓 Professional

Every mainstream relational engine is built from the same four pieces:

  • Pages. Storage is divided into fixed-size blocks — 8 KB in SQL Server and PostgreSQL, 16 KB by default in InnoDB. The engine never reads one row from disk; it reads the page the row is on. This single fact explains row size, index design and why SELECT * costs more than you think.
  • The buffer pool. An in-memory cache of pages. A query that finds its pages here is a logical read; one that does not is a physical read, and is roughly four orders of magnitude slower. “Tuning” usually means reducing the number of pages touched so more of the working set fits.
  • The write-ahead log (WAL / transaction log / redo log). The change is written to a sequential log and flushed to durable storage before the data page is. This is the D in ACID and the reason a commit is fast: appending to a log is sequential, rewriting scattered data pages is not.
  • The optimiser. SQL says what, not how. A cost-based optimiser turns your statement into a plan using statistics about the data. See chapter 27.
The path of a write through a database engine A client statement enters the engine, is logged to the write-ahead log which is flushed to disk at commit, modifies a page in the buffer pool, and is written to the data files later by a background checkpoint. Client UPDATE … THE ENGINE (MEMORY) Parse → bind → optimise produces a plan Log records appended, in order Buffer pool page 41 changed, still dirty Log file fsync AT COMMIT Data files written LATER durability lives here checkpoint, in the background
A commit waits for the log to hit disk, never for the data pages.
⚠ Trap

“The transaction log is full” is not a disk-space problem you fix by growing the disk. In SQL Server it usually means the database is in FULL recovery model and nobody is taking log backups — so the log cannot truncate and grows until the volume fills. The fix is a log backup schedule, or SIMPLE recovery if point-in-time restore genuinely is not required. See chapter 41.

Why the page size decides so much

A row lives inside a page, and a page is read or written whole. Three consequences follow, and all three come up in interviews:

  1. Narrow rows are fast rows. If a row is 100 bytes, roughly 80 fit in an 8 KB page. At 800 bytes, ten fit. Scanning a million rows therefore costs eight times as many reads for no other reason than column width. This is the real argument against SELECT * and against storing a description you never query beside a key you always do.
  2. A row that does not fit is stored elsewhere. Large values (TEXT, VARCHAR(MAX), JSONB) go off-page — SQL Server calls it LOB storage, PostgreSQL calls it TOAST — leaving a pointer behind. Reading the column then costs an extra jump. Not reading it costs nothing, which is exactly why SELECT * can be dramatically worse than naming the columns.
  3. Updates that grow a row can split a page. If an update no longer fits, the engine splits the page in two and leaves both half-empty. That is fragmentation: more pages for the same data, so more reads forever after.

In the playground, compare what the engine has to touch:

Two queries with the same WHERE clause and the same rows, moving very different numbers of bytes.
-- Both answer the same business question: "what is on order 42?"
SELECT * FROM order_lines WHERE order_id = 42;          -- every column of every matching row
SELECT sku, quantity FROM order_lines WHERE order_id = 42;  -- only the two columns needed

-- Same rows. Very different bytes read off disk, held in the buffer pool, and
-- pushed over the network — and only the second one can ever be answered from
-- an index alone, without visiting the table at all.
What the engine actually does differently
  • SELECT * — expands to every column at parse time, so adding a VARCHAR(4000) column next year silently makes this query heavier. It also guarantees a trip to the table: no narrow index can cover “all columns”.
  • order_id = 42 — an INTEGER compared to an INTEGER literal, so there is no conversion and the index on order_id can be searched directly. Quote it as '42' and some engines convert every stored value instead, which is chapter 07's whole subject.

Two families: locking readers, and MVCC

🧒 Simple

Two ways to stop people arguing over a book. One: whoever is writing in it holds it, and readers wait. Two: the writer works on a photocopy, and readers keep reading the old copy until the new one is finished. The second means nobody ever waits to read — but somebody has to tidy up all the old photocopies afterwards.

🎓 Professional

MVCC (multi-version concurrency control) keeps multiple versions of a row so that a reader sees a consistent snapshot without blocking writers. PostgreSQL, Oracle and InnoDB are MVCC by default. SQL Server historically used pessimistic locking, where readers take shared locks and can block writers — and offers MVCC behaviour by opting in to READ_COMMITTED_SNAPSHOT or SNAPSHOT isolation.

The cost of MVCC is that dead versions accumulate and must be cleaned up: PostgreSQL's VACUUM, Oracle's undo segments, InnoDB's purge thread. “Why is my PostgreSQL table 100 GB when it holds 2 GB of data?” is almost always table bloat from a long-running transaction holding back vacuum. See chapter 22.

The four engines you will meet

EnginePageConcurrencyWhere you meet it in Italy
SQL Server8 KBLocking; MVCC opt-inGestionali, manufacturing ERP, anything with a .NET front end
Oracle8 KB defaultMVCC (undo)Banks, insurance, utilities, large public sector
PostgreSQL8 KBMVCC (in-table versions + VACUUM)New builds, startups, anything cloud-native
MySQL / MariaDB16 KB (InnoDB)MVCC (undo log)Web applications, e-commerce, WordPress
SQLite deserves a line

It is a real, fully transactional SQL engine in a single file, and it is what the playground and this site's own accounts service run on. It is not a toy — but it is single-writer, so it is the wrong choice for concurrent write workloads and the right choice for almost everything else at small scale.

The numbers that make the argument

Learn these to one order of magnitude. Every optimisation decision in Part 4 is an argument about which row of this table you are landing in.

OperationRoughlyRelative
Read a page already in the buffer pool~100 ns1×
Read a page from a local NVMe SSD~100 µs1 000×
Read a page from a spinning disk~10 ms100 000×
Round trip to a database on another host~0.5 ms5 000×

The last row is why the N+1 problem is so destructive. A thousand tiny correct queries, each 0.5 ms of network, is half a second of doing nothing — and it will not show up as a slow query anywhere, because every individual query was fast.

  1. The engine reads pages, never rows. Everything about physical design follows from that.
  2. A commit waits for the log to reach disk, not the data pages. Sequential writes are why that is affordable.
  3. Narrow rows are fast rows: fewer bytes per row means more rows per page means fewer reads.
  4. Large columns are stored off-page. Selecting them costs an extra jump; not selecting them costs nothing.
  5. MVCC removes reader/writer blocking and adds a cleanup obligation. There is no version without a cost.
  6. Memory is roughly a thousand times faster than SSD and a hundred thousand times faster than spinning disk. Tuning means keeping the working set in memory.
  7. A thousand fast queries can be slower than one slow query, and no slow-query log will tell you.

Interview questions this chapter answers

What happens when you COMMIT?

The log records for the transaction are flushed to durable storage and the transaction is marked committed. The modified data pages are still dirty in memory and get written later by a checkpoint. That is what makes commits fast and what makes crash recovery possible: on restart the engine replays committed log records and rolls back uncommitted ones.

Why is SELECT * discouraged?

Three reasons, in increasing order of how much they impress. It moves bytes nobody asked for; it can force off-page LOB reads; and — the real one — it prevents a covering index from covering, so a query that could have been an index-only scan becomes an index seek plus a lookup per row. It also breaks silently when somebody adds a column.

The application is slow. Where do you start?

Establish whether it is one slow query or many fast ones, because the fixes are opposite. Wait statistics and a top-N-by-total-duration list answer that in a minute; sorting by average duration hides the N+1 case entirely. Chapter 42 is the method.