Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL and UUID as Primary Key

maciejwalkowiak.com

Post reply on HN
Print view View original

201–210 of 345 posts

Re: PostgreSQL and UUID as Primary Key

#201

Post by thiht »

brycelarkin wrote: post #55
inopinatus wrote: post #12

The best advice I can give you is to use bigserial for B-tree friendly primary keys and consider a string-encoded UUID as one of your external record locator options. Consider other simple options like PNR-style (airline booking) locators first, especially if nontechnical users will quote them. It may even be OK if they’re reused every few years. Do not mix PK types within the schema for a service or application, esp…

For postgres, you want to use “bigint generated always as identity” instead of bigserial.

Why?

Re: PostgreSQL and UUID as Primary Key

#202

Post by Pinus »

I’m not sure I understand the bit about time-ordered values being a better fit for B-trees. Actually, I’m sure that I don’t understand it... If anything, I’d naively assume that inserting already-sorted values would be a worst case, always going down the same branch of the tree and triggering re-balancing operations left and right (figuratively — literally mostly left!). The Wikipedia article on B-trees notices that there are some shortcuts that can be taken if one knows one is going to do a batch insert of sorted values. Is that what happens? But then how does Postgres know this?

Re: PostgreSQL and UUID as Primary Key

#203

Post by foreigner »

declan_roberts wrote: post #61

Call me old fashion but I really like integer autoincrement primary keys. It's easy to understand and obviously simple to sort. Furthermore when working on large batch projects you can just store the last primary key as your high water mark and get everything greater than that. I suppose TSID works to this end, but certainly more complicated.

Be advised using the last auto-incremented value as a "high water mark" and getting everything greater than that is not 100% reliable, because in some scenarios auto-incrementing values can be written out of order.

Re: PostgreSQL and UUID as Primary Key

#204

Post by conradludgate »

Pinus wrote: post #202

I’m not sure I understand the bit about time-ordered values being a better fit for B-trees. Actually, I’m sure that I don’t understand it... If anything, I’d naively assume that inserting already-sorted values would be a worst case, always going down the same branch of the tree and triggering re-balancing operations left and right (figuratively — literally mostly left!). The Wikipedia article on B-trees notices that…

Btrees don't need to be rebalanced. They only split. The cost of splitting to the root is counteracted by the cache locality of always accessing the same set of pages. Inserting into the last page only needs O(logn) page accesses and O(logn) page writes worst case.

Re: PostgreSQL and UUID as Primary Key

#205

Post by arp242 »

riku_iki wrote: post #173
arp242 wrote: post #172

Earlier quoted context omitted.

> cherry picked some fictional example What an incredibly rude and dismissive accusation. Here's my table: https://github.com/arp242/goatcounter/blob/master/db/schema.... – number of IDs is actually 7, not 6. I can give a lot more details and context on all of that and why it works the way it works and the savings are certainly not insignificant and theoretical, but save me real money in server costs every month. But…

> https://github.com/arp242/goatcounter/blob/master/db/schema .... - number of IDs is actually 7, not 6. the point is that some/many of those ids are not timezone and country, and potentially can grow high in cardinality.

No, my point is you accused me of lying at the drop of a hat for no good reason, and that this is demonstrably not true. Don't try and spinelessly slime your way out of that. And no, they I won't run out of IDs for them. "Potentially" anything can happen, but it won't happen here. After five year the furthest sequence is 0.86% on its way of being full.

Re: PostgreSQL and UUID as Primary Key

#206

Post by zxexz »

OskarS wrote: post #197
spencerap wrote: post #195

Earlier quoted context omitted.

If the key and encryption mechanism are ever leaked, those opaque external IDs can be converted easily back to sequence numbers, and vice versa, which might pose a risk for you or your users. You won't be able to rotate the encryption key without breaking anything external that tracks those encrypted IDs... third party services, SEO, user bookmarks, etc.

You store the key in the database, right? Like, if the database leaks, it doesn’t matter if your ids are sequeneced or unsequenced, because all data has leaked anyway. The key leaking doesn’t seem like a realistic security issue.

Ideally if you do this, you store the key in a separate schema with proper roles so that you can call encrypt() with the database role, which can't select the key. Even then, the decrypted metadata should not be particularly sensitive - and should immutably reference a point in time so you can validate against some known key revocation retroactively.

My take is it's rarely necessary to have a token, that you give to an external entity, that has any embedded metadata all - 99.9% of apps aren't operating at a scale where even a million-key hashmap sitting in ram and syncing changes to disk on update would cause any performance difference.

Re: PostgreSQL and UUID as Primary Key

#207

Post by fauigerzigerk »

njtransit wrote: post #128
incrudible wrote: post #43

Earlier quoted context omitted.

If an attacker can create billions of records through your API, maybe that is a problem you need to address either way.

It’s about 100 records per second for a year and a half, or 10,000 records per second for 5 days. Both are easily achievable. As an engineer, why would you ever knowingly design such a system when it’s trivial to not have this vulnerability in the first place. It’s like hosting an internal app at a company that contains a SQL injection. “Well, if a hacker can access this app, then that’s a problem that needs addressi…

That's not a great analogy. SQL injection is a completely binary issue. A single malicious statement that gets through can do all the damage.

Defending against denial of service attacks is a gradual, multifaceted problem that is pretty unlikely to hinge on the size of database keys.

If your system is dimensioned to serve a few hundered users then it's not going to be a whole lot more robust if it can theoretically create thousands of trillions of user records rather than just hundereds of millions.

In fact, infinite scalability is a risk in its own right because it can bankrupt you, which is arguably worse than a temporary outage.

That said, I tend to use 64 bit IDs by default as well unless there is a good reason not to (because, you know, what if I'm the next Google?!?).

Re: PostgreSQL and UUID as Primary Key

#208

Post by blits »

You might also be interested in TypeID, which is extension of UUIDv7 and also sortable and unlike UUIDv7 more human readable.

It is inspired by Stripe IDs and looks like that: user_2x4y6z8a0b1c2d3e4f5g6h7j8k

I've recently built a postgres extension for it that allows to use it like UUID: https://github.com/blitss/typeid-postgres

Re: PostgreSQL and UUID as Primary Key

#209

Post by arp242 »

lazide wrote: post #149
arp242 wrote: post #140

Earlier quoted context omitted.

> It’s like hosting an internal app at a company that contains a SQL injection It's nothing like that at all because the wrong SQL injection can completely ruin people's lives due to leaking stuff it shouldn't whereas the worst an int exhaustion can do is bring some app offline. Whoopdie-doo. Okay, that's not brilliant, but it's not comparable at all. And there's a reason there aren't tons of "int exhaustion attacks"…

So taking down the apps ability to insert any rows into the table (and hence breaking the app) isn’t going to impact anyone? Including the apps ability to make money? This does happen and break people. You usually don’t hear about it (except on the SRE side) because it is so obvious when it happens to someone they really don’t like talking about it.

I never said it's "not going to impact anyone", I said it's not comparable.

"Denial of service" is just not a security bug on the same level as "zomg my credit card is stolen". I have argued this many times before, e.g. https://news.ycombinator.com/item?id=39377795 – It's easy to knock most sites offline regardless just by flooding it with traffic, don't need long-term plans like this (which are likely to be detected before it even has effect).

> This does happen and break people

I have never heard about a deliberate attack using this method.

Re: PostgreSQL and UUID as Primary Key

#210

Post by globular-toast »

oppositelock wrote: post #6
fabian2k wrote: post #3

My somewhat naive understanding was that random UUIDs were not that big of a deal in Postgres because it does not cluster by primary key. And of course a UUID (16 bytes) is larger than a serial (4 bytes) or bigserial (8 bytes) by a factor of 2-4 . This certainly might matter for an index, but on a whole table level where you have 20+ bytes overhead per row this doesn't seem that big of a deal for anything except very…

Random UUID's are super useful when you have distributed creation of UUID's, because you avoid conflicts with very high probability and don't rely on your DB to generate them for you, and they also leak no information about when or where the UUID was created. Postgres is happier with sequence ID's, but keeping Postgres happy isn't the only design goal. It does well enough for all practical purposes if you need random…

The whole point of UUID is distributed creation. There's nothing about random ones (UUIDv4) that makes it better for this purpose.
Post reply on HN