Documentation

Add or Modify SORT and DIST Keys

Abstract

SummaryOverview of possible Redshift cluster performance enhancements via configuring SORT and DIST keys.

You can improve the performance of your Redshift queries through the proper use of SORT and DIST keys.

The SORT key determines the order in which rows are loaded when initially loaded into a table. Choosing the right key for sorting can lead to faster query times as large sections of data can be skipped while processing the query. Alooma does not create any SORT keys automatically.

For more information about SORT keys, including how to choose the best key to use, see https://docs.aws.amazon.com/redshift/latest/dg/c_best-practices-sort-key.html

The DIST key defines the distribution of rows to compute nodes as they're loaded into a table. Optimizing the distribution can improve query time by minimizing the impact of the query optimizer's redistribution step.

Alooma creates the DIST key equal to the primary key that we receive from the source. This is the same key consolidations are based on.

For more information about choosing the DIST key, see https://docs.aws.amazon.com/redshift/latest/dg/c_best-practices-best-dist-key.html

Redshift does not allow modifications of SORT or DIST keys in existing tables. If you want to add or modify SORT or DIST keys in an existing Redshift table, you’ll need to create a new table. The basic process is:

  1. Rename the existing table.

  2. Create a new table with the SORT/DIST keys (re)defined as part of the schema.

  3. Copy the data from the original table to the new table.

  4. Rename the new table to the original table name.

  5. Load the new table.

  6. Drop the old table.

For example:

ALTER TABLE target_table RENAME TO old_target_table;
CREATE TABLE updated_target_table(/*[replicate the original table schema.]*/);
/* The CREATE TABLE is where you copy the schema of the original
table into the updated table and also where you would add/edit
your SORT and DIST keys */
INSERT INTO updated_target_table (SELECT * FROM old_target_table);
ALTER TABLE updated_target_table RENAME TO target_table;
DROP TABLE old_target_table;

Search results

    No results found