X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 1BD8A9FB2B5 for ; Mon, 21 Aug 2006 18:06:40 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 69703-05 for ; Mon, 21 Aug 2006 18:06:30 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 541179FB2C2 for ; Mon, 21 Aug 2006 18:06:30 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k7LL6SYT019273; Mon, 21 Aug 2006 17:06:28 -0400 (EDT) To: "Eamonn Kent" cc: pgsql-performance@postgresql.org Subject: Re: Vacuum not identifying rows for removal.. In-reply-to: <9146E3EBBFBCC94D95F95A1C4065348A837AB1@exch01.xsigo.com> References: <9146E3EBBFBCC94D95F95A1C4065348A837AB1@exch01.xsigo.com> Comments: In-reply-to "Eamonn Kent" message dated "Mon, 21 Aug 2006 11:50:02 -0700" Date: Mon, 21 Aug 2006 17:06:28 -0400 Message-ID: <19272.1156194388@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.162 tagged_above=0 required=5 tests=AWL, SPF_HELO_PASS, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/319 X-Sequence-Number: 20504 "Eamonn Kent" writes: > I am using PostgreSQL 8.1.4 for an embedded application. For some > reason, vacuum is not able to identify rows that are candidates for > removal (i.e., mark space as available). > ... > We run auto vacuum and I can see from the logs that it is running quite > frequently. When I run vacuum full from the psql, I can see that space > is not being recovered. I have run vacuum full with the verbose flag > set, I can see that messages that indicate the existence of "dead row > versions that cannot be removed yet. This means you've got an open transaction somewhere that could potentially still be able to see those rows. Look around for applications sitting "idle in transaction". regards, tom lane