Thread: "Consider compacting this relation..." ???

WARNING:  relation "public.some_big_table" contains more than "max_fsm_pages" pages with useful free space
HINT:  Consider compacting this relation or increasing the configuration parameter "max_fsm_pages".

On Thu, Feb 28, 2008 at 3:14 PM, Kynn Jones <kynnjo@gmail.com> wrote:
> I just ran VACUUM ANALYZE and got this warning I've never seen before:
>
>  WARNING:  relation "public.some_big_table" contains more than
> "max_fsm_pages" pages with useful free space
> HINT:  Consider compacting this relation or increasing the configuration
> parameter "max_fsm_pages".
>
>
> What does the hint mean by "compacting"?  What exactly must I do to compact
> this table?

Vacuum full

>  Also, at the end of the VACUUM ANALYZE run I get the NOTICE:
>
> NOTICE:  number of page slots needed (294528) exceeds max_fsm_pages (153600)
> HINT:  Consider increasing the configuration parameter "max_fsm_pages" to a
> value over 294528.
>
>
> What surprises me here is that even though I've never seen this notice
> before (and I've been running VACUUM ANALYZE a lot lately!), the number of
> page slots needed is almost twice as much as the value of max_fsm_pages.
> What could I have done since the last time I ran VACUUM ANALYZE (not more
> than 24 hours ago) to make this "page slot" requirement to shoot through the
> roof like this?

Lots of deletes or updates on the table.

"Scott Marlowe" <scott.marlowe@gmail.com> writes:
> On Thu, Feb 28, 2008 at 3:14 PM, Kynn Jones <kynnjo@gmail.com> wrote:
>> What does the hint mean by "compacting"?  What exactly must I do to compact
>> this table?

> Vacuum full

CLUSTER is a good alternative also, though pre-8.3 you need to consider
that it's not MVCC-safe; that could be important in heavily concurrent
applications.

            regards, tom lane