Amazon Redshift Best Practices: Sort Keys, Distribution, and Query Tuning

Amazon Redshift is one of the most performance-sensitive cloud data warehouses to operate. Unlike BigQuery or Snowflake, which abstract much of the physical storage and compute configuration, Redshift requires data engineers to make explicit architectural decisions about how data is stored and distributed. Get these right, and Redshift is fast and cost-efficient. Get them wrong, and you spend heavily on a warehouse that still underperforms.

Sort Keys

Sort keys determine the physical order of rows on disk. Redshift stores data in 1MB immutable blocks and maintains zone maps (column statistics) per block — tracking the minimum and maximum value of each sort key column. Queries that filter on sort key columns can skip entire blocks that do not contain matching values.

**COMPOUND sort keys** are the most common type. Columns in a compound sort key are applied in order, similar to a composite index. The first column provides the most benefit; subsequent columns only prune effectively when the first column also matches. Use compound sort keys when queries consistently filter on the same columns in the same order — typically a date column first, then a business entity column.

**INTERLEAVED sort keys** weight all columns equally and prune on any column independently. They are useful when queries filter on different columns in different patterns — for example, sometimes filtering on date, sometimes on customer_id, sometimes on product_id. The downside: interleaved sort keys degrade faster as data is inserted and require more frequent VACUUM REINDEX to maintain effectiveness.

**Choosing a sort key:** For fact tables with time-series data, a date or timestamp column is almost always the right sort key prefix. Adding a high-cardinality business entity column (customer_id, order_id) as the second key can further reduce scans for entity-specific queries.

For dimension tables that are broadcast-joined (see Distribution below), sort keys matter less — queries against small dimension tables are fast regardless of sort order.

**When to skip sort keys:** Tables under a few hundred thousand rows do not benefit meaningfully from sort keys. The overhead of maintaining sort order during bulk loads outweighs the scan reduction for small tables.

Distribution Keys

Distribution keys determine which Redshift node stores which rows. Redshift is a massively parallel processing (MPP) database — queries execute across all compute nodes in parallel. Join operations require rows from both tables that share a join key to be on the same node. If they are not, Redshift redistributes one table's rows across the network during query execution — this is called a redistribution shuffle, and it is the most common cause of Redshift performance degradation.

**DISTKEY:** Choose a column that is frequently used as a join key in your most important queries. For fact tables, the highest-cardinality foreign key that is most commonly used in joins — typically customer_id or order_id. Both the fact table and the most commonly joined dimension table should share the same DISTKEY so their rows co-locate on the same node.

**DISTSTYLE ALL:** The entire table is replicated to every node. Use this for small dimension tables (under ~1 million rows). When a large fact table joins to a small dimension table with DISTSTYLE ALL, the join requires no redistribution — the dimension table is local on every node. This is the single most effective performance optimisation for common star schema query patterns.

**DISTSTYLE EVEN:** Rows are distributed round-robin across nodes regardless of any column value. Use for large tables that are rarely joined — staging tables, raw ingestion tables. EVEN distribution avoids data skew (where one node has significantly more rows than others) but cannot support efficient co-located joins.

**DISTSTYLE AUTO:** Redshift chooses the distribution style based on table size and observed query patterns. For smaller tables it uses EVEN; for larger ones it uses a column-based key based on join analysis. This is a reasonable default for new tables where you are unsure of the right strategy, but explicit key selection is usually better for known query patterns.

**Identifying distribution skew:** The query below shows the row distribution across nodes for a given table. If one node has significantly more rows than others, the DISTKEY has low cardinality or is poorly chosen:

SELECT owner, host, diskno, used, capacity

FROM stv_partitions

WHERE part_key = 0

ORDER BY owner;

VACUUM and ANALYZE

Redshift uses an immutable storage model — deleted rows are marked as deleted but not immediately removed. Rows inserted out of sort key order do not shift existing data. Over time, three things happen: dead rows accumulate and waste storage; unsorted rows at the table tail prevent zone map pruning; and column statistics become stale, causing the query planner to make suboptimal decisions.

**VACUUM** reclaims space from deleted rows and re-sorts unsorted rows:

VACUUM FULL tablename;

FULL VACUUM is the safest option but expensive on large tables — it re-sorts the entire table. Use it during maintenance windows after large bulk loads or deletes. VACUUM SORT ONLY re-sorts without reclaiming space. VACUUM DELETE ONLY reclaims space without re-sorting. For incrementally loaded tables, VACUUM SORT ONLY after each load maintains sort key effectiveness without a full table VACUUM.

Redshift runs automatic VACUUM in the background during low-activity periods. On busy clusters, manual VACUUM may still be required to maintain performance.

**ANALYZE** updates column statistics used by the query planner. Redshift runs ANALYZE automatically, but after large loads or significant schema changes, running ANALYZE manually ensures the planner has current statistics:

ANALYZE tablename;

WLM Queue Configuration

Workload Management (WLM) allows separating query workloads into queues with dedicated memory and concurrency allocations. Without WLM configuration, a single long-running analytical query can consume all cluster resources and block interactive dashboard queries.

**Short Query Acceleration (SQA):** Automatically routes short queries (estimated to complete in seconds) to a dedicated fast lane, preventing them from queuing behind long-running analytical jobs. Enable SQA on production clusters — it requires no additional configuration and significantly improves dashboard query response times.

**Manual queue configuration:** Create separate queues for different workload types — a dashboard queue with high concurrency and lower memory per query, an analytical queue with lower concurrency and higher memory for complex multi-step queries, and an ETL queue for data loading operations. Assign users or query groups to specific queues.

For most Redshift deployments, enabling Auto WLM (which dynamically assigns concurrency and memory based on observed query complexity) is more effective than manual WLM configuration, which requires ongoing tuning as workloads change.

Redshift Serverless vs Provisioned

**Redshift Serverless** bills per RPU-second (Redshift Processing Units) and scales compute automatically. There is no cluster to manage, no node type selection, and no capacity planning. Serverless is appropriate for unpredictable or intermittent workloads — development environments, analytics teams with irregular query schedules, organisations that are not yet sure of their Redshift usage patterns.

**Provisioned clusters** offer predictable cost for stable, high-utilisation workloads. You select node type and count. RA3 nodes (current generation) separate storage (Managed Storage on S3) from compute, allowing you to scale storage and compute independently. DC2 nodes co-locate storage and compute — faster for compute-intensive queries but less flexible.

For workloads with consistent daily query patterns and usage exceeding roughly 8 hours per day, provisioned clusters are more cost-effective than Serverless. Below that threshold, Serverless typically wins.

Common Performance Anti-Patterns

**Using COPY for small inserts:** The COPY command is optimised for bulk parallel loading from S3 or DynamoDB. For row-by-row inserts or small batches, COPY's overhead exceeds its benefit. Accumulate small inserts into S3 staging files and COPY in bulk — a minimum of several megabytes per file for effective parallelism.

**Selecting from large tables without sort key predicates:** A query on a 1TB table that does not filter on the sort key column scans the entire table. Every major query against a large fact table should include a date range filter that matches the sort key.

**Multi-step queries that re-read large tables:** Complex analytical queries that reference the same large table in multiple subqueries may cause Redshift to scan the table multiple times. Materialise intermediate results as temporary tables within the session:

CREATE TEMP TABLE filtered_orders AS

SELECT * FROM fct_orders WHERE order_date >= '2024-01-01';

**Late-binding views without partition pushdown:** Views defined with late binding (WITH NO SCHEMA BINDING) cannot push predicates to the underlying table. Standard views (without late binding) allow predicate pushdown and are preferred for performance.

Redshift Spectrum and External Tables

Redshift Spectrum allows querying data in S3 directly using external tables defined in an AWS Glue Data Catalog. Spectrum is useful for:

- Querying historical data in S3 that is too old to load into Redshift but occasionally needs to be included in analysis

- Federated queries joining Redshift-resident data with S3 data lake tables

- Reducing Redshift storage cost by offloading cold data to S3 while keeping recent hot data in Redshift

Spectrum charges per TB scanned from S3 ($5/TB), similar to Athena. Partition pruning applies to Spectrum queries — external tables should be partitioned by date or another filter column to minimise S3 scan cost.

For data teams evaluating Redshift performance or planning a Redshift optimisation effort, our data architecture consulting practice conducts warehouse performance assessments — 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.