I am running PostgreSQL 11 on EC2 instances that can be scaled up and down. Everytime an instance is scaled his memory specs can change. If in my configuration I have for example something like this:

effective_cache_size = 400GB

in case of big instances, if I scale the machine down of course that configuration memory won't work any more blocking the db instance from starting. So a better solution would be something like:

effective_cache_size = 80%

but that won't work. So apart writing lots of different file configurations, is there a way to have a sort of "dynamic" Postgres configuration?

  • An experimental dynamic allocator for PostgreSQL shared memory, but it's 3 years old and not moving .. – Luuk Jun 2 at 15:40
  • You could write a script that gets the memory size and calculates settings based on that and updates the configuration file before starting Postgres. – Colin 't Hart Jun 2 at 16:24
  • 1
    effective_cache_size can be changed without restart using alter system (at least on a "vanilla" Postgres installation). But that setting does not allocate any memory at all - it's just a hint to the optimizer. Over allocating it won't prevent Postgres from starting – a_horse_with_no_name Jun 3 at 0:06

Your Answer

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

Browse other questions tagged or ask your own question.