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

241–250 of 345 posts

Re: PostgreSQL and UUID as Primary Key

#241

Post by sgarland »

karmajunkie wrote: post #174
sgarland wrote: post #112

Earlier quoted context omitted.

Oh no, someone might know the number of customers, or the rate of signups. Traditional businesses can figure this out by sitting in the parking lot. Why SaaS has decided it’s a huge problem is beyond me.

i mean, you might care if the investors you’re trying to woo for that bridge round figure out your churn is a lot higher than you’re willing to admit… or worse, your traction is terrible.

How is a monotonically incrementing integer going to reveal churn or traction? You’re not reusing IDs.

Re: PostgreSQL and UUID as Primary Key

#242

Post by sgarland »

mewpmewp2 wrote: post #163
sgarland wrote: post #116

Earlier quoted context omitted.

Milliseconds matter, especially when they compound. If your DB can return a SELECT in sub-msec time (to its network boundary, obviously) instead of 10 msec, that adds up when a given page might require a dozen or more trips. Also, I have never seen devs (PMs, really – devs are the unfortunate souls slogging through tickets) suddenly care about performance-related tech debt. Why would they, when you can just click a b…

But then by default you are leaking potentially business sensitive data with your id if you are using it as public facing, which is unsecure design by default. I would rather have secure data by default and opt in to optimise when it is clear this info is fine to leak.

See other reply; I don’t believe that exposing this is as big an issue as people think. But even with that, there’s also no reason to do so. Internal ID friendly to the DB and eternal, random ID that does get exposed is a common practice. Or use JWE/JWT, and never show either.

Re: PostgreSQL and UUID as Primary Key

#243

Post by sgarland »

bruce511 wrote: post #167
sgarland wrote: post #112

Earlier quoted context omitted.

Oh no, someone might know the number of customers, or the rate of signups. Traditional businesses can figure this out by sitting in the parking lot. Why SaaS has decided it’s a huge problem is beyond me.

Yes, sure, it leaks some information - but to be fair printing an invoice also leaks information. For me the priority is security. If I get a link (visible or invisible) that contains a numeric ID, there's the possibility to tweak that link with another number. Ideally, the server treats that number as suspect. Every. Single . Time. In practice I only need one developer to miss the check in one place and I have a ser…

Call me naïve, but surely you can have fuzzing tests in CI?

I’m also going to use this as yet another example of why getting rid of QA in favor of Ship It Now was a bad idea.

Re: PostgreSQL and UUID as Primary Key

#244

Post by whynotmaybe »

thiht wrote: post #200
whynotmaybe wrote: post #70

Earlier quoted context omitted.

As uuid v7 hold time information, they can help bad actors for timing attacks or pattern recognition because they contain a time information linked to the record. You can guess the time the system took between 2 uuid v7 id's. They can only be used if they're not shown to the user. (so not in the form mysite.com/mypage? id=0190854d-7f9f-78fc-b9bc-598867ebf39a) A big serial starting at a high number can't provide the t…

I don’t understand how that’s an issue. Do you have an example of a possible attack using UUIDv7 timestamp? Is there evidence of this being a real security flaw?

The draft spec for uuid v7 has details about the security considerations : https://www.ietf.org/archive/id/draft-peabody-dispatch-new-u...

The way I see it is that uuid v7 in itself is great for some use but not for all uses.

You always have to remember that a v7 always carries the id's creation time as metadata with it, whether you want it or not. And if you let external users get the v7, they can get that metadata.

I'm not a security expert but I know enough to know that you should only give the minimal data to a user.

My only guess is that v7 being so new, attacks aren't widespread for now, and I know why the author decided not to focus on "if UUID is the right format for a key", because the answer is no 99% of the time.

Re: PostgreSQL and UUID as Primary Key

#245

Post by disneycember »

rezonant wrote: post #231
iterateoften wrote: post #221

Earlier quoted context omitted.

If you have an index on the uuid anyways having a separate big serial field for PK doesn’t help that much.

As mentioned elsewhere, it ensures the ability to perform resumable and consistent batching queries across the data set without missing records. Ordering over an insertion timestamp is not enough if two records may have the same timestamp: You may miss a record (or visit a record twice) across multiple queries.

This is solved sorting by timestamp first then by random PK UUID. Don't think a little simpler batch queries justify leaking time and quantity information or complexity of handling two types of IDs.

Re: PostgreSQL and UUID as Primary Key

#246

Post by sgarland »

lazide wrote: post #147
sgarland wrote: post #112

Earlier quoted context omitted.

Oh no, someone might know the number of customers, or the rate of signups. Traditional businesses can figure this out by sitting in the parking lot. Why SaaS has decided it’s a huge problem is beyond me.

By sitting in every parking lot, yes. Which requires physical presence. And hedge funds do indeed do it against some targets specifically as a leg up, and pay quite a bit of money to do so, presumably because it is worth it to them. It certainly helped the Allies in the war, as previous intelligence had the rate of tank production much higher, and they were expending a lot of effort trying to exceed that previous fal…

> But if we're in an engineering discussion on the topic, knowledgeable folks will bring up the issues with it, because that is what they do.

To be clear, this is not directed at you specifically, and I have no idea what your level of expertise is on anything.

In general, I’ve found HN commenters level of knowledge to be fairly bimodal. They’re either regurgitating things they read on a Medium blog, or they really know their shit.

Every time this topic comes up, people delightedly mention the German Tank Problem, but I have never, not once, seen anyone post an actual example of when a modern business got rekt by a competitor using knowledge gained from monotonic IDs.

Re: security viz. AuthZ, my stance is the same as SQL injection – it’s such a trivially easy problem to avoid that it shouldn’t be a consideration for harming the performance of the DB. (Not that SQL injection mitigations cause performance impacts; sorry, that analogy didn’t work well)

Re: PostgreSQL and UUID as Primary Key

#247

Post by laserDinosaur »

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.

>Call me old fashion but I really like integer autoincrement primary keys.

Just hope you never have to merge tables from two databases together.

Re: PostgreSQL and UUID as Primary Key

#248

Post by sgarland »

matharmin wrote: post #214
sgarland wrote: post #109

Earlier quoted context omitted.

> Postgres is happier with sequence ID's, but keeping Postgres happy isn't the only design goal. It literally is the one thing in the entire stack that must always be happy. Every stateful service likely depends on it. Sad DBs means higher latency for everyone, and grumpy DBREs getting paged.

Postgres is usually completely happy enough with UUIDv4. Overall architecture (such as allowing distributed id generation, if relevant) is more important than squeezing out that last bit of performance, especially for the majority of web applications who don't work with 10 million+ rows.

If your app isn’t working with billions of rows, you really don’t need to be worrying about distributed anything. Even then, I’d be suspicious.

I don’t think people grasp how far a single RDBMS server can take you. Hundreds of thousands of queries per second are well in reach of a well-configured MySQL or Postgres instance on modern hardware. This also has the terrific benefit of making reasoning about state and transactions much, much simpler.

Re: last bit of performance, it’s more than that. If you’re using Aurora, where you pay for every disk op, using UUIDv4 as PK in Postgres will approximately 7x your IOPS for SELECTs using them, and massively (I can’t quantify it on a general basis; it depends on the rest of the table, and your workload split) increase them for writes. That’s not free. On RDS, where you pay for disk performance upfront, you’re cutting into your available performance.

About the only place it effectively doesn’t matter except at insane scale is on native NVMe drives. If you saturate IOPS for one of those without first saturating the NIC, I would love to see your schema and queries.

Re: PostgreSQL and UUID as Primary Key

#249

Post by fovc »

sgarland wrote: post #248
matharmin wrote: post #214

Earlier quoted context omitted.

Postgres is usually completely happy enough with UUIDv4. Overall architecture (such as allowing distributed id generation, if relevant) is more important than squeezing out that last bit of performance, especially for the majority of web applications who don't work with 10 million+ rows.

If your app isn’t working with billions of rows, you really don’t need to be worrying about distributed anything. Even then, I’d be suspicious. I don’t think people grasp how far a single RDBMS server can take you. Hundreds of thousands of queries per second are well in reach of a well-configured MySQL or Postgres instance on modern hardware. This also has the terrific benefit of making reasoning about state and tran…

Scale isn’t the only reason to have distributed systems. You could very well have a tiny but distributed system

Re: PostgreSQL and UUID as Primary Key

#250

Post by blackenedgem »

kroolik wrote: post #229
groestl wrote: post #228

Earlier quoted context omitted.

Ha? Please elaborate.

When running a batched migration it is important to batch using a strictly monotonic field so that new rows wont get inserted in already processed range

It's not even necessarily it being strictly monotonic. That part does help though as you don't need to skip rows.

For me the bigger thing is the randomness. A uid being random for a given row means the opposite is true; any given index entry points to a completely random heap entry.

When backfilling this leads to massive write amplification. Consider a table with rows taking up 40 bytes, so roughly 200 entries per page. If I backfill 1k rows sorted by the id then under normal circumstances I'd expect to update 6-7 pages which is ~50kiB of heap writes.

Whereas if I do that sort of backfill with a uid then I'd expect to encounter each page on a separate row. That means 1k rows backfilled is going to be around 8MB of writes to the heap.

Post reply on HN