SQL

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, looks at statistics it keeps about each table — how many rows, how many distinct values in a column, how they are distributed — and chooses among several possible execution strategies the one it estimates will be cheapest. The same statement can therefore run one way today and a different way next month, because the data changed underneath it.

What makes one route so much cheaper than another is almost always an index. An index is a second structure, kept in sorted order, mapping the values of one or more columns to the rows that hold them — usually a B-tree, which is a shallow tree the engine can descend in a handful of steps regardless of table size. Finding one row among a million costs about twenty comparisons through an index and a million comparisons without one. That is the entire difference between a query that answers in a millisecond and the same query that answers in a second.

Indexes are not free and they are not automatic, and four properties of them explain most of the surprises. They cost write time, because every insert and update has to maintain every index on the table. Order matters in a composite index: an index on (customer_id, created_at) helps a query filtering on customer_id alone, and does nothing for one filtering on created_at alone — it is sorted by the first column first, exactly like a phone book sorted by surname then first name. Wrapping the column in a function usually disables the index, because the index stores the column, not the function of it. And an index that contains every column a query needs lets the engine answer from the index alone without touching the table at all.

What a transaction actually promises

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

Isolation is where the useful detail lives, because full isolation is expensive and every engine ships with something weaker by default. The levels are defined by which anomalies they permit, not by how they are implemented, and knowing the anomaly is what lets you recognise the bug in your own product. A dirty read sees another transaction's uncommitted work. A non-repeatable read gets two different answers to the same query inside one transaction because somebody committed in between. A phantom read gets a different set of rows for the same condition, because rows were inserted. And write skew is the one that catches experienced teams: two transactions each read a valid state, each make a change that is fine on its own, and together they break a rule neither one violated alone.

One operational rule follows from all of this: keep transactions short. An open transaction holds its locks and, on engines that keep multiple row versions, prevents the cleanup of every version created since it started — so a transaction left open for ten minutes can bloat a table and block unrelated work. 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, and a provider that never answers becomes an outage in a table you were not thinking about.

Constraints are not documentation

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 client open: each of these writes without going through your validation layer. A constraint in the database is the only rule that holds against all of them, and it holds retroactively, because the engine refuses to accept the row at all.

Use both, and let the database hold the rules that must be true of the data itself: NOT NULL where a missing value has no meaning, a foreign key where a row must refer to something that exists, a unique constraint where a duplicate is a defect, a CHECK where a value has a legal range. A nullable column is a promise that your code will handle the null everywhere, forever, and most nullable columns exist because nobody made a decision rather than because the value is genuinely optional.

Changing a schema on a live system is the part that needs a procedure rather than courage. Two rules cover most of it. First, expand and contract: add the new column, write to both, backfill, switch reads, then remove the old one — several deploys, each one safe on its own, instead of a single change that requires the code and the schema to switch at exactly the same instant. Second, know which operations take a lock that blocks writes, because on a large table that lock is an outage. Adding a nullable column without a default is usually instant; rewriting a column type is not; building an index takes a write lock unless you ask the engine to build it concurrently.

How this shows up in real delivery

The performance problem you will actually meet is not a slow query. It is a fast query executed two hundred times, once per row of a list, because an object mapper loaded the related record lazily inside a loop. The signature is a page whose latency scales with the number of items on it and a database that looks idle. Every mapper has a way to fetch the related rows in one statement; the fix is one line, and finding it is what query logging in development is for.

Connections are a resource with a hard ceiling, and it is lower than people expect — each one costs memory and, on some engines, a process. 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, and put a connection pooler in front of it when the instance count is elastic. A pool that is smaller than you assumed also explains a class of mysterious latency: the time is spent waiting for a connection, not running the query.

Read replicas are the usual answer to read load and they come with one behaviour that has to be designed for: replication is asynchronous, so a replica is a few milliseconds — occasionally a few seconds — behind. A user who saves a form and is immediately redirected to a page that reads from a replica sees their own change missing, which reads as a bug and is in fact the architecture. Route reads that must reflect a write the user just made to the primary, and let everything else go to the replicas.

Where it degrades

• Judging a query by how it feels on a development database with a thousand rows in it.
• One query per row of a list, which turns a page render into two hundred round trips.
• Read-modify-write in application code, which loses updates the moment two requests overlap.
• 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.
• Constraints left to the application, in a database that several other things also write to.
• A destructive migration deployed in one step, so a rollback of the code cannot restore the column.
• Reads sent to a replica on the path right after a write, so users cannot see their own changes.

When to use it

Related pages

• DevelopmentData ModelingA data model is a set of decisions about what must be true, written down once and enforced for as long as the data exists. It outlives the application on top of it — frameworks get replaced, the schema gets migrated — so a decision made carelessly in week two is still costing somebody in year five. This page covers entities and keys, what each normal form actually prevents, why types and nulls are modelling choices rather than technical details, and how to record time without losing history.
• DevelopmentNoSQLNoSQL is an umbrella term that names four different kinds of database by what they are not, which is why the choice is so often made badly. What they share is more useful than what they lack: data is placed on a node by a key you choose, so that key decides which queries are cheap, which are expensive and which are impossible. This page covers the four families, what a partition key really commits you to, what "eventually consistent" means for your users, and when a relational database is still the right answer.
• DevelopmentMicroservicesMicroservices are an architecture in which a system is built as a set of services that can each be deployed on their own. The defining property is independent deployability, not size — a service is the right size when one team can own it end to end. Everything good about the style follows from that independence, and everything hard about it follows from the fact that the calls between services now cross a network.