This is a much discussed topic, which always boils down to grabbing
indexes from disk.
At the moment we have bgwriter periodically flushing parts of the dirty
buffers to disk on a periodic basis. Would it be possible to have the
bgwriter take a look at the pages it has, and see if it can do any
vacuum work based on pages it is about to send to disk?
That is, quickly check table pages, prepare a list of tuples to be
cleaned, look at other pages its preparing to write and see if any of
them contain the index tuples for the data in the table pages?
When an update occurs which does not change the key values, the table
page and index pages all become dirty at once (assume key values, like
primary key, didn't change) so I would expect, without any knowledge in
the bgwriter algorithm, the bgwriter to push them all to disk within the
same batch most of the time.
It's a fairly limited case and by no means removes the requirement for
regular vacuums, but for an update heavy structure perhaps it would be
worth while? Even if it could only keep indexes clean it would help.
Just a thought..
--
