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

61–70 of 182 posts

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

#61

Post by inopinatus »

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

Caveat programmer: this could be problematic, not in the sense it doesn't work, but in the sense that someone working on backend code may have a preconceived expectation that UUIDs are also effectively a keyspace i.e. they're hard to guess. The validity of that is already challenged by variants defining temporal or logical order, and evaporates completely if you let clients declare their own that you accept at face value. Applications may have potentially guessable/gameable object identifiers sloshing around inside as a consequence, which is modestly ironic given that one benefit many folks expect from adopting UUIDs in the first place is hardening up the attack surface of trivially enumerable sequences.

There are a few mitigations but my favourite is the "casino chips" approach: pregenerate them server side, and allocate to clients on demand, including en masse if need be ("here kid, have a few million UUIDs to get you started"). Verify with whatever simple signature scheme comes with your application server framework, or at small scale just toss them in a crude LRU store.

Or, remember where the UUID came from, and apply their organisational scope to any trust you place upon it. This might work particularly for multi-tenanted SaaS. However it requires that all usage is tenant-bounded end-through-end throughout your application. This may be in conflict with a) your framework, b) your zenlike contemplation of simplicity in data management, or c) programmers in a hurry forgetting to scope their queries properly.

Ultimately, relying on UUIDs as intrinsically unguessable security tokens is probably not a great idea, but it's one that remains thoroughly embedded in the programming zeitgeist. As ever, nothing useful comes without a compromise. Keep your eyes open to the systemic consequences of design choices, and don't leave traps for your fellow developers.

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

#62

Post by NicoJuicy »

inopinatus wrote: post #61
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).

Caveat programmer: this could be problematic, not in the sense it doesn't work, but in the sense that someone working on backend code may have a preconceived expectation that UUIDs are also effectively a keyspace i.e. they're hard to guess. The validity of that is already challenged by variants defining temporal or logical order, and evaporates completely if you let clients declare their own that you accept at face v…

He's not saying clients can create their own ids. The applications can.

The concepts he's talking about are required for cqrs. Which is a popular concept applied with mostly DDD or microservices.

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

#63

Post by kstrauser »

deckard1 wrote: post #47
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:…

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…

Sure, and it makes sense there: write a service that returns unique 64 bit ints and encapsulate the complexity inside that one location. That’s easier than making every `insert` in your app code have to do a `while not unique` loop.

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

#64

Post by kortex »

inopinatus wrote: post #61
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).

Caveat programmer: this could be problematic, not in the sense it doesn't work, but in the sense that someone working on backend code may have a preconceived expectation that UUIDs are also effectively a keyspace i.e. they're hard to guess. The validity of that is already challenged by variants defining temporal or logical order, and evaporates completely if you let clients declare their own that you accept at face v…

Should you ever use a plain token (where you just check if it exists in some authed_users table) vs, I dunno, some sort of signed/HMAC type thing, where you have to call some function on it? I genuinely don't know but I know enough to generally leave authentication up to those that do know.

Maybe I'm just thinking of OAuth where there are multiple hops involved?

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

#65

Post by lhenk »

aidos wrote: post #56
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.

Laurenz (the author) was Postgres person of the week not too long ago: https://postgresql.life/post/laurenz_albe/

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

#66

Post by lhenk »

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/

Also, here's a list of blog posts from Laurenz Albe (the author of the OP post): https://www.cybertec-postgresql.com/en/author/cybertec_albe/ His blog posts are a great read, I'd recommend checking them out!

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

#67

Post by simonw »

Something I really like about integer incrementing IDs is that you can run ad-hoc "select * from table order by id desc limit 10" queries to see the most recently inserted rows.

I end up doing this a lot when I'm trying to figure out how my applications are currently being used.

Strictly incrementing UUIDs can offer the same benefit.

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

#68

Post by inopinatus »

NicoJuicy wrote: post #62
inopinatus wrote: post #61

Earlier quoted context omitted.

Caveat programmer: this could be problematic, not in the sense it doesn't work, but in the sense that someone working on backend code may have a preconceived expectation that UUIDs are also effectively a keyspace i.e. they're hard to guess. The validity of that is already challenged by variants defining temporal or logical order, and evaporates completely if you let clients declare their own that you accept at face v…

He's not saying clients can create their own ids. The applications can. The concepts he's talking about are required for cqrs. Which is a popular concept applied with mostly DDD or microservices.

I getcha, but these days the ambit reach of "application" extends to Javascript executing client-side in an environment that's basically overrun with lions/tigers/bears, and I'll suggest that's particularly a consideration when the front-end is a SPA participating in a CQRS/event-sourced overall application architecture.

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

#69

Post by lazide »

NicoJuicy wrote: post #62
inopinatus wrote: post #61

Earlier quoted context omitted.

Caveat programmer: this could be problematic, not in the sense it doesn't work, but in the sense that someone working on backend code may have a preconceived expectation that UUIDs are also effectively a keyspace i.e. they're hard to guess. The validity of that is already challenged by variants defining temporal or logical order, and evaporates completely if you let clients declare their own that you accept at face v…

He's not saying clients can create their own ids. The applications can. The concepts he's talking about are required for cqrs. Which is a popular concept applied with mostly DDD or microservices.

There definitely are people out there in this thread proposing clients be able provide UUIDs. I’ve seen it elsewhere too.

I’ve also personally experienced UUID collisions due to badly set up VM environments under Windows. It isn’t a good idea to blindly trust any value - and that includes supposedly ‘never collide’ id’s like UUID.

For what it’s worth, I also had the joy of debugging someone’s distributed hash table that was using md5 as the hash bucket key (this was... 2 decades ago?) and had no way to handle collisions because obviously that is impossible.

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

#70

Post by 3pt14159 »

> Now, sometimes a table has a natural primary key, for example the social security number of a country’s citizens.

You know, you think that, but it's never that simple. The field was added incorrectly and nobody noticed until the value is in countless tables that you now need to simultaneously update or the value is something that's supposed to be semi-secret, so now a low level support staff can't reference the row when dealing with a request. Or the table's requirements change and now you need to track two different kinds of data or data that is missing the field.

Me, I always just have the table make its own ID. It is just simpler, even when you think it is overkill.

Post reply on HN