Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL and UUID as Primary Key

maciejwalkowiak.com

Post reply on HN
Print view View original

211–220 of 345 posts

Re: PostgreSQL and UUID as Primary Key

#211

Post by Pinus »

conradludgate wrote: post #204
Pinus wrote: post #202

I’m not sure I understand the bit about time-ordered values being a better fit for B-trees. Actually, I’m sure that I don’t understand it... If anything, I’d naively assume that inserting already-sorted values would be a worst case, always going down the same branch of the tree and triggering re-balancing operations left and right (figuratively — literally mostly left!). The Wikipedia article on B-trees notices that…

Btrees don't need to be rebalanced. They only split. The cost of splitting to the root is counteracted by the cache locality of always accessing the same set of pages. Inserting into the last page only needs O(logn) page accesses and O(logn) page writes worst case.

Ah, of course. Thanks!

Re: PostgreSQL and UUID as Primary Key

#212

Post by feydaykyn »

fabian2k wrote: post #3

My somewhat naive understanding was that random UUIDs were not that big of a deal in Postgres because it does not cluster by primary key. And of course a UUID (16 bytes) is larger than a serial (4 bytes) or bigserial (8 bytes) by a factor of 2-4 . This certainly might matter for an index, but on a whole table level where you have 20+ bytes overhead per row this doesn't seem that big of a deal for anything except very…

A dev at $WORK thought it wasn't a big deal too because they "tested" on their unrestricted dev setup with 3 rows in the table.

Of course production crashed when their latest sql query did a multijoin on the real table with a few millions of rows. The size of the uuid needed to join filled the available RAM, everything slowed to a crawl and the system collapsed.

The uuid as primary key can be seen as a risk multiplicator : it will amplify any performance issue you may have, converting a temporary slowness into a full system stop.

Re: PostgreSQL and UUID as Primary Key

#213

Post by [deleted] »

saaspirant wrote: post #108
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…

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…

[deleted]

Re: PostgreSQL and UUID as Primary Key

#214

Post by matharmin »

sgarland wrote: post #109
oppositelock wrote: post #6

Earlier quoted context omitted.

Random UUID's are super useful when you have distributed creation of UUID's, because you avoid conflicts with very high probability and don't rely on your DB to generate them for you, and they also leak no information about when or where the UUID was created. Postgres is happier with sequence ID's, but keeping Postgres happy isn't the only design goal. It does well enough for all practical purposes if you need random…

> Postgres is happier with sequence ID's, but keeping Postgres happy isn't the only design goal. It literally is the one thing in the entire stack that must always be happy. Every stateful service likely depends on it. Sad DBs means higher latency for everyone, and grumpy DBREs getting paged.

Postgres is usually completely happy enough with UUIDv4. Overall architecture (such as allowing distributed id generation, if relevant) is more important than squeezing out that last bit of performance, especially for the majority of web applications who don't work with 10 million+ rows.

Re: PostgreSQL and UUID as Primary Key

#215

Post by stoperaticless »

badestrand wrote: post #186
TeeWEE wrote: post #131

Earlier quoted context omitted.

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…

You never expose the bigserial, you generate a ID (like UUID) for external use/identification and simply have an index over that column for fast selects.

Amen (or similar)

Re: PostgreSQL and UUID as Primary Key

#216

Post by stoperaticless »

OskarS wrote: post #197
spencerap wrote: post #195

Earlier quoted context omitted.

If the key and encryption mechanism are ever leaked, those opaque external IDs can be converted easily back to sequence numbers, and vice versa, which might pose a risk for you or your users. You won't be able to rotate the encryption key without breaking anything external that tracks those encrypted IDs... third party services, SEO, user bookmarks, etc.

You store the key in the database, right? Like, if the database leaks, it doesn’t matter if your ids are sequeneced or unsequenced, because all data has leaked anyway. The key leaking doesn’t seem like a realistic security issue.

> The key leaking doesn’t seem like a realistic security issue.

But it is.

If you have a password in a system, you want to rotate it regularly or at least have that ability (for example, when angry colleague leaves).

Re: PostgreSQL and UUID as Primary Key

#217

Post by stoperaticless »

jrochkind1 wrote: post #104
lulzury wrote: post #44

Earlier quoted context omitted.

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…

I don't know the math here specifically, but being hard to guess is a different quantity than chance of collision when following the algorithm . That is, if you aren't trying to have a collision and following the algorithm that has that aim, a collision can be exceedingly unlikely; but they can still be easy to guess if you are trying to predict someone else's assignment.

to illustrate point above:

Sequence that easy to guess, but will never collide until it wraps back to 1.

n1=1,n2=2,…

(Caveat: if generated only by single node/thread)

Re: PostgreSQL and UUID as Primary Key

#218

Post by stoperaticless »

scotty79 wrote: post #9

Isn't B-Tree with UUIDv4 keys getting more balanced than with UUIDv7? Doesn't longer insert time result in faster searches later?

Related pondering:

- UUIDv4 causes page fragmentation (ideally, on disk pages of data would be stored in sorted fashion; HDDs still exist)

- if in your app fresh records have more reads, having them close will help with read speed (being cpu cache friendly)

Re: PostgreSQL and UUID as Primary Key

#219

Post by kroolik »

badestrand wrote: post #186
TeeWEE wrote: post #131

Earlier quoted context omitted.

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…

You never expose the bigserial, you generate a ID (like UUID) for external use/identification and simply have an index over that column for fast selects.

Having an index over the uuid is equivalent to it being a PK, so why would you bother having both?

Re: PostgreSQL and UUID as Primary Key

#220

Post by bob1029 »

I see some comments conflating privacy of sequence statistics with global uniqueness considerations and UX.

If your concern is globally unique identifiers (i.e. so that you can merge tables across multiple instances of your database), then UUID is exactly what you want. This is entirely what it is designed for.

If your concern is the privacy of sequence statistics, then UUID incidentally solves your problem. It may not be precisely what you want, and could continue to leak private information depending on the specific variant used. If you want privacy of sequence statistics, then I would suggest something like a sha256 hash of the primary key concatenated with a cryptographic salt stored in a separate column. These make excellent identifiers in places like APIs and URLs.

If you desire a unique identifier that has a high quality UX, then this is in addition to the above columns. This sequence generally has lower entropy than the cryptographic approach (or the UX would suffer), so additional measures should be taken to protect the privacy of the identifiers (e.g. expire redemption tokens after a period of time and reissue via email).

Autoincrementing integers are really nice if you don't actually need UUIDs. Lots of tricks you can apply with them.

Post reply on HN