"Consider compacting this relation..." ???

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