Amazon redshift vacuum reindex

Question

I am running a script to get the tables to run vacuum reindex based on interleaved_skew value from svv_interleaved_columns which represents the skew ratio of interleaved columns(interleaved_skew > 1.4) as mentioned in AWS guide. The value 1.00 for interleaved_skew specifies that all the rows are in sorted order and no re index is required.

Now that I have run a vacuum reindex on a table of 8gb data, I expect the interleaved_skew value to go down but its behaving ackwardly and is increasing sometimes. And since my script is picking the tables to run vacuum reindex based on interleaved_skew and as the value is not going down to 1.00 the same tables are being picked and re index is being run again and that is killing most of my time.

I expect the tables after going through vacuum reindex and there is no flow of data into the table then that particular table should not go through vacuum reindex again as there won't be any skew. But, the tables are being picked again.

Thanks in advance,

Any explanation on stv_interleaved_counts table & how and when the values in svv_interleaved_columns changes would help me greatly

Side-comment: Interleaved indexes are rarely a good idea. They take a lot of overhead for the system to maintain via VACUUM. Are you sure that they are necessary for your use-case? — John Rotenstein
@John Rotenstein After much analysis about how performance increases by using interleaved sort keys we have gone for it as the data is huge and filters include multiple columns in most of the use case — Manideep Surabi

Joe Harris Joe Harris · Accepted Answer · 2019-09-09T17:38:02

Please have a look at our "AnalyzeVacuumUtility" on GitHub. It may provide all the functionality you are looking for.

As far as Interleaved sort keys go, I recommend this style of sort key for only for large tables that are not regularly updated. Compound sort keys will perform better in most circumstances.

Please review our "Advanced Table Design Playbook: Compound and Interleaved Sort Keys" to help with choosing the right style.

Amazon redshift vacuum reindex

1 Answers