Perils of “UUIDv4”. Everyone knows that’s what UUIDv7 was really for, and you should always convert that to binary to optimize everything.
JSR_FDED•Jun 6, 2026
Small nit: uuid7 is 128 bits (16 bytes) by definition. So there’s no need to convert it to binary. It already is. Unless you’re working with a stringified version of the uuid7.
yepyoukno•Jun 6, 2026
Oh yes, I meant don’t store as an ID in its string format!
dexterdog•Jun 6, 2026
It's just s dumb as storing dates as strings, but people still do it.
cenamus•Jun 6, 2026
But also one of the recommended ways of doing it, as it has no native Datetime type.
littlecranky67•Jun 6, 2026
But SQLite does not have a native datetime type so you have to use strings
voakbasda•Jun 6, 2026
You can use an integer
Volundr•Jun 6, 2026
How do I know the time zone of an integer? Sure there are plenty of cases where one doesn't care, but there are also many cases where the original time zone is important.
littlecranky67•Jun 6, 2026
other comment said it already, timezone information is not saved. Easiest is just to use a string.
themafia•Jun 6, 2026
> and you should always convert that to binary to optimize everything
I disagree. I tried this once. Now you need a client access layer to touch the DB in any context. All your console tools no longer work well or at all. If they show up in URLs you need to deoptimize them for transport.
You give up a lot of convenience for this optimization. You should be absolutely sure your design requires it before using it.
antihero•Jun 6, 2026
Doesn't Postgres' UUID type just do this for you anyway?
Why would you store it as as str column and not the inbuilt type for this?
Except this source code is not using :memory: The linked source code has
(defonce db
(d/init-db! "db/db.db"
{:pool-size 4 :pragma {:synchronous "FULL"}}))
That's writing to disk.
andersmurphy•Jun 6, 2026
Yes it's writing to disk (on a M1 mac which has terribly slow fsync). But, because of the transaction the fsync dance is done once per batch. Each row is the id + a 50 byte data blob.
There's only one index so there's no real write amplification. The numbers will go down as you add more data and indexes.
JSR_FDED•Jun 6, 2026
In batches
blopker•Jun 6, 2026
UUIDs are way over used. There is almost always a better key to use, usually a bigint for databases. If you're making some kind of leaderless distributed data store, then maybe, but even then there are other ID sharding strategies I'd go for first depending on the constraints.
For a single database, bigints are smaller and faster, with less footguns.
UUIDs can be nice for an opaque public ID, however I'd still prefer something like a Sqid for space and usability.
bob1029•Jun 6, 2026
I am finding UUIDs help a lot if your primary schema consumer is an LLM.
Inappropriate aliasing of integer keys allows for silent errors in queries because it will actually return some result a lot of the time. A UUID is immune to this problem. The model recognizes its mistake a lot more reliably when previously non-empty tables start showing up empty after attempting a join.
JamesSwift•Jun 6, 2026
UUIDs also have a nice benefit of it being impossible to query the wrong table with one if you mixup what an FK goes to
pyuser583•Jun 6, 2026
Yeah this is nice - also helps with grepping dump files.
mamcx•Jun 6, 2026
How is this done?
sudoshred•Jun 6, 2026
Statistically impossible to inadvertently generate a collision using UUID keys. UUID is designed to be unique when generated across any computer system. Practically speaking if you have an exactly matching pair of UUIDs from disparate system you have found the exact record match. The name gives a hint "Universally unique identifier". -Not a cryptographer.
usrnm•Jun 6, 2026
It definitely is possible, just very improbable
echoangle•Jun 6, 2026
That’s probably what’s meant by statistically impossible.
nickpeterson•Jun 6, 2026
They just mean you catch incorrect joins more easily because there is usually no overlap in keys between unrelated tables. Using int, you’re usually going to have some shared values between two unrelated tables.
masklinn•Jun 6, 2026
The U means if you join the wrong table your join will always come up empty.
It does not actually make it impossible to query the wrong table it just tells you quickly when you’ve done so.
chrismorgan•Jun 6, 2026
You can achieve this with numeric sequences too, by having a consistent step and unique offset in all your sequences. For example, if you will never exceed 16 types, reserve four bits as the type discriminant. (You don’t have to use powers of two, but it may be convenient.)
All sequences use step 16.
Type A has discriminant/offset 0, yielding IDs {0, 16, 32, 48, 64, …}.
Type B has discriminant/offset 1, mapping to IDs {1, 17, 33, 49, 65, …}.
All the way up to Type P with discriminant/offset 15 and IDs {15, 31, 47, 63, 79, …}.
This is also trivially invertible so that you can determine the type from the ID.
A more common approach is to make IDs opaque strings and put a type prefix—A0, B12, P34, that kind of thing. But this way you can keep it as a number, if you wish.
crubier•Jun 6, 2026
No one ever got fired for using UUIDs
Fabricio20•Jun 6, 2026
> bigints are smaller and faster, with less footguns
But be careful!! Javascript WILL interpret your bigints as Number() and round them down because they are too big without telling you!!!
Famously seen by every snowflake user that has interacted with Javascript, quite an annoying problem.
spiffytech•Jun 6, 2026
Fortunately we're seeing more JS DB libraries offering to read large numbers as the BigInt type.
shakna•Jun 6, 2026
But frustratingly, a JS BigInt is nothing like a BigInt in any other language.
In JS - BigInt is 64bit integer.
In anything else - BigInt is a arbitrarily large integer.
anematode•Jun 6, 2026
Hm? JavaScript BigInts are arbitrary precision, and you need to use methods like BigInt.asIntN(64, a) to convert them to 64 bits
mort96•Jun 6, 2026
I hate this so much because you can’t nicely serialise a BigInt as JSON. Using a string is nicer but it
only makes sense where int64 is used as an ID, not where it’s used as a number; and you don’t wanna have to configure this per field per query.
nh2•Jun 6, 2026
JSON has arbitrary length numbers in the spec only.
mort96•Jun 6, 2026
Completely and utterly irrelevant.
sheept•Jun 6, 2026
You can serialize a BigInt by specifying a replacer:
And then you end up with strings on the other side, not numbers.
sheept•Jun 6, 2026
No you don't? The example I gave produces
{"a":9007199254740993}
not
{"a":"9007199254740993"}
mort96•Jun 6, 2026
Oh, that's much worse! The JSON string `{"a":9007199254740993}` decodes to the object `{"a":9007199254740992}` with typical JSON parsers like JavaScript's `JSON.parse`.
Etheryte•Jun 6, 2026
This is simply not true? Or maybe I misunderstand what you mean?
paulddraper•Jun 6, 2026
!!
Node.js drivers will correctly read int64 as string or bigint, not number.
E.g. pg for PostgreSQL
Maybe there’s a buggy driver but I don’t know it.
silvestrov•Jun 6, 2026
Good trick is to prefix all such keys with magic, i.e. a couple of letters that identify type type of key.
Then it will always be a string and you will be free to change the format/type of the key in the future to UUID or whatever you like.
zmj•Jun 6, 2026
Rule of thumb: if you’re not doing math with a value, it’s not a number.
Using a Feistel cipher and base 32 encoding at the boundaries of the system can help catching vibe coded edge code that attempt to decode identifiers in javascript. It also somewhat obfuscate the cardinalities and fill rate of the tables.
andersmurphy•Jun 6, 2026
Yes this matters even more if you are doing a lot of joins. Naive string UUIDs are 32 bytes (though I use binary uuid in the post which is 16) compared to 8 bytes for a 64-bit int. This matters even more with sqlite as it uses varint encoding. The upshot of all this is your indexes take up a lot less space in memory.
PUSH_AX•Jun 6, 2026
What are uuid foot guns?
w10-1•Jun 6, 2026
Isn't the solution just to use the rowid (after doing the read-id-after-insert dance)?
How much trouble does SQLite reysing rowid's actually cause?
andersmurphy•Jun 6, 2026
You don't even need to that. SQLite auto increments the ids and is a single writer (which you should be coordinating at the application level.
Regular rowids are definitely the way to go if you can use them.
pyuser583•Jun 6, 2026
Oh gosh the ints v uuids debate for pks. This is worse than vim v eMacs or brackets v braces.
wood_spirit•Jun 6, 2026
If you need (or want the convenience of) a uuid and the time of creation is not secret then use ulids eg uuid v7.
andersmurphy•Jun 6, 2026
This is actually a draft. I Wanted to add more details about how this changes with row size etc. I might get time to update it later today.
ysleepy•Jun 6, 2026
Maybe you could explain why one would use "without rowid" in the first place.
I get saving 8 bytes per row seems attractive, but the tradeoff is not explained.
andersmurphy•Jun 6, 2026
Update the article there's now a section for UUID4 with rowid. It's less bad than UUID4 without rowid but it's still about 4-6x slower than UUID7 without rowid.
cropcirclbureau•Jun 6, 2026
Is this relevant for other databases? For postgres for example, which supports concurrent writers, wouldn't sequential keys lead to contention on the page at the frontier?
andersmurphy•Jun 6, 2026
That's a good question. I don't know the answer. I will say, generally you can get higher write throughput with a single writer. Even more so if you're prepared to shard along boundaries where you don't need atomic transactions.
Contention and coordination are real killers, concurrent writes (that require coordination like postgres) often underdeliver.
ac50hz•Jun 6, 2026
I enjoy these carefully worded posts from Anders Murphy, illustrative and informative, not opinionated and preachy. Very useful, it’s great to see the process, and ofc bookmarkeable material for sharing with others.
bambax•Jun 6, 2026
Why would you use UUIDs a primary keys? Let SQLite use rowids internally (which is automatic and invisible), and have a different (indexed) column with UUID if you need that for publishing the ID somewhere.
elcomet•Jun 6, 2026
UUID as key is useful when you have a distributed system where multiple workers create items independently
victorbjorklund•Jun 6, 2026
Because another app can then create the id and add it to the db later.
jdthedisciple•Jun 6, 2026
So UUID isn't the problem but UUID v4 is, just like any random ID-scheme, correct?
UUID v7 so far seems like the best solution if you want UUID benefits and ordering.
scotty79•Jun 6, 2026
It's " WITHOUT ROWID" problem.
Why would you force database to order rows on the drive according to random id?
chromatin•Jun 6, 2026
If you had read the article, you'd have seen that UUIDv4 with Rowid was slightly slower than UUIDv7
ItsBob•Jun 6, 2026
My rule for primary keys and id's is simple: Sequential integer (or bigint) as the PK and if I need to make it public, I have a GUID (or UUID) in the row too, e.g. tbl_person would have Id (int|bigint) and person_guid as (UUID).
The Integer id is used for joins and looks ups and such but that's it. If I need to send anything to the frontend or outside of the app/DB then that's the UUID.
kjgkjhfkjf•Jun 6, 2026
The script to create the benchmark numbers appears to be inserting 100 batches, not 10. (The benchmark numbers in the table appear to be consistent with the text, so I guess the actual script used to create them was correct.)
andersmurphy•Jun 6, 2026
Yeah that was just a holdover from when I was playing with smaller batch sizes. It's not in the actual linked source.
adityaathalye•Jun 6, 2026
Thanks for the benching, Anders! So grateful for the stuff you've shared over the years. Invariably, every single post has been useful and/or educational to me.
I read this post more as an illustration of the *value* of UUIDv7 as primary key, over integer primary keys, in lieu of minimal loss of read/write performance, and marginally more data on disk bloat.
SQLite's automatic integer rowID primary key is a no-brainer, when the SQLite application is local-only, such as application storage format (mobile and desktop). Or is never intended to grow beyond a single server instance. Basically, where each SQLite file is private to a singular instance of the application.
However, if there is even an outside chance of needing to cooperate across application instances, e.g. the minimal limit case of a personal knowledge base that should seamlessly sync across a person's devices, as well as a hosted service, then a high-quality sequential random ID starts to make a lot more sense. (No-brainer arbitrary table merges / splits / remerges, de-duplication, etc.)
Random ID primary key is a bad idea period, whether it be the UU kind or the SQ kind, or any other kind. As far as my DB knowledge goes, this class of ID destroys all tree-algorithms, and we are stuck with the fact that there is no practically better way, than an appropriate tree-structure, to group and organise a meaningful amount of data, efficiently and effectively.
adityaathalye•Jun 6, 2026
Aside: Specific to SQLite...
Thanks to its oh so convenient automatic integer rowIDs, I believe one can amortise some of the other overheads of UUIDv7s for "in-between" queries, viz. indices, joins, ctes, virtual tables etc., with appropriate schema / query design.
andersmurphy•Jun 6, 2026
I've updated the article with the correct rowid alias (integer not int) so the rowid version is now 715ms. I've also added an example of rowid and a secondary index UUID4, and that also seems to be bad for performance (as although it's not a clustered index it's still random inserts into a b-tree).
15 Comments
I disagree. I tried this once. Now you need a client access layer to touch the DB in any context. All your console tools no longer work well or at all. If they show up in URLs you need to deoptimize them for transport.
You give up a lot of convenience for this optimization. You should be absolutely sure your design requires it before using it.
Why would you store it as as str column and not the inbuilt type for this?
https://www.postgresql.org/docs/current/datatype-uuid.html
If you are using SQLite well I guess that doesn't work.
https://sqlite.org/inmemorydb.html
There's only one index so there's no real write amplification. The numbers will go down as you add more data and indexes.
For a single database, bigints are smaller and faster, with less footguns.
UUIDs can be nice for an opaque public ID, however I'd still prefer something like a Sqid for space and usability.
Inappropriate aliasing of integer keys allows for silent errors in queries because it will actually return some result a lot of the time. A UUID is immune to this problem. The model recognizes its mistake a lot more reliably when previously non-empty tables start showing up empty after attempting a join.
It does not actually make it impossible to query the wrong table it just tells you quickly when you’ve done so.
All sequences use step 16.
Type A has discriminant/offset 0, yielding IDs {0, 16, 32, 48, 64, …}.
Type B has discriminant/offset 1, mapping to IDs {1, 17, 33, 49, 65, …}.
All the way up to Type P with discriminant/offset 15 and IDs {15, 31, 47, 63, 79, …}.
This is also trivially invertible so that you can determine the type from the ID.
A more common approach is to make IDs opaque strings and put a type prefix—A0, B12, P34, that kind of thing. But this way you can keep it as a number, if you wish.
But be careful!! Javascript WILL interpret your bigints as Number() and round them down because they are too big without telling you!!!
Famously seen by every snowflake user that has interacted with Javascript, quite an annoying problem.
In JS - BigInt is 64bit integer.
In anything else - BigInt is a arbitrarily large integer.
Node.js drivers will correctly read int64 as string or bigint, not number.
E.g. pg for PostgreSQL
Maybe there’s a buggy driver but I don’t know it.
Then it will always be a string and you will be free to change the format/type of the key in the future to UUID or whatever you like.
How much trouble does SQLite reysing rowid's actually cause?
Regular rowids are definitely the way to go if you can use them.
I get saving 8 bytes per row seems attractive, but the tradeoff is not explained.
Contention and coordination are real killers, concurrent writes (that require coordination like postgres) often underdeliver.
UUID v7 so far seems like the best solution if you want UUID benefits and ordering.
Why would you force database to order rows on the drive according to random id?
The Integer id is used for joins and looks ups and such but that's it. If I need to send anything to the frontend or outside of the app/DB then that's the UUID.
I read this post more as an illustration of the *value* of UUIDv7 as primary key, over integer primary keys, in lieu of minimal loss of read/write performance, and marginally more data on disk bloat.
SQLite's automatic integer rowID primary key is a no-brainer, when the SQLite application is local-only, such as application storage format (mobile and desktop). Or is never intended to grow beyond a single server instance. Basically, where each SQLite file is private to a singular instance of the application.
However, if there is even an outside chance of needing to cooperate across application instances, e.g. the minimal limit case of a personal knowledge base that should seamlessly sync across a person's devices, as well as a hosted service, then a high-quality sequential random ID starts to make a lot more sense. (No-brainer arbitrary table merges / splits / remerges, de-duplication, etc.)
Random ID primary key is a bad idea period, whether it be the UU kind or the SQ kind, or any other kind. As far as my DB knowledge goes, this class of ID destroys all tree-algorithms, and we are stuck with the fact that there is no practically better way, than an appropriate tree-structure, to group and organise a meaningful amount of data, efficiently and effectively.
Thanks to its oh so convenient automatic integer rowIDs, I believe one can amortise some of the other overheads of UUIDv7s for "in-between" queries, viz. indices, joins, ctes, virtual tables etc., with appropriate schema / query design.