4 ms·

IMO, a good middleground is using schemes like TypeID[0], ulid[1], or KSUID[2] that provides a more compact and readable (base32) representation and provides be

by ekojs 3y ago

IMO, a good middleground is using schemes like TypeID[0], ulid[1], or KSUID[2] that provides a more compact and readable (base32) representation and provides better database locality (K-sortable). [0] https://github.com/jetpack-io/typeid https://github.com/jetpack-io/typeid [1] https://github.com/ulid/spec https://github.com/ulid/spec [2] https://github.com/segmentio/ksuid https://github.com/segmentio/ksuid

safetytrick 3y ago
ulids are beautiful little ids without many of the drawbacks and risks of other id systems. They are sufficiently unique for most work (anywhere that you don't need to actually uniquely engineer your ids) and their built in ordering and slick encoding are just icing on the cake.
hot_gril 3y ago
As noted on an earlier HN submission about typeid, K-sortable only matters to distributed DBs, and things get tricky there. Spanner for example says to use deliberately unsorted primary keys. I really think when you're picking PKs, you should simply use whatever the DBMS recommends for performance. It's not the PK's job to be typed, sequenced, human-readable, or anything like that; that can be handled by other cols and logging rules. Its job is to be fast in those joins you'll constantly be making against it.
- sgarland 3y ago
  Sortable primarily keys matters very much for any DB clustering on them, e.g. MySQL/InnoDB and SQL Server. It also matters, albeit to a much lesser extent, to others. Postgres stores tuples in a heap, but the PK is still a B+tree (ish), so an INSERT or UPDATE heavy workload will suffer somewhat.
  - hot_gril 3y ago
    Yeah, you're right. But for those, the typical PK recommendation of bigserial is already time-ordered and mostly packed.
imiric 3y ago
I'm recently finding Cuid2 to be the best of these alternative GUIDs. They seem to have all of the benefits for what you would want to use a GUID for, but none of the drawbacks of existing implementations.[1] [1]: https://github.com/paralleldrive/cuid2#comparisons https://github.com/paralleldrive/cuid2#comparisons
- bsder 3y ago
  Cuid2 is explicitly not K-sortable, no?
bsder 3y ago
Isn't UUIDv7 K-sortable?

by ekojs 3y ago

safetytrick 3y ago

ulids are beautiful little ids without many of the drawbacks and risks of other id systems. They are sufficiently unique for most work (anywhere that you don't need to actually uniquely engineer your ids) and their built in ordering and slick encoding are just icing on the cake.

hot_gril 3y ago

As noted on an earlier HN submission about typeid, K-sortable only matters to distributed DBs, and things get tricky there. Spanner for example says to use deliberately unsorted primary keys. I really think when you're picking PKs, you should simply use whatever the DBMS recommends for performance. It's not the PK's job to be typed, sequenced, human-readable, or anything like that; that can be handled by other cols and logging rules. Its job is to be fast in those joins you'll constantly be making against it.

sgarland 3y ago
Sortable primarily keys matters very much for any DB clustering on them, e.g. MySQL/InnoDB and SQL Server. It also matters, albeit to a much lesser extent, to others. Postgres stores tuples in a heap, but the PK is still a B+tree (ish), so an INSERT or UPDATE heavy workload will suffer somewhat.
- hot_gril 3y ago
  Yeah, you're right. But for those, the typical PK recommendation of bigserial is already time-ordered and mostly packed.

imiric 3y ago

I'm recently finding Cuid2 to be the best of these alternative GUIDs. They seem to have all of the benefits for what you would want to use a GUID for, but none of the drawbacks of existing implementations.[1] [1]: https://github.com/paralleldrive/cuid2#comparisons https://github.com/paralleldrive/cuid2#comparisons

bsder 3y ago
Cuid2 is explicitly not K-sortable, no?

bsder 3y ago

Isn't UUIDv7 K-sortable?