Hacker News Reader

Comment by tticvs | Hacker News Reader

Postgres is somewhat different mainly because it doesn't use clustered index primary keys. So the row's position on disk is not related to the primary key index entry's position on disk.

Additionally using the less cryptographically secure uuid v1 can be a performance optimization since it has implicit time based sorting.

View on HN

> Additionally using the less cryptographically secure uuid v1 can be a performance optimization since it has implicit time based sorting.

Except the way the fields are laid out basically defeats the point: UUIDv1 lays a 60 bits timestamp starting from the lower 32 bits, so it only sorts within a 7 minutes (2*32 * 100ns) bucket.

Hence the proposal for UUIDv6, which lays the exact same timestamp in reverse order (starting from the "top" 32b), making it naturally sortable.

Huh, you're right. I guess I gotta message an old coworker and tell them their "perf optimization" didn't work.

Lesson learned, thanks!

Do check that they're not using a "cheating" implementation, I think some DBs have "sequential UUIDs" which are either UUIDv1 the right way up (aka uuidv6) internally, or an other scheme which yields a sequence (e.g. mssql's NEWSEQUENTIALID).

Alternatively, it's possible that they created pseudo-UUIDv1 by hand putting data in UUIDv6.

Some years back (maybe 5-10?) I remember doing a test with UUIDs on Postgres, and found no speed difference between UUIDs and integer PKs.

I don't remember the parameters of the test, however.

I've experienced some performance differences with UUIDs vs integers, but it has been minor enough to never cause any issues, even in tables with billions of records.

In my testing (years ago, don't have the data) there was no detectable difference between PKs of the uuid and bigint types in my application, but there was a difference between uuid and int (int was faster). uuid is 16 bytes, bigint is 8 bytes, int is 4 bytes, so at some scale there will be a performance difference.

Reply on news.ycombinator.com