Redshift Auto Sortkeys in ATO

Hello Nilesh,

Automatic table optimization is a self-tuning capability that automatically optimizes the design of tables by applying sort and distribution keys without the need for administrator intervention.

Sorting enables efficient handling of range-restricted predicates by reducing the no. of blocks scanned hence improving the performance. You would typically use most commonly used columns in filters ( WHERE clause ) as sort keys so that it will scan only the data related to the column values and skip scanning rest of the data blocks.

If you don't explicitly define sort key in your table, Amazon Redshift uses automatic table optimization to choose the sort key as it continuously observes how queries interact with tables. If the workload pattern changes and it needs to alter the sort key column, it will do so in the background with minimum impact to the user workload.

Amazon Redshift automatically sorts data (AUTO VACCUM ) in the background to maintain table data in the order of its sort key, so if there are changes to the table and if the process finds the table will benefit from sorting ( vacuum_sort_benefit ) it will do so when the cluster is not heavily utilized.

Please refer to the below docs and let me know if you have any other questions.

https://docs.aws.amazon.com/redshift/latest/dg/t_Creating_tables.html https://docs.aws.amazon.com/redshift/latest/dg/t_Sorting_data.html