VACUUM and ANALYZE commands - AWS Prescriptive Guidance

VACUUM and ANALYZE commands

VACUUM garbage-collects and optionally analyzes a database. For most applications, it's sufficient to let the autovacuum daemon perform vacuuming. However, some administrators might want to modify database parameters for autovacuum, or supplement or replace the daemon's activities by using manually managed VACUUM commands that can be run according to a scheduler.

VACUUM reclaims storage that is occupied by dead tuples. In standard PostgreSQL operations, when tuples are deleted or made obsolete by an update, they aren't physically removed from tables until a VACUUM operation is performed. Therefore, we recommend that you run VACUUM periodically, especially on tables that are updated frequently.

Tuning VACUUM parameters is particularly important in Amazon RDS for PostgreSQL and Aurora PostgreSQL-Compatible, because these managed database services have different characteristics compared to self-managed PostgreSQL databases. These differences can affect the performance of vacuum operations. Tuning VACUUM parameters is essential to optimize the use of resources and ensure that vacuum operations do not negatively affect the performance and availability of your database system.

Here are some of the parameters that you can use with the VACUUM command in Aurora PostgreSQL-Compatible and Amazon RDS for PostgreSQL:

  • FULL

  • FREEZE

  • VERBOSE

  • ANALYZE

  • DISABLE_PAGE_SKIPPING

  • table_name

  • column_name

VACUUM ANALYZE performs a VACUUM operation followed by an ANALYZE operation for each selected table. It provides an efficient way to perform routine maintenance.

Using the VACUUM command without the FULL option reclaims space for reuse. It doesn't require an exclusive lock on the table, so you can run this command during standard reading and writing operations. However, in most cases, the command doesn't return extra space to the operating system but keeps it available for reuse within the same table. VACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, and allows unused space to be returned to the operating system. This form is much slower and requires an ACCESS EXCLUSIVE lock on each table.

For complete information about these parameters, see the PostgreSQL documentation.

In Aurora and Amazon RDS, autovacuum is a daemon (background utility) process that runs the VACUUM and ANALYZE commands regularly to clean up redundant data in the database and server. Even if you rely on autovacuuming, we recommend that you review and adjust the autovacuum settings discussed in the following sections to ensure optimal performance.