skip to content

In Redshift, how does an INTERLEAVED SORTKEY differ from a COMPOUND SORTKEY?

level: middleimportance: should knowfreq 58%

answer

  1. Both are about which blocks a scan can skip
  2. One respects the column order you wrote
  3. The other treats every listed column equally
  4. Maintenance is where the difference bites
  5. One needs VACUUM REINDEX to be restored

basics

~20 s

A Redshift compound sort key orders rows by its columns strictly left to right, so it only prunes well when the leading column is filtered. An interleaved sort key gives every listed column equal weight, helping filters on any subset, but it costs far more to maintain.

solid answer

~50 s

Both control physical row order, which drives block skipping via Redshift's per-block min/max zone maps. **Compound** (the default) sorts by the columns in the order you declare them, like a phone book sorted by last name then first name. A filter on the leading column prunes brilliantly; a filter on only the second or third column prunes almost nothing. It is cheap to maintain — new data lands at the end and `VACUUM` merges it in — and it also lets Redshift skip sorts for `ORDER BY` and merge joins on the same prefix. **Interleaved** weights every column equally using a space-filling curve over the values, so a query filtering on any one of up to eight listed columns prunes well. The price is real: reorganisation is expensive, incremental loads degrade it quickly, and restoring it needs `VACUUM REINDEX` rather than an ordinary vacuum. In practice most tables should be compound. Interleaved suits a large, rarely-reloaded table queried through genuinely unpredictable single-column filters.

code

sql · 17 lines
sql
-- compound: order matters, leading column prunes best
CREATE TABLE events_compound (
  event_ts   TIMESTAMP,
  region     VARCHAR(20),
  product_id INT,
  amount     DECIMAL(12,2)
)
COMPOUND SORTKEY (event_ts, region);

-- interleaved: equal weight, at most eight columns
CREATE TABLE events_interleaved (
  event_ts   TIMESTAMP,
  region     VARCHAR(20),
  product_id INT,
  amount     DECIMAL(12,2)
)
INTERLEAVED SORTKEY (event_ts, region, product_id);

go deeper

for a junior

Know that a Redshift sort key sets physical row order and that the default form respects the column order you declare, leading column first.

for a middle

Explain zone maps and block skipping, why a compound key's second column prunes poorly on its own, and the maintenance cost that makes interleaved a specialist choice.

for a senior

Justify a key for a real workload from query evidence, and know when to rebuild by reloading rather than vacuuming a badly disordered table.

for a principal

Weigh the maintenance budget across a schema: which tables can afford periodic reindexing, where a time-leading compound key plus good pruning is enough, and what the ad-hoc workload really costs.

## The mechanism both keys drive Redshift stores column data in 1 MB blocks and keeps min/max values for each block — **zone maps**. When a query filters on a column, the engine compares the predicate against those min/max pairs and skips any block that cannot contain a match. Skipping blocks is the difference between reading a terabyte and reading a few gigabytes. Zone maps only help when the data is physically ordered so that a given value range lives in few blocks. That ordering is what a `SORTKEY` provides. So the sort key is not an index; it is a statement about physical layout, and its whole value is measured in blocks not read. ## Compound sort keys `SORTKEY (a, b, c)` (or `COMPOUND SORTKEY (a, b, c)` — compound is the default) sorts rows by `a`, then by `b` within equal `a`, then by `c`. This is exactly the semantics of a multi-column ordering everywhere else in databases, and it inherits the same leftmost-prefix behaviour. - A filter on `a` prunes extremely well — matching rows occupy a contiguous run of blocks. - A filter on `a` **and** `b` prunes even better. - A filter on `b` alone prunes almost nothing, because for any value of `b` there are matching rows scattered under every value of `a`. Column order is therefore the whole design decision, and the usual rule is to lead with the column that appears in the most queries as a restrictive filter — very often a timestamp, because analytical queries are overwhelmingly time-bounded, and because appending new data by time keeps the table naturally near-sorted. Compound keys have two bonus effects. Redshift can skip an explicit sort for an `ORDER BY` or `GROUP BY` that matches the sort key prefix, and it can use merge joins when two tables are both distributed and sorted on the join column — the cheapest join shape available. Maintenance is cheap. New rows arrive in an unsorted region at the end of the table; `VACUUM` (or Redshift's automatic table sort running in the background) merges that region into the sorted region. Because the sort is a plain ordering, the merge is a merge. ## Interleaved sort keys `INTERLEAVED SORTKEY (a, b, c)` gives every column equal weight. Redshift maps each row to a position on a space-filling curve computed from all the listed columns, so blocks end up clustered along every dimension at once rather than strictly along the first. A filter on `b` alone now prunes meaningfully, which a compound key cannot do. The key may list at most eight columns. The costs are what make interleaved a specialist tool: - **Reorganisation is expensive.** Adding rows perturbs the curve globally, not just at the tail, so there is no cheap append-and-merge. Restoring the layout needs `VACUUM REINDEX`, which re-analyses the value distribution across all key columns and rewrites the table — far heavier than a sort-only vacuum. - **It degrades fast under incremental loads.** A table loaded hourly will drift away from a good interleaved layout between reindexes, and the pruning benefit erodes with it. - **It is worse than compound for the leading-column case.** If your queries really do filter on one predictable column, an interleaved key prunes less well than a compound key led by that column. - **No sort-avoidance bonus.** Interleaved ordering does not produce the sorted output that lets Redshift skip an `ORDER BY` sort or choose a merge join. ## Choosing between them The honest default is compound. Reach for interleaved only when all of these hold: the table is large enough that pruning matters, it is loaded in bulk and rarely (so you can afford periodic `VACUUM REINDEX`), and the workload genuinely filters on several different columns with no dominant one — for example an ad-hoc exploration table where analysts slice by any of region, product, channel or campaign. A frequently better alternative for a multi-filter workload is a compound key whose leading column is the time filter every query carries anyway, accepting that the other predicates are evaluated after the time-based pruning has already cut the scan down. ## Practical notes You can change a compound sort key in place with `ALTER TABLE tbl ALTER SORTKEY (col, ...)`, and `ALTER SORTKEY NONE` removes it. Loading into an empty table with `COPY` writes the data already sorted, which is why a rebuild-and-reload sometimes beats a vacuum on a badly disordered table. Finally, remember what a sort key is not. It does not decide which slice a row lives on — that is the DISTKEY — and it will not prevent a join from redistributing data. Sorting affects how much of each slice's data has to be read; distribution affects whether the network is involved at all.

  • Why does an interleaved sort key degrade faster than a compound one under hourly loads?
    A compound key appends new rows to an unsorted tail that a vacuum can merge into the sorted region. An interleaved layout maps rows onto a curve derived from all key columns, so new values perturb the whole arrangement rather than just the end, and restoring it means a full VACUUM REINDEX rather than a cheap merge.
  • How does a compound sort key help a query that has no filter at all on the key columns?
    It can still help if the query's ORDER BY, GROUP BY or join key matches the sort key prefix, because Redshift can then skip a sort step or use a merge join. Without any of those, the sort key simply does nothing for that query.
  • What is a good first choice of leading column for a compound sort key on an event table?
    Usually the event timestamp. Analytical queries are almost always time-bounded, so a time-leading key prunes on the predicate nearly every query carries, and because events arrive in time order the table stays close to sorted between vacuums, keeping maintenance cheap.

A compound sort key is a phone book: sorted by last name then first name, so finding all the Smiths is instant but finding everyone named John is hopeless. An interleaved key is closer to a map grid, where every axis gets equal say in which cell you land in.

saying these in an interview costs you the question

  • Claiming an interleaved sort key is simply a better compound key
  • Thinking a sort key controls which slice rows are stored on
  • Believing filters on the second compound column prune as well as the first
  • Recommending interleaved for a table loaded incrementally every hour
  • Assuming ordinary VACUUM restores an interleaved layout

context

resolving links…