Redshift Performance Tuning: Distribution and Sort Keys

Two properties of a Redshift table decide most of how fast queries on it run. The distribution style decides which compute node slice each row lives on. The sort key decides the order of rows on disk. Neither changes query results. Both change how much data has to be moved and read to produce them.

Redshift now chooses both automatically by default, and for many tables that is the right setting. Tuning still matters, because understanding the two mechanisms is how you tell whether the automatic choice is working, and what to do when it is not.

Sort keys: skipping blocks

Redshift stores each column in 1 MB blocks and records the minimum and maximum value in each block. When a query filters on a range, the engine compares the range with each block's minimum and maximum and skips blocks that cannot contain matching rows (Redshift docs: sort keys). How many blocks can be skipped depends entirely on how the rows are ordered. If the table is sorted on the filtered column, each block covers a narrow range. If it is not, every block covers nearly the whole range, and none can be skipped.

Distribution: avoiding movement and skew

A cluster's compute nodes are divided into slices, and each row is stored on one slice. A join between two tables needs matching rows in the same place. If they are not, Redshift redistributes rows across the network during the query. The developer guide states the goal plainly: minimise the redistribution step by locating the data where it needs to be before the query runs (Redshift docs: best distribution style).

The four styles (Redshift docs: distribution styles):

KEY distribution has a risk. If a few key values hold most of the rows, a few slices hold most of the data, and the query is only as fast as the fullest slice. That is skew.

Both mechanisms, simulated

To make the two ideas concrete, we simulated them in Python on one month of New York yellow taxi trips (NYC TLC: trip record data), 2,964,624 rows. This is not Redshift: the code cuts the pickup-time column into blocks and applies the minimum and maximum test, then hashes three candidate keys into eight slices.

import numpy as np
import pandas as pd

df = pd.read_parquet("yellow_tripdata_2024-01.parquet",
                     columns=["tpep_pickup_datetime", "PULocationID", "payment_type"])
pickup = df["tpep_pickup_datetime"].to_numpy()

# --- Sort key: how many blocks can min/max metadata skip? ---
ROWS_PER_BLOCK = 131_072                      # 1 MB of 8-byte timestamps, uncompressed
lo, hi = np.datetime64("2024-01-15"), np.datetime64("2024-01-16")


def blocks_read(values):
    read = total = 0
    for start in range(0, len(values), ROWS_PER_BLOCK):
        block = values[start:start + ROWS_PER_BLOCK]
        total += 1
        if block.max() >= lo and block.min() < hi:      # range overlaps the filter
            read += 1
    return read, total


shuffled = np.random.default_rng(42).permutation(pickup)
print("Blocks read for one day, pickup >= 2024-01-15 and < 2024-01-16")
for label, values in [("shuffled (no order)", shuffled),
                      ("as published", pickup),
                      ("sorted by pickup time", np.sort(pickup))]:
    read, total = blocks_read(values)
    print(f"  {label:22} {read:2} of {total} blocks")

# --- Distribution key: how evenly do rows spread over 8 slices? ---
SLICES = 8
print()
print(f"Rows per slice across {SLICES} slices")
candidates = {
    "payment_type": df["payment_type"],
    "PULocationID": df["PULocationID"],
    "tpep_pickup_datetime": df["tpep_pickup_datetime"],
}
for name, column in candidates.items():
    slice_of = pd.util.hash_array(column.to_numpy()) % SLICES
    counts = np.bincount(slice_of.astype(np.int64), minlength=SLICES)
    print(f"  KEY {name:21} distinct {column.nunique():9,}  largest slice {counts.max() / len(df):5.1%}  "
          f"smallest {counts.min() / len(df):5.1%}")
even = np.bincount(np.arange(len(df)) % SLICES, minlength=SLICES)
print(f"  EVEN (round robin)        {'':18}  largest slice {even.max() / len(df):5.1%}  "
      f"smallest {even.min() / len(df):5.1%}")

Blocks read for one day, pickup >= 2024-01-15 and < 2024-01-16
  shuffled (no order)    23 of 23 blocks
  as published            4 of 23 blocks
  sorted by pickup time   2 of 23 blocks

Rows per slice across 8 slices
  KEY payment_type          distinct         5  largest slice 78.2%  smallest  0.0%
  KEY PULocationID          distinct       260  largest slice 16.7%  smallest  6.6%
  KEY tpep_pickup_datetime  distinct 1,575,706  largest slice 12.5%  smallest 12.5%
  EVEN (round robin)                            largest slice 12.5%  smallest 12.5%

Sorting turns a full scan into a small one. With rows in random order every one of the 23 blocks contains some trips from 15 January, so all are read. Sorted by pickup time, the day sits in 2 blocks. The file as published is roughly, but not exactly, in time order, and needs 4. That middle case is what a sorted table looks like after unsorted rows have been appended, which is why sort order needs maintaining.

Low cardinality makes a poor distribution key. Payment type has five values and one dominates: 78.2 percent of rows land on a single slice. Five values can occupy at most five of the eight slices, so at least three hold nothing. The pickup zone, with 260 values of unequal popularity, is better but lopsided, from 6.6 to 16.7 percent. The timestamp, with over a million distinct values, spreads as evenly as round-robin.

The developer guide's advice matches: choose a column with high cardinality in the filtered result set. It also explains why a date makes a questionable distribution key despite spreading evenly: queries that filter to a narrow date range then do all their work on a few slices.

Choosing in practice

Distribution. From the guide's suggestions:

1. Distribute the fact table and its largest, most frequently joined dimension on their common column, so that join needs no redistribution. A fact table has only one distribution key, so choose the join that matters most.
2. Judge "largest" by the rows that take part in the join after filtering, not by table size.
3. Check the key for skew.
4. For dimensions that cannot be collocated, consider ALL, weighing the extra storage and slower loads. The guide notes there is little benefit for small dimension tables, because redistributing them is cheap.

Sort key. From the guide (Redshift docs: best sort key):

• If recent data is queried most, lead with the timestamp column.
• If one column is filtered by range or equality in most queries, sort on it.
• If a table is joined frequently on one column, making it both sort key and distribution key allows a sort merge join without a sort phase.

A compound sort key, the default type, helps queries that filter on its leading columns in order. An interleaved key weights its columns equally, at a higher maintenance cost.

A table definition and checks, written from the documentation and not run:

CREATE TABLE fact_trips (
  trip_id        BIGINT,
  pickup_at      TIMESTAMP,
  pickup_zone_id INTEGER,
  payment_type   SMALLINT,
  fare_amount    DECIMAL(10,2)
)
DISTSTYLE KEY
DISTKEY (pickup_zone_id)
SORTKEY (pickup_at);

-- Skew and sort health
SELECT "table", diststyle, sortkey1, skew_rows, unsorted, tbl_rows
FROM svv_table_info
WHERE "table" = 'fact_trips';

-- Change your mind later
ALTER TABLE fact_trips ALTER DISTSTYLE AUTO;
ALTER TABLE fact_trips ALTER SORTKEY AUTO;

In SVV_TABLE_INFO, skew_rows is the ratio of rows in the fullest slice to the emptiest, and unsorted is the percentage of rows not in sort order. A high value in either is the signal to act.

When to leave it on AUTO

If you specify nothing, Redshift applies AUTO distribution, and AWS recommends creating tables with SORTKEY AUTO. Automatic table optimization then observes how queries use the table and applies keys without intervention (Redshift docs: automatic table optimization).

A reasonable policy:

• Start with AUTO for new tables and on Redshift Serverless.
• Set keys by hand when the workload is well understood and stable, a large join is known in advance, or measurements show the automatic choice is not helping.
• Measure before and after. Use EXPLAIN to look for redistribution steps in the plan, and the system view above for skew and unsorted rows.

Common mistakes

• Distributing on a low-cardinality column such as status, type or country.
• Sorting on a column nobody filters by.
• ALL on a large or frequently updated table. Storage multiplies by the number of nodes and loads slow down.
• Functions in filters. AWS's query guidance is to avoid functions in predicates and to compare columns directly.
• Tuning tables before fixing queries. A query that selects every column or joins without a filter is slow under any key.

Keys are one layer of design. Data modelling in Redshift covers the schema they sit on, loading data with COPY covers getting rows in efficiently, and Amazon Redshift explained covers the architecture behind both.

Data warehousing on AWS with Amazon Redshift, including table design, is covered in the Data Engineering course.

Sources

• Distribution styles (Amazon Redshift Database Developer Guide) — AWS Documentation
• Choose the best distribution style (Amazon Redshift Database Developer Guide) — AWS Documentation
• Sort keys (Amazon Redshift Database Developer Guide) — AWS Documentation
• Choose the best sort key (Amazon Redshift Database Developer Guide) — AWS Documentation
• Automatic table optimization (Amazon Redshift Database Developer Guide) — AWS Documentation
• TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission