One Redshift node pegs at 100% while the others idle — how do you diagnose distribution skew?

## Why one busy slice ruins the query A Redshift cluster runs each plan step in parallel across every slice on every compute node. A step is complete only when the last slice finishes it. If one slice holds ten times the rows of its peers, it spends ten times as long on the scan, and the other slices sit idle waiting. Total elapsed time is set by the straggler, so the cluster's effective parallelism collapses toward one. That is the shape you see on the monitoring graph: a single node at 100% CPU or disk while the rest are flat. It is one of the most recognisable Redshift failure modes and a standard interview scenario. ## Step 1 — find the skewed table `SVV_TABLE_INFO` is the first stop. Per table it reports the distribution style, the sort key, size, and crucially `skew_rows` — the ratio between the number of rows on the slice holding the most and the slice holding the fewest. A ratio close to 1.0 means rows are evenly spread. A ratio of 4, or 40, points straight at the problem table. The same view carries `unsorted` and `stats_off`, which are worth reading at the same time since disorder and stale statistics often accompany a badly chosen key. ```sql SELECT "schema", "table", diststyle, tbl_rows, skew_rows, unsorted, stats_off FROM svv_table_info ORDER BY skew_rows DESC NULLS LAST; ``` ## Step 2 — confirm physically Row counts are logical; blocks are what actually get read. `STV_BLOCKLIST` records one row per 1 MB block with its table id and slice, so grouping by slice for the table in question shows the real storage imbalance. If one slice owns most of the blocks, the diagnosis is settled. ## Step 3 — find the offending value Skew almost always traces to a value distribution problem in the DISTKEY column. Group the table by its DISTKEY and look at the top values by count. The recurring offenders: - **NULL.** All NULLs hash to one slice. A column that is 30% NULL puts 30% of the table on one slice. - **Sentinels.** `-1`, `0`, `'UNKNOWN'`, `'N/A'` used as the unmatched-dimension placeholder behave exactly like NULL. - **A genuinely dominant entity.** One tenant, one merchant or one device that accounts for a large share of all rows. - **Low cardinality.** Distributing on `country_code` across a cluster with many slices leaves most slices empty and a few overloaded, however uniform the data feels. ## Step 4 — fix it In rough order of preference: 1. **Move the DISTKEY** to a column that is both a real join key and uniformly distributed with high cardinality — often a transaction or order id rather than a customer or tenant id. You lose co-location on the old join and gain it on the new one; measure which join actually moves more data. 2. **Switch to `DISTSTYLE EVEN`.** Round-robin placement makes skew impossible. You give up co-location and accept redistribution on joins, which is frequently the better trade when the alternative is a straggler slice on every query. 3. **Use `DISTSTYLE ALL`** if the table is small enough to replicate. Skew disappears and joins become local, at the cost of storage times node count and slower writes. 4. **Change the data.** If the sentinel value is an artefact of the ETL, sometimes the right answer is to stop writing it — or to split the dominant key's rows out into their own handling path. All of these are in-place operations: `ALTER TABLE tbl ALTER DISTSTYLE EVEN` and its KEY/ALL/AUTO variants rewrite the table in the background rather than requiring an unload and reload. ## Step 5 — rule out the imposters Not every hot node is distribution skew. Before reshaping a table, check whether the query is: - **Broadcasting a large table.** A `DS_BCAST_INNER` in the plan loads every node with a full copy; the symptom can look like imbalance if one node is otherwise busy. - **Spilling to disk.** A hash or sort step that exceeds its memory grant writes to disk on the slices that overflow, which is a memory-allocation problem, not a placement problem. - **Suffering sort-key skew rather than row skew.** `SVV_TABLE_INFO` also exposes a sort-key skew indicator; a badly chosen sort key produces unbalanced block sizes without unbalanced row counts. - **Hitting a single-slice step.** Some operations, such as building a small result on the leader or a `LIMIT` after a merge, are simply not parallel. The discipline is the same as any performance investigation: confirm the symptom in the system views before changing a schema, and re-measure the same query after the change rather than trusting that the ratio improved.