3 ms·
There is one thing which troubles me here: Postgres does not support clustered indexes, so the "spatial locality of the storage engine" is non-existent, only th
by masklinn 4y ago
There is one thing which troubles me here: Postgres does not support clustered indexes, so the "spatial locality of the storage engine" is non-existent, only the indexes should be impacted.
Why would you have "UUIDs writing to pages all over the place"? Or is that, and the WAL amplification, be solely about the updates to random btree leaves?
- to11mtm 4y ago(Not a DB Engineer but poke at enough different DBs to armchair semi-confidently) In the case of Postgres I'd expect to see it indeed be mostly about random updates to leaves. I suppose Partitioning could come into play but I'm not sure how you'd set it up in a way that was beneficial and dynamic enough for the situation in the first place. But, It's worth remembering that most other DBs (Oracle, MSSQL, MySql/etc) do have clustered indexes, and in some cases you can't just flip over to Postgres. (Even if you want to!)
- SigmundA 4y agoboth MSSQL and Postgresql support fill factor as well, which nobody seems to mention. Setting a lower fill factor leaves more room in the pages to avoid page splits with random writes. Also random writes can be beneficial in that you are not hot spotting the last page as you are with sequential ID's. Its not black and white, there are some good advantages with random ID's but you need to manage them properly.
- patrec 4y agoYeah, it does: https://www.postgresql.org/docs/current/sql-cluster.html https://www.postgresql.org/docs/current/sql-cluster.html