Redshift Architecture and Tuning AI Prompt

Design and optimize a Redshift deployment for this workload.

Workload: {{workload}}
Data volume: {{volume}}
Query patterns: {{query_patterns}}
Cluster type: {{cluster_type}} (provisioned vs Serverless)

1. Redshift Serverless vs Provisioned:
   Serverless: auto-scales, pay per compute-second, no cluster management
   - Use for: unpredictable workloads, intermittent usage, cost optimization
   Provisioned: fixed cluster, predictable performance and cost
   - Use for: consistent heavy workloads, >$500/month sustained use

2. Table design:
   Distribution styles:
   - DISTSTYLE KEY (column): rows with the same key on the same slice — use for large JOIN tables
   - DISTSTYLE EVEN: round-robin — use for large tables with no clear join key
   - DISTSTYLE ALL: copy to every slice — use for small dimension tables (< 1M rows)

   Sort keys:
   - COMPOUND SORTKEY (col1, col2): range scan optimization on ordered columns (date)
   - INTERLEAVED SORTKEY: equal weight to all sort key columns — use for multiple filter patterns

3. COPY command for loading:
   COPY orders FROM 's3://bucket/data/orders/'
   IAM_ROLE 'arn:aws:iam::123456789:role/RedshiftRole'
   FORMAT AS PARQUET;
   - Use PARQUET (fastest) or CSV with GZIP compression
   - Parallel loading: split files into 1× number of slices for maximum parallelism

4. Vacuuming:
   VACUUM orders TO 100 PERCENT BOOST;
   -- Reclaims space from deleted rows and re-sorts unsorted rows
   -- Schedule weekly; automatic vacuum may not keep up with high-write tables

5. WLM (Workload Management):
   - Define query queues by user group or query group
   - Short query acceleration (SQA): auto-routes short queries to a fast lane
   - Concurrency scaling: auto-adds read capacity during peak periods

Return: distribution and sort key design, COPY command, vacuum schedule, and WLM configuration.