In response to "Lee Keel" <lee_keel@xxxxxxxxxxx>: > >-----Original Message----- > >From: Tom Lane [mailto:tgl@xxxxxxxxxxxxx] > > > >"Lee Keel" <lee_keel@xxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxxxxxx Phone: 412-422-3463x4023
