PostgreSQL Performance Tuning: Key Parameters

Authors: Ben Packer and Carlota Soto

When your database tables grow and performance starts to suffer, it's time for some PostgreSQL performance database fine-tuning.

We already discussed CPU and memory sizing in the first part of this series, including tips on monitoring the best size for your use case. In part II, we’ll dig deep into the key PostgreSQL parameters you can control to optimize your database performance—complete with configuration recommendations.

Let’s get started!

Kickstarting Your PostgreSQL Performance Tuning: max_worker_processes

The max_worker_processes parameter determines the maximum number of worker processes that PostgreSQL can start. This encompasses not only query parallelism but other auxiliary processes as well, like logical replication and background tasks. Setting this parameter appropriately will help you ensure enough processes are available for these tasks without overwhelming your system.

A good general recommendation for vanilla PostgreSQL is to set max_worker_processes so it matches the total number of cores or CPUs available. However, depending on your workload, it may be advisable to use a higher number. For example, if you’re using Timescale (which uses parallelism heavily), we recommend this formula for setting up max_worker_processes:

This leaves room for processes like the checkpointer, write-ahead log (WAL) writer, vacuum processes, background workers, etc. Your max_worker_processes setting may end up as something around 4x your available CPU.

We will discuss Timescale-specific parameters like timescaledb.max_background_workers in a following article.

max_parallel_workers

This parameter specifies the maximum number of parallel workers that are available to support parallel queries specifically. Parallel query execution can significantly speed up processing for certain types of operations, like sequential scans, joins, and aggregates. This parameter ensures that parallelism is used effectively without exhausting your resources.

Similarly as before, a good default is to set max_parallel_workers equal to the number of CPUs available. This approach often aligns well with typical workloads, considering the inherent parallel processing capabilities of modern multicore CPUs.

However, depending on the specific nature of your queries and workload, you could still play around with it. For example, if you have a machine with eight cores but most queries don't benefit from parallelism, allocating all eight as parallel workers might be overkill. On the contrary, increasing max_parallel_workers could improve query performance for large queries that can effectively leverage parallelism.

If you’re raising max_parallel_workers over the recommended value, do it progressively and carefully, as increasing it too aggressively could lead to resource contention or increased wait times.

max_parallel_workers_per_gather

This parameter controls the number of parallel workers that a single query execution node can use. The default value is two, but somewhere around a quarter to a half of your CPU count is a good option. In Timescale, we start at two and progressively increase it as your CPU increases to match half your CPU.

Similarly to max_paralell_workers, if you have CPU-heavy queries that benefit from parallelism, setting max_parallel_workers_per_gather higher may make those queries run faster—this great blog post from PG Mustard has an in-depth consideration of the caveats.

Consider that allowing a single query to use a large number of cores could cause there not to be enough CPU to answer your smaller queries quickly. If you would like to ensure your application traffic is prioritized and can handle beefier queries taking longer, you should keep max_paralell_workers_per_gather low or consider disabling parallelism entirely.

max_parallel_maintenance_workers

max_parallel_maintenance_workers  controls the number of parallel workers available for maintenance operations in PostgreSQL. The default value is also two.

This setting influences maintenance tasks like CREATE INDEX and certain VACUUM operations. If these sorts of tasks seem slow and there's available CPU headroom, increasing max_parallel_maintenance_workers over the recommended value might help, although it’s not recommended to raise it over a third of CPU to ensure you’re leaving resources available for regular query processing.

Something crucial to understand, though, is that max_parallel_maintenance_workers doesn’t affect the autovacuum process. While it affects manual vacuum operations, the autovacuum system has its own separate settings, so if this is the process you’d like to optimize, you may want to fine-tune autovacuum_max_workers instead. Let’s cover it next.

autovacuum_max_workers

Once your PostgreSQL database is growing (and especially if you have a high-write workload), it becomes highly advisable to maintain the health of your tables and reclaim storage space by removing dead rows, preventing unnecessary bloating, and maintaining optimal performance. This is where the autovacuum process comes in.

The autovacuum_max_workers parameter determines the maximum number of autovacuum processes that can run concurrently, with each worker typically running on its designated core (although if there are several autovacuum workers active simultaneously, they can collectively make use of multiple cores). The default value is three.

This value will work well for many workloads, but if your database is growing quite large, you may want to increase it. However, ensure you’re doing so according to your CPU and I/O resources since the higher you set autovacuum_max_workers parameter, the more resources autovacuum will consume. In Timescale, we set it to 10 by default.

It’s worth developing a deeper understanding of bloat and vacuuming when scaling PostgreSQL, as further optimization in this area can help you keep your large databases performing well (and your costs under control). Stay tuned for more articles on this topic.

max_connections

max_connections determines the maximum number of concurrent connections allowed to the database server. This includes all connections, whether from superusers, applications, background processes, or interactive users. A common default value for max_connections in PostgreSQL is 100. In Timescale particularly, max_connections is set at 25 in the smallest compute plan and rises to 500 as the number of CPUs grows.

If your database needs many short-lived connections, the default max_connections value might be insufficient. But instead of raising it, a better solution could be using a connection pooler, which eliminates the overhead associated with opening and closing many short-lived connections on the database side. In Timescale, we offer a connection pooler based on pgBouncer.

If you decide to raise max_connections, consider that each connection in PostgreSQL typically requires additional memory, approximately in the range of 5-10 MB. If you decide to increase max_connections, ensure your system has sufficient memory to handle the additional demand.

shared_buffers

We already talked about shared_buffers in part I of this series. As we discussed, this parameter controls how much memory PostgreSQL will use for its page cache.

As a common rule of thumb, you'll want to set shared_buffers at around 25 % of the system’s total RAM:

This is a good starting point, but it is not completely set in stone. This 25 % ratio can be varied based on specific use cases and workloads. For example, if you mostly have heavy reads, you may see better performance if you increase shared_buffer over the recommended 25 %, but do it cautiously. Raising it to over 40 % of RAM is generally not recommended.

There’s also a (complex) relationship between the PostgreSQL cache and the operating system cache. As we also mentioned in part I, PostgreSQL maintains its own cache, defined by shared_buffers, but the operating system also has a file system cache that retains files read from the disk. Both caches coexist and serve the purpose of speeding up data retrieval.

There's a nuanced relationship between the PostgreSQL cache and the OS cache. When data is read or written by PostgreSQL, it first interacts with shared_buffers. When data isn't present in these buffers or when they get flushed, the OS cache comes into play.

If shared_buffers is set too low, PostgreSQL will rely more on the OS cache, which may not be as efficient performance-wise; if shared_buffers is set too high, you may see an out-of-memory error in PostgreSQL and even affect the stability of the host machine. That’s why it’s usually recommended to start with a reasonably low value of shared_buffer (a.k.a. 25 % of RAM) and raise it carefully from there.

work_mem

We also mentioned work_mem in part I. This parameter controls the amount of memory allocated for each operation within a query, such as sorting or hashing. If you have heavy queries that are not performing as expected, it may be time to fine-tune work_mem. This may be especially relevant if you are using PostgreSQL for analytics, where complex operations often demand a larger memory footprint.

In Timescale, this is our general recommendation for work_mem:

Also, if you’re a heavy user of aggregates, check out Timescale’s continuous aggregates, which will help you execute analytical queries much more efficiently.

A handy tool in your arsenal for understanding this setting is the EXPLAIN ANALYZE command, as it provides information about how much data was written to disk. For example, consider the following query:

EXPLAIN ANALYZE SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id ORDER BY orders.order_date;

With this output:

...
Hash Join  (cost=... rows=... width=...) (actual time=... rows=... loops=1)
Hash Cond: (orders.customer_id = customers.id)
...
->  Seq Scan on orders  (cost=... rows=... width=...) (actual time=... rows=... loops=1)
...
->  Hash  (cost=... rows=... width=...) (actual time=... rows=... loops=1)
Buckets: 4096  Batches: 4  Memory Usage: 40kB
...
Sort Method: external merge  Disk: 7208kB
...
Copy

The Sort Method indicates an external merge was used with 7208 kB of disk, suggesting that the data being sorted was too large for the work_mem setting. The Hash operation used four batches, suggesting it also spilled to disk.

Three last considerations:

• If several operations are running concurrently, then each operation will be allowed to use up to work_mem of memory—remember that the total memory used could be many times the value of work_mem, since this is a per-operation setting.

• Be careful not to set work_mem too high, or you’ll see out-of-memory errors. We’ll get back to this at the end of this article.

• Just like max_parallel_workers_per_gather instead of making work_mem too big, consider that it may be the case that it's a good trade-off for some queries to run slower using disk to preserve more memory for other uses. Or at least make sure that you cannot optimize those queries in other ways before increasing work_mem.

maintenance_work_mem

maintenance_work_mem is another important parameter, as it determines the amount of memory allocated for PostgreSQL maintenance operations such as VACUUM, CREATE INDEX, and ALTER TABLE, as well as database restoration activities using tools like pg_restore. Insufficient memory can slow down these maintenance tasks, potentially impacting the overall performance of your database. If your maintenance operations are taking too long to execute, this is a hint that mainenance_work_mem needs fine-tuning.

maintenance_work_mem is 64 MB by default. In Timescale, we recommend setting it up to a value higher than work_mem since operations like vacuum or dump/restore usually require more memory and are less frequent than regular query operations. As a general rule of thumb, you can set maintenance_work_mem to about 5 % of your total system RAM:

When configuring this setting, it's crucial to account for multiple maintenance operations running simultaneously. For instance, if you have several VACUUM operations occurring concurrently during peak times, ensure your system has enough memory to handle maintenance_work_mem for all these concurrent operations while noting the memory allocated for shared_buffers and work_mem. If you're too aggressive with your setting, you might encounter out-of-memory errors.

One last note on pg_restore:  you might temporarily want to boost the maintenance_work_mem for the duration of the restoration for better performance. But remember to revert to the normal value once the restoration is done.

max_locks_per_transaction

Lastly, let’s cover max_locks_per_transation. This parameter determines the maximum number of locks that a single transaction can hold. In PostgreSQL, locks are used to control access to database resources and ensure data consistency; max_locks_per_transaction ensures that the system has been pre-allocated enough memory to handle these locks.

By default, max_locks_per_transaction is set to 64 in PostgreSQL. However, this might not be sufficient for databases with complex transactions or when using extensions or features that use a higher number of locks (e.g., PostGIS or Timescale). You can inspect the current lock usage during peak load or complex operations via pg_locks to get an idea of the current lock count.

In Timescale, our recommendation is to set max_locks_per_transaction slightly higher to double the maximum number of partitions (chunks) you can reasonably anticipate having in a hypertable. This accounts for the fact that Timescale typically uses at least as many locks as there are chunks in the hypertable or twice as many if an index is used.

Note on ERROR: out of shared memory

Some of these key parameters are memory-related. When misconfigured, you may see ERROR: out of shared memory.  Let’s run through a brief checklist referencing the most common misconfiguration reasons why you might see an out-of-memory error:

• As mentioned, every transaction that modifies data or schema requires locks. One of the most frequent reasons why you may see out-of-memory errors is because max_locks_per_transaction is set too low. Use pg_locks during peak load to inspect your lock count.

• If you suspect parallelism is the issue, you can look at max_parallel_workers. If there's a high degree of parallelism in your queries, this may lead to exhaustion of dynamic shared memory—you may want to consider reducing max_parallel_workers to the number of CPUs minus one or two. You could also consider reducing max_parallel_workers_per_gather to limit parallelism for individual queries or even raising shared_buffers—but not too much, or you’ll also exhaust your memory.

• To emphasize shared_buffers: as we said previously, you may also see out-of-memory errors if you set this parameter too high. Remember the recommendation of not raising it over 40 % of your total system RAM, especially if PostgreSQL is not the only application running on your machine.

• Another common reason for out-of-memory errors is max_connections. When max_connections is set too high, and your PostgreSQL instance experiences a surge of simultaneous connections, it can exhaust the system's shared memory. The approach for fixing this depends on whether your application does indeed require a high number of connections. If it does, consider a connection pooler. If it doesn’t, check for open connections that remain idle.

Fine-Tune PostgreSQL Parameters Easily via the Timescale UI

Before we wrap up, a quick reminder: when creating a database in Timescale, the configuration values will be under the best practices given the service’s compute and storage settings (and will change when you upgrade your instance), but when it’s time to fine-tune them, you can easily do so by navigating to Operations > Database configuration:

You can easily modify key PostgreSQL parameters via the “database configuration” screen in Timescale UI 

Let’s Keep Going

In the first two articles of the series, we’ve discussed database sizing and key parameters to monitor and fine-tune. In the next article, we’ll tackle a crucial aspect of PostgreSQL configuration: indexes.

Optimizing your indexes can work wonders on your PostgreSQL performance, especially once your tables get large. Let's do it!