skip to content

In Amazon Redshift, what do the DISTSTYLE options KEY, ALL, EVEN and AUTO do?

level: juniorimportance: must knowfreq 85%

answer

  1. Every row has to live on some slice
  2. The rule decides whether a join needs the network
  3. One option duplicates the table everywhere
  4. Another hashes a column you join on
  5. Four names: KEY, ALL, EVEN, and the default

basics

~20 s

DISTSTYLE decides how a Redshift table's rows are spread across compute slices. KEY hashes one column so equal values co-locate, ALL puts a full copy on every node, EVEN round-robins rows, and AUTO lets Redshift pick and change it.

solid answer

~50 s

Redshift is a shared-nothing MPP engine: each compute node is split into slices, and every row of every table must be assigned to one. `DISTSTYLE` is that assignment rule. - **KEY** — you name one `DISTKEY` column; Redshift hashes its value and sends the row to the matching slice, so all rows sharing a value sit together. Two tables with the same DISTKEY joined on it need no data movement. - **ALL** — a full copy of the table lives on every node, so any join against it is local. You pay storage times the node count and slower writes. - **EVEN** — round-robin across slices, ignoring values. Perfectly balanced, never co-located. - **AUTO** — the default. Redshift starts a small table as ALL, switches it to EVEN as it grows, and Automatic Table Optimization may promote it to KEY based on observed queries. One DISTKEY per table, and you can change the style later with `ALTER TABLE ... ALTER DISTSTYLE`.

code

sql · 17 lines
sql
CREATE TABLE fact_orders (
  order_id    BIGINT,
  customer_id BIGINT,
  order_ts    TIMESTAMP,
  amount      DECIMAL(12,2)
)
DISTSTYLE KEY
DISTKEY (customer_id)
SORTKEY (order_ts);

CREATE TABLE dim_customer (
  customer_id BIGINT,
  name        VARCHAR(200),
  segment     VARCHAR(50)
)
DISTSTYLE ALL
SORTKEY (customer_id);

go deeper

for a junior

Be able to name the four options and say in one sentence what each does with a row. Knowing that KEY co-locates matching values and ALL copies the table everywhere is the bar here.

for a middle

Explain the hashing mechanism, why co-location removes network traffic, and the storage and write cost of ALL. Be ready to justify a choice for a given table size and join pattern.

for a senior

Show you have chosen these under pressure: skew from a lumpy or NULL-heavy DISTKEY, the one-DISTKEY constraint in a star schema, and when to change a style in place on a live cluster.

for a principal

Own the policy across a schema — where AUTO is acceptable, where explicit keys are mandated, how you measure whether the distribution design is still right as the workload shifts.

## Why distribution exists at all Amazon Redshift is a shared-nothing massively parallel processing (MPP) database. A cluster has one leader node that parses SQL and builds plans, and a set of compute nodes that do the work. Each compute node is subdivided into **slices** — independent execution units, each owning its own share of the node's memory and disk and processing only the rows it holds. Nothing is shared between slices except the network. That design forces a question on every table: *which slice holds which row?* `DISTSTYLE` is the answer. It is a per-table property, set in `CREATE TABLE` and changeable later, and it is one of the two decisions (the other being the sort key) that dominates Redshift query performance. The reason it matters is network traffic. If a join's matching rows already live on the same slice, the join runs entirely locally and the network is idle. If they don't, Redshift must move rows between nodes during the query — and on a wide fact table that movement can dwarf the actual join work. ## DISTSTYLE KEY You nominate exactly one column as the `DISTKEY`. Redshift hashes each row's value in that column and routes the row to the slice that hash maps to. The guarantee is simple: **all rows with the same value land on the same slice**. That guarantee is what buys co-location. If `fact_orders` is distributed on `customer_id` and `dim_customer` is also distributed on `customer_id`, then for any customer both the fact rows and the dimension row sit on one slice, and a join on `customer_id` never touches the network. The risks are the flip side of the guarantee. If the column's values are unevenly distributed — a few customers holding most orders, or a large block of NULLs, which all hash to a single slice — one slice ends up with far more rows than the rest. That is **distribution skew**, and it makes the whole query run at the speed of the busiest slice while the others idle. A good DISTKEY is high-cardinality, roughly uniform, and stable enough that you actually join on it. ## DISTSTYLE ALL Every node stores a full copy of the table. A join against an ALL-distributed table is always local, whatever the other table's distribution, because the matching row is guaranteed to be present wherever it is needed. You pay for that twice. Storage is multiplied by the number of nodes, and every `INSERT`, `UPDATE`, `DELETE` and `COPY` has to write to every node, so loads are slower. That makes ALL the right answer for **small, slowly-changing dimension tables** — a few million rows of country, product or customer attributes that get joined constantly and rewritten rarely — and a bad answer for anything large or write-heavy. ## DISTSTYLE EVEN Rows are assigned round-robin across slices with no reference to their contents. Storage balance is essentially perfect and skew is impossible, but no join is ever co-located: any join on an EVEN table requires Redshift to redistribute or broadcast one side at query time. EVEN is the honest choice when a table is not joined (staging tables, scan-and-aggregate tables), or when no candidate column is simultaneously the join key, high-cardinality and uniform. ## DISTSTYLE AUTO AUTO is what you get when a `CREATE TABLE` names no distribution style. Redshift manages the choice itself: a small table starts as ALL, and once it grows past an internal threshold Redshift converts it to EVEN. With Automatic Table Optimization enabled, Redshift can also observe the workload and promote a table to KEY on a column it sees being joined on. Conversions happen in the background, so a table's style can change without you doing anything. AUTO is a good default for tables whose access pattern you cannot predict yet. It is not a substitute for design when you already know the join graph — it needs to watch queries before it can act, and it will never beat a correct explicit key that you could have set on day one. ## Making the choice A practical order of questions: 1. Is the table small and joined frequently? → `DISTSTYLE ALL`. 2. Is it large, and is there one column it is almost always joined on, with many distinct and evenly spread values? → `DISTSTYLE KEY` on that column. 3. Otherwise → `EVEN`, or leave it `AUTO`. The hard constraint is that a table has **one** DISTKEY. A fact table joined to four dimensions can co-locate at most one of those joins, so you spend the key on the join that moves the most data — usually the largest dimension or the one that appears in the hottest query. ## Changing it later Distribution is not a one-way door. `ALTER TABLE tbl ALTER DISTSTYLE KEY DISTKEY (col)`, `ALTER DISTSTYLE EVEN`, `ALTER DISTSTYLE ALL` and `ALTER DISTSTYLE AUTO` all exist, and Redshift rewrites the table in the background rather than requiring you to unload and reload. That means it is reasonable to ship a first guess, measure real query plans, and correct it.

  • How many DISTKEY columns can a Redshift table have, and what does that limit mean for a star schema?
    Exactly one. A fact table can therefore co-locate at most one of its dimension joins, so you spend the DISTKEY on the join that moves the most data — typically the largest dimension — and handle the rest with DISTSTYLE ALL on the small dimensions or accept redistribution for the cold ones.
  • Why do NULL values in a DISTKEY column cause trouble?
    All rows sharing a DISTKEY value hash to one slice, and NULL is no exception — every NULL row lands on a single slice. A column that is 30% NULL therefore puts 30% of the table on one slice, producing severe skew and a query that runs at that slice's speed.
  • If you set DISTSTYLE ALL on a 500 GB table in a 10-node cluster, what happens?
    You store roughly 5 TB, since each node keeps a full copy, and every write fans out to all ten nodes, so loads and DML slow down proportionally. ALL only pays off for tables small enough that the duplication is cheap relative to the join savings.

Think of a warehouse with many workers, each with their own shelf. DISTKEY files every box with the same label on one worker's shelf; ALL gives every worker a copy of the small reference binder; EVEN just deals boxes out like cards.

saying these in an interview costs you the question

  • Thinking DISTSTYLE ALL means rows are spread evenly across all nodes
  • Believing the DISTKEY is an index that speeds up filtering
  • Saying a table can have several DISTKEY columns
  • Assuming distribution style cannot be changed after CREATE TABLE
  • Treating EVEN as always safe, ignoring that it forces data movement on joins

context

resolving links…