4

I think it's fairly well known that a normal PostgreSQL database has a starting value of 25% of system memory for its shared_buffers parameter, but I haven't been able to find anything regarding a proper value for an AWS Aurora instance.

Now I would normally use the community value for this parameter, but the existing system is already using a value of 50% of the available memory, and I'm just not certain that this is a good value.

Of course, for Aurora the shared_buffers parameter is unique for each database in the system, as opposed to normal PostgreSQL where this is cluster-wide.

Hence, my confusion. Can anyone provide guidance in this manner?

Improve this question
4

Standard PostgreSQL runs on a file system and depends on the file system cache in addition to the PostgreSQL buffer cache. Aurora PostgreSQL runs on top of Aurora Storage, which is not a file system and doesn't have a separate cache on the instance. So, with Aurora PostgreSQL we set the default for shared_buffers to 75% of the total memory on the instance. We recommend you look at your specific workload requirements to determine memory requirements for caching your working set, for managing the connections needed, and for other PostgreSQL memory requirements.

Full Disclosure: I am the Product Manager for Aurora PostgreSQL.

-Kevin Jernigan

Improve this answer
2
  • Thanks for this. Quick question if I may. What tool would you use to help examine your workload? Is it performance insights? I have a DB with 350 million rows that I'm going to be creating. It is a test to see what breaks for our current wildfly 20 / hibernate / Aurora PostreSQL server instance when we push the numbers that high. Currently we handle 40 million so this will be an interesting test. :) – D-K Jan 31 at 19:36
  • 1
    For caching, it's important to figure out how much data will you need to keep in the PostgreSQL buffer cache to ensure good performance for reads and for writes. This will include both the data itself, plus indexes on that data (to ensure repeated reads are fast). When I work with customers on this question, we usually do calculations based on the number of concurrent users / sessions, the expected transaction rate per session, and the amount of data accessed and/or modified per transaction. This approach will give you an estimated lower bound on the cache size needed. – Kevin Jernigan Feb 3 at 16:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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