> >-----Original Message-----
> >From: Tom Lane [mailto:email@example.com]
> >"Lee Keel" <firstname.lastname@example.org> writes:
> >> I have 15 or so databases that I am deleting all the data in them and
> >> re-importing on a nightly basis. (Long story here, but basically I
> >> found using the copy command was the fastest way to get the data into
> >> this read-only system from Sql Server 2000.) A couple of the
> >> are small and only take about 15 minutes to copy all the data, but
> >> others are much larger and take 45 minutes or more. So, I found that
> >> the databases run a lot faster if I perform a full vacuum on them.
> >Not so much "duh" as "maybe you should change your data import
> >It sounds like you're deleting old data with DELETE and then loading
> >Can you use TRUNCATE instead of DELETE?
> >Autovacuum won't *ever* do VACUUM FULL, and in a well-run database you
> >shouldn't need to do it manually either.
> > regards, tom lane
> Thanks for your reply. I have found that I am using delete instead of
> truncate and I can fix this.
> As for the vacuum full, I have reread the help and realize where I was
> under the wrong impression, but even the vacuum analyze does not appear
> to be running with my current settings. And according to the help and
> all that I do know about dbs, I should at least perform a vacuum analyze
> and\or reindex on a regular basis. So can you point me to any of the
> settings that I may have wrong to have this done for me automatically?
If this database is read-only aside from your massive uploads, then the
following process will suffice:
1) Truncate all tables
2) Upload new data
3) analyze database
You only need periodic vacuum/analyze if you're modifying the data. It's
likely that autovacuum isn't doing anything because there's nothing to do.
Collaborative Fusion Inc.