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

41–50 of 163 posts

Re: Choosing a Postgres primary key

#41

Post by jeremyjh »

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…

Yes to me xid has some pretty significant downsides. It is composed of a timestamp, a machine identifier, a process id, and then a sequence that starts from a random value. It can be a bit faster because it doesn't have to generate any random values to make a new ID, but I don't see how it would ever work client-side. I much prefer the simplicity of UUIDv6 and ULID, and they both perform nearly as well. UUIDv6 basically is ULID but without the standard base32 representation, which I think is unfortunate.

Re: Choosing a Postgres primary key

#43

Post by dchuk »

djbusby wrote: post #33
nargella wrote: post #31

Disclaimer: not a dba so my terms might not be appropriate I’ve seen uuid4 which replaces the first 4 bytes with a timestamp. It was mentioned to me that this strategy allows postgres to write at the end of the index instead of arbitrarily on disk. I also presume it means it has some decent sorting. [inspiration]( https://github.com/tvondra/sequential-uuids/blob/master/sequ... )

I use ULID, 128 bits, time and great sorting https://github.com/ulid/spec

Is there any way to have the database generate these automatically vs your application?

Re: Choosing a Postgres primary key

#44

Post by quesera »

CPLX wrote: post #23
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…

You really care about exposing a serial number scheme for a list of books your company publishes, or the identifier for each of the various hotels you own, or the cities you have an office in or something?

It's not only about predictability.

Exposing a serial number can also be competitive intelligence. Number of users, transactions, etc.

Sometimes this matters.

Re: Choosing a Postgres primary key

#45

Post by traceroute66 »

osigurdson wrote: post #26
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…

>> 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 sheer ignorance, i.e. demonstrably false misunderstandings about database technology.

Case in point, I placed an order on an e-commerce site a couple of weeks ago. I was not best pleased to be given a tracking URL that read: "https://example.com/order/WEB-nnnnn", where nnnn was clearly an incrementing number. Such a thing is inexcusable in 2023 !

It took a lot of strength to resist the temptation !

Re: Choosing a Postgres primary key

#46

Post by cpburns2009 »

dchuk wrote: post #43
djbusby wrote: post #33

Earlier quoted context omitted.

I use ULID, 128 bits, time and great sorting https://github.com/ulid/spec

Is there any way to have the database generate these automatically vs your application?

The common databases don't support natively support generating ULIDs to my knowledge. You can usually find extensions if you prefer generating them in the database instead of the application. I generate them in the application, and store them as a UUID in PostgreSQL to avoid needing any database extensions.

Re: Choosing a Postgres primary key

#47

Post by onphonenow »

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 recently and because of the issues copying uuids from the screen they insisted on screenshots that included the url bar showing uuid. 100% trying to tell them the ID over try the phone or even rekey would run into issues.

Give me back my sequential invoice ids!

Re: Choosing a Postgres primary key

#49

Post by magicalhippo »

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…

For one of our customers we integrate heavily with their ERP system.

They had nice sub-10 million integers as order numbers, so we used that as primary key for the order table (and as fk for 10+ child tables). Last year they changed ERP system, and now order numbers are much longer and can contain dashes.

Not the worst change, as converting integer to varchar is lossless, but we had to go over all the views and our code to make sure it could handle it.

Re: Choosing a Postgres primary key

#50

Post by rollcat »

nargella wrote: post #31

Disclaimer: not a dba so my terms might not be appropriate I’ve seen uuid4 which replaces the first 4 bytes with a timestamp. It was mentioned to me that this strategy allows postgres to write at the end of the index instead of arbitrarily on disk. I also presume it means it has some decent sorting. [inspiration]( https://github.com/tvondra/sequential-uuids/blob/master/sequ... )

Are there any clear downsides to sequential prefixes on UUIDs? I would imagine if you're producing new objects at a high enough rate, you'd have a lot of prefix collisions, which would hinder search times. I've never benchmarked to confirm that though.
Post reply on HN