UUIDs are so much better than autoincrementing ids and it's not even close

https://www.ard.ninja/blog/2023-02-26-uuids-over-autoincrementing-ids-are-so-much-better-its-not-even-close/

by ardme • 3 years ago

4 4 3 years ago

idiocrat2• 3 years ago

(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.

cratermoon2• 3 years ago

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...

idiocrat2• 3 years ago

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.

UUIDs are so much better than autoincrementing ids and it's not even close

Related Stories