Help Center > > Developer Guide> Query Performance Optimization> Tuning Queries> Routinely Maintaining Tables

Routinely Maintaining Tables

Updated at: Dec 30, 2020 GMT+08:00

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.

Related Concepts

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.


  1. Run the VACUUM or VACUUM FULL command to reclaim disk space.

      VACUUM FULL customer;

      VACUUM FULL needs to add exclusive locks on tables it operates on and requires that all other database operations be suspended.

  2. Run ANALYZE to update statistics.

    ANALYZE customer;

    Run ANALYZE VERBOSE to update statistics and display table information.

    ANALYZE VERBOSE customer;

    You can use VACUUM ANALYZE at the same time to optimize the query.

    VACUUM ANALYZE customer;

    VACUUM and ANALYZE cause a substantial increase in I/O traffic, which may cause poor performance of other active sessions.

Maintenance Suggestion

  • 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.

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?

Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel