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

On Wed, Mar 26, 2014 at 11:32 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Sun, Mar 9, 2014 at 5:28 PM, 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.
>>
>> 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.
>
> I'm not sure that we want people to automatically VF a table just
> because it's 2x bloated. Doesn't it depend on the table size? And in
> sort of a funny way, too, like, if the tables is small, 2x bloat is
> not wasting much disk space, but getting rid of it is probably easy,
> so maybe you should - but if the table is a terabyte, even 50% bloat
> might be pretty intolerable, but whether it makes sense to try to get
> rid of it depends on your access pattern. I'm not really too sure
> whether it makes sense to try to make an automated recommendation
> here, or maybe only in egregious cases.

I think here main difficulty is to decide when it will be considered good
to display such a message. As you said, that it depends on access pattern
whether 50% bloat is tolerable or not, so one way could be to increase the
bloat limit and table size threshold to higher value (bloat - 80%,
table_size = 500M) where it would make sense to recommend VF for all cases
or another way could be to consider using some auto vacuum threshold parameter
like autovacuum_vacuum_scale_factor to calculate threshold value for issuing
this message. I think parameter like scale factor can make sense as to an extent
this parameter is an indicative of how much dead space percentage is tolerable
for user.

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