Re: Potential performance issues

On Mon, Mar 1, 2021 at 8:44 AM Bob Jolliffe <bobjolliffe(at)gmail(dot)com> 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.