Cluster maintenance tasks
PolarDB clusters require periodic maintenance and cleanup. The autovacuum daemon process typically handles these tasks. You can adjust autovacuum parameters to optimize performance based on your business needs. You can also use the VACUUM command to manually manage background processes. This command is usually run from a cron or task scheduler script.
Background
You should run VACUUM operations periodically on PolarDB clusters to address the following issues.
Reclaim disk space occupied by updated or deleted rows.
Update the data statistics information used by the PolarDB query planner.
Update the visibility map to speed up index-only scans.
Protect against data loss from transaction ID wraparound or multi-transaction ID wraparound.
Each of these issues requires running VACUUM operations on PolarDB clusters at different frequencies and scopes. There are two types of VACUUM:
Standard VACUUM: Runs in parallel with database operations.
NoteParallel database operations include commands such as SELECT, INSERT, UPDATE, and DELETE. Do not use ALTER TABLE to update a table definition during a cleanup.
VACUUM FULL: Reclaims more disk space than a standard VACUUM but runs more slowly. VACUUM FULL also requires an exclusive lock on the table it is working on and cannot run in parallel with other database operations on that table.
To ensure continuous cluster operation, use the standard VACUUM. In addition, VACUUM operations generate significant I/O traffic, which can affect the performance of other active sessions.
Reclaim disk space
In PolarDB for PostgreSQL, an UPDATE or DELETE operation does not immediately remove the old version of a row. This design is for multiversion concurrency control (MVCC). An old version cannot be deleted if it might still be visible to other transactions. However, when an outdated or deleted row version is no longer needed by any transaction, the space it occupies must be reclaimed for new rows to use. This prevents unlimited growth in disk space requirements. The VACUUM command performs this cleanup.
A standard VACUUM removes dead row versions from tables and indexes and marks the space for future reuse. However, VACUUM does not return the space to the operating system unless one or more pages at the end of the table become completely empty and an exclusive table lock can be easily obtained. In contrast, VACUUM FULL actively compacts a table by writing a completely new version of the table file without the dead space. This minimizes the table's size but takes a long time to complete. It also requires extra disk space for the new copy of the table until the operation is finished.
The goal of routine cleanup is to run standard VACUUM operations frequently enough to avoid needing VACUUM FULL. The autovacuum daemon process follows this rule and never issues a VACUUM FULL. Under this approach, the goal is not to keep tables at their minimum size, but to maintain a steady state of disk space usage: space occupied by each table = minimum table size + space used up between cleanup runs. Although VACUUM FULL can shrink a table to its minimum size and return the disk space to the operating system, this is not very useful if the table will grow again in the future. Therefore, running standard VACUUM operations is a better approach for maintaining frequently updated tables than running VACUUM FULL infrequently.
Some administrators prefer to schedule cleanups themselves, for example, during periods of low load, such as at night. The difficulty with a fixed schedule is that if a table experiences an unexpected spike in update activity, it might bloat to the point where a VACUUM FULL is required to reclaim space. The autovacuum daemon process mitigates this problem because it dynamically schedules cleanup operations based on update activity. Do not disable the daemon process completely unless your workload is entirely predictable. A possible compromise is to adjust the autovacuum daemon parameters to react only to unusually high update activity. This ensures that the cluster remains stable, while periodic VACUUM operations handle batch cleanups during normal load.
If you are not using autovacuum, a common practice is to run a database-wide VACUUM operation once a day during a low-load period, and perform more frequent cleanups on heavily updated tables as needed. Some installations with very high update rates might vacuum their busiest tables every few minutes. If your cluster contains multiple databases, make sure to vacuum each one. You can use the vacuumdb tool for this purpose.
When a table accumulates many dead row versions due to extensive update or delete activity, a standard VACUUM might not be sufficient. For this scenario, use VACUUM FULL or one of the table-rewriting variants of CLUSTER or ALTER TABLE. These commands rewrite the entire table and build new indexes for it. All of these options require an exclusive lock. Note that the old table and indexes are not released until the new ones are complete. This temporarily requires extra disk space roughly equal to the size of the table.
If you have a table whose entire contents are periodically deleted, use TRUNCATE instead of DELETE followed by VACUUM. TRUNCATE immediately removes all content from a table without requiring a subsequent VACUUM or VACUUM FULL to reclaim the now-unused disk space. However, be aware of its impact on MVCC.
The autovacuum daemon process
PolarDB for PostgreSQL supports an optional but highly recommended feature called autovacuum, which automates the execution of VACUUM and ANALYZE commands. When enabled, autovacuum checks for tables that have had many tuples inserted, updated, or deleted. These checks rely on the statistics collection feature. You must set track_counts to true to use autovacuum. In the default configuration, autovacuum is enabled and its related parameters are correctly configured.
The autovacuum daemon process actually consists of multiple processes. It includes a persistent background process, the autovacuum launcher, which starts autovacuum worker processes for all databases. The launcher attempts to start a worker process for each database within each autovacuum interval (autovacuum_naptime). Therefore, if an installation has N databases, a new worker process is started every autovacuum_naptime/N seconds. The maximum number of concurrent worker processes is autovacuum_max_workers. If the number of databases to be processed exceeds autovacuum_max_workers, the next database is processed as soon as the first worker process finishes. Each worker process checks every table in its database and runs VACUUM or ANALYZE as needed. You can set log_autovacuum_min_duration to monitor the activity of autovacuum worker processes.
If multiple large tables become eligible for cleanup in a short period, all autovacuum worker processes might be busy cleaning them for a long time. This can prevent other tables and databases from being cleaned until a worker process becomes available. There is no limit on the number of worker processes within a single database, but worker processes try to avoid repeating work already done by other processes. Note that the number of running worker processes does not count toward the max_connections or superuser_reserved_connections limits.
A table is vacuumed when its relfrozenxid value is greater than autovacuum_freeze_max_age. This also applies to tables for which the maximum freeze age is modified using storage parameters. Otherwise, the table is also vacuumed if the number of dead tuples since the last VACUUM exceeds the vacuum threshold. The vacuum threshold is defined as:
Here, the vacuum base threshold is autovacuum_vacuum_threshold, the vacuum scale factor is autovacuum_vacuum_scale_factor, and the number of tuples is pg_class.reltuples. The number of dead tuples is obtained from the statistics collector. It is a semi-accurate count, updated by each UPDATE and DELETE command, because some information might be lost under high load. If the table's relfrozenxid value is greater than the vacuum_freeze_table_age transaction age, the entire table is scanned to freeze old tuples and increment relfrozenxid. Otherwise, only pages that have been modified since the last vacuum are scanned.
A similar threshold is used for ANALYZE:
This threshold is compared with the number of tuples inserted, updated, or deleted since the last ANALYZE.
Temporary tables cannot be accessed by autovacuum. Therefore, you must perform vacuum and analyze operations on temporary tables using SQL commands within the session.
The default thresholds and scale factors are specified in the postgresql.conf cluster configuration file, but you can override these parameters on a per-table basis. If a setting is modified using a table's storage parameters, that value is used when the table is processed. Otherwise, the global settings are used.
When multiple worker processes are running, the autovacuum cost delay parameter is "balanced" among all of them to ensure that the total I/O impact on the system remains constant. However, a worker process is not included in the balancing algorithm if it is processing a table for which the autovacuum_vacuum_cost_delay or autovacuum_vacuum_cost_limit storage parameter is set.
Routine reindexing
In some scenarios, you need to rebuild indexes to maintain their performance. You can do this by periodically running the REINDEX command or using a multi-step independent reconstruction. Periodic index rebuilding helps reclaim and reuse completely idle B-tree index pages. When many key values on a B-tree index page are deleted, the system reclaims the completely idle pages, but partially filled pages can still lead to wasted space. In addition, queries that use a newly built B-tree index are slightly faster than those that use an index that has been updated many times. This is because on a new index, logically contiguous pages are usually also physically contiguous. This consideration does not currently apply to non-B-tree indexes. For these reasons, you should periodically rebuild indexes to improve access speed.
The potential bloat of non-B-tree indexes has not been well quantified. Therefore, if you use non-B-tree indexes, you should periodically monitor their physical size.
For reindexing operations, REINDEX is safe and easy to use in all cases, but the command requires an exclusive lock. Alternatively, you can rebuild indexes using a sequence of creation and replacement steps. This method involves first creating an index using CREATE INDEX CONCURRENTLY. If the index is created successfully and is available, you can replace the original index with the new one using a combination of the ALTER INDEX and DROP INDEX commands. If an index involves a uniqueness or other constraint, you might need to use ALTER TABLE to update the existing constraint to the one enforced by the new index. You must perform careful checks before you use the multi-step reconstruction method. There are restrictions on which indexes can be rebuilt this way, and any errors that occur must be handled promptly.