Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 237B32E0040 for ; Tue, 29 Apr 2008 05:37:13 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 74990-02 for ; Tue, 29 Apr 2008 05:37:10 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from westnet.com (westnet.com [216.187.52.2]) by developer.postgresql.org (Postfix) with ESMTP id 1F6E12E0032 for ; Tue, 29 Apr 2008 05:37:09 -0300 (ADT) Received: from westnet.com (localhost [127.0.0.1]) by westnet.com (8.14.0/8.14.0) with ESMTP id m3T8b8d4001633; Tue, 29 Apr 2008 04:37:08 -0400 (EDT) Received: from localhost (gsmith@localhost) by westnet.com (8.14.0/8.13.2/Submit) with ESMTP id m3T8b7mc001629; Tue, 29 Apr 2008 04:37:07 -0400 (EDT) X-Authentication-Warning: westnet.com: gsmith owned process doing -bs Date: Tue, 29 Apr 2008 04:37:07 -0400 (EDT) From: Greg Smith X-X-Sender: gsmith@westnet.com To: Gauri Kanekar cc: pgsql-performance@postgresql.org Subject: Re: Replication Syatem In-Reply-To: <7e4ba9550804282246p73c62fc6y5e7cb7dda55a6b50@mail.gmail.com> Message-ID: References: <7e4ba9550804280638ie81dc23w472785c83cd38fcf@mail.gmail.com> <7e4ba9550804280705q43c51e66l104a5ff9272bbd4d@mail.gmail.com> <20080428162243.GN2970@commandprompt.com> <7e4ba9550804282246p73c62fc6y5e7cb7dda55a6b50@mail.gmail.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.292 tagged_above=0 required=5 tests=AWL=-1.607, FS_REPLICA=1.179, SARE_SPEC_REPLICA=0.72 X-Spam-Level: X-Archive-Number: 200804/427 X-Sequence-Number: 29920 On Tue, 29 Apr 2008, Gauri Kanekar wrote: > We do vacuum full, as vacuum verbose analyse dont regain space for us. Ah, now we're getting to the root of your problem here. You expect that VACUUM should reclaim space. Whenever you UPDATE a row, it writes a new one out, then switches to use that version. This leaves behind the original. Those now unused rows are what VACUUM gathers, but it doesn't give that space back to the operating system. The model here assumes that you'll need that space again for the next time you UPDATE or INSERT a row. So instead VACUUM just keeps those available for database reuse rather than returning it to the operating system. Now, if you don't VACUUM frequently enough, this model breaks down, and the table can get bigger with space that may never get reused. The idea is that you should be VACUUMing up now unneeded rows at about the same rate they're being re-used. When you don't keep up, the database can expand in space that you don't get back again. The right answer to this problem is not to use VACUUM FULL; it's to use regular VACUUM more often. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD