X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 86906533E4 for ; Thu, 21 Apr 2005 15:41:50 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 64288-04 for ; Thu, 21 Apr 2005 18:41:49 +0000 (GMT) Received: from priv-edtnes51.telusplanet.net (outbound04.telus.net [199.185.220.223]) by svr1.postgresql.org (Postfix) with ESMTP id A6DFE52964 for ; Thu, 21 Apr 2005 15:41:47 -0300 (ADT) Received: from localhost ([199.185.220.240]) by priv-edtnes51.telusplanet.net (InterMail vM.6.01.04.00 201-2131-118-20041027) with ESMTP id <20050421184148.VLNO12171.priv-edtnes51.telusplanet.net@localhost>; Thu, 21 Apr 2005 12:41:48 -0600 Received: from 209.17.183.249 ( [209.17.183.249]) as user a3a18850@192.168.200.1 by webmail.telus.net with HTTP; Thu, 21 Apr 2005 11:41:48 -0700 Message-ID: <1114108908.4267f3ec2e0ea@webmail.telus.net> Date: Thu, 21 Apr 2005 11:41:48 -0700 From: a3a18850@telus.net To: Bill Chandler Cc: pgsql-perform Subject: Re: Index bloat problem? References: <20050421170018.2998.qmail@web51403.mail.yahoo.com> In-Reply-To: <20050421170018.2998.qmail@web51403.mail.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit User-Agent: Internet Messaging Program (IMP) 3.1-cvs X-Originating-IP: 209.17.183.249 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.074 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_POST, FORGED_RCVD_HELO, FROM_ENDS_IN_NUMS, FROM_HAS_MIXED_NUMS, NO_REAL_NAME X-Spam-Level: ** X-Archive-Number: 200504/636 X-Sequence-Number: 12033 Quoting Bill Chandler : > Running PostgreSQL 7.4.2, Solaris. > Client is reporting that the size of an index is > greater than the number of rows in the table (1.9 > million vs. 1.5 million). Index was automatically > created from a 'bigserial unique' column. > We have been running 'VACUUM ANALYZE' very regularly. > In fact, our vacuum schedule has probably been > overkill. We have been running on a per-table basis > after every update (many per day, only inserts > occurring) and after every purge (one per day, > deleting a day's worth of data). > > What about if an out-of-the-ordinary number of rows > were deleted (say 75% of rows in the table, as opposed > to normal 5%) followed by a 'VACUUM ANALYZE'? Could > things get out of whack because of that situation? I gather you mean, out-of-the-ordinary for most apps, but not for this client? In case nobody else has asked: is your max_fsm_pages big enough to handle all the deleted pages, across ALL tables hit by the purge? If not, you're haemorrhaging pages, and VACUUM is probably warning you about exactly that. If that's not a problem, you might want to consider partitioning the data. Take a look at inherited tables. For me, they're a good approximation of clustered indexes (sigh, miss'em) and equivalent to table spaces. My app is in a similar boat to yours: up to 1/3 of a 10M-row table goes away every day. For each of the child tables that is a candidate to be dropped, there is a big prologue txn, whichs moves (INSERT then DELETE) the good rows into a child table that is NOT to be dropped. Then BANG pull the plug on the tables you don't want. MUCH faster than DELETE: the dropped tables' files' disk space goes away in one shot, too. Just my 2c.