[frollogaston]

Any kind of uuid PK is quite expensive and usually not worth, because you're so frequently joining on PKs. A safe default is to use serial PKs, then have a secondary-indexed uuid4 if you wish to publicly-expose anything. Why uuid7, is the btree performance better with it than with uuid4?

[ComputerGuru]

In practice you’ll never notice the difference between joining on a bigint vs uuid for most OLTP purposes and you’ll be able to generate the uuids in your backend instead of in the db which a) can be very helpful if you’re pre-generating linked records and inserting them pipelined rather than sequentially, saving a lot of round trip traffic, b) reduces concurrency bottlenecks on the db server.

More usefully, a uuidv7 can take the place of both the id and the created_at field (if precision is sufficient), and can (depending on your security comfort) also take the place of the uuidv4 public id field.

With uuidv7 the data is naturally sorted so you don’t run into the issues with btree worst case scenarios that you would with a uuidv4 id, and you can even take it a step further and use BRIN instead of btree indexes for a massive boost (also applicable to serial ids, though).

[masklinn]

> Why uuid7, is the btree performance better with it than with uuid4?

Yes, being ordered uuid7 leads to less splitting and index bloat than the fully random uuid4. So while the lookup is pretty similar the index is in much better shape.

And if lookup is correlated with recency (which is probably the case for most record types) recent entries will be grouped together on the same pages in the uuid7 index leading to better cache presence, whereas in a uuid4 index they’ll be randomly spread over the entire index set.