This forum post is a summary of this blog post—make sure to check it out for the complete story.
If you want to make the most of your Timescale databases, there are a few key parameters that you should be aware of and tune:
- timescaledb.max_background_workers
You should configure this setting to be equal to the sum of your total number of databases + the total number of concurrent background workers you want running at any given point in time.
Note that there is a limit of 1000 timescaledb.max_background_workers on Timescale Cloud. If you have significantly more than 1000 jobs running simultaneously, we advise you to distribute the execution of your jobs.
- max_parallel_workers
By default, the max_parallel_workers setting corresponds to the number of CPUs available.
For larger queries, PostgreSQL automatically uses parallel workers if they are available. Increasing max_parallel_workers can improve query performance for large queries that trigger the use of parallel workers.
- max_worker_processes
max_worker_processes defines the total pool of workers available to both background and parallel workers, as well as a small number of built-in PostgreSQL workers.
max_worker_processes should be AT LEAST 3 (required for checkpointer, WAL writer, and vacuum processes) plus the sum of the background workers and parallel workers.
- work_mem
work_mem defines the amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files. Sort operations are used by ORDER BY, DISTINCT, and merge joins.
The default value for this setting is four megabytes (4MB). If several operations (sort or hash) are running concurrently, then each operation will be allowed to use up to work_mem of memory. So, the total memory used could be many times the value of work_mem—this situation should be considered when specifying this setting.
If work_mem is properly tuned, major sort operations are much faster than writing and reading to disk files. If you set work_mem higher than recommended, it can badly affect the overall available system memory, which can lead to out of memory (OOM) events.
To avoid these issues, we recommend setting work_mem as = (25% of RAM) / max_connections.
- maintenance_work_mem
Setting maintenance_work_mem to less than recommended may lead to a decreased performance of vacuuming and database dump/restore operations. For example, restoring the database using pg_restore will usually involve creating some indexes, so it might be worthwhile to increase maintenance_work_mem while working with pg_restore.
The default value for maintenance_work_mem is 64MB. Our recommendation is to set it up to a higher value than work_mem in order to improve performance for VACUUM operations and database dump/restore. A good guideline: maintenance_work_mem = 0.05 * total RAM.
- shared_buffer
shared_buffer defines the amount of memory the database server uses for shared memory buffers. The default shared_buffer is typically 128 megabytes (128MB), which can be quite low for any typical system.
A reasonable amount to start with is 25% of RAM for shared_buffer.
Consider that PostgreSQL requires free memory for system buffers, sort, and maintenance operations, so it is not advisable to set shared_buffers to a majority of RAM. Nonetheless, shared_buffer can be set to higher than recommended if your use case is mostly READ operations. On the contrary, setting shared_buffer higher than recommended will decrease performance if your use case is primarily WRITE operations, as all the contents from shared_buffer must be flushed during write operations.
- max_locks_per_transaction
If the error message that appears in the logs looks like ERROR: out of shared memory, you might need to increase max_locks_per_transaction.
The above error can cause queries to fail, as the database can’t grant any more locks. A good check, once you know you’re hitting this, is to see if you’re opening an unbounded number of transactions and never closing them, or maybe they are long-running and touching a lot more objects (eg. tables, indexes, etc). But, if your application really needs a lot more locks, you can tune the parameter max_locks_per_transaction. The default value stands at 64—you might need to raise this value.
If you have any questions, don’t hesitate to ask! Also, check out this blog post if you want more context. 