It is sort of discouraging that Postgres' default configuration is so
conservative. A tweek here and there can make a big difference. It seems to me
that the default postgresql.conf should not be used for a dedicated server. In
fact, it can't because TCP/IP is disabled.
In my projects I have done the default stuff, increase buffers, sort memory,
and so on, however, some of the tunable parameters seem a bit arcane and are
not completely clear what they do or the effect they may have. (some have no
noticable effect, eventhough it looks as if they should.) I think most users,
particularly those new to SQL databases in general, would find it difficult to
tune Postgres.
Does anyone think it is a good idea, to make a postgresql.conf cookbook sort of
thing? Gather a number of tuned config files, annotated as to why the settings
are set the way they are, and the machine on which they run.
Particularly, I'd like to see if someone has been able to really understand and
manipulate the planner COST options successfully.
Alternatively, it should be possible to write a program that analyzes a target
system, asks questions like: "Is this a dedicated server?" "How much ram do you
have?" "On which volume will the database be installed?" Then perform some
tests that mimic the cost values, and create a new postgresql.conf with the
options tuned.