Re: archive_timeout?

On Tue, 2006-10-10 at 13:12 -0400, Tom Lane wrote:
> Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> > There should be a documentation note to let people know that the archive
> > will grow even when idle. Perhaps we should suggest compression in the
> > docs so that people don't get worried about many gigabytes of mostly-
> > empty files filling up their backup storage.
>
> Actually, per the previous discussion: if you want to reduce WAL traffic
> then one of the most important things to do is stretch out
> checkpoint_timeout.
>

I assume you refer to this message:
http://archives.postgresql.org/pgsql-hackers/2006-08/msg01190.php

I understand that stretching the checkpoint timeout is useful if you
have steady traffic and want to reduce the WAL volume. Higher checkpoint
intervals mean fewer copies of data pages (at least before 8.2), and
probably other data necessary at checkpoint.

However, if you have a database with long idle times, higher checkpoint
intervals combined with archive_timeout can still waste a lot of data
(unless you stretch out the checkpoint timeout by orders of magnitude).
This situation is also most the most useful situation for
archive_timeout. If someone is concerned about idle time eating up
gigabytes of backup storage, compression seems like a logical choice.

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.

Regards,
Jeff Davis