[postgis-users] Large Databases

[Michael Fuhr]

On Tue, Jun 13, 2006 at 01:05:21PM -0400, Robert Burgholzer wrote:
> This is off topic, but I was under the impression that vacuum full was
> actually the only vacuum that re-cycled used disk space?

VACUUM without FULL marks dead space for reuse but doesn't release
it back to the filesystem (i.e., it doesn't shrink the file).  VACUUM
FULL and CLUSTER return unused space to the filesystem but are more
invasive.  See the PostgreSQL documentation for more information:

http://www.postgresql.org/docs/8.1/interactive/maintenance.html#VACUUM-FOR-SPACE-RECOVERY

"The standard form of VACUUM is best used with the goal of maintaining
a fairly level steady-state usage of disk space. If you need to return
disk space to the operating system you can use VACUUM FULL -- but
what's the point of releasing disk space that will only have to be
allocated again soon? Moderately frequent standard VACUUM runs are a
better approach than infrequent VACUUM FULL runs for maintaining
heavily-updated tables."

"VACUUM FULL is recommended for cases where you know you have deleted
the majority of rows in a table, so that the steady-state size of the
table can be shrunk substantially with VACUUM FULL's more aggressive
approach. Use plain VACUUM, not VACUUM FULL, for routine vacuuming for
space recovery."

-- 
Michael Fuhr