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. But 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. This could be information such as estimates of how many rows they have and how many distinct values are in them. Many aspects of query planning depend on this statistics data being accurate.

There's more about VACUUM in Chapter 7, Routine Maintenance, and the use of statistics is covered as part of Query Optimization.