Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 4332B2E004C for ; Fri, 16 May 2008 11:44:38 -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 74046-02 for ; Fri, 16 May 2008 11:44:28 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by developer.postgresql.org (Postfix) with ESMTP id E9A222E0043 for ; Fri, 16 May 2008 11:44:34 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m4GEiVLj016997; Fri, 16 May 2008 10:44:31 -0400 (EDT) To: Dennis Wang cc: Postgresql Help Subject: Re: pg_subtrans directory grows over 1 GB, is there a way to control it? In-reply-to: References: Comments: In-reply-to Dennis Wang message dated "Fri, 16 May 2008 19:28:15 +1030" Date: Fri, 16 May 2008 10:44:31 -0400 Message-ID: <16996.1210949071@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200805/13 X-Sequence-Number: 6737 Dennis Wang writes: > I am using PostgreSQL database in a location recognition application, the files under postgresql/cluster is growing and over a few days it become very large as below. > /var/opt/postgresql/cluster/data 166015 Kilobytes (the actual database data) > /var/opt/postgresql/cluster/sys/pg_clog 74188 Kilobytes (transaction commit status data) > /var/opt/postgresql/cluster/sys/pg_subtrans 1187032 Kilobytes (transaction status data) I think the only way pg_subtrans can get that large is if you have a client that's been sitting holding an open transaction for a very long time. Take a look in pg_stat_activity. The expected size of pg_clog varies depending on what PG version you're using. In recent releases you can alter autovacuum_freeze_max_age to trade off the size of pg_clog against the frequency of forced vacuums. regards, tom lane