Since I proposed three postgresql.conf configuration files, I will start by
suggesting some settings different from the default: (Any additions or corrections
would be greatly appreciated.)
Compact:
The current postgresql.conf
Workstation:
tcpip_socket = true
max_connections = 32
shared_buffers = 1024
sort_mem = 8192
random_page_cost = 2
Server:
tcpip_socket = true
max_connections = 128
shared_buffers = 8192
sort_mem = 16384
random_page_cost = 1
The random_page_cost is changed because of an assumption that the bigger systems
will be more busy. The more busy a machine is doing I/O the lower the differential
between a sequential and random access. ("sequential" to the application is less
likely sequential to the physical disk.)
I'd like to open a debate about the benefit/cost of shared_buffers. The question
is: "Will postgres' management of shared buffers out perform O/S cache? Is there a
point of diminishing return on number of buffers? If so, what?
Sort memory makes a huge impact on queries. If you got the memory, use it.
These are just ballpark settings, I don't even know how good they are. The problem
is that server environments differ so greatly that there is no right answer. I am
just really concerned that the newbe PostgreSQL user will assume the performance
they see with the default settings are what they will judge PostgreSQL.