Amazon Redshift Performance Tuning - Optimizing Distribution Keys, Sort Keys, and WLM
Learn how to select distribution styles and sort keys, design workload management (WLM), and accelerate queries with AQUA in Amazon Redshift.
Choosing a Distribution Style
Redshift distributes data across slices on compute nodes. Choosing the right distribution style is the single most important design decision for query performance. KEY distribution spreads data based on the values of a specified column, placing rows with the same key value on the same slice. When you designate a column frequently used in JOINs as the KEY, data redistribution (network transfer) during JOINs becomes unnecessary, yielding significant performance gains. EVEN distribution uses round-robin to spread data uniformly and is suitable for tables without specific JOIN patterns. ALL distribution copies the entire table to every node, which is effective for JOINing small dimension tables. AUTO distribution lets Redshift choose automatically based on table size: small tables get ALL, and large tables get EVEN.
Sort Keys and Zone Maps
Setting a sort key causes data to be stored on disk in order of that column's values. Redshift records the minimum and maximum values per block as zone maps, and WHERE clause filters skip blocks whose zone map ranges don't match the filter condition. For example, setting a date column as the sort key and applying a filter like WHERE created_at > '2026-01-01' causes only the relevant blocks to be scanned, dramatically reducing I/O. Compound sort keys combine multiple columns, with the first column providing the most effective filtering. Interleaved sort keys assign equal weight to each column, making filtering effective regardless of which column is used, but they increase VACUUM overhead.
Workload Management and AQUA
Automatic WLM optimizes queue assignment and memory allocation based on query complexity and resource requirements. It prevents short dashboard queries from being blocked by long-running ETL queries and allocates appropriate resources to high-priority queries. Manual WLM lets you define up to 8 queues and route queries based on user groups or query groups. AQUA is a feature available on RA3 instances that executes filtering and aggregation operations on custom hardware at the storage layer. It reduces the volume of data transferred to compute nodes and can accelerate scan-intensive queries by up to 10x. To learn about data warehouse use cases, related books (Amazon) are a helpful reference.
Cost Impact of Performance Tuning
Proper performance tuning directly translates to cost savings. Optimizing distribution keys to reduce inter-node data redistribution shortens query execution time and lowers RPU consumption in Serverless. Setting sort keys to improve zone map skip rates reduces the volume of scanned data, cutting I/O costs. AQUA (Advanced Query Accelerator) is available at no additional charge on RA3 nodes and offloads LIKE clause and filtering operations to the storage layer, improving query performance.
Summary
Redshift performance tuning starts with properly configuring distribution styles and sort keys. Use SVL_QUERY_SUMMARY to identify slow queries, then improve them by adjusting distribution keys or adding sort keys. Manage workload priorities with automatic WLM and boost scan performance with AQUA to optimize response times for large-scale data analytics.