Jeff Janes <jeff.janes@gmail.com> wrote:
> archive_timeout and checkpoint_timeout have a pernicious
> interaction. Each one individually suppresses needless
> operations, i.e. not checkpointing if no WAL was written since
> last checkpoint, and not log-switching if no WAL was written since
> the last log-switch. But in combination, a checkpoint writes WAL
> that triggers a log-switch, and a log-switch writes WAL that
> triggers a checkpoint. So a server that is completely free of
> user activity will still generate an endless stream of WAL files,
> averaging one file per max(archive_timeout, checkpoint_timeout).
> That comes out to one 16MB file per hour (since it is not possible
> to set checkpoint_timeout > 1h) which seems a bit much when
> absolutely no user-data changes are occurring.
Tom actually had a patch to change that, but at the time it would
have left those of us using PITR backup techniques with no good way
to detect certain types of failures. We had a monitoring system
that looked at the last checkpoint time on the warm standby to
detect when it was "stale", and nobody could suggest a good
alternative at the time. So Tom backed off on that patch. Now that
there are alternatives, it may be time to take another look at that.
BTW, that's also why I wrote the pg_clearxlogtail utility (source
code on pgfoundry). We pipe our archives through that and gzip
which changes this to an endless stream of 16KB files. Those three
orders of magnitude can make all the difference. :-)
-Kevin