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

> 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