[postgis-users] Large Databases

[Robert Burgholzer]

This is off topic, but I was under the impression that vacuum full was
actually the only vacuum that re-cycled used disk space?

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 12:50 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Large Databases

On Tue, Jun 13, 2006 at 11:44:16AM -0400, Robert Burgholzer wrote:
> I just went through an upgrade of server, and was initially quite
> disappointed that my 2.0 Ghz with 1 G RAM was taking forever on a
> "vacuum full" of a table with about 50 million records. I abandoned
the

VACUUM FULL should seldom be necessary if you regularly perform
ordinary VACUUMs (without FULL) and if your free space map settings
are adequate (see links below).  In cases where you do want to
shrink a table, CLUSTER is sometimes faster than VACUUM FULL.

> analyze after 6 hours, and I then read the Pg manual, and changed
> maintenance_work_mem in postgresql.conf from the default value as
> follows:
> 
> #maintenance_work_mem = 16384           # min 1024, size in KB
> maintenance_work_mem = 163840
> 
> I then re-ran the vacuum and it processed in under 5 minutes.
> 
> Just food for thought that those settings can really help you out,
> although I have found it difficult to get good solid advice on which
> settings to change and what to change them to.

Here are some commonly-cited tuning guides:

http://www.powerpostgresql.com/PerfList
http://www.powerpostgresql.com/Downloads/annotated_conf_80.html

-- 
Michael Fuhr
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users