Re: Why frequently updated tables are an issue

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

> Jan Wieck <JanWieck@Yahoo.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@Yahoo.com #