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

Choosing a Postgres primary key

supabase.com

Post reply on HN
Print view View original

141–150 of 163 posts

Re: Choosing a Postgres primary key

#141

Post by remram »

abujazar wrote: post #92
glacials wrote: post #61

The author misses one advantage of UUIDs: if you’re working in high-throughput distributed systems, serial IDs create a bottleneck and single point of failure in the service handing out IDs. With UUIDs any service can generate an ID itself and tell downstream services about it in parallel—even if one of them is down, slow, or needs retrying.

This can also be achieved in distributed systems by having each node skip IDs equivalent to the number of nodes in the cluster. E.g. node 1 in a 5 node cluster assigns ids 1, 6, 11 and node 2 assigns 2, 7, 12 and so on.

You can also hand out ranges. Instead of asking the system for one ID, you ask it for 1000 contiguous IDs, and you ask for another range when you run out. This will reduce the load on the ID-creating-system by 1000x (or your choice of number) and has the advantage that systems don't need to know how many peers they have. (beware of the herd though, if you don't persist it every system will come asking at startup)

Re: Choosing a Postgres primary key

#142

Post by perrygeo »

geophile wrote: post #84

What does "SORT terribly" mean? That there is no semantically useful ordering? Well of course not, that's not what they are designed for. If you want ordering by time, then include a time-based column and sort on it. Does it mean that sorting performance is bad on UUID columns? Why? And what does "index terribly" mean? You can index UUID columns just fine, so is it a performance concern? What is the concern?

Because B-tree indexes are ordered, rows likely to be adjacent on disk (written in time order) are not at all adjacent in the index and vice-versa. There is no "hot page" in the cache representing recent records; the index node you need for any given uuid is random and makes your internal index pages effectively uncacheable. The result is increased IO and cache thrashing.

Re: Choosing a Postgres primary key

#143

Post by josevalerio »

lawrjone wrote: post #54
djbusby wrote: post #33

Earlier quoted context omitted.

I use ULID, 128 bits, time and great sorting https://github.com/ulid/spec

Wrote about my experience using ulids in Postgres if people are considering it: https://blog.lawrencejones.dev/ulid/

Good post thanks :)

Re: Choosing a Postgres primary key

#144

Post by victor106 »

adamckay wrote: post #129
oslac wrote: post #106

Earlier quoted context omitted.

UUID does not protect your records, that is, it is not a security measure against what you describe.

UUID is a part of the protection. If your access controls fail then preventing an attacker from enumerating through data by incrementing an integer is protection from Insecure Direct Object Reference. [1] Even if there are no vulnerabilities in access controls it can also prevent competitors from knowing how busy your platform is. If I register a new account on your system and I get ID 57854 and a week later I regist…

Thank you so much for this.

Re: Choosing a Postgres primary key

#145

Post by yellowapple »

spookthesunset wrote: post #102
onphonenow wrote: post #47

Earlier quoted context omitted.

I really hate this trend away from basic IDs. I feel like it's driven by folks who've never actually worked in the real world. I got account paperwork recently where the company ID account ID and invoice ID were all uuids. 100% this company if I call them will not use this BS to lookup my account and will instead use something easier try to guess like a company phone number. I also had to do some support tickets rece…

At some point those sequential integer ID's will become so long they might as well be GUIDs. Of course you could "compress" the integer using some kind of encoding scheme similar to base64. Then your long integer becomes a few characters. ... Maybe though. Even ID's in the millions are probably easier to read than a long ass GUID.

> At some point those sequential integer ID's will become so long they might as well be GUIDs

Worth bearing in mind that an unsigned 32-bit integer sequence can uniquely identify 4,294,967,295 records. If you're really storing that many records (let alone enough to exhaust an even bigger integer sequence), the length of the identifier is probably the least of your worries :)

Re: Choosing a Postgres primary key

#146

Post by yellowapple »

insanitybit wrote: post #115
hnarn wrote: post #101

Earlier quoted context omitted.

"Protecting" records by making IDs hard-to-guess just seems like putting the responsibility in the wrong place. If you're so worried about people getting their hands on the wrong records, I'd be more worried about your lack of trust in the application that queries that database in the first place: remember, even if you do make the IDs hard to guess, your "untrusted application" might at some point decide to simply le…

> "Protecting" records by making IDs hard-to-guess just seems like putting the responsibility in the wrong place. It's a pretty powerful implementation of capability based security.

I would hope that a capability-based security system entails considerably more than just knowledge of an ID.

Re: Choosing a Postgres primary key

#147

Post by Too »

glacials wrote: post #61

The author misses one advantage of UUIDs: if you’re working in high-throughput distributed systems, serial IDs create a bottleneck and single point of failure in the service handing out IDs. With UUIDs any service can generate an ID itself and tell downstream services about it in parallel—even if one of them is down, slow, or needs retrying.

This also enables treating inserts as upserts, allowing safe retries without risk of creating duplicates.

Speaking of. Does pg have any mechanism to protect double creates while using auto increments? Is there any way to provide a request-id from the client?

Re: Choosing a Postgres primary key

#148

Post by Too »

grncdr wrote: post #17
xaferel wrote: post #16

Earlier quoted context omitted.

That's a good idea but doesn't it need 2 round trips if you use an auto-increment primary key? First insert and then update by hashing the new id.

Not the poster you’re replying to, but with this approach you generally don’t store the hashed identifier. Just encode/decode at the application boundaries.

How do you decode a hash?

Re: Choosing a Postgres primary key

#149

Post by avinassh »

perrygeo wrote: post #142
geophile wrote: post #84

What does "SORT terribly" mean? That there is no semantically useful ordering? Well of course not, that's not what they are designed for. If you want ordering by time, then include a time-based column and sort on it. Does it mean that sorting performance is bad on UUID columns? Why? And what does "index terribly" mean? You can index UUID columns just fine, so is it a performance concern? What is the concern?

Because B-tree indexes are ordered, rows likely to be adjacent on disk (written in time order) are not at all adjacent in the index and vice-versa. There is no "hot page" in the cache representing recent records; the index node you need for any given uuid is random and makes your internal index pages effectively uncacheable. The result is increased IO and cache thrashing.

> Because B-tree indexes are ordered, rows likely to be adjacent on disk (written in time order) are not at all adjacent in the index and vice-versa.

But this still doesnt matter, right? If you want time ordering you'd prolly have some field like `created_at`

Post reply on HN