PostgreSQL Database Optimization
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Symptoms
- Slow Query Speed
- Very Large Table space
Resolution
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