症状

• Slow Query Speed
• Very Large Table space

ソリューション

Perform a VACUUM. This process, which is similar to garbage collection in Java, reclaims storage occupied by dead tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it's necessary to do VACUUM periodically, especially on frequently-updated tables. If a vacuum has never been performed, then it's likely that this table has a bunch of irrelevant data. The PostgreSQL documentation goes on to say: "We recommend that active production databases be vacuumed frequently (at least nightly), in order to remove expired rows."

Helpful Suggestions

Configure PostgreSQL Vacuum to occur on a nightly basis or during a period of limited use of the DB server, as it causes high I/O consumption.

Vacuum parameter information: http://www.postgresql.org/docs/8.1/static/sql-vacuum.html

Auto Vacuum documentation: http://www.postgresql.org/docs/9.1/static/runtime-config-autovacuum.html