Three Cache Layers Between Select and Disk
3 months ago
- #Performance
- #Caching
- #Postgres
- Postgres has three cache layers between SELECT and disk: shared buffers, OS page cache, and disk.
- Shared buffers is Postgres' own cache in process memory, checked first for page reads.
- OS page cache is the kernel's cache of disk blocks, reducing disk I/O if data is cached.
- Disk is the final layer, where actual I/O requests are made if data isn't in the caches.
- Increasing shared buffers can starve the OS page cache, potentially hurting performance.
- Postgres pages are 8KB, containing header data, line pointers, and heap tuples.
- work_mem controls memory for operations like sorts; too small causes disk spills, too large consumes RAM.
- Cache hit ratio in Postgres measures shared buffer hits, not actual disk reads.
- Queries fetching many rows only to discard them can cause high IOPS and performance issues.
- Postgres' MVCC causes rows to scatter across pages, increasing random I/O during queries.