PostgreSQL and UUID as Primary Key • Hacker News Board

Re: PostgreSQL and UUID as Primary Key

#131

Post by TeeWEE » Sat, Jul 06, 2024, 3:24 AM UTC

whynotmaybe wrote: post #70

GGO wrote: post #32

Earlier quoted context omitted.

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.

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 t…

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.

Re: PostgreSQL and UUID as Primary Key

#132

Post by TeeWEE » Sat, Jul 06, 2024, 3:26 AM UTC

jpollock wrote: post #115

saaspirant wrote: post #108

Earlier quoted context omitted.

Naive question. Above comment suggests using bigserial as internal identifier and uuid as public facing ID. Now let's say there's a user table and post table. Both will have only uuid available in the APIs. So every time API requests a post of the user or user of the post, we will find the the relevant row using uuid right? Since uuid will be sent by the public facing APIs? How would bigserial be used here? I don't k…

Each object has an external key and an internal key. This separation allows you to migrate to other layouts, technologies, etc. without breaking your customer's links or records. Internally, your database looks like: User ID - uint128 external_id - UUID (of some sort) name - string Post ID - uint128 UserId - uint128 (User.ID) external_id - UUID ... Then you have secondary indices on the external_id columns in both ta…

So it can’t use the internal id index, result: slow lookups for external ids.

Re: PostgreSQL and UUID as Primary Key

#133

Post by necovek » Sat, Jul 06, 2024, 3:28 AM UTC

mythrwy wrote: post #79

tantalor wrote: post #77

> inserting UUID v7 is ~2x faster and inserting regular UUID v4 Okay but why

It tells you in the previous section: "UUID - even though always looks similar - comes in multiple variants. Java's UUID.randomUUID() - returns UUID v4 - which is a pseudo-random value. For us the more interesting one is UUID v7 - which produces time-sorted values. It means that each time new UUID v7 is generated, a greater value it has. And that makes it a good fit for B-Tree index."

The benchmark seems to include generation in the total time. Not sure that's a useful comparison of how b-tree index behaves in each case (as UUIDv7 has fewer bits of randomness, it's also cheaper to generate).

Re: PostgreSQL and UUID as Primary Key

#134

Post by darby_nine » Sat, Jul 06, 2024, 3:33 AM UTC

inopinatus wrote: post #12

The best advice I can give you is to use bigserial for B-tree friendly primary keys and consider a string-encoded UUID as one of your external record locator options. Consider other simple options like PNR-style (airline booking) locators first, especially if nontechnical users will quote them. It may even be OK if they’re reused every few years. Do not mix PK types within the schema for a service or application, esp…

You really have to understand the usage patterns of a program to give a recommendation. Anyone who doesn't is just a grifter.

I don't trust anyone who gives recommendations sight unseen.

Re: PostgreSQL and UUID as Primary Key

#135

Post by necovek » Sat, Jul 06, 2024, 3:37 AM UTC

lulzury wrote: post #44

munk-a wrote: post #23

Earlier quoted context omitted.

Your understanding is correct but you're underselling very very in this context. It is astronomically unlikely to hit a collision with the advised generation methods. If you want a possibly easier to grasp parallel git relies on SHA hashes never colliding and will break in a really awful way if you can produce two commits in a tree with the same hash - it's so astoundingly unlikely that people are okay summarizing it…

What you stated makes intuitive sense, but it does make me wonder why the RFC states the following in the security considerations: > Implementations SHOULD NOT assume that UUIDs are hard to guess. For example, they MUST NOT be used as security capabilities (identifiers whose mere possession grants access). Discovery of predictability in a random number source will result in a vulnerability. https://datatracker.ietf.o…

That's mostly focusing on broken RNG properties (i.e. static seed for a pseudo random generator, an outright bad algorithm, or bad entropy source for non-pseudo RNG). They make random numbers predictable, thus your UUIDs become predictable.

Cryptographic hashes are usually also dependent on RNGs having good properties (encryption has been broken due to bad RNGs too), but they are a bit more involved to figure out even if you know the input randomness, and are easier to invalidate (change the secret).

Re: PostgreSQL and UUID as Primary Key

#136

Post by arp242 » Sat, Jul 06, 2024, 3:38 AM UTC

TeeWEE wrote: post #129

inopinatus wrote: post #12

The best advice I can give you is to use bigserial for B-tree friendly primary keys and consider a string-encoded UUID as one of your external record locator options. Consider other simple options like PNR-style (airline booking) locators first, especially if nontechnical users will quote them. It may even be OK if they’re reused every few years. Do not mix PK types within the schema for a service or application, esp…

If you have distributed data creation. (Creating data on the client). And a CRDT style mechanism for syncing, then you can’t use bigserial because of the simple fact that it is sequential. The best solution here is uuidv7. Since you can generate these at the client even when offline.

That's not true, you can increment by 2, 10, 100, or any number. I'm not saying that's necessarily the best solution, but it's not true that you can't use it.

Re: PostgreSQL and UUID as Primary Key

#137

Post by arp242 » Sat, Jul 06, 2024, 3:40 AM UTC

whynotmaybe wrote: post #70

GGO wrote: post #32

Earlier quoted context omitted.

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.

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 t…

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.

Re: PostgreSQL and UUID as Primary Key

#138

Post by rjurney » Sat, Jul 06, 2024, 3:40 AM UTC

I've started using python-ulid as ULIDs seem superior to UUIDs. https://pypi.org/project/python-ulid/

Re: PostgreSQL and UUID as Primary Key

#139

Post by necovek » Sat, Jul 06, 2024, 3:41 AM UTC

The benchmark for UUIDv7 insertion time includes UUID generation: when looking at simply index update cost, I'd like to see those decoupled.

FWIW, I do expect UUIDv7 to take roughly half the time to generate if it has roughly half the random bits of UUIDv4 and an unprotected source of entropy is used.

Re: PostgreSQL and UUID as Primary Key

#140

Post by arp242 » Sat, Jul 06, 2024, 3:47 AM UTC

njtransit wrote: post #128

incrudible wrote: post #43

Earlier quoted context omitted.

If an attacker can create billions of records through your API, maybe that is a problem you need to address either way.

It’s about 100 records per second for a year and a half, or 10,000 records per second for 5 days. Both are easily achievable. As an engineer, why would you ever knowingly design such a system when it’s trivial to not have this vulnerability in the first place. It’s like hosting an internal app at a company that contains a SQL injection. “Well, if a hacker can access this app, then that’s a problem that needs addressi…

> It’s like hosting an internal app at a company that contains a SQL injection

It's nothing like that at all because the wrong SQL injection can completely ruin people's lives due to leaking stuff it shouldn't whereas the worst an int exhaustion can do is bring some app offline. Whoopdie-doo. Okay, that's not brilliant, but it's not comparable at all.

And there's a reason there aren't tons of "int exhaustion attacks": because there's little point in doing so.