Sort & Distribution Keys in the Auto Era

If you learned Redshift before 2020, you learned to pick a distribution key and a sort key for every table, to run VACUUM after big loads, and possibly to use interleaved sort keys for tables with several filter columns. Most of that advice is now wrong or unnecessary. Redshift tables default to DISTSTYLE AUTO and SORTKEY AUTO, and a background service called Automatic Table Optimization (ATO) changes the physical design based on how the table is actually queried. This guide explains what ATO does, how to see what it is thinking, and the few situations where overriding it is still the right call.

What AUTO actually does

DISTSTYLE AUTO. A small table starts as ALL (a full copy on every node). As it grows past a threshold Redshift switches it to EVEN, and if the query history shows a consistent join column, ATO converts it to KEY on that column. The conversions run in the background, during low-load periods, without locking the table.

SORTKEY AUTO. ATO watches the filter and join predicates in your workload and, when it finds a column that would let queries skip enough blocks, assigns it as the sort key and sorts the table. It prefers single-column or compound keys. It never chooses an interleaved key.

Automatic vacuum and sort. Separately from ATO, the auto-vacuum-delete process reclaims space from deleted rows and the auto-table-sort process keeps data mostly sorted. Manual VACUUM after loads is no longer a routine task. VACUUM REINDEX exists only for interleaved keys, which is one more reason not to use them.

ATO needs query history to act on. A brand-new table with no workload stays EVEN / unsorted. The recommendations typically appear within hours to a few days of a representative workload running.

Interleaved sort keys: do not

Interleaved keys gave equal weight to several columns so that filtering on any of them skipped blocks. The cost was severe: VACUUM REINDEX rewrote the whole table, and tables with increasing columns (timestamps, IDs) degraded quickly. AWS's current sort-key guidance is to use AUTO, and a compound key where you choose manually. If you inherit a table with an interleaved key, migrate it:

ALTER TABLE fact_events ALTER SORTKEY AUTO;

Reading the recommendations

ATO writes what it wants to do, and what it has done, to system views. SVV_ALTER_TABLE_RECOMMENDATIONS lists pending changes:

SELECT database, schema, table_id, type, ddl, auto_eligible
FROM SVV_ALTER_TABLE_RECOMMENDATIONS
ORDER BY database, schema, table_id;

type is diststyle or sortkey; ddl is the exact ALTER TABLE it would run; auto_eligible is t when the table is set to AUTO and ATO will apply it itself, f when you have set the key manually and it is only a suggestion.

What it has already applied is in SVL_AUTO_WORKER_ACTION:

SELECT table_id, type, status, eventtime, sequence, previous_state
FROM SVL_AUTO_WORKER_ACTION
WHERE eventtime > DATEADD(day, -7, GETDATE())
ORDER BY eventtime DESC;

And the current state of each table is in SVV_TABLE_INFO:

SELECT "table", diststyle, sortkey1, sortkey_num,
       unsorted, stats_off, skew_rows, tbl_rows
FROM SVV_TABLE_INFO
WHERE schema = 'analytics'
ORDER BY tbl_rows DESC;

Three columns here are the ones to watch:

• skew_rows above about 4 means the distribution key is concentrating rows on a few slices; the busiest slice bounds every query's speed.
• unsorted is the percentage of rows not in sort order. Persistent values above 20% on a large table mean auto-sort is not keeping up with the load pattern.
• stats_off above 10% means the planner is working from stale statistics; auto-analyze usually fixes it, but a table loaded and immediately queried may need an explicit ANALYZE.

When to pin a key manually

AUTO is the right default. There are four cases where we still set keys explicitly.

1. You know the join before the workload exists. A star schema where every fact query joins customer_id does not need days of history for ATO to discover it. Set DISTKEY(customer_id) on the fact and the dimension up front; ATO would get there anyway, and the first week of queries runs faster.

2. A time-ordered fact table with a range filter on nearly every query. A compound sort key leading with the timestamp is the classic Redshift design and still the best one. ATO will usually choose it, but if loads are append-only in time order you get it for free:

CREATE TABLE fact_events (
  event_ts     TIMESTAMP NOT NULL,
  account_id   BIGINT NOT NULL,
  event_type   VARCHAR(32),
  payload      SUPER
)
DISTKEY (account_id)
COMPOUND SORTKEY (event_ts, account_id);

Order the compound key from most-selective-and-most-filtered to least. Put the timestamp first if nearly every query has a date range; put the tenant ID first if queries are always per-tenant and date-ranged within that.

3. Workloads ATO cannot see. Tables queried only through Spectrum external engines, or read by a datashare consumer in another account, do not generate the producer-side history ATO learns from. Set keys for the consumer's queries.

4. Large tables with many conflicting access patterns. If ATO keeps flipping a multi-terabyte table between two sort keys, each flip is a full re-sort. Pick the one that serves the SLA-bound queries and pin it. Look for repeated sortkey actions on the same table_id in SVL_AUTO_WORKER_ACTION.

Changing a key on an existing table is a single statement and runs online:

ALTER TABLE fact_events ALTER DISTKEY account_id;
ALTER TABLE fact_events ALTER COMPOUND SORTKEY (event_ts, account_id);

Compression encodings

The same "trust the default" rule applies to encodings. New columns get AZ64 for numeric and date types and LZO or ZSTD for character types. If you are migrating a table created years ago with explicit ENCODE lzo everywhere, rebuild it without the ENCODE clauses (or with ENCODE AUTO) and compare SVV_TABLE_INFO.size; AZ64 is typically both smaller and faster to scan.

A review checklist

Once a quarter, for the 20 largest tables:

1. SVV_ALTER_TABLE_RECOMMENDATIONS: anything pending with auto_eligible = f that you should apply by hand?
2. SVV_TABLE_INFO: skew, unsorted and stats_off within bounds?
3. SVL_AUTO_WORKER_ACTION: any table being flipped repeatedly?
4. Any interleaved sort keys left? Convert them.

That is the whole job in the auto era. If you have a cluster that was tuned by hand in 2018 and never revisited, our Redshift Performance Optimization service starts exactly here.