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

141–150 of 182 posts

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

#141

Post by Cthulhu_ »

the_gipsy wrote: post #139
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…

In Spain each person has a unique ID number assigned at birth. The numbers for newborns are geographically pre-distributed to guarantee uniqueness despite delay in paperwork. It is universally accepted that this ID "number" (it actually has one letter too) is all you need to identify yourself, ever. Except that I knew a coworker who had a duplicate ID. An extremely rare event, they messed up the pre-assignment and th…

The fact that it's fixed / can never be changed is a massive problem with social security numbers. That, and the fact it's often used as authentication instead of identification. They're moving away from that slowly, but it's taking a lot of time and effort.

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

#142

Post by Cthulhu_ »

Aeolun wrote: post #111
Thiez wrote: post #106

Earlier quoted context omitted.

In the Netherlands SSNs are not unique, they handed out some duplicate ones back in the day. So not great as a primary key. Besides, I think using them as primary keys is illegal anyway.

Huh? How does sign-in work for people with duplicate ID’s?

SSN's predate sign-in and the internet. Anyway there is a digital government sigle-sign-on solution called DigID, but you need to create an account etc for it using an e-mail address as the sign-in.

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

#143

Post by codeflo »

pritambarhate wrote: post #129

A little late to comment here. But for database IDs, I have found that Instagram's technique to generate IDs works very well: https://instagram-engineering.com/sharding-ids-at-instagram-... They are not serially incrementing but still sortable. Thus prevent index fragmentation issues observed with UUIDS. Are 8 bytes in length. So index size is smaller compared to UUIDs. So you get all benefits of serial IDs but they…

> they are not easily guessable

I don't see how that's true. From reading the article you linked, you only need a valid shard ID (which you can extract from known IDs), the millisecond (which is guessable) and a 10-bit sequence (which you can easily brute-force).

(And that's completely fine if their security model doesn't require unguessable IDs.)

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

#144

Post by efxhoy »

eric4smith wrote: post #136
sk5t wrote: post #114

Earlier quoted context omitted.

A vote here against integer/serial PKs, not only because they leak information, but also because they can result in incorrect joins. IME it's much more often I've quickly made a table with a serial PK and later wished it were uuid; just about never made a uuid and later wished for the compactness or natural clustering of bigint. Maybe for a table of millions and millions of time-ordered events.

Note I said "internal use". But how can primary keys result in incorrect joins? Unless you're changing a foreign key, joins will always be correct. Unless I'm doing something wrong in the last 30 years of using SQL.

If you use serial integer ids and accidentally join on the wrong tables/columns you will get rows back even if the join doesn't make sense, because all serian integer ids have values in common. If you're using UUIDs you will "never" get rows back when joining on the wrong ids and spot your mistake.

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

#145

Post by rini17 »

I'm a fan of generating primary key by copying natural key (if it's one integer) or hash of natural key. This is done only once when row is created and is never updated, even if natural key changes. In this case you are left with valuable bit of information that something happened to natural key.

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

#146

Post by BatteryMountain »

hardwaresofton wrote: post #134
BatteryMountain wrote: post #124

I feel the whole debate is overkill: 99% of businesses/systems will never have so much data that they NEED to use uuid's. I personally don't like using integers for keys either as I've been burnt by them before. I also doubt any software I build today or have built in the last 10 years will be used 100 years from now. Recently I built a new system (typical business-type backend) and forced to use sqlite + C# + dapper…

> Recently I built a new system (typical business-type backend) and forced to use sqlite + C# + dapper. Using this combination I cannot use guid/uuid as dapper cannot properly map it back to c# from sqlite Are you sure about this? This is pretty poor of a well known solution in the ORM world, SQLite or not. If you were going for sortability/understandability then I understand slapping your own together, but why not g…

The problem is when reading the data back with Dapper (writing works fine) and it has to parse the guid stored in sqlite back into a .net Guid. Thus I resorted to making the column type text and ToString() all the Guid's before writing, but that's pretty gross. With a normal string field, sqlite can set it as primary key, make unique indexes on it, use it as foreign keys. Thus my attempt to use my own generator the generate id's. So far it works great but not sure if it will be fine with 5 years of usage (basically on tables that keep growing over time, like a comments table).

But of a rabbit hole: https://github.com/DapperLib/Dapper/pull/1082

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

#147

Post by staticassertion »

weird-eye-issue wrote: post #100
staticassertion wrote: post #94

Earlier quoted context omitted.

We'll have to agree to disagree. Systems like the ones I described are a hell of a lot easier to build on long term while maintaining those invariants.

How is that easier to maintain than literally just having a UUID primary key? I feel like we must be talking about different use cases

If your goal is to use the uuid as a delegated capability it's going to be much more complex to use the primary key for your row than to use a separate key.

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

#148

Post by conradfr »

dbbk wrote: post #55
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.

Yes, I completely forgot about it. I used it a few years ago, I tried also [1] which is integers instead of strings.

[1] https://github.com/jenssegers/optimus

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

#149

Post by hardwaresofton »

BatteryMountain wrote: post #146
hardwaresofton wrote: post #134

Earlier quoted context omitted.

> Recently I built a new system (typical business-type backend) and forced to use sqlite + C# + dapper. Using this combination I cannot use guid/uuid as dapper cannot properly map it back to c# from sqlite Are you sure about this? This is pretty poor of a well known solution in the ORM world, SQLite or not. If you were going for sortability/understandability then I understand slapping your own together, but why not g…

The problem is when reading the data back with Dapper (writing works fine) and it has to parse the guid stored in sqlite back into a .net Guid. Thus I resorted to making the column type text and ToString() all the Guid's before writing, but that's pretty gross. With a normal string field, sqlite can set it as primary key, make unique indexes on it, use it as foreign keys. Thus my attempt to use my own generator the g…

Ahh thanks for noting this -- I've written off writing C# and PHP for the rest of my life for reasons, but this is a fascinating read.

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

#150

Post by squiggleblaz »

chrischen wrote: post #117
dragonwriter wrote: post #82

Earlier quoted context omitted.

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

Maybe using them as passwords is what's wrong.

How do you cope if you want to record people who don't have an SSN or equivalent? (e.g. I have none, because the country of my citizenship doesn't issue anything comparable)

Or if you're expanding to countries whose SSNs clash with each other?

SSN as PK is wrong regardless of whether you're doing SSN as password.

Post reply on HN