Re: Database size growing over time and leads to performance impact - Mailing list pgsql-admin

From Guillaume Lelarge
Subject Re: Database size growing over time and leads to performance impact
Date
Msg-id 4BAE1913.3090905@lelarge.info
Whole thread Raw
In response to Database size growing over time and leads to performance impact  ("Gnanakumar" <gnanam@zoniac.com>)
List pgsql-admin
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

pgsql-admin by date:

Previous
From: "Tomeh, Husam"
Date:
Subject: Re: Database size growing over time and leads to performance impact
Next
From: Nilesh Govindarajan
Date:
Subject: Socket & TCP connections