Memory Size Matters to PostgreSQL

Summary

Nowadays it’s not uncommon to deal with machines with hundreds of GB of RAM. Abundant memory can give PostgreSQL a massive performance boost. However things work slightly different than you may expect. Let’s find out! The problem with the shared_buffer The shared buffer is a segment allocated by PostgreSQL in the server’s memory for managing the flow of data between the data area and the backends. Whether we are reading or changing the data, the database physical pages are stored into shared buffer. During the normal database activity it’s quite normal the eviction of stored buffers to make space for data pages not present into the shared buffer. In the ancient PostgreSQL 7.4 there was a simple last recently used (LRU) algorithm in place. This simplistic approach had many limitations, in particular because it didn’t consider whether the candidate buffer was used frequently. With PostgreSQL 8.0.0 the memory manager was replaced by the adaptive replacement cache (ARC). However, because the ARC was patented it was replaced by less efficient algorithm in PostgreSQL 8.0.1. PostgreSQL 8.1 replaced again the inefficient 2q algorithm with a new implementation called the clock sweep that it’s is still in use in the modern PostgreSQL. The clock sweep In the shared buffer there is a “free list” of buffers prime candidates for replacement. The buffers that contain no valid page are always in this list. It should be possible to add buffers into this list, for example if their pages are unlikely to be needed soon but the current algorithm never does that. The normal buffer replacement strategy is therefore managed by the clock sweep via a circular buffer named nextVictimBuffer protected by a separate system-wide spinlock called buffer_strategy_lock. This spinlock provides mutual exclusion for operations that access the buffer free list or select buffers for replacement. While buffer_strategy_lock is held then no other locks of any sort should be acquired allowing the buffer repla...