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

301–310 of 345 posts

Re: PostgreSQL and UUID as Primary Key

#301

Post by VeejayRampay »

hu3 wrote: post #255
laserDinosaur wrote: post #247

Earlier quoted context omitted.

>Call me old fashion but I really like integer autoincrement primary keys. Just hope you never have to merge tables from two databases together.

This is exceptionally rare in most projects. I know of only one person in my entire career that had to do this. And they managed it just fine despite working with auto-incrementing big ints. Yet some folks advocate that all projects should pay an expensive insurance against this elusive event of two databases being merged.

I swear, it really reads like "oh you like SOME TECHNOLOGY? we'll se how you like it when FARCICALLY RARE EVENT happens

Re: PostgreSQL and UUID as Primary Key

#302

Post by netcraft »

jrochkind1 wrote: post #100
netcraft wrote: post #17

Another day, another article saying not to use UUIDs as PKs. I've maintained systems using UUIDs stored as char(36) with million record tables without issue - This is not an endorsement, just explaining that this is bikeshedding. Should you use v7 when you can? Sure. Would int/bigint be faster in your benchmarks? Sure. But the benefits totally outweigh the speed differences until you get to a very large system. But i…

Thanks, good to hear. If you are using PG, simply using it's native UUID type instead of char(36) seems like a no-opportunity-cost obvious optimization choice at least though, if you have a choice?

yes absolutely- that system was built a long time ago before the uuid type - you should def not store things in chars (and probably shouldnt use char at all, use text)

was saying that even with that poor implementation we still were not having issues using uuids

Re: PostgreSQL and UUID as Primary Key

#303

Post by timmytokyo »

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?

I don't get it either. If UUIDv7 lacks security due to its revelation of a timestamp, why don't bigserials also lack security? After all, given a bigserial ID, you can tell whether it was generated before or after some other bigserial ID and thereby infer something about the time it was generated.

Re: PostgreSQL and UUID as Primary Key

#304

Post by blackenedgem »

kroolik wrote: post #281
x3al wrote: post #269

Earlier quoted context omitted.

Strictly monotonic fields are quite expensive and the bigserial PK alone won't give you that.

PG bigserial is already strictly monotonic

No they're not, even with a `cache` value of 1. Sequence values are issued at insert rather than commit. A transaction that commits later (which makes all updates visible) can have an earlier value than a previous transaction.

This is problematic if you try to depend on the ordering. Nothing is stopping some batch process that started an hour ago from committing a value 100k lower than where you thought the sequence was at. That's an extreme example but the consideration is the same when dealing with millisecond timeframes.

Re: PostgreSQL and UUID as Primary Key

#306

Post by blackenedgem »

mixmastamyk wrote: post #196
vbezhenar wrote: post #21

Earlier quoted context omitted.

IMO using bigserial by default is wrong. Use whatever data type is appropriate. Not every table will grow to 4 billion rows and not every table will grow to even 60k rows. ID data type leaks to every foreign key referencing given table. Many foreign key usually will be indexed, so this further degrades performance. There are multiple data types for a reason.

Postgres doesn't support unsigned ints last I looked, so it's actually a ~2 billion limit. Secondly, it's not XXXk rows currently-you have to consider the complete lifetime of the table. When rows are deleted/created and how often. So what you've said is true, but the set of appropriateness for smallint is a much smaller than expected.

You can start the sequence at -2b, or wrap it around when it gets close to the signed limit. Hopefully you haven't depended on it not wrapping around by that point.

For queue tables you can even use `CYCLE` to do that automatically.

Re: PostgreSQL and UUID as Primary Key

#307

Post by almog »

bob1029 wrote: post #220

I see some comments conflating privacy of sequence statistics with global uniqueness considerations and UX. If your concern is globally unique identifiers (i.e. so that you can merge tables across multiple instances of your database), then UUID is exactly what you want. This is entirely what it is designed for. If your concern is the privacy of sequence statistics, then UUID incidentally solves your problem. It may n…

> It may not be precisely what you want, and could continue to leak private information depending on the specific variant used. If you want privacy of sequence statistics, then I would suggest something like a sha256 hash of the primary key concatenated with a cryptographic salt stored in a separate column. These make excellent identifiers in places like APIs and URLs.

Is there a reason not to use version 4 UUID and if time ordering is needed, save timestamp explicitly as another column?

Re: PostgreSQL and UUID as Primary Key

#308

Post by akira2501 »

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…

> Which requires physical presence.

It requires physical access. Presence can be achieved by placing a dash camera or window camera strategically.

> and they were expending a lot of effort trying to exceed that previous false number.

The idea here being that if the allies just made more tanks than the axis they would automatically win? It's a lionized story of intelligence agency cleverness built squarely on the back of insane military "strategy."

Re: PostgreSQL and UUID as Primary Key

#309

Post by bob1029 »

almog wrote: post #307
bob1029 wrote: post #220

I see some comments conflating privacy of sequence statistics with global uniqueness considerations and UX. If your concern is globally unique identifiers (i.e. so that you can merge tables across multiple instances of your database), then UUID is exactly what you want. This is entirely what it is designed for. If your concern is the privacy of sequence statistics, then UUID incidentally solves your problem. It may n…

> It may not be precisely what you want, and could continue to leak private information depending on the specific variant used. If you want privacy of sequence statistics, then I would suggest something like a sha256 hash of the primary key concatenated with a cryptographic salt stored in a separate column. These make excellent identifiers in places like APIs and URLs. Is there a reason not to use version 4 UUID and…

Type 4 guids are a reasonable choice.

I personally prefer the extra 128 bits of entropy, but I have no evidence that says it's more secure in practice.

Re: PostgreSQL and UUID as Primary Key

#310

Post by hu3 »

VeejayRampay wrote: post #301
hu3 wrote: post #255

Earlier quoted context omitted.

This is exceptionally rare in most projects. I know of only one person in my entire career that had to do this. And they managed it just fine despite working with auto-incrementing big ints. Yet some folks advocate that all projects should pay an expensive insurance against this elusive event of two databases being merged.

I swear, it really reads like "oh you like SOME TECHNOLOGY ? we'll se how you like it when FARCICALLY RARE EVENT happens

I know right?

"If your architecture can't withstand life threatening solar flares, third world war, sabotaging of undersea cables and 1 billion concurrent users can you even call yourself an engineer?"

Post reply on HN