Re: archive_timeout?

Jeff Davis <pgsql@j-davis.com> writes:
> Maybe I just don't understand checkpoint timeout? Could it reasonably be
> set to something like 12 hours? I can't think why not, but the config
> default is 5 minutes, so I would be hesitant to change it by that much. 

The only constraining factor on it is how much WAL data are you willing
to replay in order to recover from a crash.  If you've got a low-volume
database then replaying up to 12 hours' worth of activity might not be
unacceptable.  Also, if you have spikes of activity, then
checkpoint_segments would kick in after a spike had generated X amount
of data.  So I don't see any strong reason why it couldn't be set much
higher than archive_timeout.

Now the other side of the coin is that if you do have a steady low level
of activity then a small archive_timeout is still going to result in
shipping lots of partially-filled WAL files.  Compression might help
some, but the bottom line is simply that archive_timeout isn't an
efficient mechanism for dealing with low-volume databases.
        regards, tom lane