Re: Why frequently updated tables are an issue

Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
> But a per relation bitmap that tells if a block is a) free of dead
> tuples and b) all remaining tuples in it are frozen could be used to let
> vacuum skip them (there can't be anything to do). The bit would get
> reset whenever the block is marked dirty. This would cause vacuum to
> look at mainly recently touched blocks, likely to be found in the buffer
> cache anyway and thus dramatically reduce the amount of IO and thereby
> make high frequent vacuuming less expensive.

I don't think it would help very much to define a bit like that --- I
can't believe that very many pages would contain only frozen tuples,
unless you were to adopt an aggressive policy of using VACUUM FREEZE
a lot.

It might be interesting though to have some kind of "fast vacuum" mode
that doesn't worry about freezing tuples, but only reclaiming dead ones.
This could look at only recently touched pages, with perhaps the
cooperation of the bgwriter to keep track of candidate pages. (You'd
still have to do full-database scans for freezable tuples occasionally ...
but not very often.)

The main thing I don't see how to handle efficiently is getting rid of
the index entries for dead rows. The present indexam API is designed
for getting rid of index entries wholesale, but you'd need something
that works better for retail removal to approach vacuuming this way.

regards, tom lane