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

11–20 of 163 posts

Re: Choosing a Postgres primary key

#11

Post by williamdclt »

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 create a new digital entity for the same real-life entity is almost always useful and expected ("the setup of this user is all messed up, just disable it and create a new one")

- attributes that you thought were immutable actually are not ("surely the 'originally scheduled time' of an event is an immutable property" - except when you have a bug and events are scheduled at the wrong time and you need to fix data)

- the concept of "immutable identity" is often pretty subjective in the real world. We generally agree that a person has an immutable identity, sure, but is a 9am appointment that's moved to a week later the same appointment, or a new one? Depends on who you ask, depends on what purposes you need this concept of "identity" for.

Re: Choosing a Postgres primary key

#12

Post by williamdclt »

silvestrov wrote: post #8
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…

Semantics tend to change over time, so you will now have to change the meaning of your key. Did you know that sometimes the same social security number is assigned to multiple persons? In this case a person can change social security number. Good luck updating all of your database foreign keys in this case.

Updating foreign keys in a database is at least doable; updating external systems that have a reference to this entity is impossible

Re: Choosing a Postgres primary key

#13

Post by the_duke »

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…

Almost every time I picked a natural key I have regretted it in the long run.

Requirements change, or index sizes get bloated and hamper performance, or you need a nice, short ID for URLs , or foreign keys get more complicated with compound primary keys, or ...

Natural keys are nice in theory, but not so much in practice.

Especially if we are talking about active databases where large migrations are a burden.

Re: Choosing a Postgres primary key

#14

Post by vlovich123 »

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…

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.

Re: Choosing a Postgres primary key

#15

Post by jetpackjoe »

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.

Re: Choosing a Postgres primary key

#16

Post by xaferel »

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.

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.

Re: Choosing a Postgres primary key

#17

Post by grncdr »

xaferel wrote: post #16
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 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.

Re: Choosing a Postgres primary key

#18

Post by thewataccount »

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'm strongly against these, I've always regretted it.

SKUS, emails, etc all seemed liked good keys. They're always unique right?

Until one day when they decided to rename some skus, they suddenly want family accounts, you realize you really do want the ability to have duplicates so you can keep historical copies without ripping up your entire database.

Semantics change. A UUID/whatever does not.

I've learned you should never ever use a natural key. PKs are extremely difficult or near impossible to replace depending on your application, and if that really means using serial id's, or UUIDs, or extra lookups - it's worth doing that instead of using natural keys.

Re: Choosing a Postgres primary key

#19

Post by filleokus »

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.

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

Re: Choosing a Postgres primary key

#20

Post by ThePhysicist »

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.

Interesting approach, will consider that in the future! Though not sure if it's a good idea as that couples internal & external IDs, i.e. it's not possible to change one without also changing the other (but also not sure if that's really an issue).
Post reply on HN