• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. Query Performance Optimization
  5. Tuning Queries
  6. Routinely Maintaining Tables

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.

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.

Procedure

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

  2. 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
    NOTE:

    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.