On Wed, 2006-08-09 at 23:01, adey wrote:
> Does autovacuum replace the need for a FULL vacuum please (to recover
> free space, etc)?
In most cases, yes. The idea is that autovacuum should put the database
into a "steady state" where there is some % of each table that is free
space and being recycled without the table growing or shrinking due to
vacuuming.
But it doesn't always work out that way. If you're updating the table
very quickly, then you might outrun auto vacuum and get into a sticky
situation.
The somewhat bad situation is that your FSM settings are high enough to
store all the empty bits for reuse, but there's a LOT of them in some
often updated table. That's a bit bothersome.
The worst case scenario is that your FSM settings are not high enough,
and you've not got a table which has an increasing number of empty
spaces in it, even though the amount of data is steady state.
Generally, there are autovacuum settings and fsm settings that will keep
this from happening, but once you've got bloat, the only fix is vacuum
full, cluster, or reindex. I.e. something that copies the data into a
new file for you. All three of those are nasty in terms of the locks
they have to take out, and they block other access on the table while
running.