9.2 Redshift & Athena Query Performance Tuning & Memory Management

Redshift & Athena Query Performance Tuning & Memory Management

High-performance data analytics on AWS relies on optimizing two distinct query execution engines: Amazon Redshift (a managed Massively Parallel Processing data warehouse) and Amazon Athena (a serverless interactive query engine built on Apache Trino/Presto). This section examines the architectural mechanisms and configuration controls required to maximize throughput and minimize execution latencies in both systems.

---

Amazon Redshift Performance Optimization

Amazon Redshift achieves fast execution across petabyte-scale datasets using a Massively Parallel Processing (MPP) architecture composed of one Leader Node and multiple Compute Nodes divided into slices. Optimizing Redshift requires eliminating network data redistribution, managing memory allocation, and maintaining storage statistics.

1. Data Distribution Styles & Network Redistribution Costs

When executing joins, Redshift compute nodes must co-locate matching table rows across slices. Inter-node network data movement is the primary cause of query latency.

• DISTSTYLE KEY: Hash-distributes table rows based on values in a specified column (DISTKEY).
  • Best Practice: Set DISTKEY on the join column of two large tables (e.g., fact_sales and dim_customer on customer_id).
  • Result: Matching rows reside on the exact same compute node slice, yielding DS_DIST_NONE (zero network movement).
• DISTSTYLE EVEN: Distributes rows round-robin across all slices.
  • Best Practice: Used for tables involved in no frequent joins, or to resolve data skew.
  • Result: Triggers DS_DIST_INNER or DS_DIST_BOTH, requiring expensive network data broadcasts across nodes during joins.
• DISTSTYLE ALL: Copies the entire table to the first slice of every compute node.
  • Best Practice: Used for small, slowly-changing dimension tables (< 3 million rows).
  • Result: Guarantees local join execution (DS_DIST_NONE) at the cost of higher storage overhead.
• DISTSTYLE AUTO: Redshift automatically manages distribution strategy, starting small tables at ALL and altering to EVEN as table size scales.

2. Sort Keys (COMPOUND vs. INTERLEAVED)

Sort keys order table rows on disk blocks, enabling Redshift block metadata (Zone Maps) to skip un-referenced data blocks during query execution.

• COMPOUND SORTKEY (Default): Sorts data hierarchically based on the listed column order (col1, col2).
  • Optimal Use Case: Range filtering (WHERE date >= '2026-01-01') or equality filters matching the primary leading columns.
• INTERLEAVED SORTKEY: Gives equal weight to every column in the sort key.
  • Optimal Use Case: Ad-hoc multidimensional filtering across varied columns (e.g., searching by city, zip, or category independently).
  • Trade-off: Significantly higher VACUUM and ANALYZE processing overhead during bulk load operations.

3. Workload Management (WLM) & Disk Spill Remediation

Redshift WLM allocates cluster memory across dedicated query queues.

• Auto WLM: Uses machine learning to allocate dynamic execution memory (workmem) based on query complexity and concurrency needs.
• Identifying Memory Spills (is_diskbased = true): If a query's execution memory exceeds allocated queue memory, intermediate hash aggregation or sort steps spill to disk, degrading performance by 10x–100x.
  • Diagnosis: Query the SVL_QUERY_SUMMARY diagnostic view:

    SELECT query, stm, seg, step, label, is_diskbased, workmem, rows, bytes
    FROM svl_query_summary
    WHERE is_diskbased = 't'
    ORDER BY query DESC;
    

  • Remediation: Assign heavy analytical queries to dedicated high-memory WLM queues, increase wlm_query_slot_count for manual WLM, or optimize joins to reduce intermediate dataset sizes.

4. Table Maintenance: VACUUM & ANALYZE

• VACUUM: Reclaims disk space from deleted rows (VACUUM DELETE ONLY) and re-sorts unsorted table regions (VACUUM SORT ONLY). Redshift performs Automatic Vacuuming in the background, but manual VACUUM is required after massive ETL updates.
• ANALYZE: Updates HyperLogLog (HLL) table column statistics used by the query optimizer to choose efficient execution plans (e.g., hash joins vs. nested loops).

---

Amazon Athena Query Optimization & S3 Storage Design

Amazon Athena SQL commonly charges by data scanned, with rates varying by Region; provisioned capacity is also available. Performance tuning in Athena focuses on drastically reducing S3 data scan volume and eliminating metastore lookup latencies.

1. Storage Format & Compression Optimization

Converting raw unstructured or semi-structured data (CSV, JSON) into columnar formats with compression is the single most impactful Athena optimization.

• Predicate Pushdown: Columnar storage allows Athena to read only the specific byte ranges and row groups required by SQL WHERE clauses, skipping irrelevant data blocks entirely.

2. Solving the Small File Problem

Having millions of small files (e.g., < 128 MB) in S3 severely degrades Athena query performance due to S3 HTTP GET request latency overhead and filesystem metadata listing delays.

• Target File Size: 128 MB to 512 MB per file.
• Compaction via CTAS: Use Athena CREATE TABLE AS SELECT (CTAS) to aggregate small files into optimal Parquet files:

  CREATE TABLE analytics_db.compacted_orders
  WITH (
    format = 'PARQUET',
    parquet_compression = 'SNAPPY',
    external_location = 's3://my-analytics-bucket/compacted_orders/',
    partitioned_by = ARRAY['year', 'month']
  ) AS 
  SELECT * FROM analytics_db.raw_orders;
  

3. Partition Projection

In traditional Hive partitioning, Athena executes MSCK REPAIR TABLE or queries the AWS Glue Data Catalog to discover partition locations. For datasets with millions of partitions, Glue Metastore API rate limits introduce query start delays.

• Partition Projection Implementation: Configure table properties directly in the Glue Data Catalog so Athena algorithmically calculates S3 partition locations without making Glue API calls:

  TBLPROPERTIES (
    'projection.enabled' = 'true',
    'projection.year.type' = 'integer',
    'projection.year.range' = '2020,2030',
    'projection.month.type' = 'integer',
    'projection.month.range' = '1,12',
    'projection.month.digits' = '2',
    'storage.location.template' = 's3://my-analytics-bucket/orders/year=${year}/month=${month}/'
  )
  

---

Redshift vs. Athena Performance Optimization Comparison