Re: Does VACUUM ever free up any disk space?

On Thu, Sep 11, 2003 at 18:42:25 +0100,
  Chris Miles <chris_pg002@psychofx.com> wrote:
> I've read a lot where people recommend using VACUUM FULL
> to free up disk space, especially after many updates/inserts.
>
> But does a regular VACUUM (or VACUUM ANALYSE) ever free up
> any space?
>
> 24/7 production databases cannot be locked for long periods
> of time to run VACUUM FULL, but I do not want data files
> growing indefinitely (any more than they need to) so I hope
> the routine VACCUM ANALYSE will take care of this.

Vacuum full actually shrinks the file sizes. A normal vacuum just marks
free areas in the files so that they can be reused. If you regularly
do a normal vacuum your database should have a steady state size with
some of the disk space taken up by free space. If something unusual
happens and the database grows much bigger than it needs to be, you
can then use vacuum full to reclaim space. If your FSM setting is too
low, your normal vacuums won't mark all of the free space and this
can result in continual growth of the database files.

You also have to worry about index growth. In versions prior to 7.4
btree indexes would not reuse space when the index values were montonicly
increasing. This isn't a problem for everyone, but is for some. In 7.4
things work much better.