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

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.

> 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

The free space map can show more or less than the real amount of free
space, can't it? I worry about making a recommendation that might
turn out to be wildly inaccurate...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company