PostgreSQL TuningLast updated: 2020-08-12

Effective Cache Size in PostgreSQL

Setting effective_cache_size to 1/2 of total memory would be a normal conservative setting, and 3/4 of memory is a more aggressive but still reasonable amount.

To get a rough estimate for a good setting:

Shared Buffers

The shared_buffers configuration parameter determines how much memory is dedicated to PostgreSQL to use for caching data.

If this value is too low, the database would use more disk, which would cause more slowness, but if it is too high, could generate high memory utilization.

According to the documentation, if you have a dedicated database server with 1GB or more of RAM, a reasonable starting value for shared_buffers is 25% of the memory in your system.

work_mem

If you do a lot of complex sorts, and have a lot of memory, then increasing the work_mem parameter allows PostgreSQL to do larger in-memory sorts which, unsurprisingly, will be faster than disk-based equivalents.

It specifies the amount of memory that will be used by the ORDER BY, DISTINCT and JOIN before writing to the temporary files on disk.

As with the shared_buffers, if we configure this parameter too low, we can have more operations going into disk, but too high is dangerous for the memory usage.

The default value is 4 MB.

This size is applied to each and every sort done by each user, and complex queries can use multiple working memory sort buffers. Set it to 50MB, and have 30 users submitting queries, and you are soon using 1.5GB of real memory. Furthermore, if a query involves doing merge sorts of 8 tables, that requires 8 times work_mem. You need to consider what you set max_connections to in order to size this parameter correctly. This is a setting where data warehouse systems, where users are submitting very large queries, can readily make use of many gigabytes of memory.

log_temp_files can be used to log sorts, hashes, and temp files which can be useful in figuring out if sorts are spilling to disk instead of fitting in memory. You can see sorts spilling to disk using EXPLAIN ANALYZE plans as well. For example, if you see a line like Sort Method: external merge Disk: 7526kB in the output of EXPLAIN ANALYZE, a work_mem of at least 8MB would keep the intermediate data in memory and likely improve the query response time (although it may take substantially more than 8MB to do the sort entirely in memory, as data on disk is stored in a more compact format).

max_connections

max_connections sets exactly that: the maximum number of client connections allowed.

This is very important to some of the below parameters (particularly work_mem) because there are some memory resources that are or can be allocated on a per-client basis, so the maximum number of clients suggests the maximum possible memory use.

PostgreSQL on good hardware can support a few hundred connections. If you want to have thousands instead, you should consider using connection pooling software to reduce the connection overhead.

The default value is 100.

temp_buffers

The temporary buffers are used to store the temporary tables used in each session.

This parameter sets the maximum amount of memory for this task.

The default value is 8 MB.

maintenance_work_mem

Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY

Since only one of these operations can be executed at a time by a database session, and an installation normally doesn't have many of them running concurrently, it's safe to set this value significantly larger than work_mem.

Larger settings might improve performance for vacuuming and for restoring database dumps.

The default value is 64 MB.

autovacuum_work_mem

The vacuum uses the maintenance_work_mem by default, but we can separate it using this parameter.

We can specify the maximum amount of memory to be used by each autovacuum worker here.

wal_sync_method & wal_buffers

The amount of shared memory used for WAL data that has not yet been written to disk.

The default setting is 3% or 1/32 of shared_buffers, but not less than 64kB nor more than the size of one WAL segment, typically 16MB.

Larger settings might improve performance for vacuuming and for restoring database dumps.

The default value is 64 MB.