91–100 of 215 posts
Earlier quoted context omitted.
They suggest to use UUID v7 at the end for more sequential keys. Maybe that's the new part since v7 is relatively recent.
MSSQL has had sequential GUIDs for over a decade to solve this very problem, surprised it wasn't done sooner.
Earlier quoted context omitted.
Their uniqueness of course. > To be able to generate keys independently of the database > To move sets of related records between different databases without having to deal with renumbering everything
And adding greppability of logs in debugging situations. You can just search logs in any systems of yours with the UUID and find exact hits you are dealing with. Whereas with integers you will get all kinds of hits
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…
> I can't think of many use cases where I would sacrifice the beauty and elegance of UUIDs... I find auto-incrementing IDs far more elegant in many ways. They are much easier to make sense of for users, they give you some loose metadata (ordering, sometimes a rough time range) which can be handy in debugging stuff. At my previous workplace most things were auto-incrementing IDs, and while we got bitten by them a few…
Not to mention the distributed database situation.
In fact I like that enough that I sometimes use ULIDs for secondary unique keys just so I have something to log, even if the primary key is numeric.
It's nice that you can store ULIDs in Postgres natively as UUIDs, and it's really nice to have a timestamp embedded in the ULID if you ever need it... but it's also really tempting to use it in public-facing stuff and thus leak your creation timestamp.
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…
At scale you’re probably sharded across multiple DBs and you’re already operating through replicas. Point being you’re less likely to hit a warm cache as you scale up anyway as your application layer gets load balanced to different DB endpoints.
Auto-incrementing IDs simply don't scale beyond a single host so I don't see how they can be good for scalability. Too many devs conflate raw performance with scalability. They are not the same at all - In fact, high scalability often incurs a performance overhead.
> 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.)
So we've started making UUIDs that encode the current ISO8601 date+time in a human-readable format:
YYYYMMDD-HHMM-VRRR-RRRR-RRRRRRRRR
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…
At scale you’re probably sharded across multiple DBs and you’re already operating through replicas. Point being you’re less likely to hit a warm cache as you scale up anyway as your application layer gets load balanced to different DB endpoints.
So with high enough concurrent load it's effectively all random IO and that's the primary thing worth optimizing for.
Earlier quoted context omitted.
“5” might be the key for a thousand different records in your database. A uuid is a key for exactly one. Integer keys permit wrong joins to have the appearance of working.
[dead]
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?
“5” might be the key for a thousand different records in your database. A uuid is a key for exactly one. Integer keys permit wrong joins to have the appearance of working.
In a lot of cases that can be a pain due to overlapping integer primary keys and large parent-child table sets.
Earlier quoted context omitted.
> The moral of the story is that data locality matters, and it can pop up in the most surprising of places. Using random is typically the worst thing you can do for locality, so if you want to use UUID’s, try to use a sequential variant. UUID v7 is a good option The author isn't suggesting abandoning UUIDs, he is suggesting using something like UUIDv7 which preserves locality. For most use-cases, this seems like a ve…
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.