7 ms·
The perils of UUID primary keys in SQLite
- rajnathani 3mo agoIsn’t the case with Postgres too if one doesn’t change the default primary key index for which is B+ Trees? At least Postgres supports a hash index IIRC, but I’m not sure if most developers set it to that when they use UUIDs as primary keys.
- yepyoukno 3mo agoPerils of “UUIDv4”. Everyone knows that’s what UUIDv7 was really for, and you should always convert that to binary to optimize everything.
- JSR_FDED 3mo agoSmall 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 3mo agoOh yes, I meant don’t store as an ID in its string format!
- dexterdog 3mo agoIt's just s dumb as storing dates as strings, but people still do it.
- cenamus 3mo agoBut also one of the recommended ways of doing it, as it has no native Datetime type.
- littlecranky67 3mo agoBut SQLite does not have a native datetime type so you have to use strings
- voakbasda 3mo agoYou can use an integer
- Volundr 3mo agoHow 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.
- dexterdog 3mo agoThe integer is a UTC time so it can be sorted. If you need the time zone you store than in a smaller field.
- everforward 3mo agoThis has edge cases that are infrequent but messy because it depends presumes timezone changes are transitive across time and they often aren’t. Eg I save a date 18 months in the future in US Western time at 0600. 3 months from now the US gets rid of Daylight Savings. You saved it as 1400 UTC (which was correct), which is now an hour off because the local time zone is different. Encoding it in local time fixes that, because it delays the TZ conversion until you need it and your times stay correct as long as you update tzdb like once a month.
- littlecranky67 3mo agoother comment said it already, timezone information is not saved. Easiest is just to use a string.
- themafia 3mo ago> 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 3mo agoDoesn'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? https://www.postgresql.org/docs/current/datatype-uuid.html https://www.postgresql.org/docs/current/datatype-uuid.html If you are using SQLite well I guess that doesn't work.
- dumbledorf 3mo agoWait how is sqlite doing a million inserts a second?
- kg 3mo agosqlite is really fast. I'm surprised it's only a million.
- deleted 3mo ago[deleted]
- andersmurphy 3mo agoIt's running on an M1 mac with synchronous full. Wouldn't surprise me if it's possible to get higher numbers.
- smitty1e 3mo ago':memory:' https://sqlite.org/inmemorydb.html https://sqlite.org/inmemorydb.html
- KPGv2 3mo agoExcept 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 3mo agoYes 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 3mo agoIn batches
- blopker 3mo agoUUIDs 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.
- deleted 3mo ago[deleted]
- bob1029 3mo agoI 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 3mo agoUUIDs 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 3mo agoYeah this is nice - also helps with grepping dump files.
- mamcx 3mo agoHow is this done?
- sudoshred 3mo agoStatistically 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.
- w10-1 3mo agoIsn'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 3mo agoYou 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 3mo agoOh gosh the ints v uuids debate for pks. This is worse than vim v eMacs or brackets v braces.
- wood_spirit 3mo agoIf you need (or want the convenience of) a uuid and the time of creation is not secret then use ulids eg uuid v7.
- andersmurphy 3mo agoThis 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 3mo agoMaybe 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 3mo agoUpdate 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.
- keynha 3mo agoThe reason to use it is that it skips the double lookup. A normal rowid table with a UUID primary key keeps two B-trees: the table itself keyed by the hidden rowid, and a separate index from your UUID to that rowid. A lookup by UUID walks the index to find the rowid, then walks the table to find the row. WITHOUT ROWID makes the UUID the table's key directly, so the row sits in that leaf and you walk one tree instead of two, and you don't store the UUID a second time. The tradeoff is what the benchmark is hitting. Once the table is physically ordered by the key, a random v4 scatters every insert across the tree and you pay for the page splits. A plain rowid table keeps that churn in the secondary index, which is just the key plus a rowid, while the table itself stays append-ordered. So it only really pays off when the key is something you look up directly and is roughly sequential, which is why v7 comes back near baseline.
- cropcirclbureau 3mo agoIs 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 3mo agoThat'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 3mo agoI 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 3mo agoWhy 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 3mo agoUUID as key is useful when you have a distributed system where multiple workers create items independently
- victorbjorklund 3mo agoBecause another app can then create the id and add it to the db later.
- deleted 3mo ago[deleted]
- jdthedisciple 3mo agoSo 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 3mo agoIt's " WITHOUT ROWID" problem. Why would you force database to order rows on the drive according to random id?
- chromatin 3mo agoIf you had read the article, you'd have seen that UUIDv4 with Rowid was slightly slower than UUIDv7
- scotty79 3mo agoThat's unbelievable! As in "I did not believe it". I mean it's nominally true, but only because this table has almost nothing in it, so a second index on random uuid is a visible cost. If this was actual table in actual software it would hold few other indexes and many fields and uuid+rowid vs int primary key would be a rounding error. I never wished, gee, why didn't I use integer key. But so many times I wished I used uuid because eventually your data rows are going to need to have identity that is not local to this specific database instance.
- ItsBob 3mo agoMy 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.
- gvkhna 3mo agoI agree technically but in most use cases the timestamp from uuidv7 is not a security leak. Especially where you’re already sharing that data in some way or another. A default guid is unnecessary if you use uuidv7 I think (in most situations).
- ItsBob 3mo agoIt's more for performance that you shouldn't use them as PK's - If you insert a lot you'll get massive fragmentation over time. A sequential Id avoids that and still gives you a unique row. The Guid is purely for an external system to grab onto something that I can tie back to an actual row in the database but the external system does not need to know anything about the backend other than <guid>.
- kjgkjhfkjf 3mo agoThe 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 3mo agoYeah that was just a holdover from when I was playing with smaller batch sizes. It's not in the actual linked source.
- adityaathalye 3mo agoThanks 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 3mo agoAside: 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 3mo agoI'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).
- adityaathalye 3mo agoWell, I expect to never need WITHOUT ROWID. And even if such an arcane situation hits my system, WITHOUT ROWID has so many ifs and buts that I'll probably elect to eat the $$$ cost of running an un-optimised normie SQLite as far as possible. cf. https://sqlite.org/withoutrowid.html https://sqlite.org/withoutrowid.html > The WITHOUT ROWID syntax is an optimization. It provides no new capabilities. Anything that can be done using a WITHOUT ROWID table can also be done in exactly the same way, and exactly the same syntax, using an ordinary rowid table. The only advantage of a WITHOUT ROWID table is that it can sometimes use less disk space and/or perform a little faster than an ordinary rowid table. As of now, I am doing the following in my (Bitemporal data system) experiment (When will it see the light of day? Nobody knows.). All data are globally uniquely identified by a UUIDv7. However all tables have `rowid` integer primary key asc (which is just an alias for SQLite's autoincrement int id). The `rowid` is the basis for joins, and is the foreign key reference. This lets me offload some useful disambiguation work to the DB as well as have it enforce global (across data systems) record uniqueness guarantees, while retaining local (within process) query efficiency by retaining the ability to use integer rowids. While the idealised insert performance in your bench is indeed mind-boggling, the DB Schema isn't doing anything CPU-intensive during inserts (checks, constraints, triggers etc.). My schema / query pattern yields comparatively meagre throughput, but I am happy with the ballpark it has landed in, given all the work I'm making SQLite do for me on each `assert!` and `redact!`. cf. my dirty-but-useful-enough bench, with production-like record content: A poor man's napkin-mathy, append-only SQLite write/read benchmark https://gist.github.com/adityaathalye/3c8195dc70626b33c23867e31b377d4c https://gist.github.com/adityaathalye/3c8195dc70626b33c23867... Summary: ;; Okay, I think I can live with this... ;; - "facts" table: 12M+ records ;; - single process writes to it ;; - ~ 400 transactions/second ;; - append-only table, enforced via SQLite "before" triggers ;; - "now" table: ;; - updates on every assert/redact on "facts" table, via triggers ;; - currently at "limit case": for each read it is empty, or very small, because writes do back-to-back assert/redact of the same fact ;; - gets reads from two reader threads (evenly split) ;; - ~41,000 reads/second ;; - all reads are concurrent with writes (poor man's futures)
- michaelcampbell 3mo agoHow much time is `(random-uuid7-bytes)` taking?
- u1hcw9nx 3mo agoI can't believe I had to scroll down to this far to see someone making this point. Also INSERT speed instead of SELECT? Typically most time is spend in SELECT or UPDATE.
- andersmurphy 3mo agoAlthough not as prominent as insert SELECT and UPDATE both benefit from page cache locality, assuming rows that are stored near each other are often selected/updated together.
- andersmurphy 3mo agoAn insignificant amount for the comparison (why I didn't mention it), it's a fast implementation and the JVM C2 JIT has kicked in by the time the first batch has completed.
- deleted 3mo ago[deleted]
- knightops_dev 3mo ago[flagged]
- sedatk 3mo agoUUIDv7 and sequential integers are quite similar. Sequential integers disclose count and neighboring IDs while UUIDv7 discloses timestamp. Either can be a security issue in certain cases. So, UUIDv4 as a PK on a clustered index can be perfectly feasible for cases where you want to avoid disclosing stuff and row insertion performance isn’t that important.
- gvkhna 3mo agoUUIDv7 as pk is inherently the best option besides just a bigint where you really don’t need a public id. But a Url62 as a url safe public id from the pk is simple and straightforward to use and comes with few risks of leak issues. Wish postgres had native base62 encoding for url62 now that it has uuidv7 native.