Skip to content

cloudthinker-ai/CloudSkills / skills/connections/analyzing-redshift

analyzing-redshift

Unverified

Use when working with Redshift — amazon Redshift query performance, WLM configuration, table design analysis, vacuum/analyze status, and cluster health.

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

ShortcutCounterWhy
"I'll skip discovery and check known resources"Always run Phase 1 discovery firstResource names change, new resources appear — assumed names cause errors
"The user only asked for a quick check"Follow the full discovery → analysis flowQuick checks miss critical issues; structured analysis catches silent failures
"Default configuration is probably fine"Audit configuration explicitlyDefaults often leave logging, security, and optimization features disabled
"Metrics aren't needed for this"Always check relevant metrics when availableAPI/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 errorAssumed 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
brainstormingobra/superpowersYou MUST use this before any creative work - creating features, building components, adding functionality, modifying behavior, or planning anything new, in software or out of it (a talk, a business, a renovation).Stars: 318kUpdated Claude CodeCodexUnverifiedInstall
brand-guidelinesanthropics/skillsApplies Anthropic's official brand colors and typography to any sort of artifact that may benefit from having Anthropic's look-and-feel. Use it when brand colors or style guidelines, visual formatting, or company design standards apply.Stars: 196kUpdated Claude CodeCodexVerifiedInstall
frontend-designanthropics/skillsGuidance for distinctive, intentional visual design when building new UI or reshaping an existing one. Helps with aesthetic direction, typography, and making choices that don't read as templated defaults.Stars: 196kUpdated Claude CodeCodexVerifiedInstall
mcp-builderanthropics/skillsGuide for creating high-quality MCP (Model Context Protocol) servers that enable LLMs to interact with external services through well-designed tools. Use when building MCP servers to integrate external APIs or services, whether in Python (FastMCP) or Node/TypeScript (MCP SDK).Stars: 196kUpdated Claude CodeCodexVerifiedInstall