131–140 of 215 posts
That's... not unexpected. That's been the known technical compromise of UUID for years, and why ULID and UUID 7 are born. And usually it is still worth it.
The knowledge is definitely out there, but almost all articles will be reaching for UUIDs, so this is understandably what most people end up with :/
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…
> sacrifice the beauty and elegance of UUIDs Out of curiosity, what makes UUIDs more elegant or beautiful than just plain old integers?
This is a must if you don't want to couple your domain logic and DB. I want to generate a record with an ID inside my domain layer without depending on the DB and doing awkward DB save - DB read.
Long story short: you should probably use a bigint unless you have a pretty good reason not to. Good reasons exist! If you do use a UUID, make sure you use a version that is time-sorted.
1: https://planetscale.com/courses/mysql-for-developers/indexes...
Earlier quoted context omitted.
The alternative is having two required-unique columns only one of which is definitionally unique. It solves some problems and introduces others.
You can use UUIDv7 or ULID for that. One value with both the timestamp and random data inside.
Earlier 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…
Postgres supports UUIDs natively
Native DB support is irrelevant to me for randomized bits unless it affects storage, sorting, paging, etc. Does it?
How can this be unexpected? Isn't this what Percona discussed for MySQL already several years ago? Or am I missing something?
I think a root cause is that there’s almost never a case for preferring UUIDv4 over ULID/UUIDv7, but we still see almost all learning material reach for the former. So people only find out once they have a table that exhibits weird/low performance and suddenly need to google for specific performance problems they’ve never encountered before.
Similarly, efficiently using indexes is also a very common thing I see people not know much about, since with SQL “everything performs well until it doesn’t” (i.e. you reach scale) :)
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…
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…
And the reason for that was to have lexicographically sortable IDs (even if not monotonic, which would require an extra server) so we didn’t have to index a timestamp column for chronological ordering. The alternative was to have a traditional integer sequence but it’s not always ideal to expose those on your API.
That's what created_at and updated_at are for surely
I was going to ask, when would you only rely on a sequential id for temporal locality, wouldn't most people be indexing and sorting on `created_at` or something equivalent?
You’d typically see this as either lower performance of a table than you expect, or higher IOPS usage of your database (which gets expensive at scale).
I ran into this exact issue when building out a graph like database in Postgres many moons ago.
It’s probably less of an issue with fast nvme sad drives now, but on mechanical drives ordering and locality are massive.