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

On Tue, Apr 1, 2014 at 12:24 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Mon, Mar 31, 2014 at 12:35 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>> On Wed, Mar 26, 2014 at 11:32 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> 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.
>
>
>
> Another aspect of my ambivalence about this is that VACUUM FULL tends
> to get overused as it is. If we start making automated
> recommendations in that direction, it might cause people to lean that
> way even further, which would not, on the whole, be a good thing. On
> the other hand, if the table is 80% dead space, it's a pretty good bet
> that a VACUUM FULL is needed. Even there, though, the VACUUM FULL may
> be a pretty temporary fix unless the user also fixes the underlying
> issue that caused the table bloat to accumulate in the first place.
> Sometimes bloat is caused by a one-off issue, like one long-running
> query. But sometimes it's caused by something systematic, like
> setting the cost limit too low or the nap time too high.

Right, but it can happen even if the settings for auto vacuum are done
considering the general usage but as a one of case there is sudden spike in
update in which case it might make sense to give such a message.
However if this message keep appearing in the log every now and then,
it will mean that autovacumm settings are not appropriate for the load.
I think it will be difficult to know the exact reason for dead space, do you
think it can make sense if the message indicates (as Hint) such that,
if user observes this message repeatedly the autovacuum settings are
not as per load.
Another way could be to update docs to indicate the same.

> Just telling
> the user to run VACUUM FULL is likely to make the user conclude that
> "PostgreSQL sucks, I have to keep running VACUUM FULL all the time,
> taking a full-table lock".

Agreed user can conclude such things, but even if he figures that out himself
(which is quite possible), he will reach to same conclusion unless he is aware
that the reason could be the autovacuum settings.

Another thought that occurred to me is might be giving such an information for
Index can be more useful as there are always more chances for index bloat
especially in context of below information from docs.
"B-tree index pages that have become completely empty are reclaimed for re-use.
However, there is still a possibility of inefficient use of space: if
all but a few index
keys on a page have been deleted, the page remains allocated. Therefore, a usage
pattern in which most, but not all, keys in each range are eventually
deleted will see
poor use of space. For such usage patterns, periodic reindexing is recommended."

There are certain usage pattern's like always inserting data in particular
(increasing/decreasing) order which can lead to bloat in above context.

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