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

131–140 of 182 posts

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

#131

Post by berkes »

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

Another benefit is that clients can generate and store objects before sending them to the server.

Allowing simple caching, easy async, easy handling of offline, or far simpler clientside code for dealing with those objects. etc.

For mobile app development which relies on an online (http) backend, clientside generatable UUIDs offer almost only benefits.

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

#132

Post by runeks »

sk5t wrote: post #114
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.

> […] but also because they can result in incorrect joins.

Side question: can I get Postgres to throw an error if I try to join on two IDs where neither of the IDs have a foreign key reference to the other?

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

#133

Post by orangepanda »

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…

> With more than 25 photos and 90 likes every second

What unimaginable scale

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

#134

Post by hardwaresofton »

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 generate v1/v4/v6[0] UUIDs in your application and then send them along to teh database, possibly prefixed with whatever you want it to be sorted by (though IMO you should just add that metadata to the thing being saved and sort on that properly)?

[0]: http://gh.peabody.io/uuidv6/

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

#135

Post by hardwaresofton »

Yeah, just use a UUID unless the bits to store the UUID really are your driving limitation (they're not), having a UUID that is non-linear is almost always the most straight-forward option for identifying things, for the tradeoff of human readability (though you can get some of that back with prefixes and some other schemes). I'm not going to rehash the benefits that people have brought up for UUIDs, but they're in this thread. At this point what I'm concerned about is just... what is the best kind of UUID to use -- I've recently started using mostly v1 because time relationship is important to me (despite the unfortunate order issues) and v6[0] isn't quite so spread yet. Here's a list of other approaches out there worth looking at

- isntauuid[1] (mentioned in this thread, I've given it a name here)

- timeflake[2]

- HiLo[3][4]

- ulid[5]

- ksuid[6] (made popular by segment.io)

- v1-v6 UUIDs (the ones we all know and some love)

- sequential interval based UUIDs in Postgres[7]

Just add a UUID -- this almost surely isn't going to be what bricks your architecture unless you have some crazy high write use case like time series or IoT or something maybe.

[0]: http://gh.peabody.io/uuidv6/

[1]: https://instagram-engineering.com/sharding-ids-at-instagram-...

[2]: https://github.com/anthonynsimon/timeflake

[3]: https://en.wikipedia.org/wiki/Hi/Lo_algorithm

[4]: https://www.npgsql.org/efcore/modeling/generated-properties....

[5]: https://github.com/edoceo/pg-ulid

[6]: https://github.com/segmentio/ksuid

[7]: https://www.2ndquadrant.com/en/blog/sequential-uuid-generato...

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

#136

Post by eric4smith »

sk5t wrote: post #114
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.

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.

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

#137

Post by tgv »

weird-eye-issue wrote: post #83
tgv wrote: post #48

Earlier quoted context omitted.

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

True, the record contains both a classical sequential id and a uuid (to maintain backwards compatability), but now everything is linked through the uuid instead of the id. Convenient, indeed. And there's never much data associated with a single uuid, so performance is not an issue.

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

#138

Post by giansegato »

orangepanda wrote: post #133
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…

> With more than 25 photos and 90 likes every second What unimaginable scale

That was in 2012, when they "only" had 15M users

Today, a decade later, they're at 1.074B

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

#139

Post by the_gipsy »

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 there is another dude somewhere with his same ID. So from time to time, some system would tell him that his ID was already registered. A lot of banks and stuff like private healthcare systems like to use the DNI as usernames.

He tried to get his ID changed, but that was such a foreign concept to any of the involved institutions, that he had to give up because there simply is no such procedure. I guess he could have taken it to court, but the guy decided to just live with it (the justice system is quite slow here).

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

#140

Post by robaato »

wvenable wrote: post #116
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!

We had a classic situation at a software house I worked at in the '80s - employee numbers were 1-999 and then jumped to 5,000 - because, you guessed it, this "unique" field was used with magic numbers 1,000 - 5,000 being reserved for project ids in various key accounting systems!

And we were supposed to teach our customers good design principles...

Post reply on HN