UUIDs are so much better than autoincrementing ids and it's not even close
ard.ninja(For MS SQL server)
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.
That's why you use ksuid (https://segment.com/blog/a-brief-history-of-the-uuid/) or, if you're willing to go with a draft spec you could go with the new UUIDv7 format https://www.ietf.org/archive/id/draft-ietf-uuidrev-rfc4122bi...
Interesting.
"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.
If you need to micro-optimize this way and you're 100% sure that the database engine you're using would align accesses on word boundaries, sure. Most folks will not need it.