How do I validate Amazon Redshift cluster performance before and after a resize?

Resolution

Prerequisites

• Access to your Redshift cluster via a SQL client (Amazon Redshift Query Editor v2, psql, or any JDBC/ODBC client)
• Queries use system tables (STL_*, STV_*) and SYS monitoring views (sys_query_history, sys_load_history). You can use either — if SYS views are available on your cluster, we recommend using those as they provide better retention and performance.

When to capture metrics

Phase	Timing	Notes
Baseline (before resize)	1 day before the resize	Capture during normal peak workload hours
Post-resize	1-2 days after resize completes	Allow time for result cache warmup and data redistribution
Validation	7 days after resize	Confirm long-term stability

Important: Capture baseline and post-resize metrics at the same time of day to ensure comparable workload patterns.

---

Step 1: Capture cluster configuration

Run the following queries to document your current cluster layout before resize.

Node and slice configuration:

SELECT
    node,
    CASE WHEN node = 0 THEN 'Leader' ELSE 'Compute' END AS node_role,
    slice AS slices_on_node
FROM stv_slices
GROUP BY node, slice
ORDER BY node;

Storage capacity and usage:

SELECT 
    COUNT(DISTINCT host) AS nodes,
    SUM(capacity) / 1024 AS total_capacity_gb,
    SUM(used) / 1024 AS total_used_gb,
    ROUND((SUM(used)::FLOAT / SUM(capacity)) * 100, 1) AS pct_used
FROM stv_partitions
WHERE capacity > 0;

---

Step 2: Capture query performance baseline

Top 50 most expensive queries (last 7 days):

Using STL views:

SELECT 
    query,
    TRIM(database) AS db,
    TRIM(querytxt) AS query_text,
    ROUND(DATEDIFF(microsecond, starttime, endtime) / 1000000.0, 2) AS elapsed_sec,
    starttime,
    aborted
FROM stl_query
WHERE starttime > GETDATE() - 7
  AND userid > 1
ORDER BY elapsed_sec DESC
LIMIT 50;

Using SYS views:

SELECT 
    query_id,
    TRIM(database_name) AS db,
    LEFT(query_text, 200) AS query_text,
    ROUND(execution_time / 1000000.0, 2) AS elapsed_sec,
    ROUND(queue_time / 1000000.0, 2) AS queue_sec,
    start_time,
    status
FROM sys_query_history
WHERE start_time > GETDATE() - 7
  AND user_id > 1
  AND status = 'success'
ORDER BY execution_time DESC
LIMIT 50;

Note: Save the query results from this step. After resize, you can re-run the same workload and compare elapsed times.

Query execution time distribution:

Using SVL views:

SELECT 
    SUM(CASE WHEN elapsed < 1000000 THEN 1 ELSE 0 END) AS under_1s,
    SUM(CASE WHEN elapsed BETWEEN 1000000 AND 5000000 THEN 1 ELSE 0 END) AS "1_to_5s",
    SUM(CASE WHEN elapsed BETWEEN 5000001 AND 10000000 THEN 1 ELSE 0 END) AS "5_to_10s",
    SUM(CASE WHEN elapsed BETWEEN 10000001 AND 30000000 THEN 1 ELSE 0 END) AS "10_to_30s",
    SUM(CASE WHEN elapsed BETWEEN 30000001 AND 60000000 THEN 1 ELSE 0 END) AS "30_to_60s",
    SUM(CASE WHEN elapsed > 60000000 THEN 1 ELSE 0 END) AS over_60s
FROM svl_qlog
WHERE starttime > GETDATE() - 7
  AND userid > 1
  AND elapsed > 0;

Using SYS views:

SELECT 
    SUM(CASE WHEN execution_time < 1000000 THEN 1 ELSE 0 END) AS under_1s,
    SUM(CASE WHEN execution_time BETWEEN 1000000 AND 5000000 THEN 1 ELSE 0 END) AS "1_to_5s",
    SUM(CASE WHEN execution_time BETWEEN 5000001 AND 10000000 THEN 1 ELSE 0 END) AS "5_to_10s",
    SUM(CASE WHEN execution_time BETWEEN 10000001 AND 30000000 THEN 1 ELSE 0 END) AS "10_to_30s",
    SUM(CASE WHEN execution_time BETWEEN 30000001 AND 60000000 THEN 1 ELSE 0 END) AS "30_to_60s",
    SUM(CASE WHEN execution_time > 60000000 THEN 1 ELSE 0 END) AS over_60s
FROM sys_query_history
WHERE start_time > GETDATE() - 7
  AND user_id > 1
  AND status = 'success';

---

Step 3: Capture throughput and latency trends

Hourly throughput with percentile latency:

Using SVL views:

SELECT 
    DATE_TRUNC('hour', starttime) AS hour,
    COUNT(*) AS total_queries,
    ROUND(AVG(elapsed) / 1000000.0, 2) AS avg_elapsed_sec,
    ROUND(PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY elapsed) / 1000000.0, 2) AS p50_sec,
    ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY elapsed) / 1000000.0, 2) AS p95_sec,
    ROUND(PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY elapsed) / 1000000.0, 2) AS p99_sec
FROM svl_qlog
WHERE starttime > GETDATE() - 7
  AND userid > 1
  AND elapsed > 0
GROUP BY 1
ORDER BY 1;

Using SYS views:

SELECT 
    DATE_TRUNC('hour', start_time) AS hour,
    COUNT(*) AS total_queries,
    ROUND(AVG(execution_time) / 1000000.0, 2) AS avg_elapsed_sec,
    ROUND(PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY execution_time) / 1000000.0, 2) AS p50_sec,
    ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY execution_time) / 1000000.0, 2) AS p95_sec,
    ROUND(PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY execution_time) / 1000000.0, 2) AS p99_sec
FROM sys_query_history
WHERE start_time > GETDATE() - 7
  AND user_id > 1
  AND status = 'success'
GROUP BY 1
ORDER BY 1;

Peak concurrency:

SELECT 
    DATE_TRUNC('minute', starttime) AS minute,
    COUNT(*) AS concurrent_queries
FROM stl_query
WHERE starttime > GETDATE() - 1
  AND userid > 1
GROUP BY 1
ORDER BY concurrent_queries DESC
LIMIT 20;

---

Step 4: Capture WLM (Workload Management) performance

Queue wait times by service class:

Using STL views:

SELECT 
    service_class,
    COUNT(*) AS total_queries,
    ROUND(AVG(total_queue_time) / 1000000.0, 2) AS avg_queue_sec,
    ROUND(MAX(total_queue_time) / 1000000.0, 2) AS max_queue_sec,
    ROUND(AVG(total_exec_time) / 1000000.0, 2) AS avg_exec_sec,
    ROUND(MAX(total_exec_time) / 1000000.0, 2) AS max_exec_sec
FROM stl_wlm_query
WHERE exec_start_time > GETDATE() - 7
  AND service_class > 5
GROUP BY service_class
ORDER BY service_class;

Using SYS views:

SELECT 
    service_class_id,
    COUNT(*) AS total_queries,
    ROUND(AVG(queue_time) / 1000000.0, 2) AS avg_queue_sec,
    ROUND(MAX(queue_time) / 1000000.0, 2) AS max_queue_sec,
    ROUND(AVG(execution_time) / 1000000.0, 2) AS avg_exec_sec,
    ROUND(MAX(execution_time) / 1000000.0, 2) AS max_exec_sec
FROM sys_query_history
WHERE start_time > GETDATE() - 7
  AND user_id > 1
  AND service_class_id > 5
GROUP BY service_class_id
ORDER BY service_class_id;

---

Step 5: Check data distribution and sort key health

After a resize, data is redistributed across the new slice count. Run these to detect skew.

Distribution skew (top 20 tables):

SELECT 
    TRIM(pgn.nspname) AS schema_name,
    TRIM(pgc.relname) AS table_name,
    MAX(slice_rows) AS max_slice_rows,
    MIN(slice_rows) AS min_slice_rows,
    ROUND(MAX(slice_rows)::FLOAT / NULLIF(MIN(slice_rows), 0), 2) AS skew_ratio
FROM (
    SELECT tbl, slice, COUNT(*) AS slice_rows
    FROM stv_blocklist
    GROUP BY tbl, slice
) bl
JOIN pg_class pgc ON pgc.oid = bl.tbl
JOIN pg_namespace pgn ON pgn.oid = pgc.relnamespace
WHERE pgn.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_internal')
GROUP BY 1, 2
HAVING COUNT(DISTINCT slice) > 1
ORDER BY skew_ratio DESC
LIMIT 20;

Unsorted rows (tables needing VACUUM):

SELECT 
    TRIM(schema) AS schema_name,
    TRIM("table") AS table_name,
    size AS size_mb,
    unsorted,
    tbl_rows
FROM svv_table_info
WHERE schema_name NOT IN ('pg_catalog', 'information_schema', 'pg_internal')
  AND tbl_rows > 10000
ORDER BY unsorted DESC
LIMIT 20;

---

Step 6: Capture COPY and ingestion performance

Using STL views:

SELECT 
    query,
    TRIM(filename) AS s3_path,
    lines_scanned,
    ROUND(DATEDIFF(microsecond, q.starttime, q.endtime) / 1000000.0, 2) AS elapsed_sec,
    q.starttime
FROM stl_load_commits lc
JOIN stl_query q USING (query)
WHERE q.starttime > GETDATE() - 7
  AND q.userid > 1
ORDER BY elapsed_sec DESC
LIMIT 20;

Using SYS views:

SELECT 
    query_id,
    table_name,
    TRIM(data_source) AS s3_path,
    loaded_rows,
    loaded_bytes / 1024 / 1024 AS mb_loaded,
    ROUND(duration / 1000000.0, 2) AS elapsed_sec,
    start_time
FROM sys_load_history
WHERE start_time > GETDATE() - 7
ORDER BY duration DESC
LIMIT 20;

---

Step 7: Compare results after resize

After the resize completes and caches have warmed (1-2 days), re-run all the queries above and compare the results.

Key metrics to compare:

Metric	Good sign	Potential concern
Average query time	Lower than or equal to baseline	More than 15% higher than baseline
P95 latency	Lower than or equal to baseline	More than 25% higher than baseline
Queries per hour (throughput)	Same or higher	Significant drop
WLM queue wait time	Lower	Increased wait times
Distribution skew ratio	Close to 1.0	Greater than 5.0
Unsorted rows	Less than 5%	Greater than 20%
COPY throughput	Same or faster	Significantly slower

---

Step 8: Post-resize optimization (if needed)

If you observe regressions after resize, these maintenance operations can help:

-- Update query planner statistics for all tables
ANALYZE;

-- Re-sort and reclaim space
VACUUM FULL;

-- For specific tables with high unsorted percentage:
VACUUM SORT ONLY schema_name.table_name;

If you observe significant distribution skew on specific tables, consider reviewing your distribution key:

-- Check current distribution style
SELECT TRIM("table") AS table_name, diststyle 
FROM svv_table_info 
WHERE "schema" = 'public'
ORDER BY size DESC
LIMIT 20;

---

How to determine which monitoring views are available

If you're unsure whether to use STL or SYS views, run this query:

SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'pg_catalog' 
  AND table_name LIKE 'sys_%'
ORDER BY table_name;

If sys_query_history appears in the results, you can use the SYS view queries. If both STL and SYS views return data, prefer SYS views for better retention and performance.

---

Summary checklist

• Day -1: Run Steps 1-6 and save results as your baseline
• Day 0: Resize the cluster, wait for status "available"
• Day 0 + ~2 days: Re-run Steps 1-6, compare with baseline (Step 7)
• If regressions found: Run ANALYZE and VACUUM (Step 8)
• Day +1: Re-run comparison to confirm optimization worked
• Day +7: Final validation run to confirm stability

---

Important notes

• System table retention: STL_* tables retain approximately 2-7 days of query history depending on cluster activity. Capture your baseline before the resize.
• Time units: All elapsed, queue_time, and execution_time values in system tables are in microseconds. Divide by 1,000,000 to convert to seconds.
• userid > 1: This filter excludes internal system queries so only user-initiated workload is compared.
• Elastic vs. Classic resize: Elastic resize (same node type, different node count) completes in minutes but temporarily reduces available slices during the operation. Classic resize (different node type) takes longer but creates a new cluster from snapshot.
• Running VACUUM after resize: After a resize that changes the node/slice count, Redshift automatically redistributes data. Running VACUUM and ANALYZE after this process can improve performance of queries that rely on sort order or up-to-date statistics.

---

Related information

• Managing cluster operations (Resize)
• SYS monitoring views
• VACUUM command reference
• STL views for logging
• Amazon Redshift best practices for designing tables