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

51–60 of 182 posts

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

#51

Post by megous »

jeff-davis wrote: post #3
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?

You can also do the loop on the server side using PL/pgSQL: https://www.postgresql.org/docs/current/plpgsql-control-stru...

That's not that trivial. You can't just loop to get a unique ID. Maybe if you lock the whole table for reads first, which is quite drastic.

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

#52

Post by RedShift1 »

topspin wrote: post #40

I just started a little side project and chose to use UUID for Postgresql keys. The schema is highly generic and I anticipate the possibility of merging instances. UUID precludes collisions in such a case.

That includes foreign keys?

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

#53

Post by sa46 »

kstrauser wrote: post #43
oconnore wrote: post #33

Earlier quoted context omitted.

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.

8 extra bytes per row and per foreign key reference relative to an int64 can add up quickly especially if the row is small. I agree it’s not typically the right trade off but it’s not as absolute as you claim.

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

#55

Post by dbbk »

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’ve stuck with incremental values internally but use Hashid to convert them when exposed publicly. Seems to work well.

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

#56

Post by aidos »

pmontra wrote: post #34

Meta: this company wrote an impressive number of articles about PostgreSQL since 2013. List at https://www.cybertec-postgresql.com/en/tag/postgresql/

I just had to do a double take as I was reading a stack overflow post at the same time and recognised it as the same author.

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

#57

Post by fatsdomino001 »

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

I was debating between using ULID vs just using the same sequential bigint sharding strategy that Instagram uses[1][2].

I ended up deciding to use sharded bigints because it enables better indexing, even though there are drawbacks when first writing the instances; The benefit of faster search was more important for me.

[1]: https://instagram-engineering.com/sharding-ids-at-instagram-...

[2]: http://www.livinginthepast.org/2016/02/24/sharding-into-bigi...

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

#58

Post by throwawayboise »

kstrauser wrote: post #43
oconnore wrote: post #33

Earlier quoted context omitted.

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.

This is why we need 2TB drives now, when we used to get by with 2GB.

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

#59

Post by zzzeek »

> You are well advised to choose a primary key that is not only unique, but also never changes during the lifetime of a table row. This is because foreign key constraints typically reference primary keys, and changing a primary key that is referenced elsewhere causes trouble or unnecessary work.

in one sense I agree with the author that things are generally just easier when you use surrogate primary keys, however they really should note here that the FOREIGN KEY constraint itself is not a problem at all as you can just use ON UPDATE CASCADE.

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

#60

Post by jeltz »

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

You can just use PostgreSQL's writeable CTEs to get the same batching benefits plus the benefits from using serials. So, no, I do not think batching is a good reason for using UUIDs.
Post reply on HN