Skip site navigation (1) Skip section navigation (2)

Re: pg_subtrans directory grows over 1 GB, is there a way to control it?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Dennis Wang <dennis_02_2002(at)hotmail(dot)com>
Cc: Postgresql Help <pgsql-interfaces(at)postgresql(dot)org>
Subject: Re: pg_subtrans directory grows over 1 GB, is there a way to control it?
Date: 2008-05-16 14:44:31
Message-ID: 16996.1210949071@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-interfaces
Dennis Wang <dennis_02_2002(at)hotmail(dot)com> 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

In response to

pgsql-interfaces by date

Next:From: clarkhorseDate: 2008-05-19 01:13:52
Subject: Newbie problem with from database in C
Previous:From: Alvaro HerreraDate: 2008-05-16 14:39:39
Subject: Re: pg_subtrans directory grows over 1 GB, is there away to control it?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group