The short answer
When you create a table in Amazon Redshift, the distribution style determines how the rows are divided across the cluster slices. Choosing the right distribution style avoids network bottlenecks during query joins.
The four distribution styles
- DISTSTYLE KEY: Redshift hashes the values in a chosen column and places matching keys on the exact same slice. When you join two large tables on that key, Redshift joins the rows locally in memory without sending data across the network.
- DISTSTYLE ALL: A complete copy of the entire table is stored on the first slice of every compute node. This eliminates network movement when joining small against , but it increases storage requirements and slows down write operations.
- DISTSTYLE EVEN: Rows are distributed evenly across slices in a round-robin order without looking at row values. This prevents , but joins require Redshift to redistribute or broadcast rows over the network.
- DISTSTYLE AUTO: Redshift assigns the distribution style automatically. It starts with ALL for small tables. If the table grows past a few million rows, Redshift automatically changes the style to EVEN.
-- Fact table distributed by foreign key
CREATE TABLE sales (
sale_id BIGINT,
customer_id BIGINT,
amount DECIMAL(10,2)
)
DISTSTYLE KEY
DISTKEY(customer_id);
-- Small dimension table copied everywhere
CREATE TABLE status_lookup (
status_code VARCHAR(10),
description VARCHAR(50)
)
DISTSTYLE ALL;Practical selection rule
Use KEY on the primary join column between your two largest tables, such as order identifier or customer identifier. Use ALL on lookup tables that have fewer than 2 or 3 million rows and change rarely. Use EVEN for tables that participate in few joins or do not have a natural join key.