Mike Cann's avatar
Mike Cann
6 days ago

Your database is lying to you about time

Did you know that when you call Date.now() in a Convex mutation, it's not actually now?

I knew time was a bit special in Convex, but I didn't really understand why, or why it could be a problem. So I got Ian, one of the absolute GOAT engineers here at Convex and the person behind components like Workpool and Workflow, back in front of a whiteboard to explain it to me.

It turns out there are three different times at play, and if you walk through a table using the wrong one you can actually miss rows. This isn't just a Convex thing either, it's true of just about every database you use. Let's take a look.

Is time a lie in Convex?

So in a normal JavaScript function, if you call Date.now() twice you'll get two different values. Inside a Convex mutation you'll get the same value both times:

This is on purpose. Convex really cares about queries and mutations being deterministic, so that you can run them multiple times and get the same output. If Date.now() kept changing then it would be exposing how long the underlying system was taking to run.

So in some ways, as Ian puts it, we've introduced a little bit of a lie. When you call Date.now() it isn't now, it's a time that was assigned to your transaction when it started. Actions are less deterministic anyway, so in an action you do get the real time on every call:

Three times at play

So that's the first time. The second is _creationTime, the field that gets added to every document alongside its _id. It's based on that same start time, and if you insert several documents in one transaction it moves forward just a tiny bit for each one, so they show up in the order you inserted them:

Both of those come from the clock on whichever machine happens to be running your function, and not every machine in a big distributed system is perfectly in sync:

The third time is the database's own. Lots of transactions run at once, but when they commit Convex commits them one at a time, in a serial order. That has its own clock, in nanoseconds, and because there's only one committer it only ever goes forward. Ian calls this the commit time:

Out of order commits

So why does any of this matter?

Well say you have two mutations running at the same time. One starts at time 5 and takes a while, and the other starts at time 7 and is really quick. The quick one commits first, so a document with a _creationTime of 7 lands in the database before a document with a _creationTime of 5.

Now imagine you are walking through all of those documents, and you think you've processed everything up to 7:

So you say cool, from now on I only need to look for documents after 7. But then the slow mutation commits and goes "oh sorry, I'm late to the game, I actually have something at time 5", and you've missed it. It's a subtle lie, generally in the order of milliseconds, but that's enough to skip a row.

This isn't only _creationTime either. Say you have your own updatedAt field and a background job that walks through it, sending each change off to something like Algolia:

If you set that field to Date.now(), these can end up just out of order enough that when you say "I've read up to here", something lands just before where you got to. This happens in pretty much any database if you set a field to the current time, and it's why people who have run these things at scale end up debugging gaps:

Normally getting this right means reading from the database's write-ahead log, which you can't exactly do from your ORM.

Why use commit time?

So here's the grand reveal. Since Convex 1.43 you can put that commit time into your documents, order by it and walk through them with it. You get a number that always goes up, so you can walk your documents exactly once, in the order they were committed.

First you add a field with the v.commitTs() validator, and put an index on it:

1// convex/schema.ts
2import { defineSchema, defineTable } from "convex/server";
3import { v } from "convex/values";
4
5export default defineSchema({
6  users: defineTable({
7    name: v.string(),
8    updatedAt: v.commitTs(),
9  }).index("by_updatedAt", ["updatedAt"]),
10});
11

Then when you write to it you use this kinda magical ctx.db.vars.commitTs:

1await ctx.db.insert("users", {
2  name,
3  updatedAt: ctx.db.vars.commitTs,
4});
5

Super cool! I did ask the obvious question though. How can you use the commit time inside the mutation, when the mutation hasn't committed yet?

Is it a number or a new type?

Well for the rest of that transaction it's a placeholder. Convex doesn't know the value yet and doesn't want to lie to you and make a guess, so if you read that document back you get a CommitTsPlaceholder, which means you can't compare it to anything until the transaction commits:

Once the transaction commits it turns into a real value, which is a bigint as there are too many nanoseconds for a normal JavaScript number:

From then on it's just like any other field. You can read it, do maths with it and use it in indexes, so you could have an index on an organisation and then updatedAt, and walk each organisation's documents in the order they were committed.

Emma on our infrastructure team did a lot of the work on this and Ian wanted to brag on their behalf, because it's really thorough. If you return that document from your mutation, the client gets the real value even though the line before it was still a placeholder:

The one place it doesn't work right now is as an argument to a scheduled function:

I asked if this is unique to Convex and Ian says it's a really difficult thing to achieve with other databases. There isn't a natural way to say "I want a custom ordering on a field that gets filled in at commit time".

Do you need to care about this?

To be honest, probably not most of the time. It's definitely an advanced feature, and Ian says it's often a premature optimization.

Say you have a cron that deletes anything that hasn't been updated for a year. You don't need a strict order for that, as nobody is going to turn up and say "sorry, I forgot to mention this thing was updated a year and two days ago". Ian's rule of thumb is that once you're past about 5 minutes there's no real risk of something landing out of order, so if your queries are only looking further back than that, you're fine:

Where it really matters is when you are walking through a table in batches and the order matters, like building a queue. Workpool uses this, and so does a newer component called Batch Worker, which it's built on.

The other reason you might want it is deletes. If you are inserting and deleting many thousands of documents, then a query that starts from the beginning has to scan past all of those deleted documents (called tombstones) until they get cleaned up, and it gets slower each time:

With a commit timestamp you can just hold on to where you got up to, and you're guaranteed nothing will ever land behind it.

Why isn't creation time commit time?

So naturally I asked, if we'd had this from the start, would _creationTime just be the commit time? Apparently there was a bit of a discussion, and they had maybe three options.

The first is what we have, where you get a time you can read straight away. The second was the same, but secretly changing the time when it actually gets written. So you might read 5.01 inside your transaction and a later one would see 7.035. That would have been really convenient, but that first value can leak out into other documents and all sorts of other places, and now they're all wrong:

The third was to make it a placeholder. But then everywhere you use a document you'd have to check "is this a number yet or not?". So they went with the first option for pragmatic reasons. Most of the time people just want a regular number that's comparable to Date.now(), and most of the time two transactions racing doesn't really matter.

And I think having commit time as a field you opt into turns out really nicely, as you get to choose which fields use it and when.

So should you use Date.now or commit time?

If you want that nice ordering then use v.commitTs(). If it's mostly just a time you want to show to your users, like an updatedAt label, then Ian says to stick with Date.now(), as you can pass it straight into a Date and you don't have to deal with nanoseconds:

You aren't stuck with that decision either. Ian has had to do this for Workpool, and you can use a union so the field accepts both, then start writing commit timestamps from then on:

1updatedAt: v.union(v.commitTs(), v.number()),
2

There's a slightly awkward time where you have two numbering systems in there, milliseconds and nanoseconds, but that's a future Mike problem.

The snapshot timestamp

There was one more thing on the way when we filmed this, and it has since shipped in Convex 1.47. This one is even more subtle, so stick with me.

Every transaction reads from a snapshot of the database, and ctx.meta.getSnapshotTs() gives you that snapshot's timestamp, on the same clock as commit time.

Say your job has processed up to commit time 5 and wants everything after that. If you read from 5 to the end of time, and anything gets inserted while your transaction is running, Convex says "sorry, something has changed since you started" and has to rerun your transaction. That's an OCC:

So instead you read from where you got to up until your snapshot. Nothing will ever be written at or below that timestamp, so you don't conflict with anything, and next time you carry on from there:

1export const processNewUpdates = mutation({
2  args: { after: v.int64() },
3  handler: async (ctx, { after }) => {
4    const upTo = ctx.meta.getSnapshotTs();
5    const users = await ctx.db
6      .query("users")
7      .withIndex("by_updatedAt", (q) =>
8        q.gt("updatedAt", after).lte("updatedAt", upTo),
9      )
10      .collect();
11
12    // do something with those users, like sending them to your search index
13
14    return { users, upTo };
15  },
16});
17

So here the line is your snapshot. You only read up to it, which means that new commit at 8 doesn't bother you:

Noice! You want to keep this one in commit time land tho, as the docs say not to compare it to _creationTime or Date.now().

To be fair, most people probably aren't going to need this. It's more likely to get wrapped up inside components, and you'll just use the component.

Conclusion

So just to recap: there are three times. Date.now() is when your transaction started, _creationTime is based on that too, and neither of them follows the order things actually committed in, which can make you miss rows if you walk through a table with them. Commit time does follow that order, and now you can put it in your documents.

I think what I like most is that Convex gives you the regular number that most people want, and then this is there when you really need it. I knew time was a bit special in Convex, but this kinda blew my mind.

A massive thanks to Ian, and to Emma and Jamie for popping in. If you liked this one, I did a similar whiteboard session with Nipunn on database backups 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