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

71–80 of 163 posts

Re: Choosing a Postgres primary key

#71

Post by Ankhers »

filleokus wrote: post #19
jetpackjoe wrote: post #15

Earlier quoted context omitted.

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.

That's really clever. Have you encountered any problems with it in practice?

Not the person you replied to, but I have had some issues with this in the past. Though it is more with how it was done than the approach itself.

In this project I do not believe the IDs were always encrypted when being sent to the user. So we sometimes had to guess whether we received an encrypted ID vs a regular integer ID because it is possible for the encryption algo that was used to return a sequence of numbers.

Re: Choosing a Postgres primary key

#72

Post by mytailorisrich »

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…

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

Re: Choosing a Postgres primary key

#73

Post by cccybernetic »

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…

The existence of a synthetic key contradicts the idea that we're modeling the "world". Of course, this is a debate that’s been around forever, and I understand the advantages of synthetic keys, but I've found that the intuitive elegance of a natural key will often flow into the business logic, untangling nests of code dedicated to id look-ups, id-matching, filtering, mapping, and the general slicing and dicing and shaping of data. Queries that once referenced obscure ids now point directly to fields which intuitively make sense: yes, if I want to update the user_apps table, I know or can reasonably infer pk(user_id, app_id), and I know those values and I have them right here in my pocket, don't need to look them up, and they mean something very tangible and real and I don’t have to say “…WHERE id = ‘…’”

Of course, nothing is ever perfect and natural keys have their issues, especially when migrating data, but there’s something about them that’s always “clicked” with how I reason about software systems.

Re: Choosing a Postgres primary key

#74

Post by arp242 »

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.

One thing I've done is using the customer name codepoints of every character + object ID formatted as base-36.

So with a customer email of 'martin@arp242.net' and an object ID of 52 you end up with 1563 + 52 = 1615, or 18v in base-36. You can add a "base number" to make it a but larger, e.g. 50,000 so it becomes "13tr".

I'm sure people can figure this scheme out with enough effort; it's certainly not cryptographically secure, but it's "hidden enough" for many purposes, not much longer than numeric IDs (shorter in many cases), doesn't require any special DB-fu, and is reversible if you know the customer (which you usually do).

Re: Choosing a Postgres primary key

#75

Post by mytailorisrich »

lbriner wrote: post #30
vlovich123 wrote: post #14

Earlier quoted context omitted.

But if you’re handing out a random ID, you still need to have an index over that ID to have efficient lookups of random ID -> internal ID right? One advantage is that you only need to resolve it once at the edge and then internally you use the external facing value. Are there any others I’m missing? I agree, I would have loved a deeper dive in xid since it seemed to clearly outperform everyone else.

Not necessarily. The idea here is that the id can be exposed publically because it is random. The problem it solves is someone sees a page /accountdetails?id=123 and can easily look for /accountdetails?id=124 and assume it is likely to be valid. If you use a random id, you cannot quickly know what other ids exist which makes looking for unauthorized access to objectids much harder.

Unauthorized access is dealt with by implementing correct authorization procedures, not by obfuscating IDs, which valid ones can still be discovered through brute force or other means.

In your example those simple, sequential IDs should have no impact on security.

Re: Choosing a Postgres primary key

#76

Post by marcosdumay »

PestoDiRucola wrote: post #25
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…

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.

Re: Choosing a Postgres primary key

#77

Post by osigurdson »

philliphaydon wrote: post #64
traceroute66 wrote: post #45

Earlier quoted context omitted.

> 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…

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.

Re: Choosing a Postgres primary key

#78

Post by thefreeman »

philliphaydon wrote: post #64
traceroute66 wrote: post #45

Earlier quoted context omitted.

> 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…

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.

Look i’m not taking a stance on this argument in general, but your statement that you can brute force a guid seems misguided. If you can brute force a guid url parameter you might as well just brute force the guid session token.

Re: Choosing a Postgres primary key

#79

Post by craigkerstiens »

brightball wrote: post #62
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…

This is a very worthwhile point. You definitely need to prepare for readability if these ids will be customer facing.

While not a perfect solution to it, we use UUIDs throughout our application as IDs. But when we expose them in URLs or other places provide them as an encoded id (https://docs.crunchybridge.com/api-concepts/eid/). It at least makes them a little more compact, easier to copy/paste than UUIDs, and generally a cleaner look.

Re: Choosing a Postgres primary key

#80

Post by tpetry »

boloust wrote: post #36
cameronh90 wrote: post #22

Earlier quoted context omitted.

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…

You're right to point at performance as the main motivator for this setup. The primary key is included in all indexes, including non-clustered indexes, so in some cases there can be quite a large difference between UUID and integer PKs in terms of index size. UUID PKs are also more susceptible to fragmentation.

Thats not how PostgreSQL works. The primary key is only included in every secondary key for MySQL. PostgreSQL secondary indexes directly point at the page and rowid.
Post reply on HN