On Mon, Mar 1, 2021 at 8:44 AM Bob Jolliffe <bobjolliffe@xxxxxxxxx> wrote:
Was just about to reply similarly. Mind you it perhaps does raise the
question : are the default postgresql settings perhaps too
conservative or too static. For example, in the absence of other
explicit configuration, might it make more sense for many use cases
for postgres to assess the physical memory available and make some
half-sensible allocations based on that? I know there are downsides
to assuming that postgresql has free reign to all that it sees, but
there are clearly also some downsides in assuming it has next to
nothing. This could also be more correctly part of a package
installation procedure, but just floating the idea ... some kind of
auto-tuning vs ultra-conservative defaults.
When you spin up an Aurora or RDS instance in AWS, their default parameter group values are mostly set by formulas which derive values based on the instance size. Of course they can assume free reign of the entire system, but the values they choose are still somewhat interesting.
For example, they set `maintenance_work_mem` like this: "GREATEST({DBInstanceClassMemory/63963136*1024},65536)"
It doesn't completely remove the need for a human to optimize the parameter group based on your use case, but it does seem to give you a better novice starting point to work from. And there are definitely some formulas that I disagree with in the general case. However it is something that is adaptable for those times when you bump up the server size, but don't want to have to revisit and update every parameter to support the change.
I've been thinking a lot about running PG in containers for dev environments lately, and trying to tune to get reasonable dev performance out of a container without crushing the other services and containers on the laptop. Most developers that I've worked with over the past few years only have exposure to running PG in a container. They've simply never run it on a server or even barebones on their laptop. I think any modern approach to a default set of tuning parameters would probably also need to be "container aware", which is for all practical purposes the new default "minimal configuration" on multi-purpose systems.
