Redshift Vacuum No Impact

Question

I have a few large tables (~1/2bn rows) on AWS Redshift. These tables have a small pct_unsorted as they're Vacuum'ed every weekend. The pct_unsorted climbs by 0.5% everyday due to new records. I use SVV_TABLES and SVV_TABLE_INFO to find out the pct_unsorted and vacuum_sort_benefit.

Recently, I have noticed that when I run the Vacuum on these tables on the weekend it seems to have no impact on the pct_unsorted. Is this possible? Is it that the SVV_TABLES and SVV_TABLE_INFO are inaccurate? or is my Vacuum not doing anything?

Can you paste your exact, actual `VACUUM` command, please. – Jun 16 '21 at 15:15 — , Jun 16 '21 at 15:15

score 2 · Accepted Answer · edited Feb 22 '22 at 04:38

By default Vacuum only sorts a table when the percent unsorted is greater than 5%. See: https://docs.aws.amazon.com/redshift/latest/dg/r_VACUUM_command.html

"By default, VACUUM skips the sort phase for any table where more than 95 percent of the table's rows are already sorted. Skipping the sort phase can significantly improve VACUUM performance. To change the default sort or delete threshold for a single table, include the table name and the TO threshold PERCENT parameter when you run VACUUM."

So unless you are changing the "threshold" of the VACUUM command or the percent unsorted is > 5% the command is not sorting your table.

Thank you very much Bill Weiner this was exactly my issue, you've cleared that up for me. — finn871, Jun 17 '21 at 14:04

Redshift Vacuum No Impact

1 Answers1