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

21–30 of 345 posts

Re: PostgreSQL and UUID as Primary Key

#21

Post by vbezhenar »

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…

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.

Re: PostgreSQL and UUID as Primary Key

#22

Post by brigadier132 »

My strategy is to use v4 Uuids for anything that is not inserted frequently and don't need to be ordered (think user ids) and v7 ids for things that are.

If your dataset is small the overhead from Uuids wont matter, if your dataset is large the randomness of Uuids will save your ass when you migrate to a distributed solution.

Re: PostgreSQL and UUID as Primary Key

#23

Post by munk-a »

AtlasBarfed wrote: post #10

UUIDs are guaranteed to be unique? They often use tricks like including the MAC address of the generator machine and other ways to increase uniqueness assurances. It was my understanding that uuids are simply very very unlikely to duplicate in situations with random generation.

Your understanding is correct but you're underselling very very in this context. It is astronomically unlikely to hit a collision with the advised generation methods. If you want a possibly easier to grasp parallel git relies on SHA hashes never colliding and will break in a really awful way if you can produce two commits in a tree with the same hash - it's so astoundingly unlikely that people are okay summarizing it as "Never gonna happen" - it certainly will eventually, but it might not happen until the earth is swallowed by the sun.

Re: PostgreSQL and UUID as Primary Key

#24

Post by paulddraper »

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…

I haven't maintained any sizable database system without any issues, least of all performance ones.

I call BS.

Re: PostgreSQL and UUID as Primary Key

#25

Post by jacobgorm »

throwawayffffas wrote: post #20
AtlasBarfed wrote: post #10

UUIDs are guaranteed to be unique? They often use tricks like including the MAC address of the generator machine and other ways to increase uniqueness assurances. It was my understanding that uuids are simply very very unlikely to duplicate in situations with random generation.

They are not theoretically guaranteed they are in practice though. 2^128 and 122 are big numbers. Even if you are producing a billion per second you have a 50% chance of not getting a collision for 100 years.

I've used 128 secure-random bits for ages, not caring for any of the UUID version nonsense. Per the birthday paradox, I need to have 2*64 entries in my tables to reach 50% collision probability, and it will be a while before I can afford that much storage anyhow.

Re: PostgreSQL and UUID as Primary Key

#26

Post by big_whack »

munk-a wrote: post #19
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…

To: > Am I wrong here and this is something that really matters and you should invest more time in? Specifically, no - you don't need to worry about it. Reconfiguring your tables to use a different style of unique identifier if your tables have a unique identifier is a bit of a pain but no more so than any other instance of renaming a column - if you want to minimize downtime you add the new column, migrate data to t…

Reconfiguring tables to use a different kind of unique ID (primary key in this context) can be a much bigger pain than an ordinary column rename if it is in use by foreign key constraints.

Re: PostgreSQL and UUID as Primary Key

#27

Post by inopinatus »

vbezhenar wrote: post #21
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…

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.

Defaulting to 64-bit integers internally is to me a matter of mechanical sympathy, it has little to do with row capacity. It’s just a word size that current CPUs and memory architectures like working with.

Re: PostgreSQL and UUID as Primary Key

#28

Post by riku_iki »

vbezhenar wrote: post #21
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…

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.

there is unlikely significant performance degradation for int vs big int, but it will be huge PITA, if 10 years later and tons of legacy code written that table will grow over 4B rows..
Post reply on HN