undefined | Better HN

0 pointsCodesInChaos2y ago0 comments

Postgres handles updates as insert+delete, and its secondary indexes reference the physical location of the row, instead of the primary key. This means that whenever an update results in an insert to a different page, the index needs to be updated as well, even if the indexed column hasn't been modified.

At least it has an optimization that if the insert ends up in the same page, it won't need to update the index https://www.postgresql.org/docs/current/storage-hot.html

Replication has a similar amplification issue. Historically postgres has favored physical replication over per-row logical replication, that means that replication needs to transfer every modified page, including modified indexes, instead of just the new value of the modified row. (I think logical replication support has improved over the last couple of years).

There is the OrioleDB project, which attempts to improve on the design flaws in postgres's storage engine, but it's definitely not production ready yet.

0 comments

sgarland2y ago

It’s worse than that, unfortunately – since the entire row has to be updated due to MVCC, if a single index is updated, then _all_ indexes are updated. If you have a wide table with a lot of indexes, each UPDATE is N*index writes. This becomes even worse with multiple updates if indexed columns aren’t k-sortable (like UUIDv4), since now the DB will probably have to jump around multiple pages despite the rows being logically sequential.

j / k navigate · click thread line to collapse

0 pointsCodesInChaos2y ago0 comments

At least it has an optimization that if the insert ends up in the same page, it won't need to update the index https://www.postgresql.org/docs/current/storage-hot.html

There is the OrioleDB project, which attempts to improve on the design flaws in postgres's storage engine, but it's definitely not production ready yet.

0 comments

sgarland2y ago

j / k navigate · click thread line to collapse