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.
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.
Query Analysis and Execution Plans
The first step in performance tuning is identifying which queries are slow. Redshift provides system views that record the history and duration of executed queries, and you can find problem queries there. If you check a query's execution plan, you see where it takes time and whether large data movements are occurring between nodes. If unexpected full scans or inefficient joins are occurring, those are your targets for improvement. Identifying bottlenecks not by guesswork but based on measured data and execution plans is the starting point for effective improvement.
Table Maintenance
In Redshift, repeated data updates and deletions disturb the internal data layout, and query performance sometimes declines gradually. Maintenance work that restores order is important. By properly running the process that reorganizes the data order and reclaims deleted space and the process that refreshes statistics, you can maintain performance. There is also a mechanism that runs these automatically, but after you make large data changes, you check the state. If you make use of the feature that automatically tunes table optimization, you can maintain good performance while keeping operational effort down. Regular maintenance leads to stable performance over the long term.
Result Caching and Materialized Views
When the same query runs repeatedly, computing it from scratch every time is wasteful. Redshift has a mechanism that reuses previous results for identical queries and returns repeated queries quickly. If you turn frequently run aggregations into materialized views, which compute and hold the results in advance, you can avoid heavy computation each time. When the source data changes, you refresh the view to keep the results current. By making good use of mechanisms that reuse computed results, you can reduce recurring load and greatly improve the response of dashboards and routine reports.
Continuous Monitoring and the Tuning Cycle
Performance tuning is not a one-time task but an ongoing effort. The optimal settings change as data volume grows and usage patterns shift. You set up query monitoring rules so that you can detect extremely heavy queries and abnormal executions. You run a cycle of regularly identifying slow queries, checking their execution plans, and improving them. You measure the effect of each improvement and confirm that things actually became faster. By continuing the cycle of monitoring, analysis, improvement, and verification, you can keep response performance stable even as data scale grows.
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.
References (Official AWS Resources)
The primary sources for this page are the official AWS website and documentation. Check the official pages below for the latest specifications and pricing.
- Amazon Redshift official page
- Amazon S3 official page
- AWS Glue official page
- AWS Documentation (official)
If this page and the official documentation disagree, treat the official documentation as authoritative.