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

101–110 of 163 posts

Re: Choosing a Postgres primary key

#101

Post by hnarn »

victor106 wrote: post #90
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…

Are sequential id’s a security risk? In one of our systems we’ve seen customer guess at other accounts by just incrementing the sequence. The rule of thumb I used to use is if an Id is going to be used for lookups or being exposed externally use uuid otherwise us sequential. The hard thing about the above rule is that it’s hard to tell when you are designing the db if the id will be used externally/for lookups or not…

"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 leak the entire table instead.

There are much better solutions for this, like server-side prepared queries that do not simply return on an ID but rather as the result of a join, or just proper security practices in general, rather than reactively making something that was once guessed simply harder to guess.

Also, something like an ID is in my experience something that will be referred to orally between two human beings when discussing a problem, like referring to user 6201 or discussing invoice 540567. When you switch to UUIDs you're basically also saying "no human will ever have to say this out loud".

Re: Choosing a Postgres primary key

#102

Post by spookthesunset »

onphonenow wrote: post #47
traceroute66 wrote: post #3

Honestly that's a poor blog post. Randomly concludes "the best time-based ID seems to be xid" without saying why or comparing to others e.g. ksuid, UUIDv7 etc ("xid" is only mentioned twice in the entire blog, first in the above statement and second a link to the reference implementation). Equally unfortunate that they picked "xid" as their supposed "best" because Postgres has an internal identifier that is also call…

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.

Re: Choosing a Postgres primary key

#103

Post by spookthesunset »

traceroute66 wrote: post #45
osigurdson wrote: post #26

Earlier quoted context omitted.

>> Exposing predictable identifiers to the world is never a good thing. Sometimes it doesn't matter. Example below: https://news.ycombinator.com/item?id=34451344

> Sometimes it doesn't matter. Example below: There is a saying for the examples you and others are posting ...."The exception rather than the rule" Posting contrived examples in order to attempt to prove a point. For the majority of cases, a random ID remains the better option. But unfortunately developers still treat security as an afterthought. They continue to use "serial" because of what can only be described as…

> For the majority of cases, a random ID remains the better option.

If your ID is going to be exposed to a human you have to admit that random UUID's are kinda clumsy.

One other pro to UUID's that I don't see discussed is you can generate them client side and be 99.9999999% assured that they won't collide when stored in the DB. I kinda forget our use case for this but I think it was to make "create" and "update" REST calls a lot easier.

Re: Choosing a Postgres primary key

#104

Post by spookthesunset »

philliphaydon wrote: post #88
osigurdson wrote: post #77

Earlier quoted context omitted.

For sensitive information it should not be trivially guessable. Naturally, we need encryption and auth, but using not guessable identifiers multiplies the (already small) probability of successful attack by 1/2^128 so it is a good idea.

There's always going to be valid reasons to have non-guessable identifiers. But it shouldn't be used for security. It's not a replacement for not checking resource access. Sometimes it's not 'sensitive', and even when it is, it doesn't really matter. An invoice id, doesn't matter. You need to be logged in to access it, and when logged in, you can only access your own invoice. If you're going to try discover other inv…

> It's not a replacement for not checking resource access.

If I had a hundred bucks every time I've seen a website fail to do this access check... I'd be a thousand-aire.

Re: Choosing a Postgres primary key

#105

Post by bob1029 »

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.

True, but then you have to plan ahead quite a bit.

The other advantage of UUIDs is in completely decoupled environments that need to be able to share entities with each other. In this situation, serialization of activity is not a concern at all - we simply wish to prevent collisions of keys across the way.

Re: Choosing a Postgres primary key

#106

Post by oslac »

victor106 wrote: post #90
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…

Are sequential id’s a security risk? In one of our systems we’ve seen customer guess at other accounts by just incrementing the sequence. The rule of thumb I used to use is if an Id is going to be used for lookups or being exposed externally use uuid otherwise us sequential. The hard thing about the above rule is that it’s hard to tell when you are designing the db if the id will be used externally/for lookups or not…

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

Re: Choosing a Postgres primary key

#107

Post by aniforprez »

ldjb wrote: post #10
traceroute66 wrote: post #3

Honestly that's a poor blog post. Randomly concludes "the best time-based ID seems to be xid" without saying why or comparing to others e.g. ksuid, UUIDv7 etc ("xid" is only mentioned twice in the entire blog, first in the above statement and second a link to the reference implementation). Equally unfortunate that they picked "xid" as their supposed "best" because Postgres has an internal identifier that is also call…

Collaborative databases (Wikidata, TheMovieDB, VNDB, etc.) all use serial identifiers. What is the problem with this? These websites don't want to hide how many entries they have (they tend to promote them), and it doesn't really matter if you iterate through all the numbers – the data is available through open licences anyway. I think there are many situations where you don't want to expose predictable identifiers,…

If your data is public and can be scraped anyway, obviously a serial identifier doesn't matter. If your data is sequestered between accounts or has tenants all sharing the same database or API, that's just one accidental permission error away from being able to scrape every single record. If your customer's data is meant to be private, simply easier to generate unique IDs for each record. Plus it makes it incredibly easy for competitors to see the size of your business simply by signing up for an account and looking at the IDs.

Re: Choosing a Postgres primary key

#108

Post by AtlasBarfed »

cameronh90 wrote: post #22
ammmir wrote: post #9

While this is a good overview of the options for primary key generation, there's no silver bullet here. Most projects that are using SQL should just use the gold standard: an auto-incrementing integer for an internal primary key. And then decouple the public-facing primary key from it into a separate column, whether it be ULID, UUID, or a random-project-slug-123. Also, during debugging, it's a lot nicer to look at sh…

Why would you use an integer primary key and a public facing UUID? That seems like it's the worst of both worlds: ugly externally visible identifiers, record bloat, a database that you can't easily merge in the event of backups or DR, and having to roundtrip to the DB before you know the ID of a record. I personally stick to UUIDs in pretty much all cases, with the exception of where there are justified and benchmark…

IMO use a UUID + a "type code" so something like:

xxxx-xxxx-xxxxxxxx-xxxx-CUST

xxxx-xxxx-xxxxxxxx-xxxx-ADDR

It makes seas of UUIDs much easier to reason about

Depends on your tolerance of wasted disk space for binary vs char, but you can shorten the binary to base64 or use a record as a primary key if you want.

Other advantages of UUIDs:

- they can be generated by clients or by the server safely

- they can be concurrently and distributedly generated without a central sequence blocking/locking ID generation

- they are distinct/unique across system migrations and mergers of systems / data / table

- time UUIDs can encode some info about when generated which can help with forensics / debugging in production

- no database-specific behavior for sequence generation, no extra database object for the sequence

- probably helps with data warehouse / data oceans for keeping data distinct and tracing back to source system

- similarly to that, for integrating systems, also makes the ids unique across system boundaries

- they are a bit more secure as stated elsewhere

Re: Choosing a Postgres primary key

#109

Post by arp242 »

dspillett wrote: post #96
arp242 wrote: post #69

Earlier quoted context omitted.

You can use bigserial instead of serial, which goes up to 9223372036854775807.

If your DB supports unsigned integers, or starting sequences from -2,147,483,648, you can double the address range. But if you are at all worried that you'll get within a couple of orders of magnitude of MAXINT32 in the lifetime of your application then you should immediately jump to 64-bit values. Doubling is often just noise and the cost of refactoring if you approach MAXINT32 much faster than expected is more or a…

PostgreSQL doesn't have unsigned integers.
Post reply on HN