Postgres Reads Causing Writes: MVCC, Page Pruning, and Hint Bits
2024-12-25
This article delves into the seemingly paradoxical behavior in PostgreSQL where simple SELECT queries can trigger data writes. Starting with the Multi-Version Concurrency Control (MVCC) mechanism, it explains concepts like row versions, pages, and tuples. Through code examples, it analyzes how hint bit updates and page pruning cause read operations to modify pages, ultimately leading to disk writes. The article also discusses the HOT update optimization strategy and uses page inspection tools to illustrate how data is stored and updated within pages.
Read more
Development
Database Performance