SQL

Sixteen surprises — what you see, the rule behind it, and the fix

Nobody searches for SQL because they want a definition. They search because a query returned the wrong rows, ran slowly, or lost an update that two users made at the same moment. Almost every such surprise comes from one of sixteen rules that tutorials skip, because a tutorial runs one query at a time on a thousand rows. Here they are: what you see, the rule that causes it, and the fix. Later sections cite the rows by number.

How a query is evaluated — the logical order, NULL, and joins

A SELECT is written in one order and evaluated in another. You write SELECT first, but it is computed almost last. Knowing the logical order explains a whole family of confusing errors — why WHERE cannot use a column alias that ORDER BY can, why WHERE cannot filter on COUNT(*) and HAVING can. The order below is logical, not physical. The planner is free to filter early, use an index to avoid sorting, or skip steps entirely. What it must not do is return a result different from the one this order would produce.

Rows 1 and 2 come from one design choice that is older than most of the people using it. NULL in SQL does not mean empty, zero or missing. It means unknown. A comparison with an unknown value is itself unknown — not false. So SQL works with three truth values: true, false and unknown. WHERE and HAVING keep a row only when the condition is true, so an unknown condition silently drops the row. That single rule produces every NULL surprise on the table above.

The NOT IN row is the one that costs real money. A query like “customers whose id is not in the list of blocked customers” works for years. Then one row in the blocked table gets a NULL id, and the query starts returning nobody at all — no error, just an empty result that looks like good news. NOT EXISTS does not have this flaw, because it asks whether a matching row exists rather than comparing values. Engines differ in one related detail. PostgreSQL and MySQL let a unique column hold any number of NULLs, because no two of them are known to be equal. SQL Server allows only one. PostgreSQL 15 added NULLS NOT DISTINCT for teams who want SQL Server’s behaviour.

The planner and indexes — you describe the result, the engine picks the route

SQL is declarative, and that is the single most important thing about it. You state which rows you want; you do not state how to find them. A component called the query planner reads your statement and the statistics the engine keeps about each table — how many rows, how many distinct values in a column, how they are distributed. It then estimates the cost of several possible strategies and chooses the cheapest. The same statement can therefore run one way today and a different way next month, because the data underneath it changed.

What makes one route so much cheaper than another is almost always an index. An index is a second structure, kept sorted, that maps the values of one or more columns to the rows holding them. Usually it is a B-tree: a shallow tree the engine descends in a handful of steps, whatever the size of the table. Finding one row among a million takes about twenty comparisons through an index and a million without one. That is the whole difference between a query that answers in a millisecond and the same query answering in a second.

An index helps only a condition it can use, and rows 7–9 are the three ways a condition stops being usable. Row 7: the index stores email, not lower(email), so a condition on the function has nothing sorted to descend. Index the expression, or store the value already lower-cased. Row 8: compare a text column with a number and the engine must convert every stored value before comparing. MySQL’s manual states it flatly — for such a comparison it cannot use an index on the column. The fix belongs in the application, often in what the ORM binds as a parameter. Row 9 is about column order, and it is the one worth a picture.

A composite index on (customer_id, created_at) is sorted like a phone book: by customer first, then by date within each customer. A query for one customer’s recent orders reads one short, contiguous range. A query for all recent orders, whoever placed them, finds matching entries scattered through every customer’s section. The old advice was that such an index does nothing for it. That is no longer quite true. PostgreSQL 18, released on 25 September 2025, added skip scan for multicolumn B-tree indexes, and MySQL has had one since 8.0.13. A skip scan jumps from one customer to the next and searches each section separately. That is fast when there are twenty customers and hopeless when there are two million. So the rule stands with an asterisk: put equality columns first and the range column last, and treat skip scan as a rescue, not a design.

The last row explains a common performance mystery. With a clustered primary keyprimary keythe column that identifies a rowThe column, or set of columns, whose value identifies exactly one row in a table and never repeats. A made-up number or a UUID usually makes a better primary key than a business value such as an email address, because business values change and every reference has to change with them.Full article →All terms, rows are physically kept in key order. A random UUIDv4 key lands each new row in a random place, splitting pages and keeping the whole table hot in memory. A sequential key appends at the end. UUIDv7 keeps the advantages of a UUIDUUIDUniversally Unique Identifier — a 128-bit id made anywhereA 128-bit identifier that any machine can generate without asking a central counter, with practically no chance of two colliding. Version 4 is random, which keeps it unguessable but scatters database inserts; version 7 starts with a timestamp, so new ids sort in the order they were created.All terms — generated anywhere, not guessable from a neighbour — while sorting by creation time, and PostgreSQL 18 added a built-in uuidv7() function. Row 4 of the APIAPIApplication Programming Interface — contract between programsThe rules one program follows to call another: which requests it may send, in what shape, and what comes back. To the caller it is a contract — everything behind it stays a black box, which is exactly what lets the other side be rewritten without breaking you.Full article →All terms Design page asks for ids that cannot be enumerated; a UUIDv7 is one the database can generate without paying for randomness in its index — though, unlike a v4, it reveals when the row was created.

Once the planner has found the rows of each table, it has to combine them, and it has three algorithms to choose from. A nested loop takes each row on one side and looks up its matches on the other, ideally through an index. It is unbeatable when one side is small. A hash join builds a hash table from the smaller side and streams the larger side past it. It wins for large, unsorted inputs joined on equality. A merge join walks two inputs already sorted on the join key. Which one is chosen depends on the planner’s row estimates. That is why a bad estimate is so expensive: a nested loop chosen for 50 rows that meets 500,000 does 500,000 lookups.

Rows 10 and 11 are not about a single query at all. OFFSET 100000 asks the engine to produce and sort a hundred thousand rows and throw them away, so the deeper the page, the slower it gets. Keyset pagination remembers where the last page ended — the created_at and id of its last row — and asks for the rows after that, which an index answers directly at any depth. It is the database half of the cursor pagination on the API Design page. Row 11, the N+1 pattern, is the performance problem you will actually meet. A page renders a list of two hundred orders, and an object mapper lazily loads each order’s customer inside the loop. The result is two hundred fast queries and a slow page, while the database looks idle. Every mapper has a way to fetch the related rows in one statement. Query logging in development is how you notice that you need it.

Transactions and isolation — what each level actually permits

A transaction is a group of statements the database treats as one. The four letters of ACID are four separate promises, and they fail in different ways. Atomicity: the whole group happens or none of it does, so a crash between two updates cannot leave half a transfer. Consistency: a transaction never ends in a state that breaks a constraint you declared. Isolation: concurrent transactions do not see each other half-finished — and this is the one that comes with settings. Durability: once the database confirms a commit, the data survives a power cut.

Most engines implement isolation with multi-version concurrency control, MVCC. An update does not overwrite a row; it writes a new version and leaves the old one for transactions that started earlier. Each transaction reads a snapshot — the versions that were committed when it started, or when its current statement started. Readers never wait for writers, which is why the approach won. Its price is that two transactions can each read a correct snapshot, make a decision on it, and never see each other’s decision. Full isolation, where the outcome always matches some one-at-a-time order, costs extra work and occasional failures, so every major engine ships with something weaker by default.

The levels are defined by which anomalies they permit, and knowing the anomaly is what lets you recognise the bug in your own product. In 1995 Hal Berenson, Jim Gray and four co-authors showed that the SQL standard’s own definitions were ambiguous and missed anomalies that real engines allowed. The vocabulary below comes largely from that paper:

Two details in that table catch experienced teams. First, the same name means different things in different engines. PostgreSQL’s repeatable read also prevents phantoms and stops a lost update with an error, while MySQL’s repeatable read lets a lost update through. And Oracle’s level called serializable is snapshot isolation, which still permits write skew — the ACIDRain paper notes that Oracle does not offer true serializability at all. Second, write skew survives every level below serializable, and it is the anomaly that looks least like a bug. Nobody overwrote anything. Each transaction checked the rule, and each was right about what it saw.

The example in the diagram comes from Martin Kleppmann’s “Designing Data-Intensive Applications”. A hospital requires at least one doctor on call. Alice and Bob are both on call and both feel unwell. Each opens the app at the same moment, which checks that at least two doctors are on call, sees two, and lets them sign off. Each transaction updates a different row — Alice’s and Bob’s — so no row is written twice and nothing conflicts. Both commit, and nobody is on call. There are three real fixes. Run the transaction at serializable and retry when it fails. Lock the rows the rule reads, with SELECT … FOR UPDATE on every on-call doctor, so the second transaction waits for the first and then sees one doctor. Or move the rule into the database as a constraint, where that is possible, so the engine enforces it whoever writes.

Serializable is cheaper than its reputation. PostgreSQL has implemented it since version 9.1 in 2011 using serializable snapshot isolation, a technique published by Michael Cahill, Uwe Röhm and Alan Fekete in 2008. Transactions run on snapshots as usual, and the engine watches for the dangerous pattern of reads and writes between them. When it finds one, it aborts one transaction with a serialization failure, SQLSTATE 40001. The cost is that your code must catch that error and run the whole transaction again, from its first read. Code that does this once, in a shared helper, gets correctness for every rule at the price of an occasional retry.

One operational rule follows from all of this: keep transactions short. An open transaction holds its locks. On engines that keep row versions, it also prevents the cleanup of every version created since it began, which is row 16. The specific habit worth having is never to make a network call inside a transaction. A payment provider that takes thirty seconds to answer becomes thirty seconds of held locks. A provider that never answers becomes an outage in a table you were not thinking about.

Constraints and schema changes — rules the engine holds for every writer

The usual argument against database constraints is that the application already validates. It does — until it is not the only writer, which happens to every system that lives long enough. A migration script, a data fix run by hand, a second service, an import job, a support engineer with a database client open: each writes without going through your validation. A constraint in the database is the only rule that holds against all of them, and it holds without exceptions, because the engine refuses the row.

Changing a live schema

Row 15 surprises teams who know their migrations are fast. Adding a nullable column in PostgreSQL changes only the catalog and finishes in milliseconds. But it needs the strongest lock on the table for those milliseconds, and it has to wait for that lock behind every query already running. Locks are granted in a queue. Once the migration is waiting for an exclusive lock, every query that arrives after it — even a plain SELECT — queues behind the migration. So a five-minute report that happens to be running can turn a two-millisecond migration into minutes of outage for that table. PostgreSQL and MySQL behave the same way here. MySQL calls it waiting for a metadata lock.

The defence is short and mechanical. Set lock_timeout in every migration to a pause your application can tolerate — a second or two — so that a migration that cannot get its lock gives up, and retry it a moment later. Split changes so each statement takes the weakest lock it can. And know which operations rewrite or scan the whole table, because those hold their lock for minutes, not milliseconds:

The last row is the reason expand and contractexpand and contracta schema change in reversible stepsChanging the shape of data in three releases instead of one migration: add the new structure and write to both, move the readers across, then remove the old structure once nothing reads it. Every step but the last can be undone, which is the point — a rename done in one migration has no way back.Full article →All terms exists. Code and schema can never switch at the same instant, because a deploy rolls out over minutes and old instances keep running. So a change becomes several deploys, each safe on its own. Add the new column. Write to both. Backfill the old rows in batches. Switch reads. Stop writing the old one. Drop it. Every step can be rolled back except the last, and by then nothing uses the old column. The Monolith → Microservices page applies the same sequence to moving a whole table between services.

Running it — connections, replicas and the cleanup nobody sees

Connections are a resource with a hard ceiling, and it is lower than people expect. In PostgreSQL every connection is a separate operating-system process, and the default limit is a hundred. A pool of twenty per application instance looks modest until the orchestrator runs fifty instances and the database refuses the thousand-and-first connection. Size the pool from what the database can serve rather than from what one instance would like. Put a connection pooler such as PgBouncer in front of it when the number of instances changes on its own. A pool smaller than you assumed also explains a class of mysterious latency: the time goes on waiting for a connection, not on running the query.

Read replicas are the usual answer to read load, and they bring one behaviour that has to be designed for. Replication is usually asynchronous, so a replica runs a few milliseconds — occasionally a few seconds — behind. A user who saves a form and is redirected to a page that reads from a replica does not see their own change. It looks like a bug and is in fact the architecture. Send the reads that must reflect the user’s own write to the primary, and let everything else go to the replicas. The same lag has a second, harsher consequence. When the primary fails and a replica is promoted, whatever had not yet reached the replica is gone or, worse, lives on in a copy nobody is reading. The GitHub case below is exactly that.

Row 16 is the cost of MVCC that nobody sees until it bills them. Every update and delete leaves an old row version behind. In PostgreSQL a background process, autovacuum, removes versions no transaction can still see. It cannot remove anything newer than the oldest open transaction, so one transaction left open for a day stops cleanup across the database, and tables swell. There is a second, rarer failure. PostgreSQL numbers transactions with a 32-bit counter, and to reuse numbers safely it must “freeze” old rows before the counter gets about two billion transactions ahead of them. If vacuum falls far enough behind, the database stops accepting writes to protect your data, and recovery means a long manual vacuum. MySQL’s InnoDB has the same shape of problem: its undo history grows behind long transactions.

Eventually one primary may stop being enough, and the order of remedies matters. First, the cheap ones: indexes, fixing N+1, a pooler, read replicas, moving reports to their own copy. Then partitioning: splitting one huge table into pieces by time or key inside the same database, so that old data can be dropped as a whole partition instead of deleted row by row. Sharding across several databases comes last. It is a different system to run, and every query that crosses shards becomes your code’s problem. The Notion case below shows both why a team eventually does it and why they wished they had started earlier.

Five cases, and what each one was missing

None of these cases is an exotic database bug. Each is one row of the table at the top, met at a scale where it could no longer be ignored.

ACIDRain is the case to show anyone who thinks rows 13 and 14 are theoretical. In 2017 Todd Warszawski and Peter Bailis of Stanford built a tool that reads the SQL an application sends and finds where concurrent API calls could interleave badly. They ran it on twelve popular self-hosted eCommerce platforms, written in four languages and used, by their count, by over two million websites. They found and verified 22 attacks. In Magento, OpenCart and Oscar a customer could buy one gift card and spend it an unlimited number of times by sending checkout requests concurrently. The finding that matters most is the split. All 22 worked under the databases’ default isolation. But 17 of them came from transactions drawn in the wrong place, or not used at all, and those survive even the strongest isolation level, because no level can protect work that is not inside one transaction. The paper opens with a precedent: in March 2014 the Bitcoin exchange Flexcoin was emptied by thousands of simultaneous transfer requests that overdrew accounts before balances were updated, and shut down.

GoCardless shows that a migration on an empty table can still take down a busy one. In January 2015 the payments company renamed two new, empty tables and recreated their foreign keysforeign keya column pointing at another table’s rowA column whose value must match the key of a row in another table — an order’s customer id must name a customer who exists. The database refuses any row that breaks the rule, whoever writes it; across services, where no single database can check it, the rule becomes the application’s job.Full article →All terms. Adding a foreign key needs a lock on the table it points to as well, and the referenced table was one of the busiest in the system. A long-running read query happened to be holding a conflicting lock. The migration queued behind it, and every API query on that table queued behind the migration. For about fifteen seconds the API timed out. Rerun against a backup, the same migration took a few hundred milliseconds. The team’s first recommendation afterwards was lock_timeout, so that a migration gives up rather than takes the application down. PostgreSQL has since lowered the lock that adding a foreign key takes, but the queue works exactly as it did, and the same story replays with any schema change that needs an exclusive lock. That is row 15.

Where it degrades

A relational database rarely fails on its own. It is failed by habits that work on a laptop and stop working at the first real load. Almost every incident is one of these:

• Judging a query by how it feels on a development database with a thousand rows in it (rows 7–12).
• Tests that never contain a NULL, an empty set or two rows with the same timestamp (rows 1–6).
• One query per row of a list, which turns a page render into two hundred round trips (row 11).
• Read-modify-write in application code, which loses updates the moment two requests overlap (row 13).
• A rule checked in code and never stated to the database, which two concurrent requests break together (row 14).
• A network call inside an open transaction, holding locks for as long as the other system takes.
• An index added for every slow query and never removed, so every write pays for all of them.
• Migrations with no lock_timeout, run at peak because they were “instant” on stagingstagingthe rehearsal environmentA copy of production that changes are tried on before real users meet them. How much it proves depends entirely on how closely it matches production — a staging environment with a tenth of the data, no real traffic and a different config demonstrates that the code starts, and not much more.All terms (row 15).
• No alert on the oldest open transaction or on transaction ID age, until the database stops (row 16).
• Reads sent to a replica right after a write, so users cannot see their own changes.

The honest limit is that the database guarantees only what it is told. It cannot protect a rule that lives only in application code, a transaction drawn around the wrong statements, or a decision made on data read in a different transaction. ACIDRain’s seventeen scope-based attacks are the proof: the engine did exactly what it promised, and the promise did not cover the business. Isolation levels, constraints and locks are tools for stating rules to the engine. A team that states none of them gets a fast, durable store that does nothing to protect its invariants. The work is deciding which rules matter enough to state there.

The practical check takes an afternoon. Run the ten slowest queries from pg_stat_statements or the slow log through EXPLAIN ANALYZE and look for estimates that miss by a factor of ten. Search the code for a SELECT followed by an UPDATE of a value computed in between. List the business rules that span rows — balances, stock, bookings, quotas — and write down how each is protected. Check that every migration sets lock_timeout. And find the alert on the oldest open transaction; if there is none, that is the first thing to add.

When to use it

Related pages

• DevelopmentData ModelingData modeling is deciding what a system stores — the entities, what identifies each, how they relate and what must always be true — before the first table exists. The model outlives the application on top of it: frameworks are replaced, the schema is migrated, and a careless decision from week two is still costing somebody in year five. The page opens with sixteen questions to ask of every table, with the answer that holds up and what the wrong one costs later, then covers entities, keys and relationships, normal forms, types and nulls, time and history, models built for analysis, five documented cases, and where it degrades.
• DevelopmentNoSQLNoSQL is the umbrella name for databases that do not store relational tables queried with SQL: key-value, document, wide-column and graph stores. Most of them place each record on a node by a key you choose, and that key decides which queries are cheap, which are expensive and which are impossible. The page opens with sixteen things a relational database did for you and what you do instead, then covers the four families, choosing the partition key, replicas and conflicts, modelling for access patterns, running one in production, five documented cases, and where it degrades.
• DevelopmentMicroservicesMicroservices are an architecture in which one system is built as a set of small services, each deployed on its own, owning its own data and owned by one team. Independent deployment is what the style buys, and every call that now crosses a network is what it costs. The page opens with sixteen problems the network adds and the pattern that answers each, then covers the nine characteristics the term was defined by, what you need before the second service, data across services, calls between services, running many of them, five documented failures, and where it degrades.