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

71–80 of 182 posts

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

#71

Post by kortex »

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…

UUIDv4 has performance implications. But I agree, if you are already coupled to the DB (due to the check loop), generally you might as well use sequential.

Seems too easy to screw up.

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

#72

Post by inopinatus »

kortex wrote: post #64
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…

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?

The comparison to OAuth is quite reasonable. Perhaps the most obvious parallel is the use of a state parameter during the three-legged exchange, without which it's exposed to a CSRF clickjacking attack.

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

#73

Post by adav »

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?

Check out linear congruential generators or other pseudorandom number generators. Then map the resulting number to letters.

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

#74

Post by stouset »

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 have this situation, it’s not even worth the time or effort to not just use UUIDs. You’re adding complication for no gain whatsoever.

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

#75

Post by kortex »

inopinatus wrote: post #72
kortex wrote: post #64

Earlier quoted context omitted.

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?

The comparison to OAuth is quite reasonable. Perhaps the most obvious parallel is the use of a state parameter during the three-legged exchange, without which it's exposed to a CSRF clickjacking attack.

Right. Maybe it's paranoid, but it seems like a bearer token has potential avenues for forgery (CSRF or others), replay attacks, add-on jacking, etc. Also harder to coordinate with distributed apps. I think the Captain Tightpants approach would be to initialize some client-side private key/cert, use that to sign each request and verify based on that cert.

That should also make it easier to, say, verify and unwrap the request at the gateway to the server, before sending it to the rest of the application-proper.

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

#76

Post by panny »

It seems like int vs bigint is brushed off rather quickly here. bigint is twice the size of int, therefore indexing will be larger as well. Furthermore, all the FK storage and indexing will also be bloated by this choice. If you design a customer table with a bigint PK, and everything will point to customer (invoices, billing statements, etc), then that's not an insignificant amount of space. While most of us may want to have "billions served" like McDonald's, the reality is my company and your company will never have 2 billion customer accounts, even in the wildest of imaginations. If you ever did reach this point, it's "a good problem to have" and relatively easy to move from int -> bigint. Moving in the reverse direction is likely difficult or impossible.

It would be nice to see real benchmarking on millions of rows to compare the three, but my gut tells me you use int by default, bigint if you outgrow int, and UUID if you have plenty of money for hardware and need distribution capabilities a UUID would enable.

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

#77

Post by wwweston »

3pt14159 wrote: post #70

> 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…

It does seem that a "natural key" is frequently just a really foreign key in a database you and your org don't manage.

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

#78

Post by tomnipotent »

3pt14159 wrote: post #70

> 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…

I've been bitten by using natural keys on several occasions, but I can' think of a time surrogate keys failed me beyond the tediousness of implementation.

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

#79

Post by simondotau »

j-pb wrote: post #46
hermanradtke wrote: post #27

Earlier quoted context omitted.

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 5…

Not the worst idea—50 days is a nice sweet spot between infrequent enough to have some indexing benefit and frequent enough that potential downsides will be discovered early in the product’s life cycle.

Personally I wouldn’t do this. A scenario where for each individual millisecond of elapsed time, 96 bits of entropy is an upgrade over 80 bits of entropy, is fairly extreme. I don't think there are many databases in the world which would ever need more collision mitigation than that.

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

#80

Post by tomnipotent »

inopinatus wrote: post #68
NicoJuicy wrote: post #62

Earlier quoted context omitted.

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.

For perspective, the npm uuid package is now being downloaded ~50M/week. It's usage is ubiquitous at this point, on any platform JS is running.

https://www.npmjs.com/browse/depended/uuid

Post reply on HN