Posted On: Dec 19, 2018

Amazon Redshift now automatically runs the VACUUM DELETE operation to reclaim disk space occupied by rows that were marked for deletion by previous UPDATE and DELETE operations. It also defragments the tables to free up consumed space and improves performance for your workloads.

VACUUM DELETE is scheduled to run based on query load and the number of deleted rows in tables. For example, VACUUM DELETE runs only sporadically during times of high load to reduce the impact on users and queries. Automatic VACUUM DELETE pauses when the incoming query load is high, then resumes later. Routinely scheduled VACUUM DELETE jobs don't need to be modified because Amazon Redshift skips tables that don't need to be vacuumed.

Additionally, all vacuum operations now run only on a portion of a table at a given time rather than running on the full table. This drastically reduces the amount of resources such as memory, CPU, and disk I/O required to vacuum.

You can track when VACUUM DELETE is running in the background by monitoring 'Space reclaimed by auto vacuum delete' on the Cluster Performance tab on the  AWS Management Console and using the Cloudwatch metric AutoVacuumSpaceFreed. For more information, see VACUUM in the Amazon Redshift Database Developer Guide.

Auto VACUUM DELETE is now available with the release version 1.0.5290 or higher in all AWS commercial regions. Refer to the AWS Region Table for Amazon Redshift availability.