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

91–100 of 182 posts

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

#91

Post by ccleve »

inopinatus wrote: post #61
tehlike wrote: post #13

Earlier quoted context omitted.

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

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…

In Java there is a UUID generator based on SecureRandom. That's about as unguessable as you're going to get.

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

#92

Post by eloff »

strangeattractr wrote: post #89

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?

I would use uuid in this case. If p and m are too large you get overflow. If they are too small your keys are guessable. If it matters, use uuid and don't waste time and mental energy on it.

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

#93

Post by eric4smith »

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.

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

#94

Post by staticassertion »

weird-eye-issue wrote: post #84
staticassertion wrote: post #20

Earlier quoted context omitted.

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

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.

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

#95

Post by ccleve »

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

The Postgres JDBC driver does not guarantee that batch inserts come back in the same order that you insert them (when you use RETURNING *). So, if you generate UUIDs server-side, you can't conveniently match them up with the records you just inserted. You're better off generating them in the app server first and then sending them to Postgres.

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

#96

Post by tehlike »

megous wrote: post #42
tehlike wrote: post #13

Earlier quoted context omitted.

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

There's nothing that prevents you from fetching a batch of N IDs from the server. Server just does +N on the sequence and you can do whatever you like with the IDs client side. You can also use one sequence for everything on the server, and then you can also pre-create ID based relationships client side.

Made the same claim here: https://news.ycombinator.com/item?id=27347243

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

#97

Post by JshWright »

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…

Speaking from personal experience, just use bigint... If you aren't dealing with billions of rows, the size difference isn't that big a deal, and if you are dealing with billions of rows, the int -> bigint migration is definitely not "relatively easy".

One of the most memorable anecdotes of my professional career is a production environment going down because we hit maxint on an important (and busy) table. The dirty hack we used to get the site back up (hint: int is _signed_), and the weeks it took to plan, test, and execute the migration.

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

#98

Post by ccleve »

ccleve wrote: post #95
tehlike wrote: post #13

Earlier quoted context omitted.

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

The Postgres JDBC driver does not guarantee that batch inserts come back in the same order that you insert them (when you use RETURNING *). So, if you generate UUIDs server-side, you can't conveniently match them up with the records you just inserted. You're better off generating them in the app server first and then sending them to Postgres.

Plus, it's way better to generate a UUID in the app server and send it to the server as a string. The reason is that you can deal with the id as a plain string and don't have to deal with a non-standard datatype in your app.

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

#99

Post by LAC-Tech »

throwawayboise wrote: post #29
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 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…

From a security perspective, you're right.

They can inherently leak how much data you do or don't have, which you may not want your competitors to know.

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

#100

Post by weird-eye-issue »

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

Earlier quoted context omitted.

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

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
Post reply on HN