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

81–90 of 182 posts

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

#81

Post by hu3 »

kaliszad wrote: post #26
hu3 wrote: post #17

Earlier quoted context omitted.

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

Not really. You should develop better tooling to visualize debugging information. Today's serious systems (this in my opinion includes e.g. collaborative rich text editors) are just too complicated to just eyeball. Pavel, a colleague of mine is developing a new collaborative rich text editor for OrgPad and here is, how we do some testing currently https://www.youtube.com/watch?v=VeVcNmNFzmc We use UUIDs for basically…

Disagree. /user/edit/5 tells me easily what record it is about on the database without having to copy paste an UUID.

Dev experience truly is a case of death by thousand cuts. I avoid every little cut I can like the plague so energy goes into making cool stuff.

> Should develop better tooling to visualize debugging information.

Thing is, why would I spend time overengineering tooling I don't need if I can get away with incrementing ids? When optimizing for value, I'd rather spend time solving business problems.

Your editor is very cool btw. And it's clearly a case where incrementing ids are not optimal.

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

#82

Post by dragonwriter »

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…

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

It’s that simple if you’re the Social Security Administration and its a table of Social Security Accounts, not people.

Other than that, using SSNs as a primary key is just plain wrong.

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

#83

Post by weird-eye-issue »

tgv wrote: post #48
throwawayboise wrote: post #29

Earlier quoted context omitted.

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.

This is an example where you don't need a UUID as the key (since you could have another field that stores this "secret" value), but it makes it very convenient if you do use UUID as primary key by default because you get that "secret" value for free (no need to create another column and index). In my projects I use it by default for all models. It comes in handy. Another use case is needing to know the primary key before inserting into the database (at either the front end or the backend, but typically the backend).

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

#84

Post by weird-eye-issue »

staticassertion wrote: post #20
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. Yo…

That sounds really overkill for simply wanting an identifier that doesn't change, isn't guessable, and doesn't reveal how many rows there are

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

#85

Post by rsync »

I have no particular expertise with modern databases and it has been decades since I did any work as a DBA.

However, I cannot imagine creating table entries without a datestamp. No matter what else you are doing, or what you index by, I would want YYYY-MM-DD_HH-MM-SS in every row.

Maybe I'm just weird that way ...

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

#86

Post by dragonwriter »

zzzeek wrote: post #59

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

ON UPDATE CASCADE avoids much developer impact, but it isn’t free and has (potentially quite large) performance impacts.

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

#87

Post by NicoJuicy »

lazide wrote: post #69
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.

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

This seems more an issue of the libraries random generator to form uuids.

Eg. I use guids ( .net) and i have never seen an issue.

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

#88

Post by NoNotTheDuo »

rsync wrote: post #85

I have no particular expertise with modern databases and it has been decades since I did any work as a DBA. However, I cannot imagine creating table entries without a datestamp. No matter what else you are doing, or what you index by, I would want YYYY-MM-DD_HH-MM-SS in every row. Maybe I'm just weird that way ...

And ideally there is a created time stamp and a last updated time stamp.

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

#89

Post by strangeattractr »

This is making me reconsider how I do IDs. I thought the performance of sequential IDs was significantly better. So my approach was to use a standard auto-increment primary ID and then obfuscate by id * p mod m where p and m are coprime and very large. then i get back the original ID using the mod inverse. Should I just be using UUID?
Post reply on HN