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 52A3ED1B1D0 for ; Fri, 21 May 2004 17:36:37 -0300 (ADT) 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 23023-04 for ; Fri, 21 May 2004 17:36:19 -0300 (ADT) Received: from lorax.kcilink.com (lorax.kciLink.com [206.112.95.1]) by svr1.postgresql.org (Postfix) with ESMTP id 190DCD1B193 for ; Fri, 21 May 2004 17:36:17 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by lorax.kcilink.com (Postfix) with ESMTP id 221283F9C for ; Fri, 21 May 2004 16:36:19 -0400 (EDT) Received: from lorax.kcilink.com ([127.0.0.1]) by localhost (lorax.kcilink.com [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 03725-03 for ; Fri, 21 May 2004 16:36:18 -0400 (EDT) Received: by lorax.kcilink.com (Postfix, from userid 8) id 9E9A43F22; Fri, 21 May 2004 16:36:18 -0400 (EDT) To: pgsql-performance@postgresql.org Path: not-for-mail From: Vivek Khera Newsgroups: ml.postgres.performance Subject: Re: Avoiding vacuum full on an UPDATE-heavy table Date: Fri, 21 May 2004 16:36:18 -0400 Organization: Khera Communications, Inc., Rockville, MD Lines: 18 Message-ID: References: <40AE274F.7020800@lulu.com> NNTP-Posting-Host: yertle.kcilink.com Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Trace: lorax.kcilink.com 1085171778 87505 65.205.34.180 (21 May 2004 20:36:18 GMT) X-Complaints-To: daemon@kciLink.com NNTP-Posting-Date: Fri, 21 May 2004 20:36:18 +0000 (UTC) User-Agent: Gnus/5.1002 (Gnus v5.10.2) XEmacs/21.4 (Reasonable Discussion, berkeley-unix) Cancel-Lock: sha1:/tkEJMmq2WeRXnC3z5NvVVNaZgQ= X-Virus-Scanned: by amavisd-new at kcilink.com X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/189 X-Sequence-Number: 7020 >>>>> "BM" == Bill Montgomery writes: BM> Is there any way to avoid doing a periodic VACUUM FULL on this table, BM> given the fairly radical usage pattern? Or is the (ugly) answer to BM> redesign our application to avoid this usage pattern? I'll bet upgrading to 7.4.2 clears up your problems. I'm not sure if it was in 7.3 or 7.4 where the index bloat problem was solved. Try to see if just reindexing will help your performance. Also, run a plain vacuum at least nightly so that your table size stays reasonable. It won't take much time on a table with only 50k rows in it. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-301-869-4449 x806 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/