Understanding Redshift's sorting and distribution styles for efficient data querying.

25 Oct 2023

When working with Amazon Redshift, understanding the sorting and distribution styles of your data is crucial for optimizing query performance. Redshift’s sorting and distribution styles determine how the data is stored and distributed across the compute nodes, which directly impacts the efficiency and speed of your data querying.

Sorting Styles

Redshift provides two sorting options for tables: compound and interleaved.

Compound Sorting

Compound sorting involves defining multiple columns as sort keys. Redshift sorts the data first by the first column, then within each group of identical values in the first column, it sorts by the second column, and so on. Compound sorting works well when your queries frequently involve filtering on specific columns or when you have a hierarchical order in your data.

To create a compound sort key, use the SORTKEY clause when creating or altering a table:

CREATE TABLE example_table (
    column1 datatype,
    column2 datatype,
    ...
    SORTKEY (column1, column2)
);

Interleaved Sorting

Interleaved sorting allows you to define multiple columns as sort keys with equal importance. Unlike compound sorting, interleaved sorting distributes the data more evenly among the slices, providing a better balance of data distribution.

To create an interleaved sort key, use the INTERLEAVED SORTKEY clause:

CREATE TABLE example_table (
    column1 datatype,
    column2 datatype,
    ...
    INTERLEAVED SORTKEY (column1, column2)
);

Distribution Styles

Redshift offers three distribution styles for tables: EVEN, KEY, and ALL.

EVEN Distribution

The EVEN distribution style evenly distributes the rows across all the compute nodes, without considering any specific column. It provides good performance for read-intensive workloads but may lead to uneven data distribution for join queries.

To use the EVEN distribution style, specify it when creating or altering a table:

CREATE TABLE example_table (
    column1 datatype,
    column2 datatype,
    ...
) 
DISTRIBUTED EVEN;

KEY Distribution

The KEY distribution style distributes the rows based on the values of one or more chosen columns. Redshift hashes the values of the chosen columns and distributes the data among slices based on the hash codes. The chosen column(s) should have high cardinality and be frequently used for joining tables.

To use the KEY distribution style, specify the column(s) when creating or altering a table:

CREATE TABLE example_table (
    column1 datatype,
    column2 datatype,
    ...
) 
DISTRIBUTED BY (column1);

ALL Distribution

The ALL distribution style makes a copy of the entire table on each slice. This style is suitable when you have small dimension tables that are frequently joined or when you need to optimize certain types of queries. ALL distribution should be used sparingly, as it increases storage requirements and doesn’t scale well for large tables.

To use the ALL distribution style, specify it when creating or altering a table:

CREATE TABLE example_table (
    column1 datatype,
    column2 datatype,
    ...
) 
DISTRIBUTED ALL;

Conclusion

Understanding Redshift’s sorting and distribution styles is essential for optimizing query performance. Choosing the right sort and distribution keys based on your data and query patterns can significantly improve the efficiency of your data querying operations. Consider experimenting with different configurations and monitoring the performance to determine the best setup for your specific use case.

References:

#redshift #database