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

151–160 of 163 posts

Re: Choosing a Postgres primary key

#151

Post by rco8786 »

okl wrote: post #132
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"

Oops yes I sure did. Thanks

Re: Choosing a Postgres primary key

#152

Post by atombender »

One major aspect of primary keys not mentioned in the article is the performance of queries. I did some benchmark recently, where serial (32 bits) was significantly faster than the native Postgres UUID type. The worst scheme you can use is a string; if you encode something like ksuid as a plain string rather than an efficiently packed byte sequence, query performance becomes significantly worse. I didn't benchmark sorting, but I assume it's similarly impacted.

Re: Choosing a Postgres primary key

#153

Post by dspillett »

arp242 wrote: post #109
dspillett wrote: post #96

Earlier quoted context omitted.

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…

PostgreSQL doesn't have unsigned integers.

Nor does MS SQL Server, though you can start IDENTITY from a maximally negative value. But any time I'd consider that I'd just jump up to an larger integer type instead.

Re: Choosing a Postgres primary key

#154

Post by yencabulator »

insanitybit wrote: post #115
hnarn wrote: post #101

Earlier quoted context omitted.

"Protecting" records by making IDs hard-to-guess just seems like putting the responsibility in the wrong place. If you're so worried about people getting their hands on the wrong records, I'd be more worried about your lack of trust in the application that queries that database in the first place: remember, even if you do make the IDs hard to guess, your "untrusted application" might at some point decide to simply le…

> "Protecting" records by making IDs hard-to-guess just seems like putting the responsibility in the wrong place. It's a pretty powerful implementation of capability based security.

Many actual capability systems explicitly construct their capabilities to be unforgeable by not making them be mere secret byte strings, but objects protected by the kernel or other environment. For example, https://www.cs.cornell.edu/courses/cs513/2000SP/L08.html

And then people naively copy operating system design methodology into web APIs... Unrevokable leakable know-it-and-you're-in secrets are a bad idea.

Re: Choosing a Postgres primary key

#155

Post by yencabulator »

perakojotgenije wrote: post #93
onphonenow wrote: post #47

Earlier quoted context omitted.

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.

To avoid the German Tank Problem and leaking your business vitals to competition, I'd recommend making invoices be scoped to the client, e.g. -. And then client_id is either random or for small business e.g. unique slug derived from client name, for example you could get PERAK-0001.

https://en.wikipedia.org/wiki/German_tank_problem#Historical...

Re: Choosing a Postgres primary key

#156

Post by yencabulator »

avinassh wrote: post #149
perrygeo wrote: post #142

Earlier quoted context omitted.

Because B-tree indexes are ordered, rows likely to be adjacent on disk (written in time order) are not at all adjacent in the index and vice-versa. There is no "hot page" in the cache representing recent records; the index node you need for any given uuid is random and makes your internal index pages effectively uncacheable. The result is increased IO and cache thrashing.

> Because B-tree indexes are ordered, rows likely to be adjacent on disk (written in time order) are not at all adjacent in the index and vice-versa. But this still doesnt matter, right? If you want time ordering you'd prolly have some field like `created_at`

Consider inserting 1000 rows.

If those rows have keys that sort near each other, you're changing a few pages.

If those rows have keys that are all over the keyspace, you're changing roughly 1000*k pages.

Re: Choosing a Postgres primary key

#157

Post by yencabulator »

antifa wrote: post #150
deepsun wrote: post #110

One more note: uuid is not easily copy-pastable, due to dash `-` in it. I prefer to use it's raw bytes and encode with base32, which is copy-pastable.

If the goal is copy-pastable, why not base36 or base62?

Because typing them or reading them back over the phone is much less human-friendly.

So far https://philzimmermann.com/docs/human-oriented-base-32-encod... has the best trade-offs I've ever seen.

(Though for encoding numbers, where small numbers deserve a shorter representation, Base58 can be nice too.. z-base-32's alphabet is still more user-friendly. https://github.com/tv42/base58 )

Re: Choosing a Postgres primary key

#158

Post by yencabulator »

philliphaydon wrote: post #42

I’ve been using HiLo for so long this isn’t something I think about. I don’t see a point in trying to hide the ID. Either it’s public or it’s private and should be verified before being accessed.

> I don’t see a point in trying to hide the ID.

Exposing sequential numbers tells your competition the size of your company's user base, user activity levels, and growth.

https://en.wikipedia.org/wiki/German_tank_problem#Historical...

Re: Choosing a Postgres primary key

#159

Post by perrygeo »

avinassh wrote: post #149
perrygeo wrote: post #142

Earlier quoted context omitted.

Because B-tree indexes are ordered, rows likely to be adjacent on disk (written in time order) are not at all adjacent in the index and vice-versa. There is no "hot page" in the cache representing recent records; the index node you need for any given uuid is random and makes your internal index pages effectively uncacheable. The result is increased IO and cache thrashing.

> Because B-tree indexes are ordered, rows likely to be adjacent on disk (written in time order) are not at all adjacent in the index and vice-versa. But this still doesnt matter, right? If you want time ordering you'd prolly have some field like `created_at`

Rows are generally written to disk in the order they're inserted, whether you have a timestamp or not. It's just how database IO works.

If your index happens to be in a different order (the position in the index is uncorrelated to the position of the row), you're gonna be thrashing pages. This isn't unique to UUID keys - text keys or anything other than current timestamp or serial id are going to have the same problem.

It's really only an issue once your index size exceeds available shared RAM. If you can fit your table's index completely in memory, you probably won't notice. But once you exceed that threshold, performance starts falling off a cliff.

Re: Choosing a Postgres primary key

#160

Post by grncdr »

Too wrote: post #148
grncdr wrote: post #17

Earlier quoted context omitted.

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.

How do you decode a hash?

ah sorry, I was not very precise about "hashed".

What I meant (and have done in the past) is to encrypt/decrypt the auto-incrementing ID at the application boundaries.

If the OP is really using a one-way hash then yes, you would have to store both IDs.

Post reply on HN