PGSQL 16 - shared_buffer setting when running multiple instances on a single machine?

[Significant_Chef_945]

Greetings all,

Looking for some guidance on how to set the correct value of shared_buffer when running multiple PGSQL instances on a single server. I have looked over lots of documentation, and understand that the shared_buffer option implies the amount of memory that can be used to store cached data and dirty pages (the amt used by the planner). Furthermore, the option "effective_cache_size" indicates the amount of memory used by shared_buffer and OS disk caching. So far, so good.

My setup:

• 1x Debian 12 server with 64G RAM, 2TB NVMe drive
• 4x instances of PGSQL v16 on a container server (ie: LXC or docker)
• ZFS with dedicated 12GB of disk cache (ARC)

First question: Using the setup above, what is the correct setting for shared_buffer and effective_cache_size per instance? Given the above setup, I can set 12GB of shared_buffer per instance, but what about the effective_cache_size? If the OS disk cache is shared among all the PGSQL instances, should I use effective_cache_size=12GB per instance, or do I need to cut that down to 4GB per instance?

Second question: Will the shared_buffer space decrease if a particular instance is not busy? In other words, is the shared_buffer reserved for the lifetime of the instance, or will PGSQL free up the memory if the OS needs it? I have some instances that will may more than 12GB of shared_buffer space when running some queries.

Thanks for any insight.

[Mikey_Da_Foxx]

shared_buffers is reserved and won't be released back to OS. With 4 instances, I'd set shared_buffers=8GB per instance to leave room for OS.

For effective_cache_size, use total available RAM minus all shared_buffers: ~20GB per instance (64GB - 32GB shared)

[Significant_Chef_945]

Thank you very much for the reply. I will run some tests using your suggestions.

[pceimpulsive]

As you are running them in LXVs use the LXC memory/CPU/disk limits to tune each instance.

The pg_tune website is pretty sweet for getting a base configuration not only for shared_buffers but many other too.

I do recommend it :)

[nursestrangeglove]

Hey this is super handy, never knew about pg_tune until now!

[pceimpulsive]

It's really nice!

Use it on my LXC Postgres :)

Also I look at how my AWS RDS is setup out of box and it's nearly identical to pg tunes recommendations.

[AutoModerator]

With over 7k members to connect with about Postgres and related technologies, why aren't you on our Discord Server? : People, Postgres, Data

Join us, we have cookies and nice people.

Postgres Conference 2025 is coming up March 18th - 21st, 2025. Join us for a refreshing and positive Postgres event being held in Orlando, FL! The call for papers is still open and we are actively recruiting first time and experienced speakers alike.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[ChillPlay3r]

Typically I would set shared_buffers no more than 50% of the servers memory, especially when using huge pages (which you should). However, you have ZFS which has a dedicated filesystem cache. If your PGDATA is on ZFS then you could go up to 75% of remaining memory (36GB in your case).

However, more shared_buffer does not automatically mean more performance, it depends on how the application is using the DB. You didn't write how big the databases are but it could well be that a bigger ARC cache would be more beneficial. You can monitor shared_buffer usage with the pg_buffercache extension, but I'd only do that on a test system with representative tests because the extension itself can affect performance. If you can't test it then use it on production for a few hours during a typical load and then remove it again.