PostgreSQL aims to be easy to maintain. But like any database, heavy activity can lead to a drop in performance due to overhead. The approach taken to handle concurrent read and write scalability in the database can leave behind significant amounts of data that need to be cleaned up properly. Understanding why that happens is valuable for modeling just how often related maintenance needs to occur. Another aspect of maintaining any database server is monitoring how well the queries it runs execute.
In this chapter, we will look into the following topics:
- Transaction visibility with multiversion concurrency control
- Vacuum
- Autoanalyze
- Index bloat
- Dump and restore
- Vacuuming the database/table
- Cluster
- Reindexing
- Detailed data and index page monitoring
- Monitoring query logs
