[matthew16550]

Using UUIDv4 as primary key has unexpected downsides because data locality matters in surprising places [1].

A UUIDv7 primary key seems to reduce / eliminate those problems.

If there is also an indexed UUIDv4 column for external id, I suspect it would not be used as often as the primary key index so would not cancel out the performance improvements of UUIDv7.

[1] https://www.cybertec-postgresql.com/en/unexpected-downsides-...

[AdieuToLogic]

> Using UUIDv4 as primary key has unexpected downsides because data locality matters in surprising places.

Very true, as detailed by the link you kindly provided. Which is why a technique I have found useful is to have both an internal `id` PK `serial`[0] column (never externalized to other processes) and another column with a unique constraint having a UUIDv4 value, such as `external_id`, explicitly for providing identifiers to out-of-process collaborators.

0 - https://www.postgresql.org/docs/current/datatype-numeric.htm...

[crazygringo]

> I suspect it would not be used as often as the primary key index

That doesn't matter because it's the creation of the index entry that matters, not how often it's used for lookup. The lookup cost is the same anyways.

[matthew16550]

The page I linked shows uses after creation where the cost can be different.