Why does a Redshift table's sort key stop pruning blocks after weeks of incremental loads?

## The mechanism that decays Redshift stores each column in 1 MB blocks and records a min and a max value for every block. Those **zone maps** are how a scan avoids I/O: given `WHERE order_ts >= '2026-08-01'`, any block whose max timestamp is earlier can be skipped without being read. The amount of I/O this saves depends entirely on how tightly each block's values cluster. In a table physically ordered by `order_ts`, one block covers a narrow slice of time and almost every block is skippable. In a table where each block holds a random assortment of timestamps, every block's range spans the whole history and nothing can be skipped — the zone maps are still there, they just never exclude anything. So the sort key's benefit is not a property of the DDL. It is a property of the current physical layout, and layout decays. ## Where the decay comes from **Appends land unsorted.** Redshift maintains a *sorted region* and an *unsorted region*. A `COPY` into an empty table with a sort key writes the data already sorted. A `COPY` or `INSERT` into a table that already has data appends to the unsorted region at the end. If the incoming batch happens to be ordered on the sort key and its values are all higher than everything already stored — the common case for a time-leading key on an append-only event table — the damage is mild, because the new blocks still have narrow, non-overlapping ranges. If the batch contains a mix of values, or backfills older periods, the new blocks overlap the whole sorted region and prune nothing. **Deletes leave rows behind.** Redshift does not remove rows in place. `DELETE` marks them, and `UPDATE` is a delete plus an insert, so an updated row leaves a dead copy in its old block and a new copy in the unsorted region. Blocks full of dead rows still have to be read and filtered, so a heavily updated table degrades on two axes at once. **The effect is invisible in the query.** Nothing in the SQL changes. The symptom is a report that took 8 seconds in March taking 90 seconds in August, with the same plan shape but far more blocks scanned. ## Diagnosing it `SVV_TABLE_INFO` carries the two numbers you need per table: - **`unsorted`** — the percentage of rows outside the sorted region. Rising values mean pruning is eroding. - **`stats_off`** — how stale the optimizer statistics are. This is a separate problem with a separate fix, but it travels with the first because both follow from loading data. ```sql SELECT "schema", "table", tbl_rows, unsorted, stats_off, sortkey1 FROM svv_table_info WHERE unsorted > 10 ORDER BY tbl_rows DESC; ``` To see whether pruning is actually happening for a specific query, compare rows scanned against rows returned in the execution-time views such as `SVL_QUERY_REPORT` and `SVL_QUERY_SUMMARY` for that query id — a scan step reading most of the table for a narrow time filter is the confirmation. ## Fixing it `VACUUM` has variants that address different halves of the problem: - **`VACUUM SORT ONLY tbl`** — merges the unsorted region into the sorted region. This is the one that restores pruning. - **`VACUUM DELETE ONLY tbl`** — reclaims space from rows marked deleted, without re-sorting. - **`VACUUM FULL tbl`** (the default form) — does both. - **`VACUUM REINDEX tbl`** — required for a table with an interleaved sort key, because restoring that layout means re-analysing the value distribution across all key columns. - **`VACUUM RECLUSTER tbl`** — a lighter-weight option that reorganises only the portion of the data that needs it, useful on very large tables where a full sort is impractical. Redshift also runs maintenance itself. Automatic table sort, automatic vacuum delete and automatic analyze run in the background during periods of low load, and for an append-only table with a time-leading sort key they are usually sufficient. They are less reliable for tables under constant heavy update pressure, or on a cluster that is never idle. **`ANALYZE` is a different tool.** It updates the statistics the planner uses to estimate cardinalities and choose join strategies. It moves no data and restores no pruning. Confusing the two is a common interview slip: vacuum fixes physical layout, analyze fixes the planner's beliefs. After a large load you generally want both. ## Designing so it decays less - Lead the sort key with the column your loads naturally advance on — usually the event timestamp — so appended batches extend the sorted region rather than overlapping it. - Load in large batches rather than many tiny ones, so each batch produces well-filled, narrow-range blocks. - Prefer deleting whole time ranges over row-by-row deletes where the model allows it, and reduce update-in-place patterns on huge tables. - For badly disordered tables, reloading into a freshly created table can beat vacuuming, because a `COPY` into an empty table writes fully sorted data in one pass. - Watch `unsorted` as an ongoing metric rather than discovering it through a slow dashboard.