DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:sender:to:subject:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references:x-google-sender-auth;
	b=gTYYxj1kL8Jv4pcRTRe4PzJ4MoIhYh8QS+laRPMr1LVaqoFTkLx+SAH4djTS0A55pT
	jUXsDWefzcF9QE4uBEaubk81ECksJC5ZSzfiZHuUh8/3PsIyz5UtsF4UoBMzk6UONzCz
	qyI0Lr+hFqY3OzIfS7gp7DL5drKS99/7ByD8A=
Message-ID: <b6e8f2e80809290250i7716ab0ap861e8876a40ca347@mail.gmail.com>
Date: Mon, 29 Sep 2008 11:50:20 +0200
From: "Peter Kovacs" <maxottovonstirlitz@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Re: Do we need vacuuming when tables are regularly dropped?
In-Reply-To: <b6e8f2e80809290237p7685dbcfm9d5c886ba225b956@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <b6e8f2e80809290237p7685dbcfm9d5c886ba225b956@mail.gmail.com>

PS:
PGSQL version is: 8.2.7. (BTW, which catalog view contains the
back-end version number?)


On Mon, Sep 29, 2008 at 11:37 AM, Peter Kovacs
<maxottovonstirlitz@gmail.com> wrote:
> Hi,
>
> We have a number of automated performance tests (to test our own code)
> involving PostgreSQL. Test cases are supposed to drop and recreate
> tables each time they run.
>
> The problem is that some of the tests show a linear performance
> degradation overtime. (We have data for three months back in the
> past.) We have established that some element(s) of our test
> environment must be the culprit for the degradation. As rebooting the
> test machine didn't revert speeds to baselines recorded three months
> ago, we have turned our attention to the database as the only element
> of the environment which is persistent across reboots. Recreating the
> entire PGSQL cluster did cause speeds to revert to baselines.
>
> I understand that vacuuming solves performance problems related to
> "holes" in data files created as a result of tables being updated. Do
> I understand correctly that if tables are dropped and recreated at the
> beginning of each test case, holes in data files are reclaimed, so
> there is no need for vacuuming from a performance perspective?
>
> I will double check whether the problematic test cases do indeed
> always drop their tables, but assuming they do, are there any factors
> in the database (apart from table updates) that can cause a linear
> slow-down with repetitive tasks?
>
> Thanks
> Peter
>