4 ms·

Out of my scope, but why are UUIDs even discussed? ULIDs (and i think Nanoids?) don't suffer these same problems. Locality and ordering alone make me[1] thi

Out of my scope, but why are UUIDs even discussed? ULIDs ~~(and i think Nanoids?)~~ don't suffer these same problems. Locality and ordering alone make me[1] think ordered ULIDs (and friends) are the only thing worth discussing. Is there some value to UUIDs over ULIDs that make these discussions largely revolve around Autoincrement vs UUIDs rather than Autoincrement vs ULIDs(and friends)? [1]: Again, totally out of my wheel house. edit: Apparently UUIDv7 exists, which is similar to ULID, so my question pertains to UUIDv5 and below, i think. edit2: I think Nanoids do suffer the same problem. They're just small... i think.

letitbeirie 3y ago
Postgres supports UUIDs natively
- unshavedyak 3y ago
  Is that worth the pain of dealing with randomized inserts? I guess i just don't mind creating a ULID (or i guess UUIDv7 is newly proposed and sortable) and inserting that. Native DB support is irrelevant to me for randomized bits unless it affects storage, sorting, paging, etc. Does it?
  - code-e 3y ago
    It does affect storage and sorting. A native UUID type uses 16 bytes. The alternative is text encoding (32 bytes for hex), or maybe a raw BYTEA. Postgres also has SortSupport for the UUID type, which basically means if the first 8 bytes only has one matching row, then the remaining 8 bytes can be skipped. Combine that with a ULID where the most first half is basically a timestamp, you'll get performance close to using a single 8byte BIGSERIAL. You can also write a plpgsql function to generate these ULIDs in the database.
- lolinder 3y ago
  ULIDs are byte-compatible with UUIDs, so the only thing Postgres's native support gives you over ULIDs is that Postgres can generate new UUIDs for you instead of having to do it in the application before insertion.
  - letitbeirie 3y ago
    It also buys you that support in the driver libraries. I ran into this last year - storing them as UUIDs works great, unless all of the drivers for the language you're using (Go, in my case) try to cast/validate those bytes as a UUID before you can access them.
ljm 3y ago
I’ve worked with ULIDs a bit but honestly haven’t operated at a scale where you might run into issues. And the main reason for choosing ULID was because I could only find experimental support for UUIDv6 or UUIDv7, and KSUID (another alternative) only had time precision down to a second. And the reason for that was to have lexicographically sortable IDs (even if not monotonic, which would require an extra server) so we didn’t have to index a timestamp column for chronological ordering. The alternative was to have a traditional integer sequence but it’s not always ideal to expose those on your API.
- cratermoon 3y ago
  Out of curiosity, why do your keys need time precision less than a second? Are they carrying some secondary expectations beyond uniqueness and k-sortable?
  - ljm 3y ago
    Uniqueness and the context is that we were modelling a chat conversation, so the limited precision presented issues with messages occasionally appearing out of order. That also applied to our test suite so the choice was to use KSUID but still index a timestamp for accuracy, or just use an alternative with more precision.
    - esafak 3y ago
      What would you use for that application if you had to choose today?
xahrepap 3y ago
ULIDs have made our MySql caches breathe a sigh of relief. One place we're avoiding ULIDs (and other counters) is in publicly-facing IDs. Preferring random to help keep them unguessable. (say what you will about security-through-obscurity). So we do ULIDs for private IDs. Random UUIDs for public IDs. Seems to work well.
- ilyt 3y ago
  I wonder if just encrypting them for public usage would be enough here. Then application can convert between public and private representation at will.
  - selcuka 3y ago
    Encryption is good until someone leaks the private key. Depending on the application, it may be very difficult to reassign all primary keys and foreign keys when that happens, especially if they have already been used as canonical identifiers. You don't have such issues with randomly generated ids, unless someone obtains a full dump of your database (in which case you will have to think about bigger problems anyway).
- smaddox 3y ago
  KSUID's are have temporal-lexicographical order plus 128 bits of entropy, which is more than UUIDv4. https://github.com/segmentio/ksuid https://github.com/segmentio/ksuid

Out of my scope, but why are UUIDs even discussed? ULIDs ~~(and i think Nanoids?)~~ don't suffer these same problems. Locality and ordering alone make me[1] thi

letitbeirie 3y ago

Postgres supports UUIDs natively

unshavedyak 3y ago
Is that worth the pain of dealing with randomized inserts? I guess i just don't mind creating a ULID (or i guess UUIDv7 is newly proposed and sortable) and inserting that. Native DB support is irrelevant to me for randomized bits unless it affects storage, sorting, paging, etc. Does it?
- code-e 3y ago
  It does affect storage and sorting. A native UUID type uses 16 bytes. The alternative is text encoding (32 bytes for hex), or maybe a raw BYTEA. Postgres also has SortSupport for the UUID type, which basically means if the first 8 bytes only has one matching row, then the remaining 8 bytes can be skipped. Combine that with a ULID where the most first half is basically a timestamp, you'll get performance close to using a single 8byte BIGSERIAL. You can also write a plpgsql function to generate these ULIDs in the database.
lolinder 3y ago
ULIDs are byte-compatible with UUIDs, so the only thing Postgres's native support gives you over ULIDs is that Postgres can generate new UUIDs for you instead of having to do it in the application before insertion.
- letitbeirie 3y ago
  It also buys you that support in the driver libraries. I ran into this last year - storing them as UUIDs works great, unless all of the drivers for the language you're using (Go, in my case) try to cast/validate those bytes as a UUID before you can access them.

ljm 3y ago

I’ve worked with ULIDs a bit but honestly haven’t operated at a scale where you might run into issues. And the main reason for choosing ULID was because I could only find experimental support for UUIDv6 or UUIDv7, and KSUID (another alternative) only had time precision down to a second. And the reason for that was to have lexicographically sortable IDs (even if not monotonic, which would require an extra server) so we didn’t have to index a timestamp column for chronological ordering. The alternative was to have a traditional integer sequence but it’s not always ideal to expose those on your API.

cratermoon 3y ago
Out of curiosity, why do your keys need time precision less than a second? Are they carrying some secondary expectations beyond uniqueness and k-sortable?
- ljm 3y ago
  Uniqueness and the context is that we were modelling a chat conversation, so the limited precision presented issues with messages occasionally appearing out of order. That also applied to our test suite so the choice was to use KSUID but still index a timestamp for accuracy, or just use an alternative with more precision.
  - esafak 3y ago
    What would you use for that application if you had to choose today?

xahrepap 3y ago

ULIDs have made our MySql caches breathe a sigh of relief. One place we're avoiding ULIDs (and other counters) is in publicly-facing IDs. Preferring random to help keep them unguessable. (say what you will about security-through-obscurity). So we do ULIDs for private IDs. Random UUIDs for public IDs. Seems to work well.

ilyt 3y ago
I wonder if just encrypting them for public usage would be enough here. Then application can convert between public and private representation at will.
- selcuka 3y ago
  Encryption is good until someone leaks the private key. Depending on the application, it may be very difficult to reassign all primary keys and foreign keys when that happens, especially if they have already been used as canonical identifiers. You don't have such issues with randomly generated ids, unless someone obtains a full dump of your database (in which case you will have to think about bigger problems anyway).
smaddox 3y ago
KSUID's are have temporal-lexicographical order plus 128 bits of entropy, which is more than UUIDv4. https://github.com/segmentio/ksuid https://github.com/segmentio/ksuid