4 ms·
That is how I used to do (and currently still do) DB design, but honestly I think if DBs start supporting UUID v7s well that I would use that as the sole primar
by hn_throwaway_99 4y ago
That is how I used to do (and currently still do) DB design, but honestly I think if DBs start supporting UUID v7s well that I would use that as the sole primary DB key as well as the external ID:
1. They are still sorted in increasing timestamp order (at millisecond granularity), so they should have good DB index characteristics.
2. At the same time, they contain 62 bits of randomness, would should pretty much eliminate IDOR attacks if there is a bug elsewhere that isn't doing proper access checks. Not good enough for secure tokens, but just good defense against access permission check bugs.
That is, you should basically get the best of both worlds: ordered keys with enough randomness to make ID-increment attacks infeasible.
- RobertRoberts 4y agoYep, I want UUID v7, because right now I am using ULID and it's fantastic, but I'd like more official and wide support as well.
- jeremyjh 4y agoI like the textual representation of ULIDs as well though; I wish they’d just adopted ULID as v7. At least it is binary compatible with existing UUID types.
- RobertRoberts 4y agoIs there not an equivalent text representation for UUIDv7?
- jeremyjh 4y agoSure you could encode it in Crockford's base-32 but if it isn't part of the standard then tools won't implement it natively, so you couldn't copy a key from a url and look it up in postgres without running it through a conversion function, for example.
- tpetry 4y agoYou can write a custom data type in pure SQL for PostgreSQL which is just transforming a visible string to the more efficient uuid type. That‘s basically how the uuid type can be implemented: For storage it‘s binary(16) but all operations transform the value to the visible string you see all the time. It‘s a pretty powerfull feature.
- yencabulator 4y agoI was enthusiastic for a while but https://www.postgresql.org/docs/current/sql-createtype.html https://www.postgresql.org/docs/current/sql-createtype.html > (This restriction is made because an erroneous type definition could confuse or even crash the server.) Uhh, what.. > Generally these functions have to be coded in C or another low-level language. Oh, okay. That sounds like this not really doable with many hosted Postgres services out there.