Not sortable. Takes a lot of space. Table relationships are annoying. Etc.
What we do instead is have a secondary UUID key and keep Bigint as primary keys. Then use the UUID column in the external context instead.
UUIDs are fine for 99.99999% of the time in your own domain.
Don’t expect universal uniqueness across all domains.
UUIDs are sortable, but don’t give you creation-order sorting (of course, its abusing bigint PKs to rely on them for that, too.) If you want creation-order sorting, storing a creation timestamp and sorting on that works, and I’ve never had a db that had a business requirement for creation order sorting and didn’t also have one for actual creation time.
Maybe concurrent inserts to multiple data stores so you don't need to wait for an initial ID from the database. You'd have to trust the client to give you a “good” UUID as well as the normal distributed problems of one of the RPCs failing. I know both Twitter (called Snowflake iirc) and Google have unique ID services for this use case.
If I have a set of linked records in a relational schema representing a complex object, I can create them all with one round trip rather than multiple.
Also, large numbers of clients can insert in that way without contention, whereas if you use a sequence generator for PKs, it becomes a resource around which there is contention when creating rows.
> You’d have to trust the client to give you a “good” UUID
Sure, this lets you scale, e.g., backend service instances (which are db clients) without (as much as otherwise) contention in the db layer, its not usually something you would do with external, untrusted clients.
> I know both Twitter (called Snowflake iirc) and Google have unique ID services for this use case.
Snowflake was one of the inspirations for the newer (draft) UUID versions [0] (though, unlike them, it had a design constraint of fitting into 64 instead of 128 bits.)
[0] https://www.ietf.org/archive/id/draft-peabody-dispatch-new-u...
I see. I'd lean towards using common table expressions. The first insert statement returns the primary key and other inserts can depend on the key. I do understand it's not a panacea and composing queries can be problematic.
> a sequence generator for PKs, it becomes a resource around which there is contention
As I understand, Postgres sequences don't block which leads to a different problem [1].
That's not correct; its trivial to come up with an ordering, and I don't know of a database in practice that doesn't permit sorting on a UUID.
> Table relationships are annoying
… in SQL,
user_id REFERENCES users
> Takes a lot of space
Yes … but also no. It's 16 B vs. a serial's 4 B, I grant, but compared to a varchar, it's immaterial. (And particular in comparison to the number of times I see people use a varchar for an enum…) Certainly there could be a case where a row is wide b/c of UUIDs, but in practice, rows are wide either because of the data, or because of poor design.
When do you need primary keys?
a better reason and justification to not use UUIDs is that they take up needless space if you're space constrained. they also have worse performance
The clustered index is actually the physical order of the data stored on disk, so any new guid causes tremendous amounts of churn.
Not sure how modern this concern is, but I would guess a LOT of SASS companies have to consider this.
Quick edit: sequential GUIDs are obviously a thing and I believe alleviate a lot of these concerns. I do not believe that was an option in sql server 2008 r2 (a version that lived a LONG time).
The performance impact of randomly distributed keys on any ordered index on that key is certainly real though, so I agree with you there.
Where date > y or (date eq y and id > x) order by date, id
This can't be completely fulfilled by an index. It'll have to sort/merge somevresults in memory, very CPU expensive.
More examples here https://www.mixmax.com/engineering/api-paging-built-the-righ...
OP called them "not sortable", which is a poor phrasing, as they're sortable. What he's likely getting at is that they're not ordered by generation time, which matters to some folks.
1. Sort by ID if you want to see things by insert order and are using serial/autoincrement for the PK.
2. Writing (or reading, or talking about) SQL queries in which the IDs are present.
I solved the first problem by using ULID at one point but in future I’d probably not use a UUID until I had a specific need for it. And as others have mentioned, if that need is about the outside world I’d probably go with an extra column.
Most RDBMSes (possibly all of the big ones?) do this by default if you opt for no ORDER BY clause, however that IS then depending on undefined behavior which is bad.
Wait, did you store them as strings instead of as a native UUID type (or any sort of 128 bit integer type)?
As with all things, there are tradeoffs that must be considered before building the system.
And an auto incrementing bigint doesn't guarantee order.
What people want is for it to be sortable by generation time, i.e., they want it to share the "larger values were generated later" property of timestamps (but without collisions) or SERIALs (but without the locking on the serial / independent generation).