How do I improve the performance and cost of my Amazon Redshift cluster as my workload increases?

Resolution

Plan capacity as an ongoing activity

To plan for capacity, make sure that you monitor your storage and compute as your data and workload grow.

To prevent performance degradation, it's a best practice to have at least 30% of free disk space to accommodate table growth, maintenance operations, and new data ingestion. With managed storage on RA3 and RG nodes, data automatically tiers between local SSD and Amazon Simple Storage Service (Amazon S3), so disk capacity isn't the only factor.

Monitor the following metrics in Amazon CloudWatch for your cluster:

• CPUUtilization
• PercentageDiskSpaceUsed
• QueueWaitTime
• ConcurrencyScalingSeconds

To identify the top 20 disk spill queries for a specified time, run the following query:

SELECT q.userid, q.query, q.starttime, q.endtime, m.query_temp_blocks_to_disk, btrim(querytxt)  
 FROM stl_query q JOIN SVL_QUERY_METRICS_SUMMARY m ON m.query = q.query 
 WHERE m.query_temp_blocks_to_disk > 0 
   AND starttime BETWEEN '2025-01-01 00:00:00' AND '2025-01-02 00:00:00' 
 ORDER BY m.query_temp_blocks_to_disk DESC 
 LIMIT 20;

Even if you have storage, the cluster can be under-provisioned on compute. If you have disk spills, high CPU utilization, long queue wait times, and increased concurrency scaling usage, then the cluster is close to its compute limits.

Scale your cluster to manage your workload.

Choose the right resize method

If your cluster is eligible, then use elastic resize for a configuration to keep slices balanced across all nodes. For more information, see How does elastic resize on my Amazon Redshift cluster work?

If the target configuration leaves slices imbalanced across nodes, then choose Classic Resize with Enhancements. For multi-node RA3 & RG targets, you have a short read-only window, then the cluster is available for reads and writes as the heavier data redistribution finishes.

To check if the slice count is balanced across nodes on the target configuration, run the following query:

select node, type, count(slice) from stv_slices group by 1,2 order by 2,1;

Note: If you used enhanced classic resize and the cluster is in heavy use during the Stage 2 process, then the cluster might experience slowness.

Use the Amazon Redshift Test Drive tool to validate your workload

Use the Amazon Redshift Test Drive tool to validate your workload on the target configuration and to compare node types and sizes. The tool helps you confirm that a new configuration meets your performance needs and identifies the most cost-effective option before you commit to it. For more information about the tool, see redshift-test-drive on the GitHub website.

Migrate to RG instances

To improve price and performance, migrate to Amazon Redshift RG instances powered by AWS Graviton. For information on speed and cost efficiency, see Amazon Redshift RG instances. For more information, see RG Migration best practices.

Monitor snapshot costs around a resize

If you turned on cross-Region snapshot copy, monitor your snapshot and data transfer costs around a resize, as snapshot activity might change. For information on pricing, see Data transfer in Amazon Redshift Pricing.

Load data in bulk instead of single row writes

Frequent single-row or small-batch INSERT statements create many small commits, leave rows out of sort order, and can build up tombstoned blocks. As the workload grows, the added overhead affects cluster throughput.

To check the breakdown of small writes, run the following query:

WITH all_insert_queries AS (
    SELECT date_trunc('day', start_time) AS day, query_type, query_id
    FROM sys_query_history
    WHERE query_type IN ('INSERT', 'COPY')          
      AND start_time >= '2026-01-01 00:00:00'  -- replace with your start time
),
rows_in_insert_step AS (
    SELECT i.day, i.query_id, i.query_type, SUM(d.output_rows) AS rowsinserted
    FROM all_insert_queries i
    JOIN sys_query_detail d USING (query_id)         
    WHERE d.step_name = 'insert'
      AND d.metrics_level = 'Step'
    GROUP BY 1, 2, 3
),
categorized_writes AS (
    SELECT query_id, day, query_type,
        CASE
          WHEN rowsinserted BETWEEN 0 AND 100 THEN '0-100'
          WHEN rowsinserted BETWEEN 101 AND 1000 THEN '101-1000'
          WHEN rowsinserted BETWEEN 1001 AND 100000 THEN '1001-100000'
          WHEN rowsinserted > 100000 THEN '> 100000'
          ELSE NULL::text
        END AS rowsinserted_type
    FROM rows_in_insert_step
)
SELECT
    query_type,
    rowsinserted_type,
    COUNT(*) AS query_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY query_type), 2) AS percentage_within_type,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS percentage_of_total
FROM categorized_writes
GROUP BY 1, 2
ORDER BY 1, 3 DESC;

It's best practice to run the COPY command to load large datasets and to directly read compressed files from Amazon S3. If you load your data in sort key order with the COPY command, then you might reduce or not need to vacuum. For more information, see Load Data in sortkey order.

Run a manual VACUUM or deep copy only after you confirm data sortedness

If you regularly observe slow performance, then confirm the root cause before you run a manual VACUUM or deep copy. For large tables, these operations can consume significant compute and I/O. It's a best practice to run these operations after you verify that data sortedness causes the slow performance and when the cluster workload is low.

Use VACUUM RECLUSTER to sort only the unsorted portions. It's a best practice to use VACUUM RECLUSTER for large tables with frequent ingestion. The table might show as not fully sorted in SVV_TABLE_INFO because it doesn't fully merge or reclaim all deleted space. For more information, see Vacuuming tables.

If query performance doesn't improve and to fully sort the table, run a deep copy to sort a large, unsorted table. Depending on the table's size and the volume of data to sort, a deep copy requires more memory and disk. For large tables, run it only during an idle cluster window.

Note: Amazon Redshift also auto-sorts in the background and reduces how often you need to run manual VACUUM.

Amazon Redshift now supports allocating extra compute for automatic optimizations. To review pricing and setup usage limits, see Cost control for autonomics using extra compute resources.

To identify the problem, run the following query to check table row count and how unsorted the table is:

SELECT "table", tbl_rows, unsorted, stats_off, sortkey1
FROM svv_table_info
WHERE "table" = 'table_name';

Note: Replace table_name with the name of your table.

To confirm that the cluster workload is low for maintenance, run the following query to check the current workload state:

SELECT state, count(query) FROM stv_wlm_query_state GROUP BY 1 ORDER BY 2 DESC;

In the output, if many queries are in the Running or Queued state, then the active workload is ongoing. It's a best practice to not run VACUUM on a large table during an ongoing workload.

To confirm that large scans cause issues for a slow query and review the scan steps in the query report, run the following query:

SELECT * FROM svl_query_report WHERE query = query_id ORDER BY segment, step, slice; 

Note: Replace query_id with your query ID.

In the output, on the scan step, if you see a large gap between rows and rows_pre_filter, then the scan reads more than it returns.

Add query guardrails with QMR rules

Create workload management (WLM) query monitoring rules (QMR) to set metric-based thresholds on queries and define what action Amazon Redshift takes to maintain cluster performance.

Use the wlm_qmr_rule_candidates.sql script from the amazon-redshift-utils repository to analyze your query history and QMR thresholds based on your workload. To see more information, see wlm_gmr_rules_candidates.sql on the GitHub website.

Use concurrency scaling for bursts

Concurrency scaling automatically adds cluster capacity when concurrency increases because of user activity, and then removes the capacity when the burst subsides.

If your main cluster runs at capacity against a growing workload, then you see an increase in your concurrency scaling over time. It's a best practice to add compute to the main cluster to maximize throughput.

Use concurrency scaling for unpredictable concurrency and use base compute for the predictable baseline.

Note: For information about pricing, see Concurrency Scaling pricing on Amazon Redshift pricing.

To track concurrency scaling usage over time, monitor the ConcurrencyScalingSeconds and ConcurrencyScalingActiveClusters metrics in CloudWatch.

Set a budget to track and control costs

Create a budget with AWS Budgets with alerts to manage your costs for Amazon Redshift, including concurrency scaling and snapshot costs.

Under Budget scope, for Filters, choose Amazon Redshift. Then, refine by usage type, such as ConcurrencyScaling, PaidSnapshots, and InterRegionSnapshotCopy.

Related information

How do I choose the right cluster size and scaling strategy for my Amazon Redshift workload?

Amazon Redshift best practices for loading data

Amazon Redshift Advisor recommendations