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

11–20 of 182 posts

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

#11

Post by kstrauser »

jeff-davis wrote: post #9
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:…

The post qualified as <= 10,000,000 total records. For that number of records, there's about a chance of about 0.00001 that you get a collision, assuming good randomness.

Sure, but stuff always grows, and the experiment gets run a bunch of times. Why not go with the built-in solution and then not have to worry about it?

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

#12

Post by tehlike »

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.

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).

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

#13

Post by tehlike »

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).

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

#14

Post by dragonwriter »

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 there any upsides to warrant the complexity?

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

#15

Post by jeff-davis »

kstrauser wrote: post #11
jeff-davis wrote: post #9

Earlier quoted context omitted.

The post qualified as <= 10,000,000 total records. For that number of records, there's about a chance of about 0.00001 that you get a collision, assuming good randomness.

Sure, but stuff always grows, and the experiment gets run a bunch of times. Why not go with the built-in solution and then not have to worry about it?

I'm just answering the poster's question directly; but in the general case, I agree with you. The cognitive overhead of dealing with the various "what ifs" usually aren't worth the couple bytes or cycles that you could save.

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

#16

Post by Benjammer »

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).

I think you can probably get the same "batching benefits" if you use a global ID generation service of some sort, with sequential IDs to improve indexing. Using sequential IDs doesn't necessitate using auto-generated sequential IDs.

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

#17

Post by hu3 »

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.

On the other hand, if you can get away with incremental ids it makes debugging much easier during development.

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

#18

Post by tehlike »

Benjammer wrote: post #16
tehlike wrote: post #13

Earlier quoted context omitted.

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).

I think you can probably get the same "batching benefits" if you use a global ID generation service of some sort, with sequential IDs to improve indexing. Using sequential IDs doesn't necessitate using auto-generated sequential IDs.

correct, but global service need local caching. Some "HiLo" type identity generation could work. Sequential-UUID simplifies it as you don't need that service, so it's preferable.

HiLo explanation below:

A client simply gets a range of ids to be used, exclusive to them. Then can use it without any roundtrip to server.

This can be achieved with a "hi" stored on service, and a batchSize that's constant in the system.

Each time a "hi" is requested, it's incremented by 1. Now the client can generate (hi * batchsize, (hi+1) * batchsize - 1).

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

#19

Post by vbsteven »

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?

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

#20

Post by staticassertion »

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 think ideally your primary key is whatever makes sense for your performance/data model, and then if you want to delegate authority with UUIDs you do that via a separate mapping.

By separating that out you can get a lot:

1. You can extend your delegate system by modifying the delegate table, rather than having to muddy your data model with authority information

2. You can TTL the mappings in the delegate table

3. You can have many mappings to the same data, but each one can have its own restrictions.

It's a bit more complex but you end up with a system that really hones in on the benefit that you're bringing up.

Post reply on HN