Re: database is growing... 1GB per day basis

On 01/12/2011 01:37 AM, Karayappalli, Raghunath (Raghu) wrote:
> I am using the version 8.2.
>
> I have enabled auto vacuum (assuming that you are referring to the
> "autovacuum = on" configuration).

Autovacuum improves significantly with every Pg release. 8.3 and 8.4
both saw quite big improvements. In 8.2, autovacuum required more tuning
to get it to work well and was less good at handling some things.

I won't be at all surprised if your database file growth is table bloat.
PostgreSQL provides very poor built-in facilities for identifying bloat,
so it's necessary to use nontrivial queries to track it down in
human-friendly form.

http://gkoenig.wordpress.com/2009/11/24/postgres-and-bloated-table/
http://wiki.postgresql.org/wiki/Show_database_bloat
http://wiki.postgresql.org/wiki/VACUUM_FULL

As for why this bloat is ocurring - it could be autovacuum not being
aggressive enough, or it could be issues with long-running transactions.

--
Craig Ringer