71–80 of 117 posts
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.
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.
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.
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.
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.
Earlier quoted context omitted.
> 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.
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.
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…
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…
Perils of “UUIDv4”. Everyone knows that’s what UUIDv7 was really for, and you should always convert that to binary to optimize everything.
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.
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-brain…
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.
It's " WITHOUT ROWID" problem. Why would you force database to order rows on the drive according to random id?
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…