Re: Open issues for HOT patch

On 9/18/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> I wrote:
> > * The patch makes undocumented changes that cause autovacuum's decisions
> > to be driven by total estimated dead space rather than total number of
> > dead tuples. Do we like this?
>
> No one seems to have picked up on this point, but after reflection
> I think there's actually a pretty big problem here. Per-page pruning
> is perfectly capable of keeping dead space in check. In a system with
> HOT running well, the reasons to vacuum a table will be:
>
> 1. Remove dead index entries.
> 2. Remove LP_DEAD line pointers.
> 3. Truncate off no-longer-used end pages.
> 4. Transfer knowledge about free space into FSM.
>
> Pruning cannot accomplish #1, #2, or #3, and without significant changes
> in the FSM infrastructure it has no hope about #4 either.

I guess we already have mechanism to remove dead index entries
outside vacuum. So my take would be handle vacuum based on dead
index entries separately. May be we can track number of dead index
entries and trigger vacuum on the base relation if it goes beyond a
threshold.

For LP_DEAD line pointers, with some adjustments to the patch, we
can make it track dead space in a page by accounting for the
the LP_DEAD pointers. So if there is a bloat because of LP_DEAD
pointers, that will be reflected in the dead space and help us trigger
vacuum on the table.

> I'm inclined to think that we should continue to drive autovac off a
> count of dead rows

If we do that, I guess it would make sense to count only non-HOT
dead tuples because HOT tuples neither create LP_DEAD line
pointers nor cause index bloats.

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com