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

31–40 of 182 posts

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

#31

Post by throwawayboise »

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…

You're not wrong, but (as I suspect is the case with a lot of us) the vast majority of my work is CRUD and I don't reach for heavyweight debugging tools unless printf() or the equivalent fails me (which is rare). Integer IDs work great in this situation.

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

#32

Post by staticassertion »

lgas wrote: post #30
staticassertion wrote: post #25

Another benefit of using sequential integers is that you can leverage a number of optimizations. For one thing you can represent a range of data more efficiently by just storing offsets. This means that instead of having to store a 'start' and 'end' at 8 + 8 bytes you can store something like 'start' and 'offset', where offset could be based on your window size, like 2 bytes. You can leverage those offsets in metadat…

Doesn't the offset approach run into trouble when sequence values get skipped due to rollbacks?

It's going to be an optimization that assumes some constraints on how you interact with your database.

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

#33

Post by oconnore »

kstrauser wrote: post #7
ainar-g wrote: post #2

I don't think I've ever seen this mentioned anywhere, but if you need a unique ID for an entity with not a lot of records planned (≤10,000,000), why not use a random int64 with a simple for loop on the application side to catch the occasional collisions? Are there any downsides besides making the application side a tiny bit more complex?

That’s the UUID approach, but worse. According to the birthday problem[1], you’re 50% likely to get a collision in 65 bit numbers after about 5 billion insertions. That’s not an awful lot. Replace that with a 128-bit UUID and you’d have to insert 22,000,000,000,000,000,000 rows to get a 50% chance. That’s probably less likely than a cosmic ray flipping a random bit in RAM and corrupting the index that way. [1] https:…

Getting a collision with this approach doesn’t matter — the whole point is to loop if you do get a collision. The only issue is getting a long string of sequential collisions, which is highly unlikely.

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

#35

Post by barrkel »

Another point: if there's any temporal locality to your future access patterns - if you're more likely to access multiple rows which were inserted at roughly the same time - then allocating sequential identifiers brings those entries closer together in the primary key index.

I used to work on a reconciliation system which inserted all its results into the database. Only the most recent results were heavily queried, with a long tail of occasional lookups into older results. We never had a problem with primary key indexes (though this was in MySQL, which uses a clustered index on the primary key for row storage, so it's an even bigger benefit); the MD5 column used for identifying repeating data, on the other hand, would blow out the cache on large customers' instances.

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

#36

Post by edoceo »

hermanradtke wrote: post #27
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…

I use a ulid[1] as a uuidv4 replacement: https://github.com/ulid/spec

I was hoping someone would mention these. Its really quite nice. 128bit, sortable, client or server generated, no collision (well almost zero: 80bits are random per millisecond)

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

#37

Post by friend-monoid »

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…

I had this issue in a case I think is interesting; a customer had a database with incremental IDs of a certain product they sold. On a web platform, the product owner in turn could log in and view a list of their products and their status. The id of the product was part of the URL; /product/851. Of course, the product owners could not get any information on IDs they didn’t own, but the numbers gave away info on how many devices existed before them. And they wanted to hide that information.

Of course, there are many ways to solve that situation, but UUIDs is one.

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

#38

Post by sombremesa »

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…

> Yes, they are guessable but your application should not rely solely on the "secrecy" of the ID to authorize access to a record

Any information you give to a potentially malicious actor can help them attack you. If you have a choice between leaking information and not leaking information, I can’t imagine why you would ever intentionally go with the former, unless you didn’t actually have a choice (feasibility, etc.).

As an example, maybe I needed to CSRF someone but needed their ID (say, in a b2b app where IDs are not generally public) - with sequential IDs I have a decent chance of cracking it with a small number of requests, especially if it’s a small userbase. Sure, the CSRF was the main issue, but this is a contrived example to illustrate the point.

Admittedly, IDs are oftentimes public information by necessity - but there’s no need to allow them to leak extra information.

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

#39

Post by aidos »

hermanradtke wrote: post #27
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…

I use a ulid[1] as a uuidv4 replacement: https://github.com/ulid/spec

They seem like what I'm looking for. I was looking at moving to something more random because our current UUIDs (uuid1) are too easy to mistake for one another at a glance.
Post reply on HN