Amazon Redshift VACUUM was not successful

0
votes

My table had 17500000 rows,

I deleted 500k rows. After deleting, I ran the vacuum command.

According to SVV_VACUUM_SUMMARY, row_delta is difference in the total number of table rows before and after the vacuum.

But when I saw the SVV_VACUUM_SUMMARY table, row_delta is 0. Shouldn't it be 500K?

Also here's the output from STL_VACUUM table:

status rows sortedrows

Started 17500000 813055

Finished 17500000 17500000

Does this mean Vacuum wasn't successful as the rows for finished is same as when it was started?

Also SVV_VACUUM_PROGRESS has status as complete for the table. So want to confirm if it was successful or not.

And checked SVV_TABLE_INFO for my table and tbl_rows has 17500000. I don't know what should be done as I think tbl_rows should be 17000000 as the vacuum operation was successful.

Any help would be appreciated.

1
amazon-web-servicesamazon-redshiftvacuum
Did you try setting threshold ? VACUUM DELETE ONLY TO 100 PERCENT – Ravi
have you ran ANALYZE after VACUUM? – AlexYes
@Ravi I did vacuum table_name to 100 percent – Chirag Sejpal
@AlexYes I haven't. Should I? – Chirag Sejpal
@ChiragSejpal try doing that, it might be that svv_table_info relies on outdated table statistics. Please let me know if that worked – AlexYes

1 Answers

3
votes

You can use the view svv_table_info to confirm whether the table is correctly vacuumed.

If, for some reason, running VACUUM my_table TO 100 PERCENT does not result in a change in the unsorted value in svv_table_info you will need to perform a deep copy.