UUID, serial or identity columns for PostgreSQL auto-generated primary keys?
cybertec-postgresql.com
UUID, serial or identity columns for PostgreSQL auto-generated primary keys?
1–10 of 182 posts
Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?
#2I 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?
Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?
#3I 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?
You can also do the loop on the server side using PL/pgSQL:
https://www.postgresql.org/docs/current/plpgsql-control-stru...
Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?
#4I 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?
The point of guids/uuids is no collision... Ever... Including for large datasets. Even when you merge multiple together in 10 years.
For your use-case you could use incremental IDs.
Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?
#5About 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 generators.
Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?
#6Another alternative is ULID, which can be stored as UUID on a Postgres side, but is more b-tree friendly.
Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?
#7I 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://en.wikipedia.org/wiki/Birthday_problem#Probability_t...
Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?
#8I 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?
I have a web service where I need to generate access tokens of five characters. I just generate a random one and check to see if it’s already in use. Been working fine for years.
Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?
#9I 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:…
The post qualified as <= 10,000,000 total records. For that number of records, there's about a chance of about 0.00001 that you get a collision, assuming good randomness.
Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?
#10UUIDs 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.