5 ms·

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

by tticvs 5y ago

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.

masklinn 5y ago
> 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.
- tticvs 5y ago
  Huh, you're right. I guess I gotta message an old coworker and tell them their "perf optimization" didn't work. Lesson learned, thanks!
  - masklinn 5y ago
    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.
GordonS 5y ago
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.
- eerikkivistik 5y ago
  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.
- sterwill 5y ago
  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.

by tticvs 5y ago

masklinn 5y ago

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

tticvs 5y ago
Huh, you're right. I guess I gotta message an old coworker and tell them their "perf optimization" didn't work. Lesson learned, thanks!
- masklinn 5y ago
  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.

GordonS 5y ago

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.

eerikkivistik 5y ago
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.
sterwill 5y ago
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.