Memory Size Matters to PostgreSQL

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, b ... Read full article.