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

121–130 of 163 posts

Re: Choosing a Postgres primary key

#121

Post by hot_gril »

jetpackjoe wrote: post #15
ThePhysicist wrote: post #2

I've had good success with using auto-incrementing BIGINTs as internal IDs and creating an additional BYTEA field as external IDs. Foreign keys would be based on the internal IDs, anything user-facing would use external IDs. I think it's a good compromise as it keeps foreign key size small and still allows hiding internal structure from users.

Rather than use an extra column, I’ve taken to hashing the internal key (with a salt based on the entity type and some secret) to create the external facing ID.

This seems expensive and requires you to really know what you're doing with the cryptography. Why not just use a random external facing ID?

Re: Choosing a Postgres primary key

#122

Post by hot_gril »

williamdclt wrote: post #11
lysecret wrote: post #6

My opinion. Always if in any way possible pick a semantic key. There is usually something defining the thing you are working on. If there isnt work on your normalisation. Main benefits to this: Avoids accidental duplication (happens so much). Avoids additional round trips to fetch the id to make a mutation. Of course if you work on something where you don’t know what it is yet (actually humans are a good example for…

I'd heavily push for the exact opposite. Every single time I've seen a primary key being defined with a natural key, it turned out that this set of attributes wasn't as immutable as we thought actually and it caused a world of pain. I find that there actually rarely is something defining the thing you're working on. The concept of "immutable identity" is rarely a useful thing in digitalized systems: - being able to c…

> Every single time I've seen a primary key being defined with a natural key, it turned out that this set of attributes wasn't as immutable as we thought actually and it caused a world of pain.

Yep, happens to the best of us. Never mess around with this, just use a bigserial.

Re: Choosing a Postgres primary key

#123

Post by cameronh90 »

hot_gril wrote: post #119
cameronh90 wrote: post #114

Earlier quoted context omitted.

What I mean is that in most cases, BOTH autoinc int and UUID is wasteful. For example if you have a situation where you really need the high performance of an integer ID like in your SQL Server example, why introduce a UUID into the equation at all? If you are in the unusual situation of needing such extreme performance that you're worrying about 4 vs 8 bytes on the PK, but also need to obfuscate the public facing ID…

> What I mean is that in most cases, BOTH autoinc int and UUID is wasteful. > I disagree. UUIDs are very useful even as internal identifiers in any area where performance isn't your top concern. If performance isn't your top concern, having both seems fine. If it is, the int is probably faster anyway.

If you don't care about performance, then obviously it's fine from a performance perspective to have both... but what does it actually give you other than problems?

Re: Choosing a Postgres primary key

#124

Post by je42 »

One note regarding uuid. It doesn't need to imply it is random. That's specific v4.

V5 are predictable uuids. That combine a ns uuid and a string, via sha1 based one way mapping resulting in a uuid.

Re: Choosing a Postgres primary key

#125

Post by meekaaku »

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…

Standard accounting practices require that invoices/receipts have sequential numbers. So it has to be integer, or some kid of sequential alphanumeric.

Even if one uses UUID as primary key, it should have separate invoice_no (in whatever formatting they require such as 2023/001, or 1001,1002...) which is the human readable and referenced number.

This is especially important if you are developing a multi-tenant system where the invoice number, say 2023/001, may exist for more than one tenant.

Re: Choosing a Postgres primary key

#126

Post by hot_gril »

cameronh90 wrote: post #123
hot_gril wrote: post #119

Earlier quoted context omitted.

> What I mean is that in most cases, BOTH autoinc int and UUID is wasteful. > I disagree. UUIDs are very useful even as internal identifiers in any area where performance isn't your top concern. If performance isn't your top concern, having both seems fine. If it is, the int is probably faster anyway.

If you don't care about performance, then obviously it's fine from a performance perspective to have both... but what does it actually give you other than problems?

Not having to import a DB extension or get a lib to generate the UUIDv4s. Not exposing your primary keys. Keeping an ordering of insertion, if you need that. Not having to read huge strings during debug. Overall it's kinda minor as UUID is at least a much better option than semantic PKs, bigger concern is performance.

Performance-wise, bigserial is probably a lot faster than UUID as a PK, even if you also have a UUID secondary index. PKs are used in tons of places in the DB and in your server-side code. The DBMS is also gonna be optimized around the regular way of doing things.

What problem do you get from bigserial? The one thing I can think of is, if you're trying to merge two databases together for whatever reason, you can't just copy the entire rows. So you copy all cols except the ID, let them get new IDs, and use the secondary identifiers when copying in related tables. It's more work, but you don't do this often, and if you do, you can automate it.

Re: Choosing a Postgres primary key

#127

Post by ASalazarMX »

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…

> In one of our systems we’ve seen customer guess at other accounts by just incrementing the sequence.

What can they do with that guess? A sensible access control would not even let them see if the account is active, cancelled, or invalid.

Re: Choosing a Postgres primary key

#128

Post by hot_gril »

hcarvalhoalves wrote: post #52
lysecret wrote: post #6

My opinion. Always if in any way possible pick a semantic key. There is usually something defining the thing you are working on. If there isnt work on your normalisation. Main benefits to this: Avoids accidental duplication (happens so much). Avoids additional round trips to fetch the id to make a mutation. Of course if you work on something where you don’t know what it is yet (actually humans are a good example for…

To add to the other comments mentioning why this is difficult in practice: It's the "Ship of Theseus" paradox [1]. Choosing a semantic key means mixing identity and attribute, while a synthetic key solves by assuming "constitution is not identity". Since a digital system is a model of the world, a synthetic key allows the system to address objects in this internal model without assuming a particular interpretation of…

Ship of Theseus is the thing I was looking for. Our dept has repeatedly suffered from this same mistake. Our team was stuck with this damn three-part PK that also gets exposed to partner teams using our service as an identifier, and is now baked into their code and their clients' code. Now we took it out as the PK, but it's still used as an identifier. We're making a bunch of hacks to ensure that ID stays unique.

The analogy went further when the partner team asked, can we have a "stable ID" that doesn't change even if that identifier changes. Our team was close to exposing our new row-level keys again. I asked, if the name changes, is it the same thing still? What if the name and the other attributes change? This isn't like a social media post that obviously has an identifier; we're modeling physical objects. Why do you need this feature again? Turns out they didn't need the feature, or even quite understand what they were asking.

The way our application is, really we didn't have to expose any identifier with guarantees about uniqueness.

Re: Choosing a Postgres primary key

#129

Post by adamckay »

oslac wrote: post #106
victor106 wrote: post #90

Earlier quoted context omitted.

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.

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 register another and get ID 57978 then I know 124 new users have signed up in that time.

1 - https://cheatsheetseries.owasp.org/cheatsheets/Insecure_Dire...

Re: Choosing a Postgres primary key

#130

Post by cameronh90 »

hot_gril wrote: post #126
cameronh90 wrote: post #123

Earlier quoted context omitted.

If you don't care about performance, then obviously it's fine from a performance perspective to have both... but what does it actually give you other than problems?

Not having to import a DB extension or get a lib to generate the UUIDv4s. Not exposing your primary keys. Keeping an ordering of insertion, if you need that. Not having to read huge strings during debug. Overall it's kinda minor as UUID is at least a much better option than semantic PKs, bigger concern is performance. Performance-wise, bigserial is probably a lot faster than UUID as a PK, even if you also have a UUID…

What I said was what's the point of having both? If you have both, you still need a UUID generation library. As I said previously, I can see the performance advantages of using ints in some cases, but in my experience they're relatively rare so I don't see the point to default to them.

As for the other advantages of UUID, I and others have covered many of them above: security, fewer roundtrips, shardable, easier to find in logs, data warehousing, backups, disaster recovery, etc. etc. The advantages of UUIDs are so great that my view is, in any serious app, you actually need to justify _not_ using them with concrete performance data that shows why using ints is a worthwhile trade-off. There are cases where ints make sense.

However my main point is, as I said, that having both ints AND uuids is of very limited usefulness.

Post reply on HN