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

131–140 of 163 posts

Re: Choosing a Postgres primary key

#131

Post by okl »

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…

Two benefits of UUIDs:

1. You can create a series of related rows without hitting the DB to obtain the next integer.

2. During development, if you mix up ids you will get an error/empty result. With integer keys you might get a row you didn't intend to get, hiding the error.

Re: Choosing a Postgres primary key

#132

Post by okl »

rco8786 wrote: post #5

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

I guess mean "consecutive" or "sequential" instead of "ordered"

Re: Choosing a Postgres primary key

#133

Post by thewataccount »

cccybernetic wrote: post #73
hcarvalhoalves wrote: post #52

Earlier quoted context omitted.

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

I originally thought the same - I manage the backend/database for a small team that works on our warehouse/website integration with a legacy ERP system.

My experiences with semantic keys has been awful. I've been told "Oh this [property] will either never need to change, and never have duplicates" of properties you'd think really should never change, several times.

Somehow a different department decided to change the skus. I've seen emails need to either have duplicates or be changed. I've seen a few instances of order numbers from the ERP system having duplicates under certain circumstances. A few cases like GTINs where every item should already have a unique one assigned - until we have an item that is missing one and will never be assigned one.... The big one is needing to archive things or keep histories of objects - if you use a synthetic key it's super easy to just add a "active" flag and you have to change very little code.

I totally get wanting to avoid extra queries and extra steps, but if the need to change a pk _ever_ occurs it's terrible to near impossible (in the case of external dependencies). Many frameworks like django make it fairly easy to naturally add that extra step without needing to make spaghetti to do your lookups

Re: Choosing a Postgres primary key

#134

Post by waspight »

I only use uuid as PK in Postgres. So many benefits, it is easy to migrate data between environments, client can generate its own ids etc.

I don’t know about performance but I think in most cases that is not a big concern anyway.

Re: Choosing a Postgres primary key

#135

Post by hot_gril »

cameronh90 wrote: post #130
hot_gril wrote: post #126

Earlier quoted context omitted.

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

> If you have both, you still need a UUID generation library

Oops, yeah, that's true.

The point of having both is that you're already using the bigserial as the PK, and you also have opaque, external-facing identifiers that refer to particular rows in some tables without you having to expose any PKs. Might only be some tables, might be some other kind of string rather than a UUID. There may even be multiple ways for users to refer to something in your system; you keep that all separate from your PKs.

> security, fewer roundtrips, shardable, easier to find in logs, data warehousing, backups, disaster recovery

I see security pros/cons on both sides; exposing PKs to users does feel wrong to me though. I don't see how UUID PKs reduce roundtrips; if your API takes a UUID, you don't have to convert it to a row ID right away, only when you're actually querying what data the client wants. If you're printing row keys in logs, you ought to prefix them either way (like "user:35" or "user:deadbeef-..."). For backups/recovery, I haven't found UUIDs helpful, maybe cause I never want to just copy whole rows.

A sharded DB takes special consideration and could go many different ways, so defaulting to UUIDs in anticipation of sharding one day is probably not going to help when that day comes. In some setups, the PK is just for that one node, and you have a global ID across nodes (which may be a composite of node-local PK + shard ID). Or you're switching to a specialized, not-so-relational DBMS for horizontal scaling. Like, serial IDs are a terrible idea in Google Spanner.

> you actually need to justify _not_ using them with concrete performance data

For what it's worth, I encountered this situation in a DB with millions of rows. UUID PKs were significantly increasing our overall application latency, so I switched us to bigserials. I'd rather not put newer systems on a track to hit that hurdle later on. It can start being a noticeable problem well before you're thinking about sharding.

Re: Choosing a Postgres primary key

#136

Post by s4i »

rollcat wrote: post #50
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.

If the prefix is incremented for every new ID, you essentially have the same problem as you do with serial: you leak information about the amount of rows in some timeframe.

As the link posted above mentions, you can alternatively use a timestamp-based prefix that wraps around after all the bits have been used. This one still leaks possible creation times of the record, so it's on par or better compared to UUIdv6, ULID, etc. (because here the exact creation time can't necessarily be deduced).

In all of these UUID solutions apart from the fully random v4, you are trading of the better index performance with some level of information leakage about the record the ID is associated with.

Re: Choosing a Postgres primary key

#137

Post by s4i »

grep_it wrote: post #35
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... )

It also has the advantage that the page being written to, the right most leaf at the end of the index, is likely to always be available in the page cache. With random you may need to constantly go to disk to fetch the page.

In this sequential UUIDs idea, I wonder how big of a deal it is if the prefix part wraps around often? E.g. using a timestamp-based prefix with 2 bytes, if you increase the prefix every 60 seconds, the prefix will be reset every 45 days or so (60 * 1000 * 2^16) according to that README. Does it make sense to fine tune this value based on the use case or what?

Re: Choosing a Postgres primary key

#138

Post by bmn__ »

arp242 wrote: post #65
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…

Just write a test case that "user 2" can't access "/order/WEB-nnnn" from "user 1" and that works too. You should have this test case anyway, even with random IDs. They can provide an extra "defence in depth" bonus, but they're of course no replacement for authentication checks. You're going to need some type of readable relatively small ID anyway, because things like "Hi there, I have a question about order b1a354c5-…

The canonical solution for that is to encode the ID appropriately. Your example with common choices:

as base64url (RFC 4648 §5): saNUxawrSZChiR8rT1N7CQ (22 octets)

as base85 (RFC 1924 §4): p&bTO@+boru-j3)#beDJ (20 octets)

as QR code: data:text/plain;charset=utf-8;base64,4paI4paA4paA4paA4paA4paA4paIIOKWiOKWhOKWiCDilogg4paIDQrilogg4paI4paI4paIIOKWiCAg4paE4paA4paI4paA4paI4paADQrilogg4paA4paA4paAIOKWiCAg4paI4paI4paA4paA4paI4paIDQriloDiloDiloDiloDiloDiloDiloAg4paA4paA4paE4paA4paIIOKWgA0K4paA4paI4paA4paA4paAIOKWhOKWiOKWhCDiloDiloDiloTiloDiloQNCuKWgCDiloDilogg4paI4paAICDiloQgIOKWgOKWiOKWhA0K4paA4paA4paI4paA4paIIOKWiOKWhOKWhOKWiCDilojiloDiloDiloANCuKWgOKWgOKWgCDiloDiloDiloAg4paA4paAICDiloAg4paA

Re: Choosing a Postgres primary key

#139

Post by hot_gril »

hot_gril wrote: post #122
williamdclt wrote: post #11

Earlier quoted context omitted.

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.

(Or other things like UUIDs, which I would not use, are still way better than semantic keys.)

Re: Choosing a Postgres primary key

#140

Post by arp242 »

bmn__ wrote: post #138
arp242 wrote: post #65

Earlier quoted context omitted.

Just write a test case that "user 2" can't access "/order/WEB-nnnn" from "user 1" and that works too. You should have this test case anyway, even with random IDs. They can provide an extra "defence in depth" bonus, but they're of course no replacement for authentication checks. You're going to need some type of readable relatively small ID anyway, because things like "Hi there, I have a question about order b1a354c5-…

The canonical solution for that is to encode the ID appropriately. Your example with common choices: as base64url (RFC 4648 §5): saNUxawrSZChiR8rT1N7CQ (22 octets) as base85 (RFC 1924 §4): p&bTO@+boru-j3)#beDJ (20 octets) as QR code: data:text/plain;charset=utf-8;base64,4paI4paA4paA4paA4paA4paA4paIIOKWiOKWhOKWiCDilogg4paIDQrilogg4paI4paI4paIIOKWiCAg4paE4paA4paI4paA4paI4paADQrilogg4paA4paA4paAIOKWiCAg4paI4paI4paA4paA4…

The problem with base64 is that it's still long and ugly, and also case-sensitive. I wouldn't want to say "order saNUxawrSZChiR8rT1N7CQ" over the telephone. Is it better than "order b1a354c5-ac2b-4990-a189-1f2b4f537b09"? Practically speaking, not really: both only really work when copy/pasted on a computer.

I described another scheme I've used in the past in another comment: https://news.ycombinator.com/item?id=34454430

Post reply on HN