On K, 2005-08-31 at 10:33 -0400, Tom Lane wrote:
> Hannu Krosing <hannu@skype.net> writes:
> > On K, 2005-08-31 at 16:50 +0900, Satoshi Nagayasu wrote:
> >> VACUUM generates a huge load because it repaires all pages
> >> on the table file.
> >>
> >> I think (more light-weight) repairing on a single page
> >> is needed to maintain free space in the specific page.
>
> > There have been plans floating around for adding a more lightweight
> > vacuum, which uses something similar to FSM to keep track of pages which
> > need vacuuming. And possibly integreated with background writer to make
> > effective use of I/O resources.
>
> > I guess it could be used for this case of "cheap page cleanups" as well.
>
> Pretty much all of these ideas fall down when you remember that you have
> to fix indexes too. There's no such thing as a "cheap page cleanup",
> except maybe in a table with no indexes. Cleaning out the indexes
> efficiently requires a certain amount of batch processing, which leads
> straight back to VACUUM.
What I was aiming for here, is cases when bgwriter kicks in after it is
safe to do the cleanup but before the changed page and it's changed
index pages are flushed to disk.
I think that for OLTP scenarios this is what happens quite often.
Even more so if we consider that we do mark quaranteed-invisible pages
in index as well.
My wild guess is that deleting all index pointers for a removed index is
more-or-less the same cost as creating new ones for inserted/updated
page. If so, the max cost factor for doing so is 2X, but usually less,
as many of the needed pages are already in memory even at the time when
it is safe to remove old tuple, which in OLTP usage is a few seconds
(usually even less than a second) after the original delete is done.
It is often more agreeable to take a continuous up-to-2X performance hit
than an unpredictable hit at unknown (or even at a known) time.
--
Hannu Krosing <hannu@skype.net>