Re: Why frequently updated tables are an issue

On 6/12/2004 3:45 PM, Tom Lane wrote:

> 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.

I thought this implies an aggressive policy of freezing everything by
default. But I guess there is something I am not aware of that makes
aggressive freezing a bad thing.

>
> 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.)

Wouldn't that screw the current FSM population mechanisms? Not that my
suggestions above wouldn't do that either :-)

>
> 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.

Which is a problem so fundamentally embedded in the index implementation
itself that is sounds more like a wholesale replacement of the index am
than some nifty changes.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #