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 541E2D1B1D1 for ; Fri, 21 May 2004 19:09:43 -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 59396-07 for ; Fri, 21 May 2004 19:09:25 -0300 (ADT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id E685DD1B211 for ; Fri, 21 May 2004 19:09:22 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i4LM9OBX016536; Fri, 21 May 2004 18:09:24 -0400 (EDT) To: Bill Montgomery Cc: pgsql-performance@postgresql.org Subject: Re: Avoiding vacuum full on an UPDATE-heavy table In-reply-to: <40AE274F.7020800@lulu.com> References: <40AE274F.7020800@lulu.com> Comments: In-reply-to Bill Montgomery message dated "Fri, 21 May 2004 11:59:11 -0400" Date: Fri, 21 May 2004 18:09:24 -0400 Message-ID: <16535.1085177364@sss.pgh.pa.us> From: Tom Lane 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/191 X-Sequence-Number: 7022 Bill Montgomery writes: > I have a particularly troublesome table in my 7.3.4 database. It > typically has less than 50k rows, and a usage pattern of about 1k > INSERTs, 50-100k UPDATEs, and no DELETEs per day. It is vacuumed and > analyzed three times per week. You probably want to vacuum (non-FULL) once a day, if not more often. Also take a look at your FSM settings --- it seems like a good bet that they're not large enough to remember all the free space in your database. With adequate FSM the table should stabilize at a physical size corresponding to number-of-live-rows + number-of-updates-between-VACUUMs, which would be three times the minimum possible size if you vacuum once a day (50K + 100K) or five times if you stick to every-other-day (50K + 200K). Your VACUUM FULL output shows that the table had bloated to hundreds of times the minimum size: > INFO: Rel xxxx: Pages: 188903 --> 393; Tuple(s) moved: 17985. and AFAIK the only way that will happen is if you fail to vacuum at all or don't have enough FSM. The indexes are looking darn large as well. In 7.3 about the only thing you can do about this is REINDEX the table every so often. 7.4 should behave better though. regards, tom lane