Recently, while reading Chapter 5 of "Database Internals", which explores buffer pools and memory management, I started thinking about how different databases implement caching strategies.
One key realization was that PostgreSQL and MySQL (InnoDB) take fundamentally different approaches. PostgreSQL relies heavily on the OS page cache with having shared buffers, whereas MySQL implements its own buffer pool for managing memory.
PostgreSQL's choice to delegate caching responsibilities to the OS Page Cache instead of managing a dedicated buffer pool like MySQL was an intentional design decision.
Instead of implementing complex memory management, PostgreSQL offloads caching to the OS, leveraging Linux's highly optimized Page Cache. This reduces PostgreSQL's code complexity and allows it to focus on query optimization and execution. By relying on the OS, PostgreSQL benefits from continuous improvements at the kernel level without requiring internal changes.
In other words, having too much memory allocated to shared_buffers don't make sense. See PostgreSQL's official documentation on memory management for more details:
There are some workloads where even large settings for
shared_buffersare effective, but because PostgreSQL also relies on the operating system cache, it is unlikely that an allocation of more than 40% of RAM toshared_bufferswill work better than a smaller amount.
The shared_buffers setting defines how much memory is allocated to PostgreSQL’s buffer pool. You can check the current setting using:
SHOW shared_buffers;
While both PostgreSQL and MySQL use memory caching to reduce disk I/O, their approaches differ significantly:
| Aspect | PostgreSQL's Shared Buffers | MySQL’s InnoDB Buffer Pool |
|---|---|---|
| Primary Purpose | Stores frequently accessed table and index pages before relying on OS Page Cache. | Acts as the primary cache for all InnoDB-related operations, minimizing OS Page Cache involvement. |
| Integration with OS Page Cache | Relies on OS Page Cache for additional caching. | Minimizes OS Page Cache usage and handles caching internally. |
| What is Cached? | Table pages, index pages (but not redo logs). | Table pages, indexes, undo logs, and redo logs. |
| Dirty Page Flushing | Background writer periodically flushes dirty pages to disk. | Uses adaptive flushing with heuristics to manage write pressure. |
| Page Replacement Policy | Uses an ARC-like clock sweep (instead of traditional LRU). | Uses LRU (Least Recently Used) with adaptive heuristics. |
| Impact of Restart | Shared Buffers start empty, but the OS may retain cached pages. | Buffer Pool contents can be persisted and restored across restarts. |
PostgreSQL and MySQL take very different approaches to memory management, each with its own benefits and trade-offs. PostgreSQL’s reliance on the OS Page Cache offers simplicity, flexibility, and better multi-tenant resource management, while MySQL’s dedicated Buffer Pool provides better predictability and restart resilience.