Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id BAB8D64FD0B for ; Mon, 24 Nov 2008 12:23:52 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 52876-10 for ; Mon, 24 Nov 2008 12:23:48 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.171]) by mail.postgresql.org (Postfix) with ESMTP id F0F4C64FD22 for ; Mon, 24 Nov 2008 12:23:47 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k40so778514ugc.7 for ; Mon, 24 Nov 2008 08:23:45 -0800 (PST) Received: by 10.66.241.13 with SMTP id o13mr1945623ugh.31.1227543824941; Mon, 24 Nov 2008 08:23:44 -0800 (PST) Received: from oxford.xeocode.com.enterprisedb.com ([87.127.95.198]) by mx.google.com with ESMTPS id x37sm3052338ugc.50.2008.11.24.08.23.43 (version=TLSv1/SSLv3 cipher=RC4-MD5); Mon, 24 Nov 2008 08:23:44 -0800 (PST) To: Heikki Linnakangas Cc: Tom Lane , PostgreSQL-development Subject: Re: Visibility map, partial vacuums In-Reply-To: <492ACE5C.70905@enterprisedb.com> (Heikki Linnakangas's message of "Mon\, 24 Nov 2008 17\:55\:08 +0200") User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux) X-Draft-From: ("nnimap+imap.gmail.com:HACKERS" 49438) References: <4905AE17.7090305@enterprisedb.com> <491D376B.9000608@enterprisedb.com> <491D7F52.6070908@enterprisedb.com> <4925664C.3090605@enterprisedb.com> <26361.1227467112@sss.pgh.pa.us> <492A6032.6080000@enterprisedb.com> <18086.1227537479@sss.pgh.pa.us> <87ljv9rvv0.fsf@oxford.xeocode.com> <492ACE5C.70905@enterprisedb.com> From: Gregory Stark Organization: EnterpriseDB Date: Mon, 24 Nov 2008 16:23:42 +0000 Message-ID: <87bpw5rt2p.fsf@oxford.xeocode.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200811/1586 X-Sequence-Number: 128298 Heikki Linnakangas writes: > Gregory Stark wrote: >> However I'm a bit puzzled how you could possibly maintain this frozenxid. As >> soon as you freeze an xid you'll have to visit all the other pages covered by >> that visibility map page to see what the new value should be. > > Right, you could only advance it when you scan all the pages covered by the > visibility map page. But that's better than having to scan the whole relation. Is it? It seems like that would just move around the work. You'll still have to visit every page once ever 2B transactions or so. You'll just do it 64MB at a time. It's nice to smooth the work but it would be much nicer to detect that a normal vacuum has already processed all of those pages since the last insert/update/delete on those pages and so avoid the work entirely. To avoid the work entirely you need some information about the oldest xid on those pages seen by regular vacuums (and/or prunes). We would want to skip any page which: a) Has been visited by vacuum freeze and not been updated since b) Has been visited by a regular vacuum and the oldest xid found was more recent than freeze_threshold. c) Has been updated frequently such that no old tuples remain Ideally (b) should completely obviate the need for anti-wraparound freezes entirely. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!