Optimize PostgreSQL Server Performance Through Configuration

Luke Hebert

- 5 minutes read - 980 words

By design, the out-of-the-box configuration for PostgreSQL is defined to be a “Jack of All Trades, Master of None.” The default configuration is painstakingly chosen to ensure that PostgreSQL will run on every environment it is installed, meeting the lowest common denominator of resources across most platforms.

Because of this, it is always recommended that one of the first actions performed after a PostgreSQL install is completed is to tune and configure some high-level settings.

There are four high-level settings discussed here: shared_buffers, wal_buffers, effective_cache_size, and maintenance_work_mem.

Let’s begin with shared_buffers.

shared_buffers

PostgreSQL uses “double buffering,” meaning it uses its own internal buffer as well as kernel buffered IO. In short, data is stored in memory twice. The PostgreSQL buffer is named shared_buffers and it defines how much dedicated system memory PostgreSQL will use for cache.

Because of PostgreSQL’s design choice to ensure compatibility on all supported machines and operating systems, this value is set conservatively low by default. As such, updating shared_buffers is one of the settings that will be the most effective in improving overall performance on most modern operating systems.

There is not one specific recommended value for shared_buffers, but the calculation to determine the value for a particular system is not especially difficult.

Generally speaking, the value for shared_buffers should be roughly 25% of the total system RAM for a dedicated DB server. The value should never be set to reserve all of the system RAM for PostgreSQL. A value over 25% of the system RAM can be useful if, for example, it is set such that the entire database working set of data can fit in cache, as this would greatly reduce the amount of time reading from disk.

Alternately, while a larger shared_buffers value can increase performance in “read heavy” use cases, having a large shared_buffers value can be detrimental for “write heavy” use cases, as the entire contents of shared_buffers must be processed during writes.

wal_buffers

Write-Ahead Logging (WAL) is a standard method for ensuring integrity of data. Much like in the shared_buffers setting, PostgreSQL writes WAL records into buffers and then these buffers are flushed to disk.

The default size of the buffer is set by the wal_buffers setting, initially at 16MB. If the system being tuned has a large number of concurrent connections, then a higher value for wal_buffers can provide better performance.

effective_cache_size

effective_cache_size has the reputation of being a confusing PostgreSQL setting, and as such, many times the setting is left at the default value.

The effective_cache_size value provides a “rough estimate” of how much memory is available for disk caching by the operating system and within the database itself, after taking into account what is used by the OS and other applications.

This value is used only by the PostgreSQL query planner to figure out whether plans it is considering would be expected to fit in RAM or not. As such, it is a bit of a fuzzy number to define for general use cases.

A conservative value for effective_cache_size would be 1/2 of the total memory available on the system. Most commonly, the value is set to 75% of the total system memory on a dedicated DB server, but can vary depending on the specific needs of a particular server workload. If the value for effective_cache_size is too low, then the query planner may decide not to use some indexes, even if they would greatly increase query speed.

work_mem

The value of work_mem is used for complex sort operations, and defines the maximum amount of memory to be used for intermediate results, such as hash tables, and for sorting. When the value for work_mem is properly tuned, the majority of sort actions are performed in the much faster memory, rather than being written and read to disk.

However, it is important to ensure that the work_mem value is not set too high, as it can “bottleneck” the available memory on the system as the application performs sort operations. In this case, for example, the system will try to allocate work_mem several times over for each concurrent sort operation.

Because of this important caveat, it is ideal to set the global value for work_mem at a relatively low value, and then alter any specific queries themselves to use a higher work_mem value:

SET LOCAL work_mem = '256MB';
SELECT * FROM db ORDER BY LOWER(name);

maintenance_work_mem

While work_mem specifies how much memory is used for complex sort operations, maintenance_work_mem specifies how much memory is used for routine maintenance tasks, such as VACUUM, CREATE INDEX, and similar.

Unlike work_mem, however, only one of these maintenance operations can be executed at a time by a database session. As a result, most systems do not have many of these processes running concurrently, so it is typically safe to set this value much larger than work_mem, as the larger amounts of available memory could improve the performance of vacuuming and database dump restores.

The default value for maintenance_work_mem is 64MB.

Wrapping Up

Using a tool such as pgtune to craft an initial configuration is worthwhile, but the key to getting the absolute best performance is benchmarking your workload and comparing against a known baseline.

It is also important to remember that even the most well-tuned database cannot salvage poorly formed queries. Developers creating applications which interface with the database need to be mindful of how queries are written.

If a query performs heavy joins or other expensive aggregate operations, or if a query is performing a full table scan where an index could be used, it will nearly always perform poorly, no matter how well the database settings are tuned.

We hope that the brief explanations above provide enough insight to allow you to go forth and tune your PostgreSQL installs. We are also here to help with PostgreSQL support and to help troubleshoot any PostgreSQL performance issues you may come across.