Routinely Maintaining Tables
To ensure proper database running, after insert and deletion operations, you need to routinely run VACUUM FULL and ANALYZE based on customer scenarios and update statistics to obtain better performance.
You need to routinely run VACUUM, VACUUM FULL, and ANALYZE to maintain tables, because:
- VACUUM FULL reclaims disk space occupied by updated or deleted data and combines small-size data files.
- ANALYZE collects statistics on tables in databases. The statistics are stored in the PG_STATISTIC system catalog. Then, the query optimizer uses the statistics to work out the most efficient execution plan.
- Run the VACUUM or VACUUM FULL command to reclaim disk space.
- VACUUM FULL:
VACUUM FULL customer; VACUUM
VACUUM FULL needs to add exclusive locks on tables it operates on and requires that all other database operations be suspended.
- VACUUM FULL:
- Run ANALYZE to update statistics.
ANALYZE customer; ANALYZE
Run ANALYZE VERBOSE to update statistics and display table information.
ANALYZE VERBOSE customer; ANALYZE
You can use VACUUM ANALYZE at the same time to optimize the query.
VACUUM ANALYZE customer; VACUUM
VACUUM and ANALYZE cause a substantial increase in I/O traffic, which may cause poor performance of other active sessions.
- Routinely run VACUUM FULL on large tables. If the database performance deteriorates, run VACUUM FULL on all tables in the database. If the database performance is stable, you are advised to monthly run VACUUM FULL.
- Routinely run VACUUM FULL on system catalogs, especially PG_ATTRIBUTE.
- The automatic cleanup process (AUTOVACUUM) in the system automatically runs the VACUUM and ANALYZE commands to recycle the record space in deleted status and update statistics in the table.