What are the benefits to use VACUUM RECLUSTER in Amazon Redshift?

Resolution

VACUUM RECLUSTER benefits

Unlike the full VACUUM SORT operation, the VACUUM RECLUSTER parameter specifically addresses data clustering and improves performance for queries that use specific column-based sorting.

The VACUUM RECLUSTER parameter is useful for tables that experience frequent inserts and updates that cause fragmented data. When you insert or update large amounts of data, you can use the parameter to post extract, transform, and load (ETL) operations.

Note: It's a best practice to use the VACUUM RECLUSTER parameter for large tables with frequent ingestion and queries that access only the most recent data. For more information, see Parameters.

The VACUUM RECLUSTER parameter reorders only the unsorted portions of a table and leaves already sorted portions intact. This parameter reduces the time needed for the VACUUM operation because it doesn't perform a full merge operation.

To sort only the portions of a table that are unsorted, run the following VACUUM RECLUSTER command:

VACUUM RECLUSTER <table_name>;

Note: Replace table_name with the name of your table.

VACUUM RECLUSTER limitations

You can't use the VACUUM RECLUSTER parameter to merge newly sorted data with the existing sorted AWS Region. You also can't use the parameter to reclaim space for rows marked for deletion.

VACUUM RECLUSTER only performs at the table level. However, the VACUUM RECLUSTER parameter doesn't support tables with interleaved sort keys and doesn't support tables with ALL distribution style.

Also, the VACUUM RECLUSTER parameter doesn't support the TO threshold PERCENT.