Redshift — Petabyte Data Warehouse

Fully managed, petabyte scale, part of AWS. From file: supports multiple databases per cluster, uses psycopg2 or boto3 via JDBC.

Architecture — MPP & Columnar

• Leader Node: manages client communication, coordinates, compiles/optimizes query plan, distributes data — does not store data, handles metadata
• Compute Nodes: store data and execute queries, each divided into slices handling a piece of data/workload
• MPP: massively parallel processing — each node works independently
• Columnar storage (like Parquet/ORC) — read-optimized for OLAP, large analytics

Parallel query execution: divides query into smaller parts executed in parallel across nodes.

Redshift Spectrum vs Athena

Distribution Styles

Sort Keys

Defines order data is stored on disk within each compute node.

• Compound: one or more columns in listed order — e.g., sortkey(orderdate, productid, region) effective when filtering/sorting on leading columns
• Interleaved: balanced order for multiple columns — e.g., sales table frequently queried by region, orderdate, productid with no priority → use interleaved

CREATE TABLE sales(
  orderid BIGINT,
  custid BIGINT,
  orderdate DATE,
  amount DECIMAL(10,2)
)
DISTKEY(customerid)
SORTKEY(orderdate); -- or INTERLEAVED SORTKEY(orderdate, customerid);

Optimization Techniques (from file)

1. Right distribution style
2. Proper sort keys
3. Avoid SELECT * (column pruning)
4. Predicate pushdown (WHERE early)
5. VACUUM and ANALYZE (update statistics, reclaim space)
6. Avoid unnecessary JOINs, prefer INNER over LEFT
7. Partitioning data

Features: highly scalable, columnar, compression, MPP, cross-DB integration, automatic optimization. Limitations: not for small data, not real-time streaming, can be expensive.