Tuesday, September 16, 2008

Re: [GENERAL] nightly vacuum

>-----Original Message-----
>From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>
>"Lee Keel" <lee_keel@trimble.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?

Please know that I know I am not a dba, but I do consider myself a
little db savy. So I am not trying to be a smarta~~, I just want to
learn more about databases and better my knowledge in that area.

Thanks for the help,
Lee

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

No comments: