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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
