Message-ID: <4BAE1913.3090905@lelarge.info>
Date: Sat, 27 Mar 2010 15:41:23 +0100
From: Guillaume Lelarge <guillaume@lelarge.info>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; fr;
	rv:1.9.1.8) Gecko/20100227 Thunderbird/3.0.3
MIME-Version: 1.0
To: gnanam@zoniac.com
CC: pgsql-admin@postgresql.org
Subject: Re: Database size growing over time and leads to performance
 impact
References: <004701cacdad$7013e9f0$503bbdd0$@com>
In-Reply-To: <004701cacdad$7013e9f0$503bbdd0$@com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit

Le 27/03/2010 14:00, Gnanakumar a écrit :
> [...]
> We're using PostgreSQL 8.2.  Recently, in our production database, there was
> a severe performance impact..  Even though, we're regularly doing both:
> 
> 1.     VACUUM FULL ANALYZE once in a week during low-usage time and
> 
> 2.     ANALYZE everyday at low-usage time
> 

Which means you can be sure you have bloated indexes.

> Also, we noticed that the physical database size has grown upto 30 GB.  But,
> if I dump the database in the form of SQL and import it locally in my
> machine, it was only 3.2 GB.  Then while searching in Google to optimize
> database size, I found the following useful link:
> 
> http://www.linuxinsight.com/optimize_postgresql_database_size.html
> 
> It says that even vacuumdb or reindexdb doesn't really compact database
> size, only dump/restore does because of MVCC architecture feature in
> PostgreSQL and this has been proven here.
> 

VACUUM doesn't compact a database. VACUUM FULL does for tables. REINDEX
does for index.

And this is why, I think, you have an issue. You do VACUUM FULL each
week, but don't do a REINDEX.

> So, finally we decided to took our production database offline and performed
> dump/restore.  After this, the physical database size has also reduced from
> 30 GB to 3.5 GB and the performance was also very good than it was before.
> 

Not surprising, indexes are recreated.

> Physical database size was found using the following command:
> 
> du -sh /usr/local/pgsql/data/base/<database-oid>
> 
> I also cross-checked this size using
> "pg_size_pretty(pg_database_size(datname))".
> 
> Questions
> 
> 1.     Is there any version/update of PostgreSQL addressing this issue?
> 

If you still want to use VACUUM FULL, then you need to use REINDEX. But
you shouldn't need VACUUM FULL. Configure autovacuum so that your tables
don't get bloated.

> 2.     How in real time, this issues are handled by other PostgreSQL users
> without taking to downtime?
> 

Using the autovacuum to VACUUM and ANALYZE when it's really needed.

> 3.     Any ideas or links whether this is addressed in upcoming PostgreSQL
> version 9.0 release?
> 


-- 
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com