PostgreSQL 14 Internals by Egor Rogov is a great book that tackles applied computer science without sugar-coating the tough parts. One intriguing topic explored in the book is how PostgreSQL executes UPDATE statements, which can be complex due to conflicts, anomalies, and deadlocks. The author highlights two specific behaviors in PostgreSQL that stand out. Firstly, PostgreSQL’s design decision to not update rows in-place can lead to write amplification, which increases disk I/O and storage space. Secondly, PostgreSQL can encounter lost updates, which is considered an anomaly that violates the SQL standard. The author also touches on the occurrence of deadlocks in PostgreSQL and provides insights on how to prevent them. Overall, PostgreSQL 14 Internals offers valuable insights for anyone working with PostgreSQL and delves into various aspects of the database’s internals.
https://patrick.engineering/posts/postgres-internals/