|
Hi, 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 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. 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. 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? 2.
How in real time, this issues are handled by other
PostgreSQL users without taking to downtime? 3.
Any ideas or links whether this is addressed in
upcoming PostgreSQL version 9.0 release? |
