vacuum delete on kinesis stream based materialized view's underlying table

Question

I have materialized view  mv_abc123 on kinesis stream. When I query the SYS_VACUUM_HISTORY, I found lots of records about table mv_tbl__mv_abc123__0.  most of them are vacuum deletes. This confused me.  As my understood, materialized view from on kinesis stream should be append-only. why redshift do vacuum delete on its underlying table. Also each vacuum delete cost several minutes and totally cost several hours each day. it is abnormal.  we have several materialized view on kinesis stream. but only one has this issue. what's the problem?

Answer

Hello,

To answer your question about why Amazon Redshift performs a vacuum delete, a vacuum delete operation may be performed to clean up tables after a load or a series of incremental updates. When you perform a delete, the rows are marked for deletion, but not removed. Amazon Redshift automatically runs a VACUUM DELETE operation in the background based on the number of deleted rows in database tables. Redshift schedules the VACUUM DELETE to run during periods of reduced load and pauses the operation during periods of high load.

Please see this documentation on [Vacuuming tables](https://docs.aws.amazon.com/redshift/latest/dg/t_Reclaiming_storage_space202.html) for more information.

For a deeper dive on why this would be happening with the underlying table one of your Kinesis Streams Materialized Views and not the others, we require details that are non-public information. Please open a support case with AWS using the following[ link](https://console.aws.amazon.com/support/home#/case/create).

vacuum delete on kinesis stream based materialized view's underlying table

Relevant content