Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id BC1D59FA617 for ; Tue, 6 Mar 2007 07:17:44 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 72329-05 for ; Tue, 6 Mar 2007 07:17:31 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from stark.xeocode.com (unknown [87.127.95.194]) by postgresql.org (Postfix) with ESMTP id EA5219FB263 for ; Tue, 6 Mar 2007 07:12:40 -0400 (AST) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with esmtp (Exim 4.63) (envelope-from ) id 1HOXb5-0006HL-6q; Tue, 06 Mar 2007 11:12:47 +0000 From: Gregory Stark To: "ITAGAKI Takahiro" Cc: "Tom Lane" , Subject: Re: Aggressive freezing in lazy-vacuum In-Reply-To: <20070306181253.64A9.ITAGAKI.TAKAHIRO@oss.ntt.co.jp> (ITAGAKI Takahiro's message of "Tue, 06 Mar 2007 19:03:03 +0900") Organization: EnterpriseDB References: <20070305182401.C4BC.ITAGAKI.TAKAHIRO@oss.ntt.co.jp> <18982.1173110551@sss.pgh.pa.us> <20070306181253.64A9.ITAGAKI.TAKAHIRO@oss.ntt.co.jp> X-Draft-From: ("nnimap+mail01.enterprisedb.com:INBOX" 9475) Date: Tue, 06 Mar 2007 11:12:47 +0000 Message-ID: <87vehebp74.fsf@stark.xeocode.com> User-Agent: Gnus/5.110006 (No Gnus v0.6) Emacs/22.0.50 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.146 tagged_above=0 required=5 tests=AWL, BAYES_50, FORGED_RCVD_HELO, SPF_SOFTFAIL X-Spam-Level: * X-Archive-Number: 200703/374 X-Sequence-Number: 99858 "ITAGAKI Takahiro" writes: > I don't think we can supply such a historical database functionality here, > because we can guarantee it just only for INSERTed tuples even if we pay > attention. We've already enabled autovacuum as default, so that we cannot > predict when the next vacuum starts and recently UPDATEd and DELETEd tuples > are removed at random times. Furthermore, HOT will also accelerate removing > expired tuples. Instead, we'd better to use WAL or something like audit > logs for keeping history information. Well comparing the data to WAL is precisely the kind of debugging that I think Tom is concerned with. The hoped for gain here is that vacuum finds fewer pages with tuples that exceed vacuum_freeze_min_age? That seems useful though vacuum is still going to have to read every page and I suspect most of the writes pertain to dead tuples, not freezing tuples. This strikes me as something that will be more useful once we have the DSM especially if it ends up including a frozen map. Once we have the DSM vacuum will no longer be visiting every page, so it will be much easier for pages to get quite old and only be caught by a vacuum freeze. The less i/o that vacuum freeze has to do the better. If we get a freeze map then agressive freezing would help keep pages out of that map so they never need to be vacuumed just to freeze the tuples in them. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com