UUIDs are so much better than autoincrementing ids and it's not even close
ard.ninja
ard.ninja
If UUIDs are not sequential (i.e. random), there could be performance problems to used those as PK with an underlying clustered index (CI).
The CIs work best with sequential values.
Inserting a random value into a CI will certainly cause: (1) touching of many pages (write amplification), (2) index fragmentation, (3) page splits, (4) pages being half-full.
Furthermore, if you have other indexes defined on the table, these problems apply to these indexes also.
This of cause depends on data quality and data volume.
For the "mostly-append" fact tables, I would advise against using UUIDs as PKs with CI.
"KSUIDs are larger than UUIDs and Flake IDs, weighing in at 160 bits. They consist of a 32-bit timestamp and a 128-bit randomly generated payload."
TTTTTTTTRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRR
T-Timestamp R-Random
There are economy arguments against using this as quasi-sequential PKs.
For efficient index lookup and sorting, best is to use CPU's native register sizes. It would be great if the PK would be a DWORD (32 bits) or QWORD (64 bits), one would assume the DB engine will align the access for efficient handling of the L1-L2 caches and prevent page tearing.