Re: Excessive growth of pg_attribute and other system tables

Steve Crawford <scrawford@pinpointresearch.com> writes:
> Just to make sure I'm understanding things correctly this time...I
> originally (mis)understood these as settings related to resources
> used _during_ vacuuming. My current understanding is that they are
> basically pointers that track what space is available for reclamation
> by vaccum and that the amount of fsm resources required depends on
> both frequency of vacuums and volume of updates/deletes.

The FSM is where VACUUM stores pointers to the free space it's found
(or created) in each table.  Subsequent INSERTs/UPDATEs will use this
free space instead of appending to the file.  So to prevent table
growth, you need enough FSM slots to remember enough free space to
satisfy all the INSERTs/UPDATEs between successive VACUUM runs.

In practice people tend to allocate enough FSM to cover all of their
database, instead of worrying about exactly which pages might contain
free space.  In a low-update-volume situation you could probably get
away with less.

> 2) What happens with all that free-space information at server restart
> (ie. does a server restart lead to dead-tuple leakage)?

Assuming you had a normal database shutdown rather than a crash, it's
written out at shutdown and reloaded.  In any case, a VACUUM recomputes
the info from scratch.

> 4) Is there a way to query what proportion of the fsm resources are in
> use and would access to that info be useful to the autovacuum daemon
> or a system tuner?

VACUUM VERBOSE will tell you about this.

            regards, tom lane