Change default value of checkpoint_segments and checkpoint_completion_target for better performance?

[odunybrad]

Thanks for providing this useful container.

I am doing a research on configuration and have a question about two postgre config: It seems you keep the default setting of checkpoint_segments to 5min and checkpoint_completion_target to 0.5.

However, The official document recommends to set checkpoint_segments to at least 10, which improves the performance: "Unless you're running on a very small configuration, you'll almost certainly be better setting this to at least 10, which also allows usefully increasing the completion target."

The document also recommends to set checkpoint_completion_target to 0.9, which is the more useful value and improves the performance: "You can spread those writes out further, lowering the average write overhead, by increasing the checkpoint_completion_target parameter to its useful maximum of 0.9"

Shall we change the default of checkpoint_segments to 10 and checkpoint_completion_target to 0.9 for better performance?
Thanks.

[natmaka]

This is heavily context-depend. Huge server apnea spikes during a commit (while the server writes a relatively huge amount of data, impeding the performance of currently running queries) may enhance global throughput in a batch-oriented context, especially on a dedicated server with spindles (rotating storage, the good ole' hard disk!) and on a huge database with many dispersed and each large updates/deletes, because it spares many disk-head seeks (which induce latency).

During checkpoint completion if PG needs to write a "large" amount of pages which are stored in a given set of disks cylinders far away, it is much better to do in one pass (a disk seek, write all, a seek back) than to spread it (a seek, write part of the dirty pages, a seek back, and a after some ten's of second a new seek, write part of the dirty pages, then seek back... and so on).

On a non-dedicated server if another process uses the storage all bets are off. On SSDs the problem is rather different. With many dispersed and each small updates/deletes this may be neglectable.