PostgreSQL 10 High Performance - Third Edition

Vacuuming and statistics

PostgreSQL databases require two primary forms of regular maintenance as data is added, updated, and deleted.

VACUUM cleans up after old transactions, including removing information that is no longer visible and returning freed space to where it can be reused. The more often you update and delete information from the database, the more likely you'll need a regular VACUUM cleaning regime. However, even static tables with data that never changes once inserted still need occasional care here. ANALYZE looks at tables in the database and collects statistics about them—information such as estimates of how many rows they have and how many distinct values are in there. Many aspects of query planning depend on this statistics ...