Great article, specially for this part:

> What can go wrong with using UUIDv7 Using UUIDv7 is generally discouraged for security when the primary key is exposed to end users in external-facing applications or APIs. The main issue is that UUIDv7 incorporates a 48-bit Unix timestamp as its most significant part, meaning the identifier itself leaks the record's creation time.

> This leakage is primarily a privacy concern. Attackers can use the timing data as metadata for de-anonymization or account correlation, potentially revealing activity patterns or growth rates within an organization. While UUIDv7 still contains random data, relying on the primary key for security is considered a flawed approach. Experts recommend using UUIDv7 only for internal keys and exposing a separate, truly random UUIDv4 as an external identifier.

▲

SahAssar a day ago | parent | next [-]

> Experts recommend

What experts? For what scenarios specifically? When do they consider time-of-creation to be sensitive?

▲

dgb23 a day ago | parent | prev | next [-]

Or just generate them in bulk and take them from a list?

▲

hn_throwaway_99 a day ago | parent | prev | next [-]

> Experts recommend using UUIDv7 only for internal keys and exposing a separate, truly random UUIDv4 as an external identifier.

So then what's the point? How I always did things in the past was use an auto increment big int as the internal primary key, and then use a separate random UUID for the external facing key. I think this recommendation from "experts" is pretty dumb because you get very little benefit using UUIDV7 (beyond some portability improvements) if you're still using a separate internal key.

While I wouldn't use UUIDV7 as a secure token like I would UUIDV4, I don't see anything wrong with using UUIDV7 as externally exposed object keys - you're still going to need permissions checks anyway.

▲

morshu9001 a day ago | parent | next [-]

I asked a similar question, and yeah it seems like this is entirely for distributed systems, even then only some of them. Your basic single DB Postgres should just have a serial PK.

▲

crazygringo a day ago | parent | prev [-]

For distributed databases where you can't use autoincrement.

Or where, for some reason, the ID needs to be created before being inserted into the database. Like you're inserting into multiple services at once.

▲

sgarland a day ago | parent [-]

Many distributed databases have mechanisms to use an auto-increment, actually - often, generating large chunks at a time to hand out.

▲

grapesodaaaaa 17 hours ago | parent [-]

Our “distributed database” at a fortune 90 company spans at least 10 different database products.

UUIDv4 lets us sidestep this.

Is it bad design? Probably. Is it going to happen at huge companies? Yes.

	▲	sgarland 11 hours ago \| parent [-]
		You’re not wrong, of course. It’s a natural consequence of the eschewing of DBAs, and the increasingly powerful compute available - even if someone did notice that the slowdown was due to the PK choice, they can often “fix” that by paying more money.

▲

andy_ppp a day ago | parent | prev | next [-]

I wish Postgres would just allow you look up records by the random component of the field, what are the chances of collisions with 80 bits of randomness? My guess is it’s still enough.

▲

jagged-chisel a day ago | parent | next [-]

You can certainly create that index.

	▲	andy_ppp a day ago \| parent [-]
		Yes, just obviously if it’s automated and part of Postgres people will use it without having to think too much and it removes one of the objections to what I think for most large systems is a sensible way to go rather than controversial because security.

▲

mamcx a day ago | parent | prev [-]

What could be better is to allow to create a type with custom display, in/out and internally set the native type IN SQL (this require to do it in c)

▲

themafia a day ago | parent | prev [-]

> growth rates

I honestly don't see how.