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

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.

In general, I think idea to log a message for Vaccum Full is okay, but it would
be more viable if we can do that without any additional cost.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com