Hello Noah,
> The usual PostgreSQL handling of a deeply workload-dependent performance
> feature is to disable it by default. That's what I'm inclined to do here, for
> every GUC the feature added. Sophisticated users will nonetheless fully
> exploit this valuable mechanism in 9.6.
>> I don't think checkpoint_flush_after is in that class, due to the
>> fsync()s we already emit at the end of checkpoints.
I agree with Andres that checkpoint_flush_after shoud not be treated as
other _flush_after settings.
> That's a promising hypothesis.
This is not an hypothesis but a proven fact. There has been hundreds of
hours of pgbenchs runs to test and demonstrate the positive impact in
various reasonable configurations.
> Some future project could impose a nonzero default
> checkpoint_flush_after, having demonstrated that it imposes negligible
> harm in the plausible cases it does not help.
I think that the significant and general benefit of checkpoint_flush_after
has been largely demonstrated and reported on the hacker thread at various
point of the development of the feature, and that it is safe, and even
highly advisable to keep it on by default.
The key point is that it is flushing sorted buffers so that it mostly
results in sequential writes. It avoids in a lot of case where the final
sync at the end of the checkpoint generates too many ios which results in
putting postgresql off line till the fsync is completed, from seconds to
minutes at a time.
The other *_flush_after do not benefit for any buffer reordering, so their
positive impact is maybe more questionnable, so I would be okay if these
are disabled by default.
--
Fabien.