Explanation of the VACUUM Syntax for Routine Maintenance of GBase 8c Database

To ensure the efficient operation of the GBase 8c database, it is necessary to perform regular maintenance. It is recommended to execute VACUUM FULL and ANALYZE commands periodically after performing insert or delete operations to update statistical information and achieve better performance.

Related Concepts

VACUUM FULL

VACUUM FULL reclaims disk space occupied by updated or deleted data and consolidates small data files.

VACUUM maintains a visibility map for each table to track pages containing rows visible to other active transactions. A typical index scan first checks the visibility map to find corresponding arrays and determine whether they are visible to the current transaction. If this fails, it falls back to heap tuple checks. Updating the visibility map can accelerate unique index scans.

VACUUM can prevent data loss due to transaction ID wraparound when the number of transactions exceeds the database threshold.

ANALYZE

ANALYZE collects statistical information about the contents of tables in the database. The results are stored in the system table PG_STATISTIC. The query optimizer uses this statistical data to generate the most efficient execution plans.

Operation Steps

1) Reclaim Disk Space Using VACUUM or VACUUM FULL

VACUUM

Performs a VACUUM operation on the table. It can run in parallel with other database operations. During execution, the following commands can be used normally: SELECT, INSERT, UPDATE, and DELETE. However, ALTER TABLE cannot be used.

For example, to perform a VACUUM operation on a regular table:

postgres=# VACUUM customer;
VACUUM

To perform a VACUUM operation on a table partition:

postgres=# VACUUM customer_par PARTITION (P1);
VACUUM

VACUUM FULL

Requires an exclusive lock on the table being executed and needs to stop all other database operations.

For example, to perform a VACUUM FULL operation on a regular table:

postgres=# VACUUM FULL customer;
VACUUM

To perform a VACUUM FULL operation on a table partition:

postgres=# VACUUM FULL customer_par PARTITION (P1);
VACUUM

2) Update Statistics Using ANALYZE

For example, to update statistics on a table:

postgres=# ANALYZE customer;
ANALYZE

3) Update Statistics with Verbose Output

Use the ANALYZE VERBOSE command to update statistics and display information about the table:

postgres=# ANALYZE VERBOSE customer;
ANALYZE

You can also perform query optimization using the VACUUM ANALYZE command:

postgres=# VACUUM ANALYZE customer;
VACUUM

VACUUM and ANALYZE cause a significant increase in I/O traffic, which may impact the performance of other active sessions. Therefore, it is recommended to set the cleanup delay using the vacuum_cost_delay parameter.

4) Dropping Tables

postgres=# DROP TABLE customer;
postgres=# DROP TABLE customer_par;
postgres=# DROP TABLE part;

Maintenance Recommendations

Database users or DBAs should perform regular maintenance operations:

1. Periodically perform VACUUM FULL on some large tables.
2. Perform VACUUM FULL on the entire database when performance degrades.
3. It is recommended to perform VACUUM FULL once a month.
4. Regularly perform VACUUM FULL on system tables, especially PG_ATTRIBUTE.
5. Enable the system's auto-vacuum threads (AUTOVACUUM) to automatically execute VACUUM and ANALYZE, reclaiming space marked as deleted and updating table statistics.

Thank you for reading!