Re: nightly vacuum

In response to "Lee Keel" <lee_keel(at)trimble(dot)com>:

> >-----Original Message-----
> >From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> >
> >"Lee Keel" <lee_keel(at)trimble(dot)com> 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
> databases
> >> 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.
> Duh!
> >
> >Not so much "duh" as "maybe you should change your data import
> process".
> >It sounds like you're deleting old data with DELETE and then loading
> new.
> >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
> >
> -------------------------------------------------
> Tom,
>
> 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.

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran(at)collaborativefusion(dot)com
Phone: 412-422-3463x4023