Re: Change default of checkpoint_completion_target

Greetings,

* Peter Eisentraut (peter(dot)eisentraut(at)enterprisedb(dot)com) wrote:
> On 2020-12-07 18:53, Stephen Frost wrote:
> >* Michael Paquier (michael(at)paquier(dot)xyz) wrote:
> >>On Sun, Dec 06, 2020 at 10:03:08AM -0500, Stephen Frost wrote:
> >>>* Alvaro Herrera (alvherre(at)alvh(dot)no-ip(dot)org) wrote:
> >>>>You keep making this statement, and I don't necessarily disagree, but if
> >>>>that is the case, please explain why don't we have
> >>>>checkpoint_completion_target set to 0.9 by default? Should we change
> >>>>that?
> >>>
> >>>Yes, I do think we should change that..
> >>
> >>Agreed. FWIW, no idea for others, but it is one of those parameters I
> >>keep telling to update after a default installation.
> >
> >Concretely, attached is a patch which changes the default and updates
> >the documentation accordingly.
>
> I agree with considering this change, but I wonder why the value 0.9. Why
> not, say, 0.95, 0.99, or 1.0?

The documentation (which my patch updates to match the new default)
covers this pretty well here:

https://www.postgresql.org/docs/current/wal-configuration.html

"Although checkpoint_completion_target can be set as high as 1.0, it is
best to keep it less than that (perhaps 0.9 at most) since checkpoints
include some other activities besides writing dirty buffers. A setting
of 1.0 is quite likely to result in checkpoints not being completed on
time, which would result in performance loss due to unexpected variation
in the number of WAL segments needed."

Thanks,

Stephen