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.