Why a file is not enough, and what a database actually gives you

Your app needs to remember something, so you save it to a file. That works, and for a long time it is enough.

Then one day you save two things and only one of them comes back. The file is still there. It is just missing something you know you put in it.

This is what actually happened, why a file cannot be patched into a database, and what it costs you that there is only ever one of it.

The short version

• The moment you run a second copy of your app, a file has no way to stop two writers from destroying each other’s work.
• Fixing that properly means putting one program in front of the file to decide who goes first. That program is a database. You do not really choose one, you run out of ways to avoid one.
• On top of taking turns it gives you three things you would otherwise have to invent: finding one row among millions without reading them all, grouping changes so they all happen or none do, and knowing what it promised you when the power fails.
• All of that works because there is exactly one of it, and that is also the bill. Scale your app and you exhaust its connection limit long before you exhaust the machine.
• You cannot fix that by running two databases. Nobody sells that, and the reason is worth understanding.

Why a file stops working

Your app got busy, so you ran a second copy. That is normal and it worked. But each copy had its own file. You saved to one and then read from the other.

So you fix it. One file, in one place, both copies using it. The disagreement goes away, because there is only one file left to disagree with.

Then two people save at the same moment. Both copies open the file, both write, and the file ends up holding something neither of them sent. One of those two writes should have won. Neither did.

A file will let you do this. It does not know that two things are writing. It does not know there is a second one at all.

So they need to take turns, and a file cannot make them. A file has no opinion about who opens it. Somebody has to stand in front of it and decide who goes first, so you write that: one program, sitting in front of the file, and nothing else allowed to touch it.

That program is a database. You did not decide to use one. You ran out of ways to avoid it.

Everything your app needs to remember between one request and the next now lives in one place. That has a name: state. There is exactly one of it, and not because nobody has got round to fixing that. That is the whole point. Two copies of your app can agree on what is true only if there is one place that decides.

Three more things you would have to invent

Taking turns is all that the thing you just built does, and it is not enough.

Finding one row among millions

Imagine your list is not four items but four million. Somebody asks for one of them, and your program opens the file and reads from the top until it finds it. On a good day it is the first row. On a bad day it reads four million rows to answer one question.

It keeps a second, smaller thing beside your data, and that thing is sorted. Think of it as signposts: you read one sign, it tells you which way to go, and you never look at the rest.

All of it, or none of it

Someone buys the last laptop. Two things have to change: the shop has one laptop less, and that person now has an order. Stop it in the middle and the laptop is gone from the shop with no order to show for it. Nobody bought anything, and a laptop just disappeared. Nothing failed.

So a database lets you draw a line around several changes: all of them, or none of them. If anything goes wrong before the end, everything goes back to how it was.

Knowing what it actually promised

Your database says saved. Half a second later the power goes. Was it? Your change is probably still in memory, and memory does not survive a power cut.

What it costs that there is only one of it

When an app gets busy you run more copies. That is what the first and second episodes were about, so you already have three. Then it gets busy again and you do the same thing. Three copies become five, the deploy works, and the site goes down.

Not the app. Not the deploy. The database ran out of connections.

A connection sounds like a wire. It is not. When your app connects to Postgres, the database starts a whole separate program just for you. It stays running until you disconnect, it holds your settings and your half-finished work, and it costs the same whether you are talking or not.

Raising the limit is the obvious fix and it is a trap. Those are real programs on one real machine with a fixed number of cores. Past about twice your core count, adding more does not get more work done. They wait on each other, and the machine gets less done than before.

The fix is to stop every copy from keeping its own.

Why you cannot just run two

The connections are fixed, but look at the picture. There is still one database and everything points at it. So run two.

Two databases are the same for about a second. Then someone buys a laptop on the left one and someone else buys the same laptop on the right one. Both say yes. You have sold one laptop twice. Same failure as the two files, except this time it costs money.

In October 2018, GitHub had this happen for forty-three seconds. Network equipment was being replaced, routine work, and for forty-three seconds the connection between two data centres was cut. Then it came back on its own.

In those forty-three seconds the system did exactly what it was designed to do. The other side could not see the first one, decided it must be gone, and promoted its own database to be in charge. Nobody made a mistake. The failover worked.

GitHub did not work properly for the next twenty-four hours. Not one second of that day was spent bringing servers back, because the servers were fine. The whole day went on deciding which of the two databases was true. They could have thrown those 950 writes away and been back in minutes. They chose the day instead.

The three ways out, and what each costs

Notice what none of them do. Not one gives you two places to write the same row. That was never for sale.

Here is what I would actually do, and you can disagree with me. Do not build any of it. Your database provider has all three behind a button. Click the button.

Your app exists to solve one problem. If you spend your time solving every problem you will do all of them badly, and nobody is going to use your product because you wrote your own caching layer. But understand what is behind the button anyway. Not so you can build it. So that when something breaks, you know where to look.

The one I got wrong

Mine is the smallest possible example. First year of college. The page tries to load, waits, and then says it cannot log me in.

My database was on MongoDB Atlas, which only lets in addresses you have put on a list, and mine was not on it. Everything was broken, not only login. Login is just the first door, so it is the only broken thing you see.

You can rent all of this. You cannot rent understanding it.

What is actually under everything

One program that owns your data and decides who touches it and when. Everything else here was the price of there being one of it.

Next: a query that worked for two years and then did not, with nothing about it changed.