analyzing-redshift

name	analyzing-redshift
description	Use when working with Redshift — amazon Redshift query performance, WLM configuration, table design analysis, vacuum/analyze status, and cluster health.
connection_type	redshift
preload	false

Redshift Analysis Skill

Analyze and optimize Redshift clusters with safe, read-only operations.

MANDATORY: Two-Phase Execution

You MUST follow this two-phase pattern. Skipping Phase 1 causes hallucinated schema/table names.

Phase 1: Discovery (ALWAYS run first)

#!/bin/bash

# 1. List schemas
psql "$REDSHIFT_URI" -c "SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema', 'pg_catalog') ORDER BY schema_name;"

# 2. List tables in a schema
psql "$REDSHIFT_URI" -c "SELECT tablename, tabletype FROM pg_catalog.svv_tables WHERE table_schema = 'public' ORDER BY tablename;"

# 3. Get table schema (never assume column names)
psql "$REDSHIFT_URI" -c "\d public.my_table"

# 4. Table design (dist/sort keys)
psql "$REDSHIFT_URI" -c "SELECT tablename, diststyle, sortkey1 FROM pg_catalog.svv_table_info WHERE schema = 'public' ORDER BY tablename;"

# 5. Sample data
psql "$REDSHIFT_URI" -c "SELECT * FROM public.my_table LIMIT 5;"

Phase 1 outputs:

• Schemas and tables
• Column names and data types
• Distribution style and sort keys

Phase 2: Analysis (only after Phase 1)

Only reference schemas, tables, and columns confirmed in Phase 1.

Shell Script Patterns

Helper Function

#!/bin/bash

# Core Redshift query runner — always use this
rs_query() {
    local query="$1"
    psql "${REDSHIFT_URI}" -t -A -F$'\t' -c "$query"
}

# AWS CLI helper for Redshift
rs_aws() {
    aws redshift "$@" --output json
}

Anti-Hallucination Rules

• NEVER reference a schema or table without confirming via svv_tables or information_schema
• NEVER reference column names without seeing them in table description
• NEVER assume distribution style — always check svv_table_info
• NEVER guess sort keys — always verify from table definition
• NEVER assume WLM queue configuration — always query system tables

Safety Rules

• READ-ONLY ONLY: Use only SELECT, EXPLAIN, system table queries
• FORBIDDEN: DROP, ALTER, INSERT, UPDATE, DELETE, VACUUM, ANALYZE without explicit user request
• ALWAYS add LIMIT to queries on user tables
• Use EXPLAIN before running expensive queries
• Query system tables (STL_, STV_, SVV_) for metadata

Common Operations

Cluster Health Overview

#!/bin/bash
echo "=== Cluster Info ==="
rs_query "SELECT host, version() as version;"

echo ""
echo "=== Node Slices ==="
rs_query "SELECT node, slice, type FROM stv_slices ORDER BY node, slice;"

echo ""
echo "=== Disk Usage ==="
rs_query "SELECT owner, host, diskno, used, capacity, ROUND(used::float/capacity*100, 1) as pct_used FROM stv_partitions WHERE part_begin = 0 ORDER BY pct_used DESC;"

echo ""
echo "=== Table Sizes ==="
rs_query "SELECT schema, \"table\", size as size_mb, tbl_rows, diststyle, sortkey1 FROM svv_table_info ORDER BY size DESC LIMIT 20;"

Query Performance Analysis

#!/bin/bash
echo "=== Slow Queries (last 24h) ==="
rs_query "SELECT query, TRIM(querytxt) as query_text, starttime, endtime, DATEDIFF(second, starttime, endtime) as duration_sec, aborted FROM stl_query WHERE starttime > DATEADD(hour, -24, GETDATE()) AND userid > 1 ORDER BY duration_sec DESC LIMIT 20;"

echo ""
echo "=== Queue Wait Times ==="
rs_query "SELECT query, service_class, total_queue_time/1000000.0 as queue_sec, total_exec_time/1000000.0 as exec_sec FROM stl_wlm_query WHERE starttime > DATEADD(hour, -24, GETDATE()) ORDER BY total_queue_time DESC LIMIT 15;"

echo ""
echo "=== Disk-Based Queries (spilled to disk) ==="
rs_query "SELECT query, segment, step, rows, bytes, label FROM stl_disk_full_scan WHERE starttime > DATEADD(hour, -24, GETDATE()) ORDER BY bytes DESC LIMIT 10;" 2>/dev/null

WLM Configuration

#!/bin/bash
echo "=== WLM Configuration ==="
rs_query "SELECT service_class, num_query_tasks, query_working_mem, max_execution_time, user_group_wild_card, query_group_wild_card FROM stv_wlm_classification_config ORDER BY service_class;"

echo ""
echo "=== WLM Queue Status ==="
rs_query "SELECT service_class, num_queued_queries, num_executing_queries, num_executed_queries FROM stv_wlm_service_class_state ORDER BY service_class;"

echo ""
echo "=== Concurrency Scaling ==="
rs_query "SELECT service_class, query_priority, concurrency_scaling FROM svv_wlm_query_queue_state ORDER BY service_class;" 2>/dev/null

Table Design Analysis

#!/bin/bash
echo "=== Tables with Skewed Distribution ==="
rs_query "SELECT schema, \"table\", diststyle, skew_rows, skew_sortkey1 FROM svv_table_info WHERE skew_rows > 2.0 ORDER BY skew_rows DESC LIMIT 15;"

echo ""
echo "=== Vacuum Status ==="
rs_query "SELECT schema, \"table\", empty, unsorted, stats_off, tbl_rows FROM svv_table_info WHERE unsorted > 5 OR stats_off > 5 ORDER BY unsorted DESC LIMIT 20;"

echo ""
echo "=== Tables Needing ANALYZE ==="
rs_query "SELECT schema, \"table\", stats_off FROM svv_table_info WHERE stats_off > 10 ORDER BY stats_off DESC LIMIT 20;"

Output Format

Present results as a structured report:

Analyzing Redshift Report
═════════════════════════
Resources discovered: [count]

Resource       Status    Key Metric    Issues
──────────────────────────────────────────────
[name]         [ok/warn] [value]       [findings]

Summary: [total] resources | [ok] healthy | [warn] warnings | [crit] critical
Action Items: [list of prioritized findings]

Target ≤50 lines of output. Use tables for multi-resource comparisons.

Counter-Rationalizations

Shortcut	Counter	Why
"I'll skip discovery and check known resources"	Always run Phase 1 discovery first	Resource names change, new resources appear — assumed names cause errors
"The user only asked for a quick check"	Follow the full discovery → analysis flow	Quick checks miss critical issues; structured analysis catches silent failures
"Default configuration is probably fine"	Audit configuration explicitly	Defaults often leave logging, security, and optimization features disabled
"Metrics aren't needed for this"	Always check relevant metrics when available	API/CLI responses show current state; metrics reveal trends and intermittent issues
"I don't have access to that"	Try the command and report the actual error	Assumed permission failures prevent useful investigation; actual errors are informative

Common Pitfalls

• Distribution key skew: Poor dist key choice causes data skew — check skew_rows in svv_table_info
• Sort key staleness: Unsorted regions degrade query performance — check unsorted percentage
• WLM queue contention: Too many queues or wrong concurrency causes queueing — monitor queue wait times
• COPY vs INSERT: Use COPY for bulk loads — INSERT is single-row and very slow
• Encoding: Column compression encoding affects scan speed — check with ANALYZE COMPRESSION
• Leader node queries: Some functions run only on leader node — avoid in large distributed queries
• Commit queue: Frequent small commits block writes — batch operations where possible