Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id D7DAD64FCED for ; Tue, 28 Oct 2008 05:50:00 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 74664-09 for ; Tue, 28 Oct 2008 05:49:53 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from outmail129151.authsmtp.co.uk (outmail129151.authsmtp.co.uk [62.13.129.151]) by postgresql.org (Postfix) with ESMTP id 7458F64FC56 for ; Tue, 28 Oct 2008 05:49:53 -0300 (ADT) Received: from mail-c188.authsmtp.com (mail-c188.authsmtp.com [62.13.128.25]) by punt8.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id m9S8nnvh032697 for ; Tue, 28 Oct 2008 08:49:49 GMT Received: from [192.168.0.3] (85-211-99-122.dyn.gotadsl.co.uk [85.211.99.122]) (authenticated bits=0) by mail.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id m9S8nmAu041392 for ; Tue, 28 Oct 2008 08:49:48 GMT Subject: VACUUMs and WAL From: Simon Riggs To: pgsql-hackers Content-Type: text/plain Date: Tue, 28 Oct 2008 08:49:45 +0000 Message-Id: <1225183785.3971.114.camel@ebony.2ndQuadrant> Mime-Version: 1.0 X-Mailer: Evolution 2.12.0 Content-Transfer-Encoding: 7bit X-Server-Quench: 60c2697d-a4cd-11dd-aecc-001871e930f4 X-AuthRoute: OCdxZQATClZOTQEd DAteCiNZVAwpPBRK HVkIKg5MOFUSTAAU Mk9RLVdJK3UEQkpF VCReGBUITgIzDi13 YxUuPkFCYEpOVxVq WEJNS1hWHEtgBAID Bx8AWxBsdgdEZ3x1 Z0N9Xj4GVSdyKxsM B05QFG4PZylhaGEW HkZYJAFSeQQeY05B OQRiVXJZNXhWMykz WldrZmBuZDgOc3xb HhczBHkvfGMQVjc6 S1g9NBFH X-Authentic-SMTP: 61633235383639.cat.dmpriest.net.uk:1378/Kp X-Report-SPAM: If SPAM / abuse - report it at: http://www.authsmtp.com/abuse X-Virus-Status: No virus detected - but ensure you scan with your own anti-virus system! 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: 200810/1376 X-Sequence-Number: 126393 Looking at a VACUUM's WAL records makes me think twice about the way we issue a VACUUM. 1. First we scan the heap, issuing a HEAP2 clean record for every block that needs cleaning. 2. Then we scan the index, issuing WAL records as appropriate. 3. Then we rescan the heap, issuing a HEAP2 clean record for every block. I don't see a reason why we would issue 2 WAL records per block for a VACUUM, nor why we would prune and remove in two steps, dirtying the block each time. Seems like we could write approximately half the amount of data that we do. Surely we can come up with a better plan than that one? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support