The correlation of locality and sequential keys was a coincidence. It's unfortunate that folks rely on this coincidence for performance. Databases can be made with better algorithms now that UUIDs are common. It would be a mistake to enshrine sequential indexes as some special optimization, just to be blown away later when better algorithms get used.
Unexpected downsides of UUID keys in PostgreSQL
181–190 of 215 posts
Re: Unexpected downsides of UUID keys in PostgreSQL
#182Earlier quoted context omitted.
You can incorrectly join on any columns you want. That's not really the fault of the column types IMO, that's a problem in the layers above.
True, but you can't incorrectly join on UUID due to its anti-collision nature.
As sibling says, you can make exactly the same mistake and it not colliding just means no join - but if it's an outer join and one of many joins or in a nested query it might not be immediately obvious it's happened, or what the issue is even if it is.
Re: Unexpected downsides of UUID keys in PostgreSQL
#183Earlier quoted context omitted.
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…
The one problem with this "perfectly" sequential UUIDs is that it can easily lead to index bloat. Imagine you have such sequential UUIDs generated over a year, for example. And then you delete e.g. 99% of old data (say, everything except some records that you're required to keep for audit purposes or whatever). If there was an index, the "old" part will be 99% empty. For regular UUIDs this would be fine, because new…
Different UUID generators for different use cases! I'm also a fan of v5 for generating stable identifiers, when tracking things such as DNS names or URLs. Useful in web crawlers, host inventories, etc.
Re: Unexpected downsides of UUID keys in PostgreSQL
#184Re: Unexpected downsides of UUID keys in PostgreSQL
#185Earlier quoted context omitted.
Out of my scope, but why are UUIDs even discussed? ULIDs ~~(and i think Nanoids?)~~ don't suffer these same problems. Locality and ordering alone make me[1] think ordered ULIDs (and friends) are the only thing worth discussing. Is there some value to UUIDs over ULIDs that make these discussions largely revolve around Autoincrement vs UUIDs rather than Autoincrement vs ULIDs(and friends)? [1]: Again, totally out of my…
ULIDs have made our MySql caches breathe a sigh of relief. One place we're avoiding ULIDs (and other counters) is in publicly-facing IDs. Preferring random to help keep them unguessable. (say what you will about security-through-obscurity). So we do ULIDs for private IDs. Random UUIDs for public IDs. Seems to work well.
KSUID's are have temporal-lexicographical order plus 128 bits of entropy, which is more than UUIDv4.
Re: Unexpected downsides of UUID keys in PostgreSQL
#186Another (potentially!) significant problem with uuids is that they're inefficient to store. Compared to an 8 byte value obviously they double the storage size. But it's actually worse - if you have loosely ordered integers, even with gaps, you can compress those down to even less, like practically 1 byte on average. That makes uuids ~16x worse for disk storage, ~2x worse for memory storage (cache). That + Losing loca…
Oh yeah, this also makes pagination trivial. You can basically just give your clients a literal number indicating where they are. So good, you basically get paging APIs for free.
Re: Unexpected downsides of UUID keys in PostgreSQL
#187I’ll genuinely never understand why people benchmark in this way. They are doing a thing that is slow and inefficient in a fundamental way and then producing a blogpost about it being slow and inefficient in a much more trivial respect. If you care about performance, select count(uuid) from records; Makes absolutely no sense. You know the uuid is unique so you are deliberately selecting a value and then throwing it a…
I can't imagine that the author is saying "don't count uuids this way!" but instead is trying to explain how locality works in btree indices, using a trivial example to demonstrate it.
Re: Unexpected downsides of UUID keys in PostgreSQL
#188I’ll genuinely never understand why people benchmark in this way. They are doing a thing that is slow and inefficient in a fundamental way and then producing a blogpost about it being slow and inefficient in a much more trivial respect. If you care about performance, select count(uuid) from records; Makes absolutely no sense. You know the uuid is unique so you are deliberately selecting a value and then throwing it a…
I should add for people who are not aware, the same concept is very important to bear in mind if there is a WHERE EXISTS or WHERE NOT EXISTS subclause to your query. Something like
select
u.username as good_user
from
users u
where
not exists (
select 1 from naughty_users n where n.id = u.id
)
If you spend a while looking at explain plans for your queries you will spot common patterns like this where you can avoid a table scan often.
Re: Unexpected downsides of UUID keys in PostgreSQL
#189Have a look at ULIDs
Re: Unexpected downsides of UUID keys in PostgreSQL
#190Earlier quoted context omitted.
Everything breaks at scale. In my experience most tables don't end up with more than a few million rows and will work fine with this. If you did want to transition a large table to be UUID only, the nice thing about this approach is that you could do it with no down time. If you are using a DB that only scales writes vertically though (most DBs, including distributed DBs) then how are you actually going to scale the…
I'm not so much concerned with figuring out scaling in terms of volume as I expect to be able to handle millions of rows in a single DB and that would be an implementation detail and fine tuning. I'm more concerned about scaling in terms of complexity and keeping the system easy to reason about when more people, tech are involved. Lets say I have a -[1:N]- in two tables in a relational DB. This works fine at first ev…
I think switching this with zero downtime to do foreign key references with UUIDs will be easier than any of the pain you would deal with from having to do cross-DB joins.
What specific issues are you worried about with the integer key? Usually the issue is dupming data into something like a staging or development environment rather than a production concern. If you attempt to dump 2 datasets into one db you will have a conflict. Or if you write to an environment and then dump into you will have a conflict.