Re: Vacuum, visibility maps and SKIP_PAGES_THRESHOLD

On Fri, May 27, 2011 at 11:10 AM, Greg Stark <gsstark(at)mit(dot)edu> wrote:
> It would be nice if the VM had a bit for "all-frozen" but that
> wouldn't help much except in the case of truly cold data. We could
> perhaps keep the frozen data per segment or per VM page (which covers
> a large section of the table) which would at least mean that would
> have a fixed amount of data become vacuum-dirty when a tuple is
> updated rather than a whole table which could be arbitrarily large.

Instead of just having one bit, it might be useful to have a
relfrozenxid counter for each, say, 64MB chunk, rather than just one
for the whole table. At least in theory, that would give us the
possibility of freezing only portions of the table that were most
urgently in need of it. I'm not sure how exactly what algorithm we'd
want to apply, though.

In general, ISTM that the problem with VACUUM is that we don't know
whether we're "keeping up" or "getting behind". For
checkpoint_completion_target, we measure how fast we're writing pages
relative to when the checkpoint needs to be done. We write faster if
we get behind, where behind can mean either that checkpoint_segments
is going to expire too soon, or that checkpoint_timeout is going to
expire too soon. VACUUM has a very similar problem: operations that
use XIDs or create dead tuples create the need for maintenance which
VACUUM then performs. We want to vacuum fast enough to keep up with
the work, but not so fast that we tax the I/O subsystem more than
necessary. But unlike the checkpoint process, vacuum's
decision-making is all local: it has no idea whether it's keeping up.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company