Re: Feature Request --- was: PostgreSQL Performance Tuning

Michael Stone wrote:
> On Fri, Apr 27, 2007 at 09:27:49AM -0400, Carlos Moreno wrote:
>> Notice that the second part of my suggestion covers this --- have
>> additional
>> switches to initdb
<snip>
> If the person knows all that, why wouldn't they know to just change the
> config parameters?
>

Exactly.. What I think would be much more productive is to use the great amount
of information that PG tracks internally and auto-tune the parameters based on
it. For instance:

Why does the user need to manually track max_fsm_pages and max_fsm_relations? I
bet there are many users who have never taken the time to understand what this
means and wondering why performance still stinks after vacuuming their database
( spoken from my own experience )

How about work_mem? shared_buffers? column statistics sizes? random_page_cost?

Couldn't some fairly simple regression tests akin to a VACUUM process spot
potential problems? "Hey, it looks like you need more fsm_relations.. I bumped
that up automatically for you". Or "These indexes look bloated, shall I
automatically reindex them for you?"

I'm sure there are many more examples, that with some creative thinking, could
be auto-adjusted to match the usage patterns of the database. PG does an
excellent job of exposing the variables to the users, but mostly avoids telling
the user what to do or doing it for them. Instead, it is up to the user to know
where to look, what to look for, and how to react to things to improve
performance. This is not all bad, but it is assuming that all users are hackers
( which used to be true ), but certainly doesn't help when the average SQLServer
admin tries out Postgres and then is surprised at the things they are now
responsible for managing. PG is certainly *not* the only database to suffer
from this syndrome, I know..

I like to think of my systems as good employees. I don't want to have to
micromanage everything they do. I want to tell them "here's what I want done",
and assuming I made a good hiring choice, they will do it and take some liberty
to adjust parameters where needed to achieve the spirit of the goal, rather than
blindly do something inefficiently because I failed to explain to them the
absolute most efficient way to accomplish the task.

Granted, there are some people who don't like the developers making any
assumptions about their workload. But this doesn't have to be an either/or
proposition. I don't think any control needs to be abandoned. But
self-adjusting defaults seem like an achievable goal ( I know, I know, "show us
the patch" ). I just don't know if this feeling has resonated well between new
users and long-term developers. I know it must be grating to have to answer the
same questions over and over and over "have you analyzed? Did you leave
postgresql.conf at the defaults??". Seems like a win-win for both sides, IMHO.

In closing, I am not bashing PG! I love it and swear by it. These comments are
purely from an advocacy perspective. I'd love to see PG user base continue to grow.

My .02

-Dan