[postgis-users] Large Databases

[Robert Burgholzer]

Thanks Jim/Michael,
This is good information. It seems that while Postgres is an industry
scale rdbms, the actual type of work that the DB is required to do in a
GIS and scientific analysis tool environment may really be charting new
territory -- requiring a new set of assumptions in terms of proper care
and feeding.

To do some tests on these ideas would be a logical step in terms of
coming up with a set of recommendations for the maintenance of a
spatial/scientific DB. If you all, or anyone else are interested, I
think that it might be worthwhile to devise a testing protocol, and a
set of test cases/datasets to explore optimization. 

Any interest in doing this?

r.b.

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Jim
C. Nasby
Sent: Tuesday, June 13, 2006 7:33 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Large Databases

On Tue, Jun 13, 2006 at 02:51:42PM -0400, Robert Burgholzer wrote:
> 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.

BTW, proper use of lazy vacuum should eliminate the need for vacuum full
in almost all cases. The one exception is if you often update the entire
table, though in a case like that you'll almost certainly get better
performance from something like:

BEGIN;
LOCK table;
SELECT blah INTO table_new FROM table;
CREATE INDEX ... ON table_new ...;
DROP table;
ALTER TABLE table_new RENAME table;
COMMIT;

An alternative to the LOCK would be creating a rule or trigger that
dis-allowed people from changing the table while this was running.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby at pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users