[postgis-users] Large Databases

[Robert Burgholzer]

Very interesting. This is probably why I found that without vacuum full,
my 2 gigabyte database ballooned to over 10 Gb in a few short weeks. Our
db is used for the parameterization and calibration of an extremely
large environmental model, and so parameter values are generated and
regenerated as many as 10 times per day - causing there to be a whole
lot of unreleased file space. As one butts up against the partition max
size, this extra disk space can become an issue.

Thanks for the insight.
r.b.

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Michael Fuhr
Sent: Tuesday, June 13, 2006 2:03 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Large Databases

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-F
OR-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
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users