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

111–120 of 182 posts

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

#111

Post by Aeolun »

Thiez wrote: post #106
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 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?

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

#112

Post by sk5t »

BenjiWiebe wrote: post #108
nimish wrote: post #101

Earlier quoted context omitted.

It's the german tank problem. Serial IDs, with some light assumptions, leak information about the total count of items.

Just pick a random number at the beginning, and start incrementing IDs from there. Like personal checks starting at 1000 so they're always(ish) 4 digit. Of course, maybe pick another starting number that's less obvious.

That's not effective at all.

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

#114

Post by sk5t »

eric4smith wrote: post #93

Simple rules: Use integer primary keys internally for identifiers and relationships. Use English/Other Language permalinks for URL's Use UUID's in places like API's one-time action links and "private" links that you only want to share with other people. Worked fine for me for many, many years.

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.

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

#115

Post by twhitmore »

panny wrote: post #76

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

In datamodelling, tables can often be categorized by lifetime. 'Business Relationships' eg. customers, suppliers, products have a fairly long lifetime; whereas 'Business Transactions' are created on a much higher frequency.

I'm generally fairly comfortable using int for business relationships, and bigint (long) for transaction data.

For performance, insertion speed often seems to be dominated by 'commit latency' to sync to the disk; rather than by record size. I would agree that record size affects table scan, but for many datamodels keying may often be a relatively small proportion compared to the size of text fields and other data.

I like to model keyspaces to work for 200 years, for the largest forseeable market growth, times at least a factor of 10 for safety.

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

#116

Post by wvenable »

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 finally got our company to standardize on someone's employee number as a primary key for everything employee related. It's a simple monotonically increasing integer value -- the best possible primary key.

We moved to a new HR system and they have a set of "reserved" employee numbers that cannot be used and we have employee numbers in that range. Arg!

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

#117

Post by chrischen »

dragonwriter wrote: post #82
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.

Maybe using them as passwords is what's wrong.

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

#118

Post by iratewizard »

dragonwriter wrote: post #82
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.

Right? Credit card numbers as a primary key is far more efficient.

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

#119

Post by sneak »

Thiez wrote: post #106
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 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.

Imagine a man with a stick or gun coming to your office to shackle you in chains for how you arranged your database schema.

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

#120

Post by mrweasel »

Thiez wrote: post #106
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 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.

In Denmark they are specifically not allowed to be used as a primary key. I mean many do, but technically your suppose to have a separate internal ID and then you use the SSN to look up that ID.
Post reply on HN