Choosing a Postgres primary key
supabase.com
Choosing a Postgres primary key
1–10 of 163 posts
Re: Choosing a Postgres primary key
#2I'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.
Re: Choosing a Postgres primary key
#3Honestly 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 called "xid" and is very much NOT to be used as a primary key !
Downplays the many issues with "serial", including somehow thinking the word might "might" has a place next to the words "not want to expose them to the world though" .... you DON'T, full stop. Exposing predictable identifiers to the world is never a good thing.
I'm not really sure what that blog post is supposed to be achieving really. I didn't learn anything.
Re: Choosing a Postgres primary key
#4Pretty uninformative post. Goes from count(*) to some uuids, but fails to see the bigger picture.
Depending on the data, but assuming most data isn't big data:
- Use integers internally, maybe suffixed by a shard-id to prevent collision, but keep order.
- Use (random) external ids to access from the outside.
Note that certain uuids will still leak some information: time between records, number of machines, etc.
Re: Choosing a Postgres primary key
#5Good intro article. I'd always heard that serial ints aren't guaranteed to be ordered but never knew why (because they are generated non-transactionally..so if an INSERT transaction rolls back the id that would have been used is effectively consumed/skipped).
What I see a lot in practice is a bigint numeric id for internal use (better for joins, FKs) and also a textual token for public use, perhaps with a typed prefix indicate the type of record it's identifying (U-AS234FDS for User, etc)
Re: Choosing a Postgres primary key
#6My 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 that) uuid or int might make sense but I hear so many times picking non semantic as a default.
Re: Choosing a Postgres primary key
#7I'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.
I agree, this is the way to do it. Anything originating from the outside is bound to change, being email address, social security numbers, ...
The post also completely ignores foreign keys.
It is an absolute advantage to size and speed to have foreign keys to be int4/int8 and not an email address or UUID.
Re: Choosing a Postgres primary key
#8My 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.
Re: Choosing a Postgres primary key
#9While 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 short primary keys than to have UUIDs flooding the screen.
Re: Choosing a Postgres primary key
#10Honestly 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…
Collaborative databases (Wikidata, TheMovieDB, VNDB, etc.) all use serial identifiers. What is the problem with this? These websites don't want to hide how many entries they have (they tend to promote them), and it doesn't really matter if you iterate through all the numbers – the data is available through open licences anyway.
I think there are many situations where you don't want to expose predictable identifiers, but there are also examples where predictable identifiers may actually be beneficial.