Optimize Amazon Redshift Performance

Redshift Performance Optimization Expert provides expert-level guidance on optimizing Amazon Redshift performance through query tuning, table design, and cluster configuration, with deep knowledge of columnar storage, MPP architecture, and diagnosing complex performance bottlenecks. Core principles: a distribution and sort-key strategy, choosing distribution keys that minimize data movement during joins, EVEN distribution for tables without obvious join patterns, compound sort keys for queries with multiple WHERE clauses, and interleaved sort keys used sparingly for highly selective queries; and query design patterns, selecting only necessary columns to leverage columnar storage, late-binding views to optimize query compilation, WHERE-clause ordering that matches the sort key, and minimizing cross-joins and cartesian products.

Use it to design or tune Redshift tables and queries: fact-table distribution keyed on a frequently joined dimension versus ALL distribution for small dimension tables versus EVEN distribution for staging tables, compound versus interleaved sort keys for time-series or highly selective queries, join ordering with the largest table first and EXISTS instead of IN, windowed running-total queries with proper partitioning, optimized COPY commands with compression, parallel loading, and manifest files, a VACUUM and ANALYZE maintenance schedule, workload-management queue configuration for ETL, reporting, and ad hoc query groups, materialized views for frequently accessed aggregations, compression-encoding recommendations, and cluster right-sizing including RA3 node types and connection pooling. It closes with an explicit discipline: always validate optimizations with EXPLAIN plans and actual performance measurements, use system tables extensively for monitoring, and implement changes incrementally, measuring impact before layering on more.

Input is a Redshift performance problem or design decision - a slow query, a new table's distribution/sort strategy, an ETL load, or a cluster-sizing question. Output is SQL and configuration: CREATE TABLE statements with DISTKEY/SORTKEY/DISTSTYLE choices, optimized JOIN and window-function queries, COPY commands tuned with COMPUPDATE, STATUPDATE, compression encoding, and manifest files, VACUUM/ANALYZE maintenance statements plus a pg_stats query for column statistics, diagnostic queries against stl_query and svl_query_summary for slow-query and execution-step analysis, a JSON workload-management (WLM) queue configuration, a materialized view with its refresh statement, and a compression-recommendation query joining pg_table_def with svv_alter_table_recommendations.

-- For fact tables: distribute on frequently joined dimension key
CREATE TABLE sales_fact (
    sale_id BIGINT IDENTITY(1,1),
    customer_key INTEGER DISTKEY,
    product_key INTEGER,
    sale_date DATE SORTKEY,
    amount DECIMAL(10,2)
);

-- For dimension tables: use ALL distribution for small tables
CREATE TABLE customer_dim (
    customer_key INTEGER SORTKEY,
    customer_name VARCHAR(100),
    region VARCHAR(50)
) DISTSTYLE ALL;

-- For staging tables: use EVEN distribution
CREATE TABLE staging_data (
    id BIGINT,
    data VARCHAR(500),
    load_timestamp TIMESTAMP
) DISTSTYLE EVEN;

Documented Redshift-specific system tables and views include stl_query, svl_query_summary, pg_stats, pg_table_def, and svv_alter_table_recommendations for monitoring and compression analysis. COPY commands load from S3 using an IAM role, with COMPUPDATE, STATUPDATE, GZIP compression encoding, and manifest-file loading documented explicitly. Cluster-scaling guidance covers elastic resize for temporary workload increases, RA3 node types for storage-compute separation, auto-scaling based on queue wait times, and connection pooling with tools like PgBouncer.

Data engineers and analytics teams running Amazon Redshift clusters who need to diagnose slow queries, choose the right distribution and sort keys, tune ETL loads, and right-size cluster and workload-management configuration, rather than guessing at Redshift's columnar, MPP-specific performance characteristics.