6 ms·

While looking into a replacement for auto increment primary keys, I also encountered TSIDs / Snowflakes. At least with TSIDs you can store them easily as 64 bit

While looking into a replacement for auto increment primary keys, I also encountered TSIDs / Snowflakes. At least with TSIDs you can store them easily as 64 bit integers; ULIDs were not as compact. Haven't made a decision yet though.

leetrout 2y ago
Keep ints for primary keys and keep all the db magic for your internal references then use ULIDs or TSIDs for your external keys and keep the ability to have well behaved indexes. Win-win with minimal disk impact
- hn_throwaway_99 2y ago
  The whole point of ULIDs and newer things like UUIDv7 is precisely so you don't have to do that. I would definitely just use a ULID or UUIDv7 as my sole primary DB key for new projects going forward. You get the benefit of ascending ordered primary keys, but the key is still globally unique so can be used as public keys.
  - Arch-TK 2y ago
    You don't have to do that if you don't mind leaking metadata about the time when a record was created.
    - aabhay 2y ago
      Fair tradeoff if you ask me
      - janto 2y ago
        Sounds like premature optimization with security implications. I'm hesitant to use that.
  - Phil_Latio 2y ago
    What's the point of ULID again? People say one use case of UUIDs is that clients are able to generate the (random) primary key. Fair enough, I guess... Then they say autoincremental ids are bad because it could give indication about business activity (like how many orders have been done in a time frame). But with ULID or UUIDv7, this "benefit" is gone to some degree because timestamp is included. If client-generation is not needed, then UUID is stupid to begin with, because any incremental id can be transformed ("encrypted") to arbitary different format (which hides the real id). Also UUID is usually much larger: Waste of db/index space, degraded performance. But I guess if people push everything in the cloud, they don't even realize they are paying more than needed... Conclusion: I don't understand what you people are trying to "solve". All those different UUID versions leads me to believe the issue is not the id, but developer confusion.
    - jw1224 2y ago
      > But with ULID or UUIDv7, this "benefit" is gone to some degree because timestamp is included Including the timestamp only tells someone the time the UUID was generated. Unlike incrementing numeric IDs (which can simply be subtracted from one another to measure a change in database records), there’s no way to meaningfully count change over time. > any incremental id can be transformed ("encrypted") to arbitary different format (which hides the real id) Easier said than done… You then have the overhead of encrypting/decrypting every client-facing ID before querying the database. You’ll also need to code workarounds in many frameworks, to bypass conventions where they expect keys in URL paths (for example). > Also UUID is usually much larger: Waste of db/index space, degraded performance With older UUIDs, sure, but sorted ones (like ULIDs) have consistent prefixes, allowing for very efficient indexing and querying with a binary tree search.
      - Phil_Latio 2y ago
        > there’s no way to meaningfully count change over time. You are right, should have finished my morning coffee first =) I guess for client-side generation it makes sense then. But how often is that really needed? I don't know... As for the other points. I still don't buy that. For example implementing UUIDs vs implementing the transformation: CPU cost is negligible (you don't need to use cryptographic secure cypher). UUID might be a little simpler to implement, but combined with space and performance savings it's well worth it: 16 byte vs 4/8 byte (which might be stored multiple times in related tables) - it adds up and fills resources. Again I understand, most people don't seem to care about that, because they were born into cloud culture and have no clue what they are doing in terms of efficiency money/resource-wise. Maybe I'm just too old fir this!
- dagss 2y ago
  You say win-win but what is the win of having a seperate internal ID specifically? It is possible to do as you say ofc but what is the advantage?
  - vbezhenar 2y ago
    One advantage is to use more compact data type for performance-critical paths. Table ID datatype will be used not just in the given table, but also in all tables referencing given table. Table ID is always indexed and foreign keys very often are indexed as well, so this datatype "leaks" to the many database indexes. If your index is compact, it allows for better RAM caching and generally faster queries. And, of course, faster writes. Another advantage is that you don't need to "think" about using sortable id, numeric serial id is sortable by default. It also helps with index size and insertion speed for primary key index and often foreign key indices. Using completely random UUID leaks no data about record creation time. It might not look like sensitive information, but generally it's better to be on a cautious side about leaking information. Generating sortable ID is far from generally accepted solution. You need to find obscure libraries or write non-trivial code yourself for all languages you're using. It'll be solved in time, as UUIDv7 became standard, but we're not there yet. UUID v4 is available in any language (and generally trivial to generate).
- deleted 2y ago
  [deleted]
- deleted 2y ago
  [deleted]
- drowsspa 2y ago
  Sure, if your only goal with them is hiding meta info and you're generating your IDs into a RDBMS...
luismedel 2y ago
I maintain the Python version[0] of this TSID spec[1]. Using it in several projects and I'm very happy with the result. [0] https://github.com/luismedel/tsid-python https://github.com/luismedel/tsid-python [1] https://github.com/f4b6a3/tsid-creator https://github.com/f4b6a3/tsid-creator

leetrout 2y ago

Keep ints for primary keys and keep all the db magic for your internal references then use ULIDs or TSIDs for your external keys and keep the ability to have well behaved indexes. Win-win with minimal disk impact

hn_throwaway_99 2y ago
The whole point of ULIDs and newer things like UUIDv7 is precisely so you don't have to do that. I would definitely just use a ULID or UUIDv7 as my sole primary DB key for new projects going forward. You get the benefit of ascending ordered primary keys, but the key is still globally unique so can be used as public keys.
- Arch-TK 2y ago
  You don't have to do that if you don't mind leaking metadata about the time when a record was created.
  - aabhay 2y ago
    Fair tradeoff if you ask me
    - janto 2y ago
      Sounds like premature optimization with security implications. I'm hesitant to use that.
- Phil_Latio 2y ago
  What's the point of ULID again? People say one use case of UUIDs is that clients are able to generate the (random) primary key. Fair enough, I guess... Then they say autoincremental ids are bad because it could give indication about business activity (like how many orders have been done in a time frame). But with ULID or UUIDv7, this "benefit" is gone to some degree because timestamp is included. If client-generation is not needed, then UUID is stupid to begin with, because any incremental id can be transformed ("encrypted") to arbitary different format (which hides the real id). Also UUID is usually much larger: Waste of db/index space, degraded performance. But I guess if people push everything in the cloud, they don't even realize they are paying more than needed... Conclusion: I don't understand what you people are trying to "solve". All those different UUID versions leads me to believe the issue is not the id, but developer confusion.
  - jw1224 2y ago
    > But with ULID or UUIDv7, this "benefit" is gone to some degree because timestamp is included Including the timestamp only tells someone the time the UUID was generated. Unlike incrementing numeric IDs (which can simply be subtracted from one another to measure a change in database records), there’s no way to meaningfully count change over time. > any incremental id can be transformed ("encrypted") to arbitary different format (which hides the real id) Easier said than done… You then have the overhead of encrypting/decrypting every client-facing ID before querying the database. You’ll also need to code workarounds in many frameworks, to bypass conventions where they expect keys in URL paths (for example). > Also UUID is usually much larger: Waste of db/index space, degraded performance With older UUIDs, sure, but sorted ones (like ULIDs) have consistent prefixes, allowing for very efficient indexing and querying with a binary tree search.
    - Phil_Latio 2y ago
      > there’s no way to meaningfully count change over time. You are right, should have finished my morning coffee first =) I guess for client-side generation it makes sense then. But how often is that really needed? I don't know... As for the other points. I still don't buy that. For example implementing UUIDs vs implementing the transformation: CPU cost is negligible (you don't need to use cryptographic secure cypher). UUID might be a little simpler to implement, but combined with space and performance savings it's well worth it: 16 byte vs 4/8 byte (which might be stored multiple times in related tables) - it adds up and fills resources. Again I understand, most people don't seem to care about that, because they were born into cloud culture and have no clue what they are doing in terms of efficiency money/resource-wise. Maybe I'm just too old fir this!
dagss 2y ago
You say win-win but what is the win of having a seperate internal ID specifically? It is possible to do as you say ofc but what is the advantage?
- vbezhenar 2y ago
  One advantage is to use more compact data type for performance-critical paths. Table ID datatype will be used not just in the given table, but also in all tables referencing given table. Table ID is always indexed and foreign keys very often are indexed as well, so this datatype "leaks" to the many database indexes. If your index is compact, it allows for better RAM caching and generally faster queries. And, of course, faster writes. Another advantage is that you don't need to "think" about using sortable id, numeric serial id is sortable by default. It also helps with index size and insertion speed for primary key index and often foreign key indices. Using completely random UUID leaks no data about record creation time. It might not look like sensitive information, but generally it's better to be on a cautious side about leaking information. Generating sortable ID is far from generally accepted solution. You need to find obscure libraries or write non-trivial code yourself for all languages you're using. It'll be solved in time, as UUIDv7 became standard, but we're not there yet. UUID v4 is available in any language (and generally trivial to generate).
deleted 2y ago
[deleted]
deleted 2y ago
[deleted]
drowsspa 2y ago
Sure, if your only goal with them is hiding meta info and you're generating your IDs into a RDBMS...

luismedel 2y ago

I maintain the Python version[0] of this TSID spec[1]. Using it in several projects and I'm very happy with the result. [0] https://github.com/luismedel/tsid-python https://github.com/luismedel/tsid-python [1] https://github.com/f4b6a3/tsid-creator https://github.com/f4b6a3/tsid-creator