Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Why isn't your customer using autovacuum? If there are concrete
> reasons why that doesn't get the job done for him, it would be
> more useful in the long run to work on fixing that.
FWIW, we're using autovacuum here, at slightly more aggressive
settings from the default, and we still rely on manual vacuums for
two main reasons:
(1) VACUUM FREEZE ANALYZE after a bulk load to avoid the hint bit
rewrite costs for end users and the unpredictable anti-wraparound
autovacuum when all the loaded data suddenly hits the freeze
threshold.
(2) For base backups of databases across a slow WAN, we do a "diff"
rsync against a copy of the hot standby here. (Well, actually, to
save space we do it against a hard-link copy of the previous base
backup against which we have run a "diff" rsync from the hot
standby.) If we don't do a VACUUM FREEZE ANALYZE before each base
backup, it at least doubles the size of base backups, due to the
hint bit and xmin freeze changes that occur after the initial copy
of a tuple is backed up.
Simon's recent work, if it makes it in, will deal with (1), and it
may be possible to deal with (2) using much more aggressive
configurations for autovacuum, although I suspect it might take
another tweak or two to the back end to really cover that without
manual vacuums.
-Kevin