Aurélie Chastain runs the booking system for a veterinary practice in Rennes. The monthly report that lists which animals are overdue for a vaccination took 40 seconds to produce, on a database of 12,000 animals, on a machine that was doing nothing else. Adding one line to the database made it take 60 milliseconds, and that line did not change a single character of the query.
A database is not a place to put things. It is a set of promises about what will still be true after a crash, and a set of trades about what will be fast. Every cluster here is one of those two, and the practical half of the topic is knowing which one you are currently paying for.
The line that took 40 seconds off
Aurélie's query asks for animals whose last vaccination date is older than a year. Without help, the database has one way to answer: read all 12,000 rows and check each date. An index is a second copy of the column, kept sorted, and with one the database can jump straight to the boundary date and read only the rows past it. The whole repair was CREATE INDEX ON animals (last_vaccination).
The reason this is not simply free, and the reason databases do not index everything by default, is that the copy has to be kept true. Every insert and every update to that column now writes twice: once to the row and once to the index. On a table that is read constantly and written rarely, that is an excellent trade. On a table taking a write per second and read once a month, it is the wrong way round, and the index is a tax paid all day for a saving taken once.
The command that ends the argument is EXPLAIN, which asks the database what it intends to do before it does it. It answers with the plan: a sequential scan of 12,000 rows, or an index scan of 340. Reading that output is the single most transferable skill on this topic, and it is what the language for asking a database questions is ultimately for.
Three facts that produce most wrong answers
The first is the one that silently returns nothing. NULL is not equal to NULL, so IS NULL is the only test that works. NULL does not mean zero or empty. It means unknown, and two unknowns cannot be declared equal, so WHERE last_vaccination = NULL is not an error and matches no rows at all. Aurélie had exactly this in an earlier version, and it reported that every animal was up to date, which is the worst kind of bug: confident, plausible and silent.
The second is the one that hangs the machine. A join with no ON condition is every row against every row. Joining 12,000 animals to 400 owners without saying how they relate asks for 4,800,000 rows, and the database will do it, because it is a legal request. The symptom is not an error message. It is a query that has not come back.
The third is the one that matters when something fails halfway. A transaction is all of it or none of it. Moving an animal to a new owner is two writes, and a crash between them leaves an animal owned by nobody. Wrapped in BEGIN and COMMIT, either both writes land or neither does, and there is no state in between that anybody can observe. This is the promise the word database is actually pointing at, and it is the reason deciding what the tables are before you fill them comes before any of the tooling.
The order, and what skipping it costs
The order that works is the language first, then the shape, then the engine. Almost nobody does it that way, because the engine is what a job advertisement names and the shape is what nothing names at all.
Taken backwards, the failure has a signature. Somebody learns an object mapper before learning SQL, gets a working application, and then meets a slow page they cannot reason about, because the layer that writes the queries for them is now the layer hiding what is slow. The mapper is not the problem and the layer between your objects and the tables is a genuine subject. It is only unreadable to somebody who never saw the thing it is generating.
The order
Four rungs, and each one is invisible if you started above it
Almost everybody starts on the third rung, because it is the one with a name on a job advertisement.
- 04The other shapeDocuments and keys, when a fixed row is genuinely the wrong container.Skip the first three and this is a guess
- 03The engineWhich product, which dialect, and what it does under a crash.Usually chosen before you arrive
- 02The shapeWhat the tables are, what is unique, and what is not allowed to be missing.Skip it and no index saves you
- 01The languageAsking questions directly, and reading the plan the database answers with.Skip it and the mapper is opaque
The rungs are not difficulty. The first is the easiest to learn and the one most often postponed, because an object mapper will write passable queries for a beginner and stop doing so at exactly the point it matters.
Where people stall
The stall on this topic is choosing a database before knowing the shape of the data, and it is usually decided by which one somebody read about most recently. The honest position is that when rows and columns are the wrong shape is a real answer to a real question, and the question is whether the data has a fixed shape and relationships that matter. Aurélie's does: animals belong to owners, appointments belong to both, and every report she writes is a join. A document store would have made her first week faster and her fourth month considerably worse.
The second stall is smaller. People treat the database as somebody else's department right up until the day a page is slow, and then discover that the fix was never in the application code at all. It is one of the few places in programming, from the first variable to a deployed system where the cheapest repair is reliably one line long and reliably in a file nobody on the team has opened.
What to open, in 2026
SQLite is the right place to learn, and increasingly the right place to ship. It is a single file, it needs no server, it is in the public domain, and it replaced the habit of inventing a file format and writing a parser for it. A practice the size of Aurélie's could run on it permanently.
PostgreSQL is the default when a server is warranted, is free and open source, and took that position from MySQL over roughly the last decade on the strength of correctness and of doing more of the work inside the database. Both understand EXPLAIN, both enforce transactions properly, and the SQL that this topic teaches moves between them with small dialect differences rather than a rewrite. Which of the two arrives on a given project is usually decided before anybody is hired, which is why the databases you will actually be handed is framed the way it is.
What none of this answers is what happens when one machine is no longer enough to hold the thing, which is a different subject with different promises and is where the guarantees above start being negotiated away deliberately.