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 E19AC5349F for ; Tue, 19 Apr 2005 15:02:51 -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 16924-01 for ; Tue, 19 Apr 2005 18:02:42 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 08560534FC for ; Tue, 19 Apr 2005 15:02:41 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j3JI2Z5U023184; Tue, 19 Apr 2005 14:02:35 -0400 (EDT) To: josh@agliodbs.com Cc: "Tambet Matiisen" , "Bill Chandler" , "pgsql-perform" Subject: Re: Question on REINDEX In-reply-to: <200504191056.16688.josh@agliodbs.com> References: <200504190857.16634.josh@agliodbs.com> <22304.1113927842@sss.pgh.pa.us> <200504191056.16688.josh@agliodbs.com> Comments: In-reply-to Josh Berkus message dated "Tue, 19 Apr 2005 10:56:16 -0700" Date: Tue, 19 Apr 2005 14:02:34 -0400 Message-ID: <23183.1113933754@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/462 X-Sequence-Number: 11859 Josh Berkus writes: >> Not at all. What it says is that you expect 100% of the pages to have >> useful amounts of free space, which is a *much* weaker criterion. > Hmmmm .... actually, it seems like, if you are vacuuming regularly, you only > *do* need to track pages that have been touched by DELETE or UPDATE. Other > pages would have already been vacuumed and not have any useful free space > left. Yes? Well, the space has to be remembered until it's reused. On the other hand, there's nothing that says FSM has to be aware of all the free space available at all times --- the real criterion to avoid bloat is that after a VACUUM, enough space is logged in FSM to satisfy all the insertions that will happen before the next VACUUM. So you could have situations where free space is temporarily forgotten (for lack of slots in FSM), but other free space gets used instead, and eventually a later VACUUM re-finds that free space and puts it into FSM. I think it's true that the more often you vacuum, the less FSM you need, but this doesn't have much to do with how much free space is actually out there on disk. It's because you only need enough FSM to record the free space you'll need until the next vacuum. regards, tom lane