Mike Cann's avatar
Mike Cann
4 days ago

Turns out database backups are NOT trivial

How hard can backing up a database be? You loop over every table, copy all the rows out and you're done, right?

That's pretty much what I thought anyway. So when I asked Nipunn, one of the OG engineers here at Convex, what he wanted to do an engineering deep dive on and he said backups, I was a bit surprised. I mean, surely backups are pretty simple aren't they?

Well it turns out no. If your data is changing while you copy it, you can end up with a backup of data that never actually existed at any point in time:

So in this little example Nipunn and I are in the table, then I get removed, and then Jamie gets added. There was never a moment when all three of us were in the table at once, but that's what the backup says happened.

So Nipunn sat me down at a whiteboard, put up with all my dumb questions, and we started from the naive version and worked our way up to what Convex actually does. I learnt a ton from this one, so let's take a look.

Where do we start?

We started with a really simple users table with an ID and a name. The simplest thing you could do is have a backup API endpoint that loops over all the IDs and returns the whole table as a big blob of JSON.

The first place that gets challenging is when you have a lot of data and it doesn't all fit in one response, so you paginate it. Say we take 4 rows at a time, then you call the endpoint three times to get the three pages:

To know where each page should start you need a cursor, which is just a word for where you got up to. So you pass in the cursor, get some rows back, and go round in a loop:

1let cursor = null;
2for (;;) {
3  const rows = await backup(cursor);
4  if (rows.length === 0) break;
5  save(rows);
6  cursor = rows.at(-1).id;
7}
8

Simple enough, right?

Where does this go wrong?

The problem is that those tables are being modified while you are doing this. If you have a popular project you might have lots of requests a second changing your data, and those are the projects you most want to be backing up.

So say your backup takes an hour. You're going to get the people on the early pages as they were at the start of that hour, and the people on the later pages as they were at the end of it:

Maybe that's good enough for you, but that's how you end up with me and Jamie in that backup from earlier. Nipunn calls this an unsound abstraction, because it has these gotchas that break your mental model of what a backup is. You'd assume a backup is your data at a point in time, and this isn't that.

What does a database actually store?

At this point my brain jumped ahead to the write-ahead log, and Nipunn said "bingo".

You see, the table we look at in the Convex dashboard is how we think about the data, but it's very rarely what a database stores underneath. Underneath it's usually a journal of every change that has happened:

Postgres calls this the write-ahead log and at Convex we call it the document log. If you run Convex locally you can actually crack open the SQLite file (I did a video on it) and there it is, a table called documents.

Each entry says which document changed, what the change was, and when. That "when" is a timestamp in nanoseconds that uses something called a hybrid logical clock, which sounds fancy but is basically a normal clock with a guarantee that time only ever goes up. So when a new entry gets written, its timestamp is the max of the previous entry's timestamp plus one, and the current time.

How does the log help?

So now there's a very different way to get the data out, which is to paginate over the log instead of over the IDs.

This has one really big benefit. Every point in the log is a state your database was actually in, so you don't get those views of data that never existed. It's also really easy to catch up on changes and stream them somewhere else as they happen:

The downsides tho are that you get the same row more than once (document 2 is in there as an add and then as an update), and to work out what your table looks like right now you have to replay every change since the beginning of time. If your project has been going for a couple of years that's not really feasible:

So, what do we do?

Thanks to my event sourcing days my brain went straight to snapshots, and I actually got this one right too. Noice!

You take a snapshot of the table at some point in the log, then to get to the current time you only have to replay the changes after that snapshot:

I asked if this is just how Convex works in general and it kind of is. When your query starts Convex picks a timestamp, and every read in that query is guaranteed to be at that timestamp:

So for a backup you read through all the IDs at your snapshot, then catch up on the log afterwards, and you're done. Sounds pretty good right?

Any other issues to worry about?

Well, keeping a snapshot around is expensive so a database only keeps a handful of them, and only for recent points in time. If you want to read the table as it was further back than that you either have to read all of the data, or start from a snapshot and work backwards through the log for each document.

So as long as your backup finishes before its snapshot goes away, this works great. But Nipunn told me they had people with so much data that the backup took hours, and for one customer it took days:

"It was a little embarrassing when that happened" was how Nipunn put it, and it sent them back to the drawing board.

What's the better algorithm?

Convex cleans up old versions of your documents with a process called retention. Or compaction, as apparently there's an ongoing holy war internally about what to call it 😅

For indexes that window is 4 minutes by default, so if you can do your read inside those 4 minutes things go great. Past that every read has to walk backwards through the log to work out what the data used to look like, and the further behind you get the longer each of those walks takes. It goes quadratic:

So just reading the users table has its problems, just reading the documents table has its problems, and reading one and then the other has its problems too. The key insight is that you do what Nipunn calls a dance, going back and forth between the two so your snapshot never gets too old.

He drew it up as a 2D diagram, with IDs going across and timestamps going down, and honestly this was my favourite bit of the whole thing. Here's the real one from the whiteboard:

Nipunn's whiteboard: the users table and document log at the top, and below them a blue diagram with IDs going across and timestamps going down, with numbered pages stepping down and to the rightNipunn's whiteboard: the users table and document log at the top, and below them a blue diagram with IDs going across and timestamps going down, with numbered pages stepping down and to the right

And here's a cleaned up version:

Pages 1 and 2 go across, reading through the table by ID as far as it can without getting close to that 4 minute limit (by default it starts catching up once it's about 30 seconds behind). Page 3 then goes down, catching up on every change made since it started, like me changing my address or Jamie being added. Once it has caught up it goes back to going across, and it keeps doing that until it reaches the end. The catch-up pages get bigger as you go along, because you're now keeping track of more people.

So you might get a row more than once, and they don't come out in timestamp order either, it's a bit of a mishmash. But when you reach the end you're guaranteed to have a consistent view of your data.

Oh and one thing I really like about this. If you took a snapshot and the backup took two hours, you'd finish with a backup of your data from two hours ago. Because this one keeps catching up, you finish with a backup of your data from right now:

It does mean you don't know what timestamp your backup will be at until it finishes, because when you start it's still in the future. Which is a bit of a mind bender, but as Nipunn says it's sort of ideal, because it'll be really up to date when you get it.

He also made a point I think is really nice about agents. This algorithm is trickier to describe but it's sound, so an agent using it can't really mess it up and doesn't need to read the source to understand how it works behind the scenes.

By the way, I asked about what happens when you delete a row, and it leaves behind what's called a tombstone. Which is a pretty morbid name but I love it. RIP Mike:

Old versions in the document log get kept for much longer than those 4 minutes (the open source backend defaults to 14 days), but they do eventually go. I asked if Postgres and MySQL do this too and Nipunn says they all have some version of this. Postgres, for example, calls it vacuuming.

What's next for backups?

This algorithm only came together a month or two before we filmed, and it powers a public endpoint called data sync that shipped in July.

Nipunn told me that for a bit they thought about gluing something together just for those big customers, but they really wanted to step back and make a solid building block, and having a public API is a really good way to force yourself to do that. Then you can build more on top of it, and so can everybody else.

You call it in a loop just like our naive version from the start, except now it's sound:

1let cursor;
2while (true) {
3  const res = await fetch(`${deploymentUrl}/api/v1/data/sync`, {
4    method: "POST",
5    headers: {
6      Authorization: `Convex ${deployKey}`,
7      "Content-Type": "application/json",
8    },
9    body: JSON.stringify({ cursor }),
10  });
11  const page = await res.json();
12
13  // apply page.truncates, then page.values, and save them with the cursor
14  await savePage(page);
15
16  cursor = page.pagination.nextCursor;
17  if (page.status.type === "upToDate") break;
18}
19

I had my agent run this against the demo deployment for one of my components, and it took 18 pages to get through 162 rows. The first 17 pages all came back with a status of snapshotting, and the last one with upToDate. That status is how you know you've got a consistent copy, as before that you might have some people and not others. It needs a Pro plan, and the docs have the details on saving each page safely.

And if you keep hold of that cursor you can come back whenever you like and carry on from where you left off (within the deployment's retention window), which is how you keep something else up to date with your Convex data.

What even is OLAP?

The big thing this unlocks is OLAP, so I got Nipunn to explain what it actually means.

There are two main types of database. OLTP (online transaction processing) is what Convex is. Think banks, where you take money out of my account and put it in yours, and it either all happens or none of it does. OLAP (online analytical processing) is for analytics over big piles of data. I did ask why it's not OTP and OAP, given online is one word, and my best guess is that "on-line" used to be two 🤷

The difference that really matters is how they store things on disk:

OLTP databases are row-oriented, so all the values for one row sit together, which is great for updating a couple of rows at once. OLAP databases are column-oriented, so all the names sit together, which makes it really quick to go through every name or every transaction. You can't store it both ways, so a really common setup is to copy your data from an OLTP database into something like Databricks, Snowflake, ClickHouse or DuckDB:

Convex has been able to do this for a while through our Fivetran connector, which is still what we recommend at scale. But little old me with my side project doesn't really want to sign up for another third party service.

So now you can point data sync at whatever you like. It's the regular price of getting data out of Convex (database reads and bandwidth), and Nipunn says he's been able to one-shot a little app that sends data to an OLAP database. He's also working on better analytics support built into Convex, which you can follow on that roadmap item.

Anything else about backups?

One more thing Nipunn wanted to point out. If you pay for a dedicated deployment, which runs on its own hardware, you also get what's called a physical backup. On a shared deployment your data sits in a database alongside other people's, so we have to iterate over your data to back it up. With your own database we can back up the whole thing at the level of the database, kind of like taking the entire documents table and putting it somewhere:

Those are faster and cheaper, but you can't download them or look through them the way you can with a normal backup. So you still want both, one so you can look at your data and the other so you can restore it.

So how do you back up your Convex app?

You don't have to build any of this yourself, as all that's needed for a normal backup is to press "Backup Now" on your deployment's backups page in the dashboard. On a Pro plan you can schedule daily or weekly ones too. If you'd rather have the command line do it, this generates an export in the same format:

1npx convex export --path ~/Downloads
2

Conclusion

So just to recap: if you just copy rows out of a live database you can get a backup of data that never existed. Reading the log fixes that but you'd be replaying everything since the beginning of time, and snapshots help until your backup takes longer than they stick around for. So you end up with this dance between the table and the log, which gets you a consistent copy that's up to date when it finishes.

I think what I like most is Nipunn's point about sound abstractions. If you get the building block right then people (and their agents) can use it without having to understand all of this, and I guess that's kind of what Convex is all about.

I went into this thinking backups were pretty simple and came out with a lot more respect for them, so a massive thanks to Nipunn for taking the time. If you liked this one, I did a similar whiteboard session with Ian on how time works in databases too.

Until next time, cheerio! 👋

All gas, no breakages

Convex is the reactive backend platform that keeps up with you and your agents. Database, functions, workflow, sync, search, file storage, and more. All TypeScript, zero glue.

Get started