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?
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.