> where leaking the creation time in the UUID is acceptable.
I've seen tons of people cite this as a problem for various reasons, but not a single person has ever given an actual example in the wild of it causing problems. This isn't war; the German Tank Problem is not nearly as applicable as people think.
Worse (from a DB perspective), it's often used as an excuse for poor API design. "I have to have a random ID, because the API is /api/user/<user-id>" and the like. OK, so don't do that? Put the user ID into a JWT (encrypted first if you'd like). Or use an external ID that is random, and an internal ID that's a sequential ID which maps to the external. Use the internal ID as the PK.
"$person_name's lawyer could not be reached for comment, but it is noted that the userid mentioned in the lawsuit was created on 2022-12-03, the same day as the photos were uploaded, and the same day as the arrest warrant was issued..."
Using the id directly as the pk admittedly just makes things a bit simpler, one less bit of information to track down. I know bridge tables are not hard, but it's just one extra step that the developer has to be aware of, plan ahead for (and possibly all your teams and customer support people need to be aware of when they are tracking down an issue with an account)
UUIDv4 lets you move fast now and pay your performance-piper next year rather than now.
PK should be bigserial no matter what. "Use an external ID that is random" has the same clustering problem mentioned above since you will need an index on it, so it's not always the right answer, but I would default to it.
Not necessarily. Depending on your secrecy desire, you could include the internal ID in JWT or cookie, and then locate the row (including the external UUID) using that. Another option would be to build an index with `INCLUDE external_id`. That way, it doesn’t impact the B+tree (kind of) but it still gets pulled along for query results. The leaf nodes would become larger, but the overall ordering wouldn’t change (I think – never actually tried this).
If you mean to encrypt your internal IDs when sending to clients and decrypt when receiving, it'd work. I've never seen it done this way, maybe because with standard 2048-bit RSA you get a 2048-bit output at least, which would bloat up the API responses.
I've seen tons of people cite this as a problem for various reasons, but not a single person has ever given an actual example in the wild of it causing problems. This isn't war; the German Tank Problem is not nearly as applicable as people think.
Worse (from a DB perspective), it's often used as an excuse for poor API design. "I have to have a random ID, because the API is /api/user/<user-id>" and the like. OK, so don't do that? Put the user ID into a JWT (encrypted first if you'd like). Or use an external ID that is random, and an internal ID that's a sequential ID which maps to the external. Use the internal ID as the PK.