[dragonwriter]

The reason to use UUIDs as primary keys is to allow creating records including primary keys outside of the database before posting them, especially in distributed systems.

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.

[Alupis]

I've become of the mindset that most records should have creation and update timestamps - even if you do not currently plan to use them. They might come in handy later, and the code to implement them in most languages/frameworks is trivial anyway.

[theptip]

These days ULID (https://github.com/ulid/spec, or any of the variants), or most recently the new UUID versions (https://www.ietf.org/archive/id/draft-peabody-dispatch-new-u...) give you creation-order sorting.

[sa46]

What's the benefit of having the primary key before the row is stored in the database?

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.

[dragonwriter]

> What’s the benefit of having the primary key before the row is stored in the database?

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...

[sa46]

> I can create them all with one round trip rather than multiple.

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].

[1]: https://news.ycombinator.com/item?id=27843084