Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 5CAE437BCE6 for ; Mon, 29 Sep 2008 06:37:49 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 83864-09 for ; Mon, 29 Sep 2008 06:37:41 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.186]) by postgresql.org (Postfix) with ESMTP id AEB4137BA30 for ; Mon, 29 Sep 2008 06:37:40 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id c7so657150nfi.23 for ; Mon, 29 Sep 2008 02:37:36 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:sender :to:subject:mime-version:content-type:content-transfer-encoding :content-disposition:x-google-sender-auth; bh=4jCdSxjXD6X7kT+JfxMwAb4pgbF9wv375+aTGIpHchk=; b=iflqxzyzsbfYiLcEN/XayjS709VxqV56BLNeeXQgNE+JCZZuAOgVg1ubBMWmr8QrlU HkaE10/oGY9YnSPvGfuBe8rYD9/j1SGj+XTL/6zc6iyIxRmOSm+wlaO0sEc4TWijOt94 HKyBdUPYeU4Kg1VBUNjTM7NCx1iLImXbLi94U= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:sender:to:subject:mime-version:content-type :content-transfer-encoding:content-disposition:x-google-sender-auth; b=akxOhLmsTjUpPk3jlJ9LktyhLM/3agmreNMKwZp1LqKqDgJYYi4L4fKNRyLQ3a1uDX r80XfxT8cRrsorXRDANNMUlSWxGwikPxnUgFMFBhh5cUwjHFxJCw/SkXbF2udWTu/LF7 xE4RTPK4cVCn3Q40UUB0odL2ptIyJUA1XqqQc= Received: by 10.210.123.2 with SMTP id v2mr5920544ebc.186.1222681056438; Mon, 29 Sep 2008 02:37:36 -0700 (PDT) Received: by 10.210.43.4 with HTTP; Mon, 29 Sep 2008 02:37:36 -0700 (PDT) Message-ID: Date: Mon, 29 Sep 2008 11:37:36 +0200 From: "Peter Kovacs" To: pgsql-admin@postgresql.org Subject: Do we need vacuuming when tables are regularly dropped? MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Google-Sender-Auth: c653ea9cffcb284d X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200809/327 X-Sequence-Number: 30420 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