AWS
Microsoft Azure
Google Cloud
Key takeaways
1
Redshift Advisor evaluates real cluster behavior, including query patterns, table design, data loading, and workload configuration.
2
Recommendations appear when Redshift detects an observed condition outside its expected best-practice range. They may remain visible until Advisor has collected enough subsequent workload evidence to determine that the condition has improved.
3
The recommendations fall into three main groups: table design, data loading and maintenance, and workload management.
4
Automatic Table Optimization can handle recurring sort key and distribution key changes, but it does not replace every Advisor recommendation.
5
Advisor improves performance and efficiency, but it does not manage Reserved Node purchasing, cluster sizing, query rewrites, or Serverless RPU capacity.
Amazon Redshift Advisor monitors clusters and workgroups in the Redshift console and surfaces recommendations when it detects measurable optimization opportunities.
Its recommendations reflect real workload behavior. Sort key recommendations are based on filtering and joins, compression recommendations on column data, and workload recommendations can reveal queue waits, memory pressure, or disk spilling.
Advisor does not always show implementation risk, whether a change should be automated, or how it affects the broader Redshift cost picture. This guide explains what each recommendation means, why it appears, and what to do next.
Recommendations are available in the console, through ListRecommendations, or via the AWS CLI. Advisor prioritizes them by estimated impact and removes them when the underlying condition is resolved.
Recommendations may remain after implementation until Advisor collects enough new workload evidence. They also change as query patterns, data volumes, table structures, and workloads change.
In the console, choose Advisor. Programmatic access through ListRecommendations requires
Advisor is not available in every AWS Region. In Redshift Data Sharing environments, it can also consider query patterns across producer and consumer clusters or workgroups.
Its recommendations reflect real workload behavior. Sort key recommendations are based on filtering and joins, compression recommendations on column data, and workload recommendations can reveal queue waits, memory pressure, or disk spilling.
Advisor does not always show implementation risk, whether a change should be automated, or how it affects the broader Redshift cost picture. This guide explains what each recommendation means, why it appears, and what to do next.
Short Answer
Redshift Advisor analyzes cluster metrics, query history, table structure, and data-loading patterns. It can recommend changes to sort keys, distribution, compression, statistics, COPY operations, WLM memory, Short Query Acceleration, and database isolation.Recommendations are available in the console, through ListRecommendations, or via the AWS CLI. Advisor prioritizes them by estimated impact and removes them when the underlying condition is resolved.
How Redshift Advisor Generates Recommendations
Advisor compares observed metrics from provisioned clusters and Serverless workgroups with expected best-practice ranges. When a metric falls outside that range, it can create an observation and recommendation.Recommendations may remain after implementation until Advisor collects enough new workload evidence. They also change as query patterns, data volumes, table structures, and workloads change.
In the console, choose Advisor. Programmatic access through ListRecommendations requires
redshift:ListRecommendations; the AWS CLI is also supported.
Advisor is not available in every AWS Region. In Redshift Data Sharing environments, it can also consider query patterns across producer and consumer clusters or workgroups.
Redshift Advisor Coverage and Applicability
Recommendation applicability depends on whether the workload runs on a provisioned cluster or Redshift Serverless, and some recommendations also depend on manual WLM or table automation settings.| Recommendation | Likely scope | Operational risk | Related automation |
|---|---|---|---|
| Alter sort keys | Provisioned and Serverless tables | High for large tables |
SORTKEY AUTO
|
| Alter distribution keys | Provisioned and Serverless tables | High for large tables |
DISTSTYLE AUTO
|
| Alter compression encodings | Provisioned and Serverless tables | Medium to high |
ENCODE AUTO
|
| Update table statistics | Provisioned and Serverless | Low | Automatic analysis |
| Skip compression analysis during COPY | Provisioned and Serverless COPY workloads | Low |
Predefined encodings or ENCODE AUTO
|
| Compress S3 files loaded by COPY | Provisioned and Serverless COPY workloads | Low | Upstream ETL automation |
| Split S3 objects loaded by COPY | Provisioned and Serverless COPY workloads | Low to medium | Upstream ETL automation |
| Reallocate WLM memory | Provisioned clusters using manual WLM | Medium | Automatic WLM |
| Enable Short Query Acceleration | Provisioned cluster WLM | Low to medium | SQA and automatic WLM |
| Isolate multiple active databases | Architecture-dependent | High | Separate clusters or workgroups |
| Change column data types | Provisioned and Serverless tables | High | No direct automatic equivalent |
The risk levels above are practical editorial guidance rather than an AWS guarantee. Actual implementation risk depends on table size, concurrency, dependencies, workload criticality, and rollback requirements.
A recommendation appears when frequently filtered columns are missing or the key order no longer matches the workload. Use ALTER TABLE to change it, preferably during a moderate-traffic window for large tables. For changing workloads, SORTKEY AUTO may be more sustainable.
Advisor analyzes join patterns, data distribution, and potential data movement to identify tables that can benefit significantly from KEY distribution. It provides grouped ALTER TABLE statements that change the table’s distribution style and distribution key.
Implement the complete recommendation group, because the benefit may depend on colocating multiple related tables. DISTSTYLE ALL can still be appropriate for some small dimension tables, but it should be presented as a separate table-design option rather than as a documented Advisor output.
For sort-key and distribution-key recommendations, no finding may mean that Redshift has not collected enough representative workload history, that the observed benefit is too small to justify a recommendation, or that current query patterns do not consistently support a specific change.
Treat Advisor as one source of optimization evidence rather than a complete certification of table design.
Advisor evaluates the table schema and workload and can provide ALTER TABLE … ALTER COLUMN … ENCODE statements for columns that would benefit from a different encoding.
Applying the recommendation consumes cluster resources and may require temporary locks, so schedule large encoding changes during a low-traffic period. A full table recreation is not normally required, although limitations such as interleaved sort keys may require an alternative migration approach.
ANALYZE COMPRESSION can also be used independently when selecting encodings for new tables or ETL pipelines..
This recommendation commonly appears after large loads, deletes, or frequent changes. Run ANALYZE on the affected tables, especially after major ETL operations.
Automatic analysis helps, but heavily updated tables may still require deliberate maintenance.
When encodings are already known and defined in the table DDL, use COMPUPDATE OFF to skip repeated analysis.
Advisor recommends compression when it detects uncompressed source objects. Update the upstream ETL process to write supported compressed formats. The source draft recommends compressed objects between 1 and 128 MB.
Advisor identifies loads with too few source files for effective parallelism. Split large files into multiple smaller compressed objects so available slices can work concurrently.
Advisor may recommend reducing the number of configured slots so that more memory is available to each active query. Review peak concurrency before making the change to ensure the observation period included normal high-demand workloads.
This recommendation applies to provisioned manual WLM configurations. Redshift Serverless manages resource allocation differently and does not support the same query-concurrency and memory-percentage settings.
Advisor recommends SQA when interactive or reporting queries regularly queue behind batch workloads. Enable it in WLM and review the maximum execution-time threshold used to classify short queries.
Advisor looks for concurrent, unrelated activity. When contention is persistent, consider separate clusters or Serverless workgroups.
Redshift Data Sharing can preserve access to shared datasets without duplicating all data.
The documented Advisor workflow is generally to add a new column using the recommended data type, backfill it from the existing column, and then update queries and ingestion processes to use the new column.
Before applying the change:
The 11 Redshift Advisor Recommendations
1. Alter Sort Keys
Sort keys determine how data is physically ordered. Advisor reviews columns used in filters and joins, then compares those patterns with the current sort key.A recommendation appears when frequently filtered columns are missing or the key order no longer matches the workload. Use ALTER TABLE to change it, preferably during a moderate-traffic window for large tables. For changing workloads, SORTKEY AUTO may be more sustainable.
2. Alter Distribution Keys
Distribution style controls how rows are spread across compute nodes. Poor choices create unnecessary data movement during joins.Advisor analyzes join patterns, data distribution, and potential data movement to identify tables that can benefit significantly from KEY distribution. It provides grouped ALTER TABLE statements that change the table’s distribution style and distribution key.
Implement the complete recommendation group, because the benefit may depend on colocating multiple related tables. DISTSTYLE ALL can still be appropriate for some small dimension tables, but it should be presented as a separate table-design option rather than as a documented Advisor output.
What It Means When Advisor Shows No Recommendation
The absence of an Advisor recommendation does not prove that a table is fully optimized. Advisor is selective and evidence-based.For sort-key and distribution-key recommendations, no finding may mean that Redshift has not collected enough representative workload history, that the observed benefit is too small to justify a recommendation, or that current query patterns do not consistently support a specific change.
Treat Advisor as one source of optimization evidence rather than a complete certification of table design.
3. Alter Compression Encodings
Redshift applies compression at the column level. Incorrect or missing encodings increase storage use and scan I/O.Advisor evaluates the table schema and workload and can provide ALTER TABLE … ALTER COLUMN … ENCODE statements for columns that would benefit from a different encoding.
Applying the recommendation consumes cluster resources and may require temporary locks, so schedule large encoding changes during a low-traffic period. A full table recreation is not normally required, although limitations such as interleaved sort keys may require an alternative migration approach.
ANALYZE COMPRESSION can also be used independently when selecting encodings for new tables or ETL pipelines..
4. Update Table Statistics
The query planner relies on row counts, value distribution, and column frequency. Stale statistics can lead to inefficient join orders and scan strategies.This recommendation commonly appears after large loads, deletes, or frequent changes. Run ANALYZE on the affected tables, especially after major ETL operations.
Automatic analysis helps, but heavily updated tables may still require deliberate maintenance.
5. Skip Compression Analysis During COPY
When COPY loads an empty table without predefined encodings, Redshift can sample incoming data to select compression. That helps with an initial load but adds overhead to predictable ETL jobs.When encodings are already known and defined in the table DDL, use COMPUPDATE OFF to skip repeated analysis.
6. Compress S3 Files Loaded by COPY
Uncompressed staging files increase S3 storage and the volume transferred during COPY.Advisor recommends compression when it detects uncompressed source objects. Update the upstream ETL process to write supported compressed formats. The source draft recommends compressed objects between 1 and 128 MB.
7. Split S3 Objects Loaded by COPY
COPY loads in parallel. A few large files can leave some compute slices idle while others continue processing.Advisor identifies loads with too few source files for effective parallelism. Split large files into multiple smaller compressed objects so available slices can work concurrently.
8. Reallocate WLM Query Slots
In provisioned clusters using manual WLM, Advisor looks for queues with consistently unused query slots. Because a queue’s memory is divided among its configured slots, unused slots can leave memory underutilized.Advisor may recommend reducing the number of configured slots so that more memory is available to each active query. Review peak concurrency before making the change to ensure the observation period included normal high-demand workloads.
This recommendation applies to provisioned manual WLM configurations. Redshift Serverless manages resource allocation differently and does not support the same query-concurrency and memory-percentage settings.
9. Enable Short Query Acceleration
Short Query Acceleration gives small, fast queries priority instead of making them wait behind long analytical jobs.Advisor recommends SQA when interactive or reporting queries regularly queue behind batch workloads. Enable it in WLM and review the maximum execution-time threshold used to classify short queries.
10. Isolate Multiple Active Databases
Multiple databases on one cluster share compute and storage. Separate teams or applications can therefore interfere with each other.Advisor looks for concurrent, unrelated activity. When contention is persistent, consider separate clusters or Serverless workgroups.
Redshift Data Sharing can preserve access to shared datasets without duplicating all data.
11. Change Column Data Types
Advisor can identify columns that may benefit from a more appropriate data type based on the table schema, stored values, and observed workload.The documented Advisor workflow is generally to add a new column using the recommended data type, backfill it from the existing column, and then update queries and ingestion processes to use the new column.
Before applying the change:
Check whether existing values can be converted safely.
Identify views, stored procedures, dashboards, and ETL processes that reference the original column.
Test query behavior and application compatibility.
Schedule large backfill operations during a lower-traffic period.
In some environments, teams may choose to rebuild and replace the table because of application dependencies, deployment processes, or rollback requirements. However, this is an alternative migration approach, not Redshift Advisor’s default recommendation.
Compression automation is handled separately through ENCODE AUTO. When a table uses ENCODE AUTO, Redshift can automatically manage column compression encodings as table characteristics change.
Before manually applying a compression recommendation, check whether the table currently uses ENCODE AUTO. Assigning explicit column encodings can change the table’s automatic encoding behavior.
These features address different areas:
When Automatic Table Optimization Is the Better Answer
Sort-key and distribution-key recommendations can return as data volumes and access patterns change. Automatic Table Optimization can reduce repeated manual tuning by allowing Redshift to manage tables configured with SORTKEY AUTO and DISTSTYLE AUTO.Compression automation is handled separately through ENCODE AUTO. When a table uses ENCODE AUTO, Redshift can automatically manage column compression encodings as table characteristics change.
Before manually applying a compression recommendation, check whether the table currently uses ENCODE AUTO. Assigning explicit column encodings can change the table’s automatic encoding behavior.
These features address different areas:
SORTKEY AUTO manages sort-key selection.
DISTSTYLE AUTO manages distribution-style selection.
ENCODE AUTO manages column compression encodings.
Automation does not make Advisor irrelevant. Statistics, COPY configuration, workload management, Short Query Acceleration, database isolation, and data-type migrations may still require separate review.
Use automatic settings for tables with changing or unpredictable access patterns. Retain manual settings where the workload is stable, tested, and intentionally tuned.
For provisioned clusters, Advisor does not decide whether to resize a cluster, change node families, or purchase Reserved Nodes. These decisions require a separate review of utilization, coverage, pricing, workload stability, and expected future demand.
For Redshift Serverless, Advisor does not recommend the correct base RPU capacity. Serverless capacity and cost management should also consider AWS-native controls such as price-performance targets, AI-driven scaling behavior, maximum capacity, and maximum RPU-hour usage limits.
Advisor also does not rewrite individual SQL queries or replace lifecycle, migration, security, governance, and architecture reviews.
These gaps matter because a technically efficient Redshift environment can still be expensive. Advisor may reduce scan time, load duration, or queue waits while the organization continues to use an unsuitable capacity configuration or pricing model.
These gaps matter because a technically efficient cluster can still be expensive. Advisor may reduce scan time or queue waits while the organization continues paying a higher compute rate because commitment coverage has not been evaluated.
A practical native workflow is:
Use automatic settings for tables with changing or unpredictable access patterns. Retain manual settings where the workload is stable, tested, and intentionally tuned.
What Redshift Advisor Does Not Cover
Advisor focuses on table design, workload configuration, statistics, and data-loading behavior. It does not provide a complete Redshift cost-management or capacity-management strategy.For provisioned clusters, Advisor does not decide whether to resize a cluster, change node families, or purchase Reserved Nodes. These decisions require a separate review of utilization, coverage, pricing, workload stability, and expected future demand.
For Redshift Serverless, Advisor does not recommend the correct base RPU capacity. Serverless capacity and cost management should also consider AWS-native controls such as price-performance targets, AI-driven scaling behavior, maximum capacity, and maximum RPU-hour usage limits.
Advisor also does not rewrite individual SQL queries or replace lifecycle, migration, security, governance, and architecture reviews.
These gaps matter because a technically efficient Redshift environment can still be expensive. Advisor may reduce scan time, load duration, or queue waits while the organization continues to use an unsuitable capacity configuration or pricing model.
These gaps matter because a technically efficient cluster can still be expensive. Advisor may reduce scan time or queue waits while the organization continues paying a higher compute rate because commitment coverage has not been evaluated.
Native AWS Workflow for Redshift Reservations
Provisioned Redshift customers can use AWS Cost Explorer to review AWS-generated Redshift reservation recommendations.A practical native workflow is:
1
Open reservation recommendations in AWS Cost Explorer.
2
Filter the recommendations for Amazon Redshift.
3
Review the suggested reservation configuration and estimated savings.
4
Compare the recommendation with current Reserved Node coverage and utilization.
5
Check existing reservation expiration dates before purchasing additional commitments.
6
Confirm that the underlying workload is expected to remain stable for the commitment term.
Cost Explorer recommendations are based primarily on historical usage. They should not be treated as forecasts of future workload demand.
Before purchasing a reservation, account for planned migrations, node-family changes, resizing, workload retirement, seasonality, and expected growth. Historical stability can support a purchase decision, but it does not guarantee future utilization.
Apply lower-disruption actions such as statistics updates and COPY-process improvements first. Schedule table rebuilds, sort key changes, distribution changes, and data type migrations during controlled maintenance windows.
Treat recurring sort and distribution recommendations as candidates for Automatic Table Optimization.
After each change, give Advisor time to observe the updated workload. Confirm the recommendation clears and check query duration, queue time, disk spilling, load duration, and scan volume.
Finally, review Redshift costs separately. Advisor can improve the cluster, but Reserved Node coverage and utilization require another analysis layer.
Commitment management addresses a separate question: whether stable provisioned usage should be covered by a pricing commitment and how that commitment should be evaluated over time.
AWS Cost Explorer provides native Amazon Redshift reservation recommendations based on historical usage. Organizations may also use third-party commitment-management platforms for additional analysis or operational workflows.
Any specific claims about Usage.ai analyzing Redshift utilization, identifying stable usage, recommending Reserved Nodes, or managing Redshift commitments should be supported by first-party Usage.ai product documentation that clearly explains:
Before purchasing a reservation, account for planned migrations, node-family changes, resizing, workload retirement, seasonality, and expected growth. Historical stability can support a purchase decision, but it does not guarantee future utilization.
A Practical Monthly Advisor Workflow
Begin with the highest-impact item, but assess operational risk before implementing it.Apply lower-disruption actions such as statistics updates and COPY-process improvements first. Schedule table rebuilds, sort key changes, distribution changes, and data type migrations during controlled maintenance windows.
Treat recurring sort and distribution recommendations as candidates for Automatic Table Optimization.
After each change, give Advisor time to observe the updated workload. Confirm the recommendation clears and check query duration, queue time, disk spilling, load duration, and scan volume.
Finally, review Redshift costs separately. Advisor can improve the cluster, but Reserved Node coverage and utilization require another analysis layer.
Redshift Advisor and Commitment Management
Redshift Advisor focuses on technical efficiency inside Amazon Redshift, including table design, statistics, loading behavior, and workload configuration.Commitment management addresses a separate question: whether stable provisioned usage should be covered by a pricing commitment and how that commitment should be evaluated over time.
AWS Cost Explorer provides native Amazon Redshift reservation recommendations based on historical usage. Organizations may also use third-party commitment-management platforms for additional analysis or operational workflows.
Any specific claims about Usage.ai analyzing Redshift utilization, identifying stable usage, recommending Reserved Nodes, or managing Redshift commitments should be supported by first-party Usage.ai product documentation that clearly explains:
Which Redshift products and configurations are supported.
How utilization and stability are evaluated.
Eligibility requirements.
Recommendation methodology.
Limitations and exclusions.
How its analysis differs from AWS Cost Explorer.
Without a supporting source, avoid making specific product-capability claims and describe the section as vendor positioning rather than independently verified functionality.
In data-sharing environments, Advisor can also consider query patterns from Serverless consumers when evaluating shared producer tables.
However, Advisor does not determine the appropriate base RPU capacity for a Serverless workgroup. Serverless capacity and cost decisions should be reviewed separately using controls such as:
Does Redshift Advisor work with Redshift Serverless?
Yes. Redshift Advisor supports Serverless workgroups and can analyze applicable table design, statistics, data-loading, and workload behavior.In data-sharing environments, Advisor can also consider query patterns from Serverless consumers when evaluating shared producer tables.
However, Advisor does not determine the appropriate base RPU capacity for a Serverless workgroup. Serverless capacity and cost decisions should be reviewed separately using controls such as:
Base capacity.
Price-performance targets.
AI-driven scaling behavior.
Maximum capacity.
Maximum RPU-hour usage limits.
Query latency and workload demand.
Actual RPU consumption.
These controls help teams balance performance and cost, but they are separate from the table-level and workload recommendations produced by Advisor.
AWS Trusted Advisor evaluates the broader AWS environment across areas such as cost optimization, security, performance, resilience, and service limits. It may identify an idle or underused Redshift cluster, but it does not provide the same table-level and workload-specific recommendations generated by Redshift Advisor.
The two services are complementary rather than interchangeable.
Allow normal workloads to run, then review the recommendation again. You should also confirm the result using relevant performance metrics rather than relying only on whether the Advisor item disappears.
Before acting, review:
What is the difference between Redshift Advisor and AWS Trusted Advisor?
Redshift Advisor focuses specifically on Amazon Redshift. It evaluates table structures, query patterns, data-loading processes, statistics, workload queues, and other cluster-level conditions.AWS Trusted Advisor evaluates the broader AWS environment across areas such as cost optimization, security, performance, resilience, and service limits. It may identify an idle or underused Redshift cluster, but it does not provide the same table-level and workload-specific recommendations generated by Redshift Advisor.
The two services are complementary rather than interchangeable.
Why does a recommendation remain after I implement it?
Advisor may need additional workload history before it can confirm that the underlying condition has improved. A recommendation does not necessarily disappear immediately after an ALTER TABLE, ANALYZE, WLM update, or ETL change.Allow normal workloads to run, then review the recommendation again. You should also confirm the result using relevant performance metrics rather than relying only on whether the Advisor item disappears.
Should every Redshift Advisor recommendation be implemented?
No. Advisor recommendations are based on observed inefficiencies, but implementation still requires operational judgment.Before acting, review:
The estimated impact of the recommendation.
The size and importance of the affected table.
The resources required to implement the change.
Whether the workload pattern is temporary or permanent.
Whether an automatic feature would solve the problem more sustainably.
A statistics refresh may be safe to apply quickly, while a data type migration or distribution-key change may require testing and a maintenance window.
However, Advisor does not provide a complete pricing or commitment strategy.
Provisioned customers should separately review cluster sizing, Reserved Node recommendations, reservation coverage, utilization, and expiration dates. Redshift Serverless customers should separately review RPU consumption, capacity settings, price-performance controls, maximum capacity, and RPU-hour limits.
Use Advisor for technical optimization, then evaluate capacity and commitment decisions through the appropriate AWS-native or independently substantiated third-party workflow.
Can Redshift Advisor reduce Redshift costs?
Advisor can reduce indirect costs by improving storage efficiency, query execution, data loading, and resource utilization. Better compression may reduce storage consumption, while improved sort and distribution choices can reduce scan volume and data movement.However, Advisor does not provide a complete pricing or commitment strategy.
Provisioned customers should separately review cluster sizing, Reserved Node recommendations, reservation coverage, utilization, and expiration dates. Redshift Serverless customers should separately review RPU consumption, capacity settings, price-performance controls, maximum capacity, and RPU-hour limits.
Use Advisor for technical optimization, then evaluate capacity and commitment decisions through the appropriate AWS-native or independently substantiated third-party workflow.
Frequently asked questions
What is Amazon Redshift Advisor?
Amazon Redshift Advisor is a built-in optimization service that analyzes cluster performance metrics, query history, table structure, and data-loading patterns. It generates recommendations for areas such as sort keys, distribution keys, compression, table statistics, COPY operations, workload management, and Short Query Acceleration.
Is Redshift Advisor free?
Yes. Redshift Advisor is included with Amazon Redshift at no additional charge. However, implementing certain recommendations; such as rebuilding a large table or changing its sort key may consume cluster resources.
How often does Redshift Advisor update recommendations?
Redshift Advisor does not publish a fixed update schedule. Recommendations appear when Advisor detects an observed condition outside its expected best-practice range.
Does Redshift Advisor work with Redshift Serverless?
Yes. Advisor supports applicable Redshift Serverless workloads, including table design, statistics, data loading, and shared-data access patterns.
Why is Redshift Advisor not showing in the console?
Advisor is only available in supported AWS Regions. If it does not appear in the Redshift console, first check regional availability. If the region is supported, verify that the IAM identity has the redshift:ListRecommendations permission.
Share
Facebook
X
LinkedIn
Reddit
