Housekeeping Jobs

PostgreSQL requires certain routine jobs to run so it can keep operating efficiently. Failure to vacuum or analyze tables can cause severe performance degradation and even service outages. However, because running maintenance can take resources away from the application workload, it can cause problems of its own. Achieving the right configuration and cadence can be a fraught endeavor.

Vacuum

Experienced DBAs use either their own scripts or PostgreSQL’s autovacuum feature to automate database maintenance. Autovacuum is a helpful tool, but it uses a simple heuristic and has a number of configuration parameters. Using it properly can be an art.

AutoDBA handles maintenance differently. Benefitting from CrystalDB’s cloud native architecture, it provides additional resources to maintenance operations, so they do not need to share resources with the application workload. Many of the remaining decisions then reduce to cost optimization. For example, vacuuming reclaims unused space in table files, reducing the need for storage. AutoDBA also detects circumstances where deferred vacuuming causes increased query latency or threatens availability when the database runs out of 32-bit transaction IDs (XID wraparound).

Analyze

When selecting a query plan, the PostgreSQL query optimizer relies on estimates of the sizes of inputs and intermediates. These estimates are derived from statistics that are collected ahead of time and stored. These statistics become inaccurate if the size or composition of records in the table changes, which can lead the query planner to select slow and expensive queries.

AutoDBA makes sure that these estimates are up to date by running an analyze job to update statistics whenever it detects that the data distribution may have changed. It throttles this process to ensure that it never interferes with foreground processing.