X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 70EC9D1B54C for ; Sun, 19 Oct 2003 19:05:19 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 44726-05 for ; Sun, 19 Oct 2003 16:04:49 -0300 (ADT) Received: from davinci.ethosmedia.com (unknown [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id 86543D1B548 for ; Sun, 19 Oct 2003 16:04:47 -0300 (ADT) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3791051; Sun, 19 Oct 2003 12:05:26 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: Seum-Lim Gan , Tom Lane Subject: Re: index file bloating still in 7.4 ? Date: Sun, 19 Oct 2003 12:04:23 -0700 User-Agent: KMail/1.4.3 Cc: pgsql-performance@postgresql.org References: <19692.1066578442@sss.pgh.pa.us> In-Reply-To: MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200310191204.23888.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/498 X-Sequence-Number: 4246 Gan, > Oh, so in order to reclaim the disk space, we must run > reindex or vacuum full ? > This will lock out the table and we won't be able to do anything. > Looks like this is a problem. It means we cannot use it for > 24x7 operations without having to stop the process and do the vacuum full > and reindex. Is there anything down the road that these operations > will not lock out the table ? I doubt it; the amount of page-shuffling required to reclaim 90% of the space in an index for a table that has been mostly cleared is substantial, and would prevent concurrent access. Also, you seem to have set up an impossible situation for VACUUM. If I'm reading your statistics right, you have a large number of threads accessing most of the data 100% of the time, preventing VACUUM from cleaning up the pages. This is not, in my experience, a realistic test case ... there are peak and idle periods for all databases, even webservers that have been slashdotted. -- Josh Berkus Aglio Database Solutions San Francisco