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

UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

cybertec-postgresql.com

Post reply on HN
Print view View original

41–50 of 182 posts

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#41

Post by ainar-g »

staticassertion wrote: post #21
ainar-g wrote: post #2

I don't think I've ever seen this mentioned anywhere, but if you need a unique ID for an entity with not a lot of records planned (≤10,000,000), why not use a random int64 with a simple for loop on the application side to catch the occasional collisions? Are there any downsides besides making the application side a tiny bit more complex?

Is the goal here to save space?

The goal is to have a non-sequential ID (for example, to hide the information about the actual size of the data set) in a situation where one needs to support a storage that doesn't have a native support for UUIDs (that isn't just “convert to TEXT”) and reuse as much of the schema as possible without [ab]using ORMs. For example, an application that has to support SQLite as well as PostgreSQL and maybe some other storages.

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#42

Post by megous »

tehlike wrote: post #13
magicpointer wrote: post #5

About UUID as Primary Key and performance, the following article has some insights and benchmarks as well: https://www.2ndquadrant.com/en/blog/sequential-uuid-generato... Essentially, they observed sizeable performance improvements by using UUID generators that are tweaked to get more sequentia resultsl. It results in better indexes. The articles compares sequences, random UUIDs and 2 kinds of sequentialish UUID gene…

Mentioned this in a sibling comment: There's another benefit to UUID - You can generate them anywhere including application side. Doing this on application side would have tremendous batching benefits or inserting objects with relationships at the same time (Vs waiting first insert to return an ID to be used in the FK).

There's nothing that prevents you from fetching a batch of N IDs from the server. Server just does +N on the sequence and you can do whatever you like with the IDs client side.

You can also use one sequence for everything on the server, and then you can also pre-create ID based relationships client side.

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#43

Post by kstrauser »

oconnore wrote: post #33
kstrauser wrote: post #7

Earlier quoted context omitted.

That’s the UUID approach, but worse. According to the birthday problem[1], you’re 50% likely to get a collision in 65 bit numbers after about 5 billion insertions. That’s not an awful lot. Replace that with a 128-bit UUID and you’d have to insert 22,000,000,000,000,000,000 rows to get a 50% chance. That’s probably less likely than a cosmic ray flipping a random bit in RAM and corrupting the index that way. [1] https:…

Getting a collision with this approach doesn’t matter — the whole point is to loop if you do get a collision. The only issue is getting a long string of sequential collisions, which is highly unlikely.

But now you’ve tried code complexity for a few bytes if storage. That’s just not worth it.

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#44

Post by alexis2b »

vbsteven wrote: post #19

I’m currently prototyping a little database+api+cli todo app and I want identifiers that can be abbreviated in the same way as partial git commit hashes can be used on the command line. What should I use? I was thinking of generating random character strings and simply retry when the db throws duplicate key error on insert. No sharding is necessary and I’d like to have efficient foreign keys. Any thoughts?

You could try NanoID[0]? Seems available in many languages.

[0] https://blog.bibekkakati.me/nanoid-alternative-to-uuid

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#45

Post by nirvdrum »

dragonwriter wrote: post #14
ainar-g wrote: post #2

I don't think I've ever seen this mentioned anywhere, but if you need a unique ID for an entity with not a lot of records planned (≤10,000,000), why not use a random int64 with a simple for loop on the application side to catch the occasional collisions? Are there any downsides besides making the application side a tiny bit more complex?

> if you need a unique ID for an entity with not a lot of records planned (≤10,000,000), why not use a random int64 with a simple for loop on the application side to catch the occasional collisions? What’s the use case for this where UUIDv4 or sequential ID isn’t better? Because it sounds like a solution in search of a problem. > Are there any downsides besides making the application side a tiny bit more complex? Are…

I wouldn't say it's a particularly great upside, but I've found tooling doesn't tend to work as well with UUID keys as it does integer keys. E.g., Hasura won't map UUID keys to the GraphQL ID type [1], which makes working with them unnecessarily difficult. Arguably, the issue should be fixed with the tool (or a different tool chosen), but there's only so many battles to pick and sometimes that decision is out of your control.

[1] -- https://github.com/hasura/graphql-engine/issues/3578

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#46

Post by j-pb »

hermanradtke wrote: post #27
magicpointer wrote: post #5

About UUID as Primary Key and performance, the following article has some insights and benchmarks as well: https://www.2ndquadrant.com/en/blog/sequential-uuid-generato... Essentially, they observed sizeable performance improvements by using UUID generators that are tweaked to get more sequentia resultsl. It results in better indexes. The articles compares sequences, random UUIDs and 2 kinds of sequentialish UUID gene…

I use a ulid[1] as a uuidv4 replacement: https://github.com/ulid/spec

They almost got it right, a better implementation would overflow regularly to make use of the entire key space, and counter untuitively more resistant to overflows.

Clocks aren't reliable enough for timestamps anyways so garbage collection is the only thing you kinda wanna rely on them for.

A good sweet spot seems to be, 32bit milliseconds + 96bit of entropy. This overflows appeoximately every 50 days, allowing for 50 day rolling data retention.

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#47

Post by deckard1 »

kstrauser wrote: post #7
ainar-g wrote: post #2

I don't think I've ever seen this mentioned anywhere, but if you need a unique ID for an entity with not a lot of records planned (≤10,000,000), why not use a random int64 with a simple for loop on the application side to catch the occasional collisions? Are there any downsides besides making the application side a tiny bit more complex?

That’s the UUID approach, but worse. According to the birthday problem[1], you’re 50% likely to get a collision in 65 bit numbers after about 5 billion insertions. That’s not an awful lot. Replace that with a 128-bit UUID and you’d have to insert 22,000,000,000,000,000,000 rows to get a 50% chance. That’s probably less likely than a cosmic ray flipping a random bit in RAM and corrupting the index that way. [1] https:…

for what it's worth, YouTube still uses 11 character base64 strings for their video ids, which are assumed to be 64-bit ints. They also allow unlisted videos, which people usually take to mean "semi-private".

It's an interesting tradeoff. The UX of the smaller YouTube video id links is probably of some benefit to them. Plus they have private videos for when you really don't want your video to be viewed, with unlisted being the middle ground of easy sharing but also keeping it exclusive.

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#48

Post by tgv »

throwawayboise wrote: post #29
conradfr wrote: post #10

UUIDs are great when you use the id "publicly" but using an incremental value would be too revealing for different reasons. So it's good to know that performances are not bad.

I don't think a lot of the argument that integer IDs reveal too much. Yes, they are guessable but your application should not rely solely on the "secrecy" of the ID to authorize access to a record. If you are worried about someone crawling your public API with wget or curl and an incrementing counter you should re-think whether your data are really public or not, or maybe rate-limit anonymous users, etc. They also re…

So we run surveys among general and specialized audiences (among other things), and these surveys link to custom scripting, images, videos, etc. The URLs have to be freely accessible, but if they are sequential, anyone can simply try to guess what's in other surveys, potentially getting information about their competitors.

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#49

Post by magicpointer »

Scarbutt wrote: post #24
magicpointer wrote: post #5

About UUID as Primary Key and performance, the following article has some insights and benchmarks as well: https://www.2ndquadrant.com/en/blog/sequential-uuid-generato... Essentially, they observed sizeable performance improvements by using UUID generators that are tweaked to get more sequentia resultsl. It results in better indexes. The articles compares sequences, random UUIDs and 2 kinds of sequentialish UUID gene…

Careful with leaking sensitive information with semi-sequential UUIDs though.

To alleviate the issue of having a sequential part, they make it wrap around so that you cannot tell the order between two UUIDs. It's already some protection, and the random part is still large.

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#50

Post by Matrixik »

magicpointer wrote: post #5

About UUID as Primary Key and performance, the following article has some insights and benchmarks as well: https://www.2ndquadrant.com/en/blog/sequential-uuid-generato... Essentially, they observed sizeable performance improvements by using UUID generators that are tweaked to get more sequentia resultsl. It results in better indexes. The articles compares sequences, random UUIDs and 2 kinds of sequentialish UUID gene…

There is also CUID:

https://github.com/ericelliott/cuid

Post reply on HN