I have a database that only receives updates maybe a few times a week.
It is the storage for some web content where the content is not
changed very often.
I set archive_timeout to 5 minutes, because when an update is made I
would like the log that contains that change to get archived fairly
soon, and I'm OK with having 16MB of log for each update, or sequence
of closely timed updates.
However, 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.
Other than running a streaming standby server, which is rather
excessive for such a simple use case, is there some way to avoid this?
Ideally I could just set checkpoint_timeout to 0 (meaning infinity)
but that is not an allowed setting.
(This is 9.1.4, but the same behavior applies to other versions,
including 9.2beta)
Thanks,
Jeff