Re: nightly vacuum

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Lee Keel" <lee_keel(at)trimble(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: nightly vacuum
Date: 2008-09-16 14:57:49
Message-ID: 6218.1221577069@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Lee Keel 2008-09-16 15:14:53 Re: nightly vacuum
Previous Message Michael Fuhr 2008-09-16 14:39:20 Re: client_encoding / postgresql strange behaviour