I have been thinking about postgresql.conf and I have made a few posts already
about it. My concern is that Postgres, as default, is not very well tuned. One
can even say the default is pretty much a bad configuration.
My idea of a postgresql.conf "cookbook" seemed not such a good idea, some liked
it, some did not, and the discussion degenerated into a discussion about the
values.
How about this: we have just just two or three default configuration files?
Compact, Workstation, and server.
"Compact" would be the current postgresql.conf.
"Workstation" would boost the number of buffers, sort and vacuum memory, in
essence Postgres would be configured to use about 64M~128M efficiently, maybe
limited to 16 backends. Say 4096 buffers, Sort mem setting of 4096.
"Server" would have a huge number of buffers, large numbers for sort, and a
boosted vacuum memory. A server would be assumed to have lots of memory and be
limited to 128 backends. Say 65536 buffers, Sort memory of 32768.
We could also tune some of the optimizer parameters. For instance, on "Server"
rand_page_cost would be lower because of concurrent disk operations. We could
even try to tune some of the wal settings accordingly.
I know these things are all documented, and shame on the dba for not reading
the documentation, but all the help we can give to someone new to PostgreSQL
makes it that much more likely that they will be able to use it successfully.
Besides, if we put out a small number of specific versions of postgresql.conf,
a more focused feedback about performence issues on optimization can be
obtained.