Amazon Redshift Performance Optimization: Sort Keys, Distribution, and WLM

Amazon Redshift remains one of the most widely deployed cloud data warehouses in enterprise environments, particularly for organisations with deep AWS investment. It is also one of the most frequently misconfigured — many Redshift clusters run expensive queries against poorly designed table schemas that negate Redshift's columnar performance advantages.

This guide covers the physical design decisions and operational practices that determine Redshift performance: sort keys, distribution styles, workload management, and the maintenance operations that keep a Redshift cluster running efficiently over time.

Redshift Architecture: Why Physical Design Matters

Redshift is a massively parallel processing (MPP) columnar database. Queries are distributed across multiple compute nodes (slices), each holding a portion of the data. A query's performance is determined by:

1. How much data each node needs to read (reduced by sort keys and column projection)

2. How much data needs to be redistributed across nodes at query time (reduced by distribution styles)

3. How much compute is available to run the query (managed by workload management)

Unlike modern serverless warehouses (BigQuery, Snowflake), Redshift's performance is directly coupled to physical design decisions made at table creation time. Choosing the wrong sort key or distribution style is not self-correcting — it results in slow queries until the table is redesigned and data reloaded.

Sort Keys

Redshift stores data in 1MB blocks on disk. Each block has min/max statistics recorded in zone maps. When a query has a filter condition on a sort key column, Redshift uses zone maps to skip blocks that cannot contain matching rows — this is called zone map pruning.

The performance impact of sort keys depends on the selectivity of the filter. A query filtering to a two-week date range in a multi-year table can skip 96% of blocks if the table is sorted by date. The same query without a sort key reads every block.

**Compound sort keys** specify multiple columns in order. Blocks are sorted first by the first column, then by the second within each first-column value, and so on. Compound sort keys are effective when queries always filter on the first key column (or the first few). They are ineffective for queries that filter only on a column that is not the leading column.

**Interleaved sort keys** give equal weight to all sort key columns, optimising for queries that filter on any combination of key columns. Interleaved sort keys have higher maintenance cost (VACUUM REINDEX is slower and more expensive than standard VACUUM) and are generally recommended only for tables with very diverse filter patterns. For most analytical tables where date is the primary filter, compound sort keys are the right choice.

**Choosing sort keys:** sort on the column(s) most commonly used in WHERE clauses and range filters. For time-series data, sort by the date/timestamp column. For dimension tables, sort by the primary key. Do not sort by high-cardinality random columns (UUIDs) — the distribution provides no pruning benefit.

Distribution Styles

Redshift distributes each table's rows across the cluster's compute slices. When two tables are joined, Redshift must either have the joining rows on the same slice (collocated join) or redistribute one table's rows to match the other. Redistribution — called a hash redistribute or broadcast — requires network transfer and is expensive.

**KEY distribution:** rows are distributed based on the hash of a designated column. Two tables distributed by the same column will have the same key values on the same slices, enabling collocated joins. Use KEY distribution for large fact tables and their largest dimension tables, distributing on the join key.

**EVEN distribution:** rows are distributed evenly in round-robin fashion. No redistribution when joining with a KEY-distributed table (data must move). Use for tables that are not frequently joined to large tables, or for tables where any join column would produce skew.

**ALL distribution:** a complete copy of the table is stored on every slice. Joins to ALL-distributed tables are always collocated — no redistribution needed. Use for small dimension tables (under a few million rows) that are frequently joined to large fact tables. Avoid for large tables — storing complete copies across all slices is expensive in storage and makes writes slower.

**AUTO distribution:** Redshift chooses the distribution style automatically based on table size, initially using ALL for small tables and switching to EVEN as the table grows. AUTO is appropriate for tables where you are uncertain of the access pattern, but for known high-volume analytical tables, explicit KEY distribution on the join column is typically more efficient.

**Identifying distribution problems:** run EXPLAIN on slow queries. Redistribution operations appear as DS_BCAST_INNER or DS_DIST_INNER in the query plan. DS_BCAST_INNER means a full broadcast redistribution; DS_DIST_INNER means a hash redistribution. Both are expensive on large tables — if you see them, the distribution style is wrong for that join.

Workload Management (WLM)

WLM controls how Redshift allocates memory and concurrency to different types of queries. The default WLM configuration puts all queries in a single queue, which means a long-running ETL job can block short dashboard queries.

**WLM queue design:** create separate queues for different workload types. A typical configuration:

- ETL queue: high memory allocation (60–70% of total memory), low concurrency (2–4 slots), assigned to ETL user group

- Reporting queue: moderate memory (20–30%), moderate concurrency (5–10 slots), assigned to BI tool service accounts

- Short query queue: low memory per slot (10–15%), high concurrency (10–20 slots), assigned to interactive analysts

**Short-query acceleration (SQA):** automatically routes queries estimated to complete in under a configurable time (up to 20 seconds) to a dedicated SQA queue, bypassing the main WLM queue. Enables sub-second dashboard query responses even when the ETL queue is saturated. Enable SQA for all production clusters.

**Concurrency scaling:** automatically adds cluster capacity during high-concurrency periods, routing overflow queries to additional clusters. Concurrency scaling is billed per second of additional cluster usage. For variable workloads with occasional spikes (end-of-month reporting, post-release analytics), concurrency scaling is more cost-effective than over-provisioning the base cluster.

VACUUM and ANALYZE

Redshift requires regular maintenance that many teams neglect:

**VACUUM** reclaims storage from deleted rows and re-sorts data that was inserted out of sort key order (which happens with most incremental loads). Rows marked as deleted in Redshift are not immediately removed — they remain on disk and consume storage until VACUUM reclaims them. On heavily updated tables, up to 50% of storage may be consumed by deleted rows.

VACUUM SORT ONLY re-sorts unsorted rows without reclaiming deleted space. VACUUM DELETE ONLY reclaims deleted space without re-sorting. VACUUM FULL does both. For tables with heavy incremental inserts and sorts, VACUUM SORT ONLY nightly and VACUUM FULL weekly is a reasonable maintenance schedule.

Automatic VACUUM (enabled by default) handles light VACUUM operations in the background, but it has limited capacity and may not keep up with heavy workloads. Monitor stl_vacuum and svv_vacuum_progress to verify VACUUM is keeping pace.

**ANALYZE** updates table statistics used by the query planner to choose efficient query plans. Tables without current statistics cause the query planner to make suboptimal join order and distribution decisions. Automatic ANALYZE (enabled by default) handles most use cases, but large tables loaded in bulk should be manually ANALYZE'd after significant changes to ensure statistics are current.

Column Encoding

Redshift stores each column with a compression encoding that reduces storage and improves query performance (less I/O). Encodings are applied at table creation or via ALTER COLUMN.

The ENCODE AUTO option (the default for new tables) allows Redshift to automatically apply the optimal encoding as data is loaded. For existing tables, run ANALYZE COMPRESSION to get encoding recommendations, then ALTER the column or recreate the table with the recommended encodings.

The most important encodings to understand:

- **AZ64**: Redshift's proprietary high-compression encoding, optimal for numeric and date columns

- **ZSTD**: general-purpose high-ratio compression, good for string columns

- **LZO**: fast decompression, good for columns with high read frequency relative to write frequency

- **RAW**: no compression, only appropriate for tiny lookup tables or columns that are frequently modified

Poor column encoding is a hidden Redshift performance and cost issue. A table with RAW encoding on a billion-row fact table uses 3–5x more storage than the same table with AZ64/ZSTD, which means every scan reads 3–5x more I/O.

For Amazon Redshift architecture, performance tuning, and cloud data platform design, our cloud engineering services covers AWS data infrastructure — contact us to discuss your Redshift environment.

A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.