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

81–90 of 163 posts

Re: Choosing a Postgres primary key

#81

Post by marcosdumay »

saiya-jin wrote: post #51
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…

I hate UUIDs with passion currently - not postgres but recently spent so much extra time on relatively small table (35 mil records in few columns) and doing some queries and updating subset of it. UUIDs there is stored in Oracle 'raw' datatype which to me is the worst combination possible, basically string stored in small binary blob, due to binary nature all needs to be converted to hex all the time for matching and…

AFAIK, the Oracle way is to store them as Number. The type is made just large enough for them.

But if you want to talk about Oracle's usability, there are much larger fish to fry. I wouldn't recommend anybody to use that database.

Re: Choosing a Postgres primary key

#82

Post by Scarbutt »

mytailorisrich wrote: post #72
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…

As it happens in at least some jurisdictions invoice numbers have to be sequential by law.

That's orthogonal to having a programatic ID for them

Re: Choosing a Postgres primary key

#83

Post by saiya-jin »

marcosdumay wrote: post #81
saiya-jin wrote: post #51

Earlier quoted context omitted.

I hate UUIDs with passion currently - not postgres but recently spent so much extra time on relatively small table (35 mil records in few columns) and doing some queries and updating subset of it. UUIDs there is stored in Oracle 'raw' datatype which to me is the worst combination possible, basically string stored in small binary blob, due to binary nature all needs to be converted to hex all the time for matching and…

AFAIK, the Oracle way is to store them as Number. The type is made just large enough for them. But if you want to talk about Oracle's usability, there are much larger fish to fry. I wouldn't recommend anybody to use that database.

more often than not one doesn't have any choice in DB, especially when it comes bundled in product like in my case

Re: Choosing a Postgres primary key

#84

Post by geophile »

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?

Re: Choosing a Postgres primary key

#85

Post by michaelmior »

seanhunter wrote: post #68
michaelmior wrote: post #39

Earlier quoted context omitted.

Probably not, but you definitely should care about not issuing sequential credit card numbers. Probably not the best example, but I don't think it's hard to imagine some scenario in between the two that still presents a concern.

You can't just use sequential numbers for credit cards because they have a builtin checksum validation called "Luhn's algorithm" https://www.creditcardvalidator.org/articles/luhn-algorithm

Sure, that wasn't really my point though. My point was that there are some cases where sequential identifiers would cause security concerns and some cases where they would not.

Re: Choosing a Postgres primary key

#86

Post by PestoDiRucola »

marcosdumay wrote: post #76
PestoDiRucola wrote: post #25

Earlier quoted context omitted.

One thing I don’t see being mentioned in this thread (I only skimmed the article, so I don’t know if it’s mentioned there) is that you can run out of numbers when using serial, as they have a max, so if you are planning to have a table which will have over 2147483647 rows, then you might look into other types to use as a unique identifier.

> you can run out of numbers when using serial Yes, that's why you use bigserial.

But the comment I replied to was mentioning serial though.

Re: Choosing a Postgres primary key

#87

Post by orf »

Not a fantastic post, which is a shame as Supabase is quite an interesting company.

The answer is almost always "use biginteger identity", and almost never "use integer serial".

UUIDs have a place but are often better suited in larger, distributed and more complex data stores than postgres.

Using `xid` is such a poor choice I'm surprised it was even mentioned.

The "key" thing to remember is you don't have to expose your primary key to the world. Use UUIDs or shortcodes or whatever for external representations. Use bigints internally. This will prevent a world of pain.

Re: Choosing a Postgres primary key

#88

Post by philliphaydon »

osigurdson wrote: post #77
philliphaydon wrote: post #64

Earlier quoted context omitted.

Changing the nnnnn should not magically give you an invoice. If your argument That a guid is better used here then you’re wrong. If the endpoint is not secure it doesn’t matter if your used a sequential id or a random id/guid. You could brute force a discovery. You should always validate the input and verify the accessed invoice belongs to the person requesting it.

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 invoices, well you know the user who is trying to hit random urls to find an invoice that doesn't belong to him. If you want to prevent random signups from doing it, block users who don't have => 1 paid order from accessing the page entirely.

Claiming "Exposing predictable identifiers to the world is never a good thing." tho is just FUD. There are use cases. But it's not 'never' a good thing.

Re: Choosing a Postgres primary key

#89

Post by dspillett »

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…

> That seems like it's the worst of both worlds: ugly externally visible identifiers

Sometimes the external identifier is needed due to interaction with external systems, so it isn't really your choice as the DB/app designer.

>, record bloat,

Depending on the DB, the opposite can be true. In SQL Server if the integer key is the clustering key, which is usually the case for a table's primary key then you may get a smaller DB then using a UUID alone because the clustering key is included in all non-clustered indexes on the table (so the 4 or 8 bytes saved by just having the UUID and not also an integer ID is quickly lost to a larger amount of bloat).

> and having to roundtrip to the DB before you know the ID of a record.

For internal use you should just use the integer ID for the most part, the UUID or similar being for external references.

Unless of course the UUID is for security purposes (making enumerating records impractical for instance) in which case you'll be using the UUID in your own application directly. In these case you shouldn't look up the ID, just query by the UUID. Usually you are wanting to access the base record anyway so that isn't an extra JOIN, and if you are just referring to child tables (so wouldn't need to reference the main entity if not looking up the internal ID) the extra JOIN is usually insignificant, pulling back a single row, compared to the latency hit of a full round-trip to lookup the ID separately.

> I stick to UUIDs … with the exception of where there are justified … performance reasons not to.

A perfectly valid approach.

Though it does vary by DB, and what looks like a smell to someone with most posgres experience is often the more efficient method elsewhere, so you need to take that into account when looking at other projects (or working on your own if support for varied DBs in the backend is desired or a requirement).

Re: Choosing a Postgres primary key

#90

Post by victor106 »

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…

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. Requirements change later.

Post reply on HN