Re: Single pass vacuum - take 1

On Fri, Jul 15, 2011 at 7:23 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> My additional requests would be that we can easily tell which blocks
> have been modified like this, that we have a way to turn this off if
> we get bugs for next few releases, that we check it all works with Hot
> Standby just fine and that all the block inspection tools support it.

To me, this seems like a bit too much of an internal change to justify
adding a GUC. But it probably is a good idea to ping the authors of
the various block inspection tools -- does contrib/pageinspect care
about this sort of thing, or just the out-of-core stuff?

I'd also think that it would be a good idea to consider (most likely
as a separate patch) the idea you suggested upthread: skip phase 2 if
the number of dead tuples is insufficient to justify the cost of
scanning the indexes. I've been wondering if it might make sense to
couple that change with a decrease in vacuum_scale_factor - in effect,
vacuum more frequently, but don't scan the indexes every time.

One possibly useful metric for benchmarking these sorts of changes is
to run a write workload for a while until the size of the tables
stabilize and then measure how big they are. If a given change causes
the table size to stabilize at a lower value, that suggests that the
change makes vacuum more effective at controlling bloat, which is
good. Conversely, if the change causes the table size to stabilize at
a higher value, that suggests that we've made vacuum less effective at
controlling bloat, which is bad. In fact, I'd venture to say that
anything that falls into the latter category is dead on arrival...

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