Re: issue log message to suggest VACUUM FULL if a table is nearly empty

On Mon, Mar 10, 2014 at 4:24 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Mon, Mar 10, 2014 at 5:58 AM, Wang, Jing <jingw(at)fast(dot)au(dot)fujitsu(dot)com> wrote:
> > Enclosed is the patch to implement the requirement that issue log message to
> > suggest VACUUM FULL if a table is nearly empty.
> >
> > The requirement comes from the Postgresql TODO list.
> >
> > [Solution details]
> >
> > A check function is added in the function 'lazy_vacuum_rel' to check if the
> > table is large enough and contains large numbers of unused rows. If it is
> > then issue a log message that suggesting using 'VACUUM FULL' on the table.
> >
> > The judgement policy is as following:
> >
> > If the relpage of the table > RELPAGES_VALUES_THRESHOLD(default 1000) then
> > the table is considered to be large enough.
> >
> > If the free_space/total_space > FREESPACE_PERCENTAGE_THRESHOLD(default 0.5)
> > then the table is considered to have large numbers of unused rows.
> >
> > The free_space is calculated by reading the details from the FSM pages. This
> > may increase the IO, but expecting very less FSM pages thus it shouldn't
> > cause
>
> I think it would be better if we can use some existing stats to issue warning
> message rather than traversing the FSM for all pages. For example after
> vacuuming page in lazy_scan_heap(), we update the freespace for page.
> You can refer below line in lazy_scan_heap().
> freespace = PageGetHeapFreeSpace(page);
>
> Now it might be possible that we might not get freespace info easily as
> it is not accumulated for previous vacuum's. Incase there is no viable
> way to get it through vacuum stats, we are already updating fsm after
> vacuum by FreeSpaceMapVacuum(), where I think it should be possible
> to get freespace.

yes this way it works without extra penalty. But the problem is how to calculate
the free space which is left in the skipped pages because of visibility bit.

In a normal scenario, the pages which are getting skipped during vacuum process
are less in number means then this approach is a good choice.

Regards,
Hari Babu
Fujitsu Australia