Three Cache Layers Between Select and Disk
Summary
This article explains the three cache layers between PostgreSQL SELECT and disk: shared_buffers, OS page cache, and disk I/O, with how they compete for RAM and how a mis-tuned configuration can hurt performance. It uses a real-world incident to show high IOPS caused by a poorly chosen index on JSONB data, and suggests practical fixes like a GIN index and partial index. It also covers MVCC and how updates scatter data across pages, impacting IO.