Managing writes, connections, and maintenance

Now, we will look at a few other parameters that can impact performance:

The checkpoint_segments parameter was covered in Chapter 2, Server Architecture. When we increase shared_buffers, it might be necessary to increase this value too. In a busy system with a lot of changes being made to the data, a low value will result in frequent checkpoints. We can increase this number and spread out the process of writing a relatively larger volume of data at each checkpoint (resulting from fewer checkpoints) using checkpoint_completion_target. The default value of 3 is usually too low for any system with frequent writes.

We can limit the maximum number of connections allowed on the server using the max_connections parameter. This parameter by itself does not have an impact on performance. However, there is the work_mem parameter, which we covered earlier. If the maximum number of connections is kept high and work memory is also high, the memory requirements...