Re: Database size growing over time and leads to performance impact

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