PostgreSQL - tuning multiple clusters on server

Question

I usually use http://pgtune.leopard.in.ua/ for tuning postgresql parameters depending on hardware resources.

But now I want to tune two clusters on same server.

How should I tune PostgreSQL configuration in this case?

For the sake of simplicity let say both clusters would be under similar load.

Now with this setup:

DB Version: 10
OS Type: Linux
DB Type: Online transaction processing systems
RAM: 2 GB
Number of CPUs: 2
Data Storage: SSD 

pgtune.leopard recommends this configuration:

max_connections = 300
shared_buffers = 512MB
effective_cache_size = 1536MB
maintenance_work_mem = 128MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 1747kB
min_wal_size = 2GB
max_wal_size = 4GB
max_worker_processes = 2
max_parallel_workers_per_gather = 1
max_parallel_workers = 2

So do I need to "slice" parameters in half or generally just use suggested configuration for each cluster? Or maybe it is recommended to use some different strategy here?

Answer 1

You should probably cut max_connections by a lot. I don't know why it would recommend such a high setting in the first place. I'd also cut shared_buffers down to the default (128MB) or maybe lower, as PostgreSQL runs on top of the OS file cache and with two separate instances it would probably be best to let the OS decide how to use the available memory, both between instances and for other uses.

I'd leave effective_cache_size and effective_io_concurrency alone. Those are short term issues, and postgresql makes no effort to partition them between connections even within an instance. It is possible that two connections on separate instances want to make use of those "facilities" at the exact same time and interfere with each other, but it is also possible that two connections on the same instance would. So the mere fact that they are being shared is not a reason to lower them. These setting don't do much for transaction processing anyway, they are mostly important for large analytics-type queries.