Long Running VACUUM DELETE Redshift

Hello,

Redshift automatically performs vacuum sort and vacuum delete operations on tables in the background implicitly. These background vacuums run during periods of reduced loads and are paused during periods of high load and that vacuum is not prioritize in comparison to other cluster workload. That is to say when there are high workload on the cluster the vacuum operation will pause to prioritize other workloads. Thus, it's common to see long running vacuum operation during cluster peak hours. So, I feel your case might be normal considering these factors.

Automatic vacuum operations pause if any of the following conditions are met:

• A user runs a data definition language (DDL) operation, such as ALTER TABLE, that requires an exclusive lock on a table that automatic vacuum is currently working on.
• A user triggers VACUUM on any table in the cluster (only one VACUUM can run at a time).
• A period of high cluster load.

Furthermore, below factors may affect the performance of Redshift Auto Vacuum:

• High percentage of unsorted data
• Large table with too many columns
• Interleaved sort key usage
• Irregular or infrequent use of VACUUM
• Concurrent tables, cluster queries, DDL statements, or ETL jobs

You can also override the WLM slot count when you have occasional queries that take a lot of resources in the cluster, such as when you perform a VACUUM operation in the database. see here for ref : https://docs.aws.amazon.com/redshift/latest/dg/tutorial-wlm-query-slot-count.html

To troubleshoot performance issues with VACUUM in Amazon Redshift please see our post: https://repost.aws/knowledge-center/redshift-vacuum-performance