Database

There was a time when a program wrote whatever it needed to remember straight into a file. An order came in and a line got appended to the orders file; someone signed up and a line got appended to the members file. Writing was the easy part. Everything after that was the problem.

First, finding anything took time. A file has no ordering, so pulling one specific order meant reading from the top to the bottom. A million lines meant reading a million lines. Second, when two people edited the same file at once, whoever saved last overwrote the other person's changes completely. Third, the same fact sat in several files. If a member's name lived in the members file and again in the orders file, an update could touch one and miss the other, and from that point nobody could say which copy was right. Fourth, if the program died mid-write, a half-written line stayed behind. That's how money gets taken with no order record to show for it.

Pagedeck lived through a miniature version of this for the first six months. Contract details for enterprise accounts sat in one spreadsheet that Sofia and Grace both edited. One morning twenty renewal dates Sofia had corrected were back at their old values. Grace had hit save in a tab she'd opened before the edits. Sorting out which values were right took two days, and two contracts close to expiry were nearly missed.

A database solves those four things once, in one place, instead of in every program separately. The database decides where data goes and in what shape; the program only states what it wants. Lookups run off an index built in advance, edits arriving at the same moment get lined up and applied one at a time, and work cut off halfway is rolled back as though it never happened. Finding one record out of several million in a hundredth of a second stops depending on how good the programmer was.

The terms under this one fall into eight groups. Which engine you pick is kinds, and how you talk to the one you picked is querying. How the inside is laid out is structure, and squeezing speed out of that layout is performance. Keeping values from being left halfway is consistency, and looking after the thing while it runs is operations. The last two are tooling, meaning how code deals with the database, and security, meaning who is allowed to see what.

Kinds covers what you choose to run, and there are four, divided by the shape the data is stored in.

1. Relational database (RDB) — stores data in tables with rows and columns, and links tables together with values that point at each other. Members live in one table, orders in another, and each order row carries a member number. It's the default for business systems, so most projects start here.
2. NoSQL — a family that doesn't require the shape of a table to be fixed in advance. Records can carry different fields from each other, and adding a new field doesn't mean altering a structure. In exchange, tying records to one another is left to the program.
3. Key-value store — hangs one value on one key and retrieves it by that key only. The simplicity makes it fast, and it's used to park things like login sessions or frequently recomputed results.
4. Vector database — stores the meaning of text as a list of numbers and finds entries that are close in meaning rather than close in spelling. It's what lets "how do I get a refund" and "I want my money back" come back as the same question.

Querying covers how you get stored data back out. Structured Query Language (SQL) is the standard language for telling a relational database what you want. What separates it from other languages is that you write what you want, not how to find it. Ask for members who signed up last month and have never paid, and the database picks which table to read first and in what order to match them up. That's why the same sentence keeps working when the data grows a hundredfold.

Structure covers how the inside is laid out, and there are six terms in it.

1. Schema — the design document that says which tables exist, which columns each one has, and what is allowed in each column. Declare that the signup column takes dates only and stray text can never get in at all.
2. Table — the actual storage unit holding data in rows and columns. One row of the members table is one member; one column is one field such as a name or a signup date.
3. Primary key — the column that points at exactly one row. Names can't do it because two people can share one, so the usual answer is a separate number that never repeats.
4. Foreign key — a column that points at a row in another table. Attach the member number column on the orders table to the members table and the database itself blocks an order from existing for a member who doesn't.
5. Normalization — the design of splitting tables so the same fact is never written in two places. Instead of repeating a company name on every order row, the name lives once in a company table and the orders point at it by number. When the company renames itself, one row changes.
6. Denormalization — the design of allowing duplication on purpose for speed. If one screen has to join six tables, a few frequently used values get copied into a single table ahead of time. The cost is that the copies have to be updated whenever the original changes.

Performance covers pulling speed out of the same data. A database index works like the index at the back of a book. Instead of reading the body from the start you check the index for a page number, and a query that used to scan a million rows reads a handful. It isn't free: an index takes space and has to be updated every time a row is inserted, so you only put one on columns that get searched often. The N+1 problem is the opposite landmark. You fetch a list of 100 items, then ask for each item's detail separately, so 101 queries go out where fetching them together takes 2.

Consistency covers keeping values from being left in a halfway state. A transaction bundles several operations so that either all of them happen or none of them do. Tie the subtraction of ten thousand won from a balance to the writing of a payment record, and a server dying between the two never leaves money missing with no record of it. ACID is the name for the four properties such a bundle has to hold: atomicity, meaning all or nothing; consistency, meaning it never ends in a state the rules forbid; isolation, meaning one bundle doesn't wander into another; and durability, meaning that once the database says it's done, a power cut doesn't take it back.

Operations covers looking after a running system, and there are three terms.

1. Migration — moving the structure, such as adding a column or renaming one. Deploying code replaces the old version outright, but data stays where it is, so the procedure for moving a million existing rows into the new shape has to be written too.
2. Replication — keeping copies of the same data on several servers. Service continues when one machine dies, and read-only requests can be sent to a copy to take load off the original.
3. Sharding — cutting data into pieces spread across several servers. It comes out when the data no longer fits on one machine, and the cost is that questions spanning several pieces become hard to answer.

Tooling covers how code deals with a database. Object-relational mapping (ORM) connects objects in code to tables, so you read and write as though you were handling ordinary code rather than writing query text. Less typing, but no visibility into which query actually goes out, and this is where the N+1 problem grows most often.

Security covers who gets to see what. Row level security attaches permissions to individual rows rather than to a whole table. Two people can read the same documents table and someone at company A sees only company A's documents, because the database filters it. If the program forgets a condition, the database blocks it a second time.

The eight groups usually arrive in that order. Choosing a kind, laying out structure and writing queries goes quietly; performance shows up when usage grows, and consistency and security follow once money or permissions are involved. Operations is the most careful work, because it happens while everything is already running.

"Should this go in the database, or is a file fine?"

The real question is whether anyone will search it by condition later or edit it at the same time as someone else. If neither, a file is cheaper.

"That screen is slow on the database side. One query is scanning the whole thing every time."

The bottleneck is how stored data is being fetched, not the application code.

"This release has a structure change, so the migration has to run before the app comes up."

Flip the order and the new code looks for a column that doesn't exist yet, and the service stops.

At Pagedeck this word came up most in the week complaints piled up about the document list screen taking eight seconds. Theo looked into it and found two causes. After fetching 100 items, the code asked for each author's name separately, so 101 queries went out, and the column used to filter by company had no index, so each of those 101 queries scanned everything.

Two fixes went in: author names were fetched in one batch, and an index was added on the company number column. Eight seconds became 0.3 seconds. What Mia asked was why it had only surfaced now. Free plan accounts hold a few dozen documents, so the slowness never showed until enterprise accounts with tens of thousands of documents came on.

The most common mistake is seeing a database as nothing more than a large box that holds data. Putting values into tables of rows and columns and taking them back out looks like a spreadsheet, so the assumption is natural. Putting values in and pulling them out is genuinely one of the easier things a database does. The hard parts are sequencing hundreds of requests that want to edit the same row, refusing to leave a half-applied state behind, and picking the one row matching a condition out of tens of millions without reading them all. Treat it as a box and design gets skipped. Columns get bolted on as needs arise and the same fact goes into several tables, until the customer base grows and nobody can say which value is correct. Fixing it then means redesigning tables and moving accumulated data without taking the service down, which turns a day of design work into a job of several weeks.

The belief that a slow database can be fixed by renting a bigger server is common too. Raising the specification does help for a while, which makes the idea feel confirmed. But most slow queries are slow because of how much they read, not because the machine ran out of capacity. A query scanning a whole table without an index gets twice as fast on a server twice the size, while adding an index can cut the rows read by a factor of several hundred. Paying for capacity leaves the cause in place and raises the monthly bill. Data keeps accumulating, so the larger machine falls short again eventually, and by then the fix is harder than it was.