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

On Wed, Mar 19, 2014 at 6:25 AM, Wang, Jing <jingw(at)fast(dot)au(dot)fujitsu(dot)com> wrote:
> On Friday, 14 March 2014 2:42 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>> I think it might be okay to even change this API to return the FreeSpace, as the other place it is used is for Index Vacuum, so even if we don't have any intention to print such a message for index in this patch,
>> but similar information could be useful there as well to suggest a user that index has lot of free space.
>
> Enclosed please find the new patch which get the FreeSpace for one relation from the return of FreeSpaceMapVacuum() function. This function and the fsm_vacuum_page() function have been slightly modified to get the FreeSpace and no I/O burden increasing. The little side-effect is it will calculate FreeSpace for every table even the table is very small.

I think that can also be avoided, because by the time you call
FreeSpaceMapVacuum(), you already have the required information
based on which you can decide not to ask for freespace if
required.

Can't we avoid the new calculation you have added in
fsm_vacuum_page(), as this function already updates the size,
so might be we can get it from current calculation done in
function.

+ #define RELPAGES_VALUES_THRESHOLD 1000
+ #define FREESPACE_PERCENTAGE_THRESHOLD 0.5
Is there any basis to define above hash defines, we already
have one number similar to above for deciding Truncate of relation.

In anycase, I think the patch's direction is better than previous and
can be further discussed/reviewed during next CF, as it's already
quite late for 9.4.

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