151–160 of 215 posts
Earlier quoted context omitted.
A 32bit integer hash won't have locality either.
Ok. Pick a resolution where you won't get collisions and that is a native datatype.
Your solution just doesn't really make any sense. The time ordered UUID suggested in the blog post makes way more sense.
I can't think of many use cases where I would sacrifice the beauty and elegance of UUIDs to optimize access times by a millisecond or two. UUID is totally worth the cost. UUID actually performs much better than I thought based on the author's example with a COUNT query. COUNT queries aren't very efficient because typically, all records are traversed; here we're talking about a 50% slowdown on 10 million records... Ho…
It is not a matter of a couple milliseconds. The loss of locality for reads is bad, especially for data sets that don't fit into cache / RAM (while the active set would). Where it really bites you is writes, because it can trigger pretty massive write amplification. Imagine you have 128 GB index on UUID column, that's ~16M pages (8kB) and insert 1M random values. Congrats! You've probably just wrote 8GB to the WAL, b…
UUIDs belong where you can't afford that simplicity. Where you e.g. cannot coordinate the creation of your primary keys. Or where you cannot allow them to be predictable. There you pay the price.
In practice I noticed that the size of PKs and their poor locality start to play a role only after a huge basket of lower-hanging fruit has been collected. There are relatively few places where their role is dramatic.
Earlier quoted context omitted.
I feel that you don't understand what locality is about: the property of IDs that were generated closely together in time to be close together numerically. You don't get that by "hashing" the UUID (which makes no sense anyway, since you might as well just take some truncation of the UUID). In fact the whole idea behind a hash is to destroy this property of the input data. The reason the numerical locality matters is…
I thought you were talking about cache locality within the CPU. Real cache locality. If you care about the insert location, why not just add a brin index on a timestamp field and use that instead of assuming that the index is sequential.
Earlier quoted context omitted.
It is not a matter of a couple milliseconds. The loss of locality for reads is bad, especially for data sets that don't fit into cache / RAM (while the active set would). Where it really bites you is writes, because it can trigger pretty massive write amplification. Imagine you have 128 GB index on UUID column, that's ~16M pages (8kB) and insert 1M random values. Congrats! You've probably just wrote 8GB to the WAL, b…
If you want locality, speed, simplicity, etc above all, use an incremented integer and be done with it. UUIDs belong where you can't afford that simplicity. Where you e.g. cannot coordinate the creation of your primary keys. Or where you cannot allow them to be predictable. There you pay the price. In practice I noticed that the size of PKs and their poor locality start to play a role only after a huge basket of lowe…
You often don't realize you you have non-simple needs until your application is reasonably mature, and in production. If you already picked integer keys, you now either forever deal with the issues caused by not using UUIDs, or you deal with the unknown-but-non-zero pain of converting to UUIDs.
Earlier quoted context omitted.
I agree that UUIDv7 will usually be the best choice. The one downside I can think of is that it may sometimes be necessary to let people know the identity of a thing without also telling them exactly when that thing was created.
COMB UUIDs can help here.
UUID is also used to avoid leaking information about the underlying system. This includes temporal information that could be used to infer the size of the dataset for all users. If this isn’t a concern, then using a timestamp based approach as recommended in this article is a good approach. That is the default in MongoDB. If it is a concern, one approach is to use random UUIDs to give to end users but then internally…
Use hashids to avoid the leak, and as a bonus your client-facing keys will be short and easily copy-pastable.
Earlier quoted context omitted.
> sacrifice the beauty and elegance of UUIDs Out of curiosity, what makes UUIDs more elegant or beautiful than just plain old integers?
Nothing. Integers are simpler and faster. They're also incorrect about COUNTs requiring a full record scan.
Earlier quoted context omitted.
It is not a matter of a couple milliseconds. The loss of locality for reads is bad, especially for data sets that don't fit into cache / RAM (while the active set would). Where it really bites you is writes, because it can trigger pretty massive write amplification. Imagine you have 128 GB index on UUID column, that's ~16M pages (8kB) and insert 1M random values. Congrats! You've probably just wrote 8GB to the WAL, b…
this sounds horrible until you realise that a modern SSD will write that in about 1.5 sec.
I mentioned only WAL for simplicity, but it also has to modify and write out the index pages themselves, and write them out eventually. And that's going to be mostly random I/O. Flash storage is good at handling that, ofc, but if things are adding up like this ...
Not to mention you still have to copy the WAL over network to replica, or perhaps to multiple replicas. And if you have physical backups with PITR, you gotta keep all the WAL somewhere too.
> if you want to use UUID’s, try to use a sequential variant. UUID v7 is a good option. Hopefully it’s coming to PostgreSQL 17 Yeap, UUIDv7 directly addresses the issues in the article. Warmly recommended. (There is the issue that exposing ULIDs to users may 'leak' information about when things were created etc, but that is usually not a problem.)
I also like UUIDv8. It's broadly similar to v7 but the vendor is free to define how the timestamps are represented, how many bits to split between timestamps/randomness, define own packing, etc. So we've started making UUIDs that encode the current ISO8601 date+time in a human-readable format: YYYYMMDD-HHMM-VRRR-RRRR-RRRRRRRRR This is especially useful for things you have few of (no more than a couple per minute), th…
I can't think of many use cases where I would sacrifice the beauty and elegance of UUIDs to optimize access times by a millisecond or two. UUID is totally worth the cost. UUID actually performs much better than I thought based on the author's example with a COUNT query. COUNT queries aren't very efficient because typically, all records are traversed; here we're talking about a 50% slowdown on 10 million records... Ho…
Recently, I wrote a client-side wallet app for cryptocurrency use case and it required transactions to be created and signed entirely on the client-side and so the transaction UUID had to be created on the client side (before signing) and I was surprised at how elegant and simple the application's logic turned out to be (both on the front end and back end). One of the best things about UUIDs is that if your front end…