What are the most effective techniques for optimizing Redshift sort and distribution keys?

1 What are sort and distribution keys?

Sort and distribution keys are two types of table properties that you can define when you create or alter a table in Redshift. They affect how Redshift stores and accesses your data, and therefore, how fast your queries run. Sort keys determine the order of the rows within each block of data on disk, while distribution keys determine how the rows are distributed across the nodes and slices of the cluster. By choosing the right sort and distribution keys, you can reduce the amount of disk I/O, network traffic, and data redistribution that your queries need, and improve the performance and efficiency of your data warehouse.

2 How to choose sort keys?

There are two types of sort keys in Redshift: compound and interleaved. Compound sort keys sort the rows by the first column in the key, then by the second column, and so on. Interleaved sort keys sort the rows by all the columns in the key in a balanced way. The choice of sort key depends on your query patterns and data characteristics. Generally, compound sort keys are more suitable for queries that filter or join on the first few columns in the key, while interleaved sort keys are more suitable for queries that filter or join on multiple columns in the key. However, interleaved sort keys also require more maintenance and can degrade over time, so you need to monitor and analyze their performance regularly.

3 How to choose distribution keys?

There are three types of distribution styles in Redshift: even, key, and all. Even distribution randomly distributes the rows across the nodes and slices, without using a distribution key. Key distribution uses a distribution key column to hash the rows and assign them to the nodes and slices. All distribution replicates the entire table to every node and slice. The choice of distribution style depends on the size and frequency of your tables and queries. Generally, even distribution is good for large tables that are not frequently joined, key distribution is good for large tables that are frequently joined on the distribution key column, and all distribution is good for small tables that are frequently joined with other tables.

5 How to test and compare sort and distribution keys?

Testing and comparing the effects of different sort and distribution keys on your queries can be done using methods and metrics. To do this, you can use the CREATE TABLE AS or CTAS command to create temporary tables with different sort and distribution keys, the ALTER TABLE APPEND or ATA command to append data from one table to another, or the ALTER TABLE command to change the sort and distribution keys of an existing table. The metrics you can use to measure and compare the performance of your queries include execution time, query throughput, scan time, sort time, join time, and redistribute time. By utilizing these methods and metrics, you can evaluate which sort and distribution keys best suit your data and queries.

6 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?