We have a postgres 12 environment. Which has 130GB of RAM and 32 CPUs, have been monitoring the memory usage of the server in recent months and it uses around half of the memory on the server, I understand that making shared_buffers changes involves a restart but if effective_cache_size is changed do I need to do shared_buffers at same time (know dont need to restart for effective_cache_size just reload).

The server is a heavily transactional system.

Some of the important postgres configuration:

listen_addresses = '*'
max_connections = 1000
effective_cache_size = 60GB
shared_buffers = 25GB
temp_buffers = 32MB
max_prepared_transactions = 1000
work_mem = 256MB
maintenance_work_mem = 1GB

effective_io_concurrency = 200
random_page_cost = 1.1

max_worker_processes = 14
max_parallel_workers_per_gather = 7
max_parallel_workers = 14
max_parallel_maintenance_workers = 7

wal_level = logical
wal_buffers = 1MB
checkpoint_timeout = 5min
checkpoint_completion_target = 0.9
max_wal_senders = 6
max_wal_size = 4GB
min_wal_size = 256MB
wal_keep_segments = 400
wal_sender_timeout = 5min
wal_receiver_timeout = 5min
max_replication_slots = 6 

I also have a replica slave server which has slightly less resources would this make any difference?

I have seen that the recommendation for shared_buffers is half or more of the server.

Apart from shared_buffers being altered to a higher value, the server doesnt use more memory (60GB out of 130GB and Swap at 5GB/14GB)

Any help is much appreciated.

  • 1
    "The server is a heavily transactional system.' - How heavy?...i.e. how many rows are being written per minute (or whatever time unit you find suitable)? How big is the database itself?
    – J.D.
    Sep 14, 2022 at 21:12
  • 2
    effective_cache_size does not "allocate" any memory. It's merely a hint to the optimizer how likely some block will be in the (file system) cache.
    – user1822
    Sep 14, 2022 at 21:16
  • "have been monitoring the memory usage of the server" How? Does this monitoring assess filesystem cache usage?
    – jjanes
    Sep 14, 2022 at 21:59
  • How many queries are running at any given moment? How many parallel workers at any given moment?
    – jjanes
    Sep 14, 2022 at 22:01
  • If you have 32 real CPUs (not "virtual" CPUs), then 14 would seem too low for max_*workers. But that doesn't matter unless your queries are such that they would actually benefit from parallel workers.
    – jjanes
    Sep 14, 2022 at 22:06

1 Answer 1


I'll go through your settings and point out where I think there is room for improvement. A assume you have local SSD storage or similar.

  • max_connections = 1000

    Not a good idea with 32 cores; the default 100 is ample. Use a connection pool and limit its size to something slightly above 32 - read this for more details.

  • max_prepared_transactions = 1000

    Only if you know you need prepared transactions, and certainly not 1000. Prepared transactions are a health risk for your database.

  • work_mem = 256MB

    Very high for transactional workload, but that won't do any harm.

  • effective_io_concurrency = 200
    random_page_cost = 1.1

    Local SSD, right?

  • max_worker_processes = 14
    max_parallel_workers_per_gather = 7
    max_parallel_workers = 14
    max_parallel_maintenance_workers = 7

    With a transactional workload you normally want to optimize throughput, and parallel query is bad for that. It makes queries faster at the expense of extra resources. Do you want one accidental big query to hog 8 of your 32 cores, when you need them for the transactional workload? No.

    Set max_parallel_workers_per_gather to 0 and leave max_parallel_maintenance_workers to something more moderate like the default 2.

  • wal_buffers = 1MB

    It might not matter with your workload, but there is no benefit in setting it so small. Leave it at its default value -1.

  • checkpoint_timeout = 5min
    max_wal_size = 4GB

    Now here is the biggest potential for tuning a write-heavy workload. Fewer checkpoints mean fewer writes. Set max_wal_size large (dozens of GB is just fine) and crank up checkpoint_timeout to half an hour or more.

    The only price you are paying is a little more disk space and long recovery time in the unlikely event of a crash.

  • wal_keep_segments = 400

    Replication slots are more effective.

One additional parameter you might contemplate is setting synchronous_commit = off. If you can afford losing half a second of committed transactions in the unlikely event of a crash, that is an easy way to boost performance.

  • thanks on the above. I have a master and slave setup with the master being significantly more in terms of CPUs and memory. I read that setting the max_worker stuff has to be the same on the slave and with the slave only having half of the amount of cores thought that was necessary.
    – rdbmsNoob
    Sep 15, 2022 at 7:27
  • Do you have any recommendation on a connection pooler, I have seen pgBouncer and pgPool but our application hits 2 different databases mainly and we experience alot of idles in transactions at the moment because the session hangs on one of the databases due to a large query and doesnt return. We also use prepared queries as well (again thought it was wise to set it to the same amount of max_connections). We have multiple app servers hitting the DB master server. The server mainly is under pressure on big SELECTs and the parallel workers get used for some of these.
    – rdbmsNoob
    Sep 15, 2022 at 7:28
  • You can certainly set max_parallel_workers_per_gather different on primary and standby. Your application should have a built-in connection pool, but if that does not work, use pgBouncer. You will never have a chance of good performance with long idle in transaction. Fix your application! Sep 15, 2022 at 8:02
  • thanks, I can add the idle_in_transaction_timeout so the queries would get cancelled but not the answer. SELECTs looks to be the ones hardest hitting. Was looking at more the servers memory usage for postgres being low (using only half) and thought the system would perform better if it was using more (not all for obvious reasons). We do have replication slots in use as well. Id say the system is under more stress on reads as opposed to writes. Looked online tuning websites (pgTune/Cybertec but their configs looked vastly different to what i have in place.
    – rdbmsNoob
    Sep 15, 2022 at 8:21
  • @rdbmsNoob "The server mainly is under pressure on big SELECTs" - You likely have a software issue then, more than a hardware / configuration issue. You should find out why those SELECTs are taking long (EXPLAIN ANALYZE is your friend) and fix them. Also your database doesn't sound as transactional as I initially was thinking (less than 100 DML transactions per minute). Any way to roughly quantify the amount (in GBs) of data being changed over that hour timeframe?...that would be relevant to Lauren's suggestion on tuning the max_wal_size.
    – J.D.
    Sep 15, 2022 at 12:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.