Re: decoupling table and index vacuum

On Wed, Feb 9, 2022 at 7:43 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Wed, Feb 9, 2022 at 1:18 AM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:

> I think that dead index tuples really don't matter if they're going to
> get removed anyway before a page split happens. In particular, if
> we're going to do a bottom-up index deletion pass before splitting the
> page, then who cares if there are going to be dead tuples around until
> then? You might think that they'd have the unfortunate effect of
> slowing down scans, and they could slow down ONE scan, but if they do,
> then I think kill_prior_tuple will hint them dead and they won't
> matter any more.

Actually I was not worried about the scan getting slow. What I was
worried about is if we keep ignoring the dead tuples for long time
then in the worst case if we have huge number of dead tuples in the
index maybe 80% to 90% and then suddenly if we get a lot of insertion
for the keys which can not use bottom up deletion (due to the key
range). So now we have a lot of pages which have only dead tuples but
we will still allocate new pages because we ignored the dead tuple %
and did not vacuum for a long time.

In short I am worried about creating a sudden bloat in the index due
to a lot of existing dead tuples.

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com