Optimize Amazon Redshift Performance

This is an Amazon Redshift performance-optimization skill covering distribution and sort-key strategy, query design, ETL loading, maintenance, monitoring, and cluster configuration for columnar, MPP-architecture data warehouses. Its core table-design guidance is distribution-strategy-specific by table role:

-- 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;

Beyond distribution, it covers compound sort keys for time-series queries with multiple WHERE clauses, and warns that interleaved sort keys should be used sparingly, only for highly selective queries. Query-level guidance includes ordering joins with the largest table first, preferring EXISTS over IN, and structuring window functions with explicit PARTITION BY and frame clauses.

Use this skill when diagnosing or preventing Redshift performance bottlenecks - choosing distribution and sort keys, tuning joins and window functions, configuring COPY loads and VACUUM/ANALYZE maintenance, setting up Workload Management (WLM) query groups, or deciding when a materialized view or compression change is worth making. It explicitly cautions that every optimization should be validated with EXPLAIN plans and actual measurements rather than applied speculatively, and changes should be made incrementally. It is not a generic SQL-tuning skill for arbitrary databases - the guidance (DISTKEY/SORTKEY choices, columnar storage behavior, MPP data movement) is specific to Redshift's architecture and doesn't transfer to row-oriented databases.

Input is table schemas, queries, or a Redshift cluster's current configuration and workload; output is DDL with explicit DISTKEY/SORTKEY/DISTSTYLE choices, optimized query rewrites, COPY commands (with COMPUPDATE, STATUPDATE, COMPRESSENCODING, and MANIFEST-based loading), a VACUUM/ANALYZE maintenance schedule, a WLM configuration defining named query groups with slot counts, timeouts, and memory allocation (an ETL queue, a reporting queue, and an ad hoc queue in the given example), and diagnostic queries against system tables like stl_query and svl_query_summary for identifying and analyzing slow queries.

Built around Redshift's own system tables and views (pg_stats, stl_query, svl_query_summary, svv_alter_table_recommendations), S3 as the COPY source with IAM role-based credentials, materialized views for aggregation caching, and connection-pooling tools like PgBouncer for managing concurrent connections.

Data engineers and analysts operating an Amazon Redshift warehouse who need to design tables, tune queries, configure workload management, and diagnose performance issues using Redshift-specific mechanisms rather than generic relational-database tuning advice.