There are a few ways to combine all of this information into a process for tuning
new server. Which is the best is based on what else you expect the server to be
doing, along with what you're looking to adjust yourself versus taking rule of
thumb estimates for.
Initial server tuning can be turned into a fairly mechanical process:
shared_buffersto. Start at 25 percent of system
shared_buffersplus the OS cache.
max_connections, then by two. This gives you
maintenance_work_memto around 50 MB per GB of RAM.
checkpoint_segmentsto at least 10. If you have server-class
wal_buffersto 16 MB.
default_statistics_target(to 100, the modem default) and
max_fsm_pagesbased on what you know about the database workload.
work_memin particular can end up being very different from what's suggested here.
If your database server is sharing hardware with another use, particularly the common
situation where a database-driven application is installed on the same system, you
cannot be nearly as aggressive in your tuning as described in the last section. An exact
procedure is harder to outline. What you should try to do is use tuning values for the
memory-related values on the low side of recommended practice:
shared_buffersat first, even on
effective_cache_sizeto 50 percent or less of system RAM, perhaps
The other suggestions in the above section should still hold - using larger values for
checkpoint_segments and considering the appropriate choice of
for example, are no different on a shared system than on a dedicated one.
Then, simulate your application running with a full-sized workload, and then
measure available RAM to see if more might be suitable to allocate toward the
database. This may be an iterative process, and it certainly should be matched with
application-level benchmarking if possible. There's no sense in giving memory to
the database on a shared system if the application, or another layer of caching such
as at the connection pooler level, would use it more effectively. That same idea - get
reasonable starting settings and tune iteratively based on monitoring - works well
for a dedicated server, too.