4 ms·

> if you want to use UUID’s, try to use a sequential variant. UUID v7 is a good option. Hopefully it’s coming to PostgreSQL 17 Yeap, UUIDv7 directly addresses

> if you want to use UUID’s, try to use a sequential variant. UUID v7 is a good option. Hopefully it’s coming to PostgreSQL 17 Yeap, UUIDv7 directly addresses the issues in the article. Warmly recommended. (There is the issue that exposing ULIDs to users may 'leak' information about when things were created etc, but that is usually not a problem.)

rollcat 3y ago
I also like UUIDv8. It's broadly similar to v7 but the vendor is free to define how the timestamps are represented, how many bits to split between timestamps/randomness, define own packing, etc. So we've started making UUIDs that encode the current ISO8601 date+time in a human-readable format: YYYYMMDD-HHMM-VRRR-RRRR-RRRRRRRRR This is especially useful for things you have few of (no more than a couple per minute), that you regularly need to cross-reference with other systems (e.g. files on S3).
- JdeBP 3y ago
  Enough iterations in this direction, and people will come full circle to how message IDs operated on Usenet. <datetime.processID.sequencenumber@domain> for example.
- pgaddict 3y ago
  The one problem with this "perfectly" sequential UUIDs is that it can easily lead to index bloat. Imagine you have such sequential UUIDs generated over a year, for example. And then you delete e.g. 99% of old data (say, everything except some records that you're required to keep for audit purposes or whatever). If there was an index, the "old" part will be 99% empty. For regular UUIDs this would be fine, because new entries would get routed to this part of the index and the space would be reused. Not so for sequential UUIDs (v7/v8). This is mostly why year ago I wrote "sequential-uuids" extension, doing roughly what v7/v8 do, but wrapping the timestamp once in a while. Of course, if you don't delete data, this is not an issue and v7/v8 will work fine.
  - rollcat 3y ago
    Different UUID generators for different use cases! I'm also a fan of v5 for generating stable identifiers, when tracking things such as DNS names or URLs. Useful in web crawlers, host inventories, etc.

rollcat 3y ago

I also like UUIDv8. It's broadly similar to v7 but the vendor is free to define how the timestamps are represented, how many bits to split between timestamps/randomness, define own packing, etc. So we've started making UUIDs that encode the current ISO8601 date+time in a human-readable format: YYYYMMDD-HHMM-VRRR-RRRR-RRRRRRRRR This is especially useful for things you have few of (no more than a couple per minute), that you regularly need to cross-reference with other systems (e.g. files on S3).

JdeBP 3y ago
Enough iterations in this direction, and people will come full circle to how message IDs operated on Usenet. <datetime.processID.sequencenumber@domain> for example.
pgaddict 3y ago
The one problem with this "perfectly" sequential UUIDs is that it can easily lead to index bloat. Imagine you have such sequential UUIDs generated over a year, for example. And then you delete e.g. 99% of old data (say, everything except some records that you're required to keep for audit purposes or whatever). If there was an index, the "old" part will be 99% empty. For regular UUIDs this would be fine, because new entries would get routed to this part of the index and the space would be reused. Not so for sequential UUIDs (v7/v8). This is mostly why year ago I wrote "sequential-uuids" extension, doing roughly what v7/v8 do, but wrapping the timestamp once in a while. Of course, if you don't delete data, this is not an issue and v7/v8 will work fine.
- rollcat 3y ago
  Different UUID generators for different use cases! I'm also a fan of v5 for generating stable identifiers, when tracking things such as DNS names or URLs. Useful in web crawlers, host inventories, etc.