Vaccuming Redshift interleaved sort keys

Question

I have a couple of tables I have added interleaved sort keys to and used the copy command to bulk load a lot of data into. The documentation states we need to use the Reindex option with Vacuum for interleaved sort keys. Trouble with this is for a table the size of mine it will take forever. I know with other sort keys using just "sort only" option would work fine. What needs more clarity in the documentation is whether Vacuuming using sort only option would work fine on tables with interleaved sort keys. Can someone please clarify whether this is a viable solution.

Answer 1

The answer to your question will depend upon how you are using the table, as different types of usage patterns will create different fragmentation/sparcity issues on your interleaved sort keys. For example, if you commonly delete all rows in the table and re-insert them, your needs will be very different from someone who just adds new rows, and always in sort key order.

Can you provide additional information on your usage patterns?

Answer 2

With interleaved sort keys, you'll need to do a VACUUM REINDEX, which will take longer than a normal VACUUM. I don't think that a VACUUM SORT ONLY will actually work in this case.

From the documentation:

Analyzes the distribution of the values in interleaved sort key columns, then performs a full VACUUM operation. VACUUM REINDEX takes significantly longer than VACUUM FULL because it makes an additional pass to analyze the interleaved sort keys. The sort and merge operation can take longer for interleaved tables because the interleaved sort might need to rearrange more rows than a compound sort.

If a VACUUM REINDEX operation terminates before it completes, the next VACUUM resumes the reindex operation before performing the full vacuum operation.

VACUUM REINDEX is not supported with TO threshold PERCENT.