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

91–100 of 163 posts

Re: Choosing a Postgres primary key

#91

Post by CPLX »

quesera wrote: post #44
CPLX wrote: post #23

Earlier quoted context omitted.

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.

Yes of course. Which is why I picked a few examples where it doesn’t matter. Stuff you’d just always have a public catalog of.

Re: Choosing a Postgres primary key

#92

Post by abujazar »

glacials wrote: post #61

The author misses one advantage of UUIDs: if you’re working in high-throughput distributed systems, serial IDs create a bottleneck and single point of failure in the service handing out IDs. With UUIDs any service can generate an ID itself and tell downstream services about it in parallel—even if one of them is down, slow, or needs retrying.

This can also be achieved in distributed systems by having each node skip IDs equivalent to the number of nodes in the cluster. E.g. node 1 in a 5 node cluster assigns ids 1, 6, 11 and node 2 assigns 2, 7, 12 and so on.

Re: Choosing a Postgres primary key

#93

Post by perakojotgenije »

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…

integer/year works almost flawlessly as invoice ID. It is unique and readable.

If the company has more than one unit that creates invoices than just add unit ID, so it becomes invoice_id/unit_id/year. Still unique and very much readable.

Re: Choosing a Postgres primary key

#94

Post by dspillett »

tpetry wrote: post #80
boloust wrote: post #36

Earlier quoted context omitted.

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.

It works this way in SQL Server too, and some other DBs, if you have a clustered index (usually recommended). The clustering key is included in all non-clustered indexes on the table. Not that this doesn't mean NCIs inherit any extra fragmentation potential from the clustering key, as it is effectively INCLUDed and not considered by of the key of the supporting index.

Postgres tables are more like what SQL Server calls a heap table (one without a clustering key). Some of the issues that make clustered tables the standard recommendation in SQL Server are very similar to those that make VACUUM a requirement in postgres. IIRC postgres tables are more efficient than SQL Server's heap tables in most cases because they are the only option so are actively optimised for, where in SQL Server head tables are generally (in all but the few circumstances where they are more efficient) considered a second class type.

Re: Choosing a Postgres primary key

#95

Post by kiwicopple »

quartz wrote: post #58

Given the advantages of sortable UUIDs and this post's conclusion that XID is the best option right now, is Supabase planning to add pg_idkit to their list of supported extensions?

>pg_idkit to their list of supported extensions

For some of these simpler extensions, we're looking at using AWS's TLE (https://github.com/aws/pg_tle), which would allow user-contributed extensions. If we can pull that off, we'll probably look again at the current set of extensions we offer and then see which ones can be ported to a TLE instead

Re: Choosing a Postgres primary key

#96

Post by dspillett »

arp242 wrote: post #69
PestoDiRucola wrote: post #25

Earlier quoted context omitted.

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 use bigserial instead of serial, which goes up to 9223372036854775807.

If your DB supports unsigned integers, or starting sequences from -2,147,483,648, you can double the address range.

But if you are at all worried that you'll get within a couple of orders of magnitude of MAXINT32 in the lifetime of your application then you should immediately jump to 64-bit values. Doubling is often just noise and the cost of refactoring if you approach MAXINT32 much faster than expected is more or a problem than the extra storage cost of bigger keys.

Re: Choosing a Postgres primary key

#97

Post by osigurdson »

philliphaydon wrote: post #88
osigurdson wrote: post #77

Earlier quoted context omitted.

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.

There's always going to be valid reasons to have non-guessable identifiers. But it shouldn't be used for security. It's not a replacement for not checking resource access. Sometimes it's not 'sensitive', and even when it is, it doesn't really matter. An invoice id, doesn't matter. You need to be logged in to access it, and when logged in, you can only access your own invoice. If you're going to try discover other inv…

>> It's not a replacement for not checking resource access. It isn't a replacement but it does compliment security. Imagine that your service accidentally hands out the wrong jwt for a short period of time. The attacker has the jwt and a known url but not the url to access another user's information. It will take the attacker a long time to find the url if it is a 128bit uuid. By that time the jwt has expired. I'm not suggesting that this will always work but it does reduce the probability of a successful attack - and security is 100% about probability.

>> Claiming "Exposing predictable identifiers to the world is never a good thing." tho is just FUD

Fully agree, this post is a great example: I can increment the id in the url and see the next post - no harm done.

Re: Choosing a Postgres primary key

#98

Post by badestrand »

PestoDiRucola wrote: post #86
marcosdumay wrote: post #76

Earlier quoted context omitted.

> you can run out of numbers when using serial Yes, that's why you use bigserial.

But the comment I replied to was mentioning serial though.

I think you can just make that dependent on the table's data. For a table of user posts use bigserial but for a list of companies, prices or names the serial will surely be sufficient.

Re: Choosing a Postgres primary key

#99

Post by jeffomatic »

There's some skepticism in the comments around the recommendation for xid. I'm curious if anyone here is using it in production at scale, and can comment on the practical realities.

I saw xid make the rounds about a year ago, and the promise of a pseudo-sortable 12-byte identifier that is "configuration free" struck me as a bit far-fetched.

In particular, I wondered if the xid scheme gives you enough entropy to be confident you wouldn't run into collisions. UUIDv4 doesn't eat a full 16 bytes of entropy for nothing. For example, if you look at the machine ID component of xid, it does some version of random assignment (either pulling the first three bytes from /etc/machine-id, or from a hash of the hostname). 3 bytes is 16777216 values, i.e., with 600 hosts you have a 1% chance of running into a collision. Probably too close for comfort?

There are settings where you can build some defense-in-depth against ID collisions, like a uniqueness constraint in your DB (effectively a centralized ticketing system). But there are many settings where that kind of thing wouldn't be practical. Off the top of my head, I'm thinking of monitoring-type applications like request or trace IDs.

Re: Choosing a Postgres primary key

#100

Post by dspillett »

saiya-jin wrote: post #51
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…

I hate UUIDs with passion currently - not postgres but recently spent so much extra time on relatively small table (35 mil records in few columns) and doing some queries and updating subset of it. UUIDs there is stored in Oracle 'raw' datatype which to me is the worst combination possible, basically string stored in small binary blob, due to binary nature all needs to be converted to hex all the time for matching and…

> basically string stored in small binary blob, due to binary nature all needs to be converted to hex all the time for matching and readability, atrocious performance on stored procedures

But this give a significant performance benefit for storage (storing in the display format gives 4 bits per byte, or less if you include decorations like the '-' characters, rather than 8) and more importantly when joining (it doesn't need to convert for this, so on a 64-bit architecture each comparison is a pair of 64-bit compares in the CPU) rather than a more complex string comparison.

If you are converting between string and binary representations more often than on input to your stored procedures or for output, then something is very wrong (the query planner are likely not able to use indexes that it could too, so scanning instead of seeking).

Post reply on HN