Memory Size Matters to PostgreSQL
a year ago
- #Memory Management
- #PostgreSQL
- #Database Performance
- PostgreSQL's shared buffer is a memory segment for managing data flow between data area and backends.
- PostgreSQL evolved from LRU to ARC, then to 2q, and finally to clock sweep algorithm for buffer management.
- Clock sweep algorithm manages buffer replacement via a circular buffer named nextVictimBuffer and a spinlock buffer_strategy_lock.
- Usage counters on buffers are incremented when pinned, affecting eviction decisions.
- Bulk operations like VACUUM or large sequential scans use a small ring buffer to avoid affecting the shared buffer.
- PostgreSQL recommends setting shared_buffer to 25% of system RAM, with an upper limit of 40%.
- Performance with large shared buffers depends on whether the data area fits within the shared buffer.
- Modern DDR4 memory throughput affects the time required for full shared buffer scans.
- A practical upper limit for shared_buffer is around 64GB to avoid performance regression.
- It's crucial to adjust the default shared_buffer setting for optimal performance in production environments.