Routine maintenance table
In order to ensure the effective operation of the database, the database must periodically perform VACUUM FULL and ANALYZE based on customer scenarios after inserting/deleting operations, and update statistical information to obtain better performance.
Related concepts
Use the VACUUM, VACUUM FULL, and ANALYZE commands to regularly maintain each table for the following reasons:
VACUUM FULL can reclaim the disk space occupied by updated or deleted data, and merge small data files at the same time.
VACUUM maintains a visualization map for each table to keep track of the pages containing arrays that are visible to other active transactions. A normal index scan first fetches the corresponding array through the visualization map to check whether it is visible to the current transaction. If it cannot be obtained, then check it by grabbing the heap array. So updating the visual map of the table can speed up unique index scans.
VACUUM can avoid the loss of original data caused by transaction ID overlap when the number of executed transactions exceeds the database threshold.
ANALYZE collects statistics related to the contents of tables in the database. Statistical results are stored in the system table PG_STATISTIC. The query optimizer uses these statistics to generate the most efficient execution plan.
Steps
Use the VACUUM or VACUUM FULL command to reclaim disk space.
VACUUM :
VACUUM the table
openGauss=# VACUUM customer;
VACUUM
Can run in parallel with database operation commands. (During execution, statements that can be used normally: SELECT, INSERT, UPDATE, and DELETE. Statements that cannot be used normally: ALTER TABLE).
Perform VACUUM operations on table partitions
openGauss=# VACUUM customer_par PARTITION ( P1 );
VACUUM
VACUUM FULL :
openGauss=# VACUUM FULL customer;
VACUUM
An exclusive lock needs to be added to the table being executed, and all other database operations need to be stopped.
Use the ANALYZE statement to update statistics.
openGauss=# ANALYZE customer;
ANALYZE
Use the ANALYZE VERBOSE statement to update statistics and output table-related information.
openGauss=# ANALYZE VERBOSE customer;
ANALYZE
You can also execute the VACUUM ANALYZE command at the same time for query optimization.
openGauss=# VACUUM ANALYZE customer;
VACUUM
Description: VACUUM and ANALYZE cause a large increase in I/O traffic, which may affect the performance of other active sessions. Therefore, it is recommended to use the "vacuum_cost_delay" parameter to set "GUC Parameter Description > Resource Consumption > Cost-Based Cleaning Delay" in the "Developer Guide".
drop table.
openGauss=# DROP TABLE customer;
openGauss=# DROP TABLE customer_par;
openGauss=# DROP TABLE part;
When the result is displayed as the following information, it means the deletion is successful.
DROP TABLE
maintenance advice
Regularly perform VACUUM FULL for some large tables, and perform VACUUM FULL for the entire database after the performance declines. Currently, VACUUM FULL is tentatively scheduled to be performed once a month.
Regularly do VACUUM FULL on the system table, mainly PG_ATTRIBUTE.
Enable the system automatic cleaning thread (AUTOVACUUM) to automatically execute VACUUM and ANALYZE, reclaim the record space marked as deleted, and update the statistical data of the table.
Top comments (0)