Optimize PostgreSQL Server Performance Through Configuration

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 for PostgreSQL is fairly painstakingly chosen to ensure that it will run on every environment it is installed, meeting the lowest common denominator resources across most platforms.

Because of this, it's always recommended that one of the first actions performed once an install of PostgreSQL is completed, would be to tune and configure some high-level settings.

There are four high-level settings which will be 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 that PostgreSQL uses its own internal buffer as well as kernel buffered IO. In short, this means that 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 the shared_buffers is one of the settings which 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 for shared_buffers 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_buffer 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 settings, and as such, many times the setting is left to the default value.

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

This value is used only by the PostgreSQL query planner to figure out whether plans it's considering would be expected to fit in RAM or not. As such, it's 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 discrete needs on 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 help 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, then the majority of sort actions are performed in the much-faster memory, rather than being written and read to disk.

However, it's 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's 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's 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 https://pgtune.leopard.in.ua/#/ 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's 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 go forth and tune your PostgreSQL installs! We're also here to help with PostgreSQL support and to help troubleshoot any PostgreSQL performance issues you may come across.