REDSHIFT SORTKEY AND DISTKEY HOW TO
REDSHIFT SORTKEY AND DISTKEY MANUAL

Key – The data is distributed across the nodes by the values in the column defined as the DISTKEY.Even – The data in the table is spread evenly across the nodes in a cluster in a round-robin distribution.All – A copy of the entire table is replicated to every node.In Amazon Redshift, the data distribution pattern is determined by two physical table settings: distribution style (DISTSTYLE) and distribution key (DISTKEY).Īmazon Redshift has three distribution styles: Data distribution also affects the performance of GROUP BY operations. This enables Amazon Redshift to join the rows locally on each node without having to move data around the nodes. For example, the optimal way to distribute data for tables that are commonly joined is to store rows with matching join keys on the same nodes. To achieve the best possible query performance, data needs to be distributed across the compute nodes in a way that is optimal to the specific workload that is being run on the cluster. This allows Amazon Redshift to run queries against each compute node in parallel, dramatically increasing query performance.

Distribution keysĪmazon Redshift has a massively parallel processing (MPP) architecture, where data is distributed across multiple compute nodes (see the following diagram). In this section, I give a high-level overview of distribution and sort keys, then I explain how they’re automatically set by ATO.

REDSHIFT SORTKEY AND DISTKEY MANUAL

Finally, I present the results of a test that show ATO improved performance on this benchmark, without requiring any manual tuning. Then I outline the steps to set up and run a test of ATO on the Cloud DW benchmark derived from TPC-H using a 30 TB dataset.

REDSHIFT SORTKEY AND DISTKEY HOW TO

I also explain how ATO works and how to enable and disable it. In this post, I explain what sort and distribution keys are and how they improve query performance. ATO automatically monitors a cluster’s workload and table metadata, runs artificial intelligence algorithms over the observations, and implements sort and distribution keys online in the background, without requiring any manual intervention, and without interrupting any running queries. At AWS re:Invent 2020, Amazon Redshift announced a new feature to automate this process: automatic table optimization (ATO). More recently, Amazon Redshift Advisor provided suggestions, but these still had to be manually implemented. In the past, setting sort and distribution keys was an involved manual process that required a skilled resource to analyze a cluster’s workload and choose and implement the right keys for every table in the data model. In Amazon Redshift, you implement this by setting sort and distribution key table attributes. You do so by sorting table rows and rearranging rows across a cluster’s nodes. Although Amazon Redshift has excellent query performance out of the box, with up to three times better price performance than other cloud data warehouses, you can further improve its performance by physically tuning tables in a data model. Amazon Redshift is a cloud data warehouse database that provides fast, consistent performance running complex analytical queries on huge datasets scaling into petabytes and even exabytes with Amazon Redshift Spectrum.

Redshift sortkey and distkey