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.
> 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.
> 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.
> Since workloads commonly are interested in recently inserted rows
That's only true for very specific types of applications. There's nothing general about that.
Plenty of applications grab rows from all time, and there's nothing special about the most recent ones. The most recent might also be the least popular rows, since few things reference them.
"Approval" / "Golden Master" / "Snapshot" / "Characterization" testing can be very helpful.
They all seem to be names for more or less the same idea.
The first time a test runs successfully it auto captures the output as a file. This is the "approved" output and is committed with the code or saved in whatever test system you use.
The next time the test runs, it captures the new output and auto compares it with the approved output. If identical, the test passes. If different, the test fails and a human should investigate the diff.
The technique works with many types of data:
* Plain text.
* Images of UI components / rendered web pages. This can check that your code change or a new browser version do not unexpectedly change the appearance.
* Audio files created by audio processing code.
* Large text logs from code that has no other tests. This can help when refactoring, hopefully an accidental side effect will appear as an unexpected diff.
As an asside, I've found IntelliJ very helpful in this situation as it can load many repos into one project then doing commits / pushes / branches etc across various repos at the same time just seemed to work the way I wanted without much thinking about it.
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-...