4 ms·

Nothing wrong with 128bits primary key. Postgres is a horrible DB that can’t order rows on disk, so it doesn’t matter if you use UUIDs or not, you’re SOL. In f

Nothing wrong with 128bits primary key. Postgres is a horrible DB that can’t order rows on disk, so it doesn’t matter if you use UUIDs or not, you’re SOL. In fact, you could use 512bits primary key with MySQL and still, range queries would be 10x faster and 10x less space in ram than in Postgres. This article explains why that is.

jashmatthews 2y ago
Clustered tables and sequential keys have their own downsides though like lock contention on the "last" page.
- srcreigh 2y ago
  Not really a problem in practice. Most clustered tables don’t have a single last page. For ex it’s common to order a table by (used_id,pk) so a users data is grouped together. Dropbox did this For the ones which do have a single last page, it’s easy to remove that, you don’t have to use sequential IDs. Use a uuid. Basically this problem only happens when you cluster globally by mistake. just don’t make that mistake.
chipdart 2y ago
> Nothing wrong with 128bits primary key. Postgres is a horrible DB that can’t order rows on disk, so it doesn’t matter if you use UUIDs or not, you’re SOL. Why do you believe a database should order rows on disk?
- stoperaticless 2y ago
  1. Faster access by that column. 2. Other DBs allow it/default to it.
- hobs 2y ago
  https://www.postgresql.org/docs/current/sql-cluster.html https://www.postgresql.org/docs/current/sql-cluster.html Also postgres can do this, it just doesn't maintain a clustered index as a going forward concern, which is an odd choice but postgres is certainly full of them.
- srcreigh 2y ago
  In Postgres every read happens in increments of 8kb If your rows are not ordered on disk, the amount of data you need to load to query 100 rows is insane 10kb query result (100 rows 100 bytes) requires almost 1mb of data to be loaded- it’s 99% waste Postgres is 100x slower than other DBs for range queries Every single other database in existence has this feature except for Postgres

jashmatthews 2y ago

Clustered tables and sequential keys have their own downsides though like lock contention on the "last" page.

srcreigh 2y ago
Not really a problem in practice. Most clustered tables don’t have a single last page. For ex it’s common to order a table by (used_id,pk) so a users data is grouped together. Dropbox did this For the ones which do have a single last page, it’s easy to remove that, you don’t have to use sequential IDs. Use a uuid. Basically this problem only happens when you cluster globally by mistake. just don’t make that mistake.

chipdart 2y ago

> Nothing wrong with 128bits primary key. Postgres is a horrible DB that can’t order rows on disk, so it doesn’t matter if you use UUIDs or not, you’re SOL. Why do you believe a database should order rows on disk?

stoperaticless 2y ago
1. Faster access by that column. 2. Other DBs allow it/default to it.
hobs 2y ago
https://www.postgresql.org/docs/current/sql-cluster.html https://www.postgresql.org/docs/current/sql-cluster.html Also postgres can do this, it just doesn't maintain a clustered index as a going forward concern, which is an odd choice but postgres is certainly full of them.
srcreigh 2y ago
In Postgres every read happens in increments of 8kb If your rows are not ordered on disk, the amount of data you need to load to query 100 rows is insane 10kb query result (100 rows 100 bytes) requires almost 1mb of data to be loaded- it’s 99% waste Postgres is 100x slower than other DBs for range queries Every single other database in existence has this feature except for Postgres