I wonder if the issue is with exposing internal IDs to end users. I'm sure the experts here have already thought of this, but could someone explain why using encryption or even an HMAC for external views of a primary key doesn't make sense? Maybe because the extra processing is more expensive than just using UUIDv4? Using a KDF such as argon2id on the random bits of a UUIDv7 seems like it might work well for external IDs.

(And why the heck are different types or variants of UUIDs called "versions"?)

▲

Hizonner 12 hours ago | parent | next [-]

Because now, for the rest of eternity, every single person who writes any code that moves data from this table to somewhere else, for any purpose, has to remember that the primary key gives away the creation time of something, which can potentially be linked to something else. A lot of people won't notice that, and a lot of people who do notice it will get the remediation wrong. And you can now forget using a simple view on the database to give any information to any person or program that shouldn't get the creation times.

You've embrittled your system.

	▲	gfody 11 hours ago \| parent [-]
		the question was why not use encryption (sqids/hashids/etc) to secure publicly exposed surrogate keys, I don't think this reply is on point .. surrogate keys ideally are never exposed (for a slew of reasons beyond just leaking information) so securing them is a perfectly reasonable thing to do (as seen everywhere on the internet). otoh using any form of uuid as surrogate key is an awful thing to do to your db engine (making its job significantly harder for no benefit) > You've embrittled your system. this is the main argument for keeping surrogate keys internal - they really should be thought of like pointers, dangling pointers outside of your control are brittle. ideally anything exposed to the wild that points back to a surrogate key decodes with extra information you can use to invalidate it (like a safe-pointer!)

▲

bri3d 11 hours ago | parent | prev | next [-]

It does make all kinds of sense and is a majorly underutilized tool.

▲

gfody 12 hours ago | parent | prev [-]

> but could someone explain why using encryption or even an HMAC for external views of a primary key doesn't make sense?

it does make sense and it's what you should do instead of using a UUID as PK for this purpose.