[GGO]

I dont understand the recommendation of using bigserial with uuid column when you can use UUIDv7. I get that it made sense years ago when there was no UUIDv7, but why do people keep recommending it over UUIDv7 now beats me.

[whynotmaybe]

As uuid v7 hold time information, they can help bad actors for timing attacks or pattern recognition because they contain a time information linked to the record.

You can guess the time the system took between 2 uuid v7 id's.

They can only be used if they're not shown to the user. (so not in the form mysite.com/mypage? id=0190854d-7f9f-78fc-b9bc-598867ebf39a)

A big serial starting at a high number can't provide the time information.

[TeeWEE]

Big serial is sequential and it’s very easy to guess the next number. So you got the problem of sequential key attack…

If you use only uuid in your outwards facing api then you still have the problem of slow queries. Since you need them to find the object (as mentioned below)

UUIDv7 has a random part, can be created distributedly, and indexes well.

It’s the best choice for modern application that support distributed data creation.

[thiht]

I don’t understand how that’s an issue. Do you have an example of a possible attack using UUIDv7 timestamp? Is there evidence of this being a real security flaw?

[arp242]

For almost all use cases just showing a UUIDv7 or sequential ID is fine. There are a few exceptions, but it's not the common case.

[OskarS]

Can I ask (as a humble application developer, not a backend/database person), if the two requirements are:

1. The UUIDs should be ordered internally, for B-tree performance

2. The UUIDs should not be ordered externally, for security reasons

Why not use encryption? The unencrypted ID is a sequential id, but as soon as it leaves the database, it's always encrypted. Like, when getting it out:

    SELECT encrypt(id) FROM table WHERE something = whatever;

and when putting stuff in:

    UPDATE table SET something = whatever WHERE id = decrypt(<encrypted-key>)

Seems like the best of both worlds, and you don't need to store separate things.

[sebazzz]

> As uuid v7 hold time information, they can help bad actors for timing attacks or pattern recognition because they contain a time information linked to the record.

Are you then not doing security by randomness if that is the thing that worries you?

[dajtxx]

The comment above warns against it due to the embedded timestamp info as a info leak risk. Perhaps that was a problem for them in some circumstance.

[inopinatus]

It wasn’t a problem for me directly but was observed and related by a colleague: an identifier for an acquired entity embedded the record’s creation timestamp and effectively leaked the date of acquisition despite it being commercial-in-confidence information. Cue post-M&A ruckus at board level.

Just goes to show you can’t inadvertently disclose anything these days.

[mixmastamyk]

You're saving storage space but potentially leaking details. Is that ok for your application? No one can answer but your org.

[hoffs]

The details part is so miniscule that I doubt it even matters. You'd have difficult time trying to enumerate uuidv7s anyways.