Structured Query Language

Getting stored data back out used to require knowing how it was stored. The program said which file to read from, starting at which offset, and in what order to match things up. That arrangement carries two costs, and both of them grow with the size of the system.

The first is that every change to the storage layout means rewriting all the retrieval code. At Pagedeck, adding one column to the document table meant editing ten query sites alongside it, none of which were wrong before the change.

The second is that performance depends on a person's judgment. When a question arrived about members who signed up last month without a payment, Theo decided himself to read the member table first and match payments against it. At ten thousand members that order was fast. At a million, the same code took minutes. Which order wins changes as data grows, and with the order written into the code, a person has to go back and choose again — usually after the slowness has already been reported.

What this language inverted is the direction of the instruction. Instead of writing how to find something, you write what you want. Ask for last month's signups with no payment record and the database picks which table to read first, and by which method, based on how large the data currently is. The statement stays the same while the plan behind it moves.

Five things.

1. It delegates execution. The statement carries the desired result, and the database builds a plan from its own internal statistics. The same statement stands whether there are ten thousand members or a million, and when the better order changes, the database changes it without anyone editing the query.
2. It joins scattered tables. Member details and payment records live in different places, connected only by an id, and pulling them together into one result fits in a single statement. That is what makes a design with several tables practical.
3. It aggregates in one line. Getting payment counts and totals per plan takes loops and temporary variables in a program; here you state only what to group by and what to count, and the result comes back as rows.
4. It travels between products. Because there is a standard, most basic statements survive a change of database product. Date functions and performance settings still differ per product, so a move is not free, just far cheaper than rewriting the retrieval logic.
5. People can read it. The syntax sits close to English word order, so someone who is not an engineer picks up the basic shapes in a few days. Self-serve analytics — an environment where people read data without going through an analyst — rests on that property.

There are things to watch. Several statements produce the same result at very different speeds. Put a condition on a column with no index, the prepared lookup structure, and the database scans the whole table. Run a heavy query against the database customers are using and the service slows with it, so aggregation runs on a replicated copy instead.

Nothing replaces it outright. Three things share the work.

1. Object-relational mapping. Treat data as objects in code and the tool generates statements for you. Reading and writing one record is easier this way and produces fewer mistakes. Join four tables and aggregate, though, and the generated statement is often slow, so the statement ends up being written by hand anyway.
2. Query styles from NoSQL systems, which are not tied to a table layout. They suit pulling one whole document at a time, with the program doing any joining. Data whose shape changes often sits better there, since there is no layout to migrate when a field appears.
3. Drag-and-drop analysis tools. The user writes no statement, but the tool composes one internally and sends it. That is a shell over this language rather than an alternative to it, which matters when the tool produces a number nobody can explain.

One thing looks like an alternative and is not. Tools that answer a question asked in plain language remove the effort of writing a statement, but checking whether the answer is right means reading the statement that was generated. When conditions stack up, what got excluded exists only in that statement, so anyone about to report a number still has to be able to read one.

The choice comes down to the shape of the data. Many relationships between tables and frequent aggregation favour this language; records with differing fields that get read whole favour the other family.

"Can you share the query behind that number? I want to check the conditions."

Verify what was counted, as a statement, rather than the result.

"That's running without an index, which is why it takes three minutes."

The problem is the database-side preparation rather than the statement.

"Let's run aggregations on the replica. On production it slows everything down."

Keep heavy queries off the database customers are using.

At Pagedeck, Theo saves frequently used queries, names them, and shares them. The point is to stop two people counting the same number differently, and a named query is documentation as much as a shortcut. With two versions of the statement that counts signups in circulation, the figures stop matching in meetings.

Mia writes queries too, for conditions the screens do not offer. Statements that change data are blocked by permissions and only reading is open. A statement meant only to look at something dropping a table is a real event, not a hypothetical one.

Using this language and relational databases as the same word is common. They almost always appear together, so it is a natural mistake. But one is a language and the other is a system that stores and returns data. Several products accept the same statement, and layers that accept something close to it sit on top of non-tabular stores too. Treat them as one thing and switching stores looks like having to learn querying from scratch, which makes the storage decision feel larger than it is.

The second is that it belongs to engineers. It looks like code, so it reads that way. But read-only statements are simple enough to pick up in a few days. When a marketer counts signups per campaign and a product manager checks a condition the screens do not expose, the two-day round trip on every question disappears. What has to be restricted is permissions, not learning. Read access to a replica costs nothing and removes a whole category of waiting.