31–40 of 163 posts
I’ve seen uuid4 which replaces the first 4 bytes with a timestamp. It was mentioned to me that this strategy allows postgres to write at the end of the index instead of arbitrarily on disk. I also presume it means it has some decent sorting.
[inspiration](https://github.com/tvondra/sequential-uuids/blob/master/sequ...)
Combined with a machine identifier you can obtain globally unique identifiers that are totally ordered.
Disclaimer: not a dba so my terms might not be appropriate I’ve seen uuid4 which replaces the first 4 bytes with a timestamp. It was mentioned to me that this strategy allows postgres to write at the end of the index instead of arbitrarily on disk. I also presume it means it has some decent sorting. [inspiration]( https://github.com/tvondra/sequential-uuids/blob/master/sequ... )
My opinion. Always if in any way possible pick a semantic key. There is usually something defining the thing you are working on. If there isnt work on your normalisation. Main benefits to this: Avoids accidental duplication (happens so much). Avoids additional round trips to fetch the id to make a mutation. Of course if you work on something where you don’t know what it is yet (actually humans are a good example for…
Disclaimer: not a dba so my terms might not be appropriate I’ve seen uuid4 which replaces the first 4 bytes with a timestamp. It was mentioned to me that this strategy allows postgres to write at the end of the index instead of arbitrarily on disk. I also presume it means it has some decent sorting. [inspiration]( https://github.com/tvondra/sequential-uuids/blob/master/sequ... )
While this is a good overview of the options for primary key generation, there's no silver bullet here. Most projects that are using SQL should just use the gold standard: an auto-incrementing integer for an internal primary key. And then decouple the public-facing primary key from it into a separate column, whether it be ULID, UUID, or a random-project-slug-123. Also, during debugging, it's a lot nicer to look at sh…
Why would you use an integer primary key and a public facing UUID? That seems like it's the worst of both worlds: ugly externally visible identifiers, record bloat, a database that you can't easily merge in the event of backups or DR, and having to roundtrip to the DB before you know the ID of a record. I personally stick to UUIDs in pretty much all cases, with the exception of where there are justified and benchmark…
The primary key is included in all indexes, including non-clustered indexes, so in some cases there can be quite a large difference between UUID and integer PKs in terms of index size.
UUID PKs are also more susceptible to fragmentation.
Earlier quoted context omitted.
But if you’re handing out a random ID, you still need to have an index over that ID to have efficient lookups of random ID -> internal ID right? One advantage is that you only need to resolve it once at the edge and then internally you use the external facing value. Are there any others I’m missing? I agree, I would have loved a deeper dive in xid since it seemed to clearly outperform everyone else.
Not necessarily. The idea here is that the id can be exposed publically because it is random. The problem it solves is someone sees a page /accountdetails?id=123 and can easily look for /accountdetails?id=124 and assume it is likely to be valid. If you use a random id, you cannot quickly know what other ids exist which makes looking for unauthorized access to objectids much harder.
Honestly that's a poor blog post. Randomly concludes "the best time-based ID seems to be xid" without saying why or comparing to others e.g. ksuid, UUIDv7 etc ("xid" is only mentioned twice in the entire blog, first in the above statement and second a link to the reference implementation). Equally unfortunate that they picked "xid" as their supposed "best" because Postgres has an internal identifier that is also call…
You really care about exposing a serial number scheme for a list of books your company publishes, or the identifier for each of the various hotels you own, or the cities you have an office in or something?
Earlier quoted context omitted.
But if you’re handing out a random ID, you still need to have an index over that ID to have efficient lookups of random ID -> internal ID right? One advantage is that you only need to resolve it once at the edge and then internally you use the external facing value. Are there any others I’m missing? I agree, I would have loved a deeper dive in xid since it seemed to clearly outperform everyone else.
Not necessarily. The idea here is that the id can be exposed publically because it is random. The problem it solves is someone sees a page /accountdetails?id=123 and can easily look for /accountdetails?id=124 and assume it is likely to be valid. If you use a random id, you cannot quickly know what other ids exist which makes looking for unauthorized access to objectids much harder.
Depending on how highly aesthetic URLs are valued, it's not unlikely that after being in business for a while, the density of your keyspace will mean that even random IDs are found.
A better rationale for disconnecting public and private IDs is to make certain types of database migration a little bit easier. I don't think it's a huge win though, it's a chunk of work to maintain multiple IDs, do the required inderictions all the time, ensure public IDs are always sent to front end, and so on, while adding a translation layer as part of a database migration delays this kind of work until it's actually needed, if ever.