6.2 Redshift Data Modeling: Distribution Styles (KEY, EVEN, ALL) & Sort Keys

6.2 Redshift Data Modeling: Distribution Styles (KEY, EVEN, ALL) & Sort Keys

Physical Data Modeling in an MPP Warehouse

In traditional relational OLTP databases, database design focuses on normalization (3rd Normal Form) to eliminate data redundancy and preserve transactional integrity. In an MPP analytical data warehouse like Amazon Redshift, physical data modeling prioritizes query execution efficiency, parallelism, and minimizing network data movement.

When a query executes across an MPP cluster, compute node slices process their local data partitions in parallel. However, if a query joins two large tables whose rows reside on different compute nodes, Redshift must transfer data across the internal interconnect network—a costly operation known as Data Redistribution. Physical data modeling in Redshift relies on two fundamental pillars to eliminate redistribution and disk I/O overhead:

1. Distribution Styles: Control how table rows are partitioned across cluster slices.
2. Sort Keys: Control physical row ordering on disk to enable Zone Map block skipping.

---

Redshift Data Distribution Styles

Redshift provides three primary explicit distribution styles, along with an automated option:

[ KEY Distribution ]   ==> Rows hashed by column value (Colocated Joins)
[ EVEN Distribution ]  ==> Round-robin row allocation (Balanced Compute, Broadcast Joins)
[ ALL Distribution ]   ==> Full table copy on every node (Dimension Table Optimization)

1. DISTSTYLE KEY

In KEY distribution, table rows are assigned to slices by hashing the value of a designated column (DISTKEY). All rows with matching key values land on the exact same compute node slice.

• Primary Use Case: Joining large fact tables (e.g., fact_sales and fact_orders). If both tables are configured with DISTSTYLE KEY on order_id, Redshift performs a Colocated Join (DS_DIST_NONE). Slices join their local datasets in memory without moving any data across the network.
• Data Skew Warning: You must select a high-cardinality column as the DISTKEY. If a column has low cardinality or skewed value distributions (e.g., country_code where 90% of rows are 'US'), one slice will hold 90% of the table data. This creates a skewed slice, causing straggler operations where the entire cluster waits for one overloaded node to finish.

2. DISTSTYLE EVEN

In EVEN distribution, table rows are distributed across slices using a round-robin algorithm, regardless of data values.

• Primary Use Case: Tables that are not frequently joined, or tables that lack a clear, high-cardinality join key.
• Trade-Off: EVEN distribution guarantees perfectly balanced storage and CPU utilization across all slices. However, joining two EVEN distributed tables forces Redshift to broadcast or redistribute rows across nodes (DS_BCAST_INNER or DS_DIST_BOTH), incurring substantial network latency.

3. DISTSTYLE ALL

In ALL distribution, a full copy of the entire table is replicated onto slice 0 of every compute node in the cluster.

• Primary Use Case: Small dimension tables (typically under 3 MB, or up to 300 MB compressed) in a star schema model (e.g., dim_store, dim_product_category).
• Benefits: When joining a massive KEY-distributed fact table with an ALL-distributed dimension table, the join executes locally on every node without network traffic.
• Trade-Off: Increases storage footprint across nodes and adds maintenance overhead during INSERT, UPDATE, or DELETE statements, as changes must be synchronized across all compute nodes.

4. DISTSTYLE AUTO

By default, Redshift assigns DISTSTYLE AUTO to new tables. Redshift can begin with DISTSTYLE ALL for a small table and later choose EVEN or KEY as Automatic Table Optimization learns from table size and workload patterns. Treat the chosen style as service-managed rather than a fixed ALL-to-EVEN sequence.

---

Redshift Sort Keys & Zone Map Block Skipping

1 MB Disk Blocks & Columnar Storage

Redshift stores data on disk in 1 MB immutable physical blocks. Unlike row-oriented databases that store entire record rows together, Redshift uses Columnar Storage. Each 1 MB block contains values for a single column. This allows query scans to read only the specific columns referenced in a SELECT statement, dramatically reducing disk I/O.

Zone Maps & Block Skipping

For every 1 MB block, Redshift maintains in-memory metadata called Zone Maps. A Zone Map records the minimum and maximum values of the column data stored within that specific 1 MB block.

When a query executes with a WHERE clause filter (e.g., WHERE sale_date >= '2026-01-01'), Redshift compares the query predicate against the block's Zone Map:

• If the query filter range falls completely outside the block's [min, max] range, Redshift skips reading that 1 MB disk block entirely.
• Sorting table data directly increases Zone Map efficiency, enabling massive Block Skipping.

Block 1: [2026-01-01 to 2026-01-15] --> READ BLOCK
Block 2: [2026-01-16 to 2026-01-31] --> READ BLOCK
Block 3: [2026-02-01 to 2026-02-15] --> SKIP BLOCK (Zone Map Pruning)

Compound Sort Keys vs. Interleaved Sort Keys

Redshift supports two types of sort key configurations:

Best Practice: Use Compound Sort Keys for date/timestamp columns, frequently filtered dimensions, and primary join keys. Reserve Interleaved Sort Keys strictly for tables where queries filter across different attribute combinations without a dominant prefix column.

---

Table Maintenance: VACUUM & ANALYZE

1. VACUUM Operations

Because Redshift block storage uses append-only operations, UPDATE and DELETE queries do not physically erase old data on disk immediately. Instead, rows are marked with a logical delete vector (tombstone). Furthermore, newly inserted rows are appended to an unsorted region at the end of the table.

To restore performance, data engineers must run the VACUUM command:

• VACUUM DELETE ONLY: Reclaims disk space occupied by deleted rows without re-sorting the table.
• VACUUM SORT: Sorts the unsorted region and merges it with existing sorted blocks.
• VACUUM FULL: Reclaims space from deleted rows and re-sorts all table data blocks.
• VACUUM REINDEX: Re-analyzes interleaved sort keys to restore equal column weighting.

Note: Redshift executes Auto-Vacuum in the background during periods of low cluster activity, but manual VACUUM commands remain essential after large batch ETL loads.

2. ANALYZE Operations

The Redshift cost-based query optimizer relies on accurate table statistics stored in the pg_statistic system catalog to generate optimal execution plans (e.g., choosing Hash Joins vs. Nested Loops). Running ANALYZE table_name updates column distribution statistics. Redshift automatically triggers Auto-Analyze, but executing ANALYZE explicitly after major bulk loads ensures optimal query planning.

---

DDL Code Example: Star Schema Data Modeling with Sort & Distribution Keys

-- 1. Create Small Dimension Table using DISTSTYLE ALL
CREATE TABLE dim_customer (
    customer_id     BIGINT NOT NULL,
    first_name      VARCHAR(50),
    last_name       VARCHAR(50),
    email           VARCHAR(100),
    account_tier    VARCHAR(20),
    created_at      TIMESTAMP,
    PRIMARY KEY (customer_id)
)
DISTSTYLE ALL
COMPOUND SORTKEY (customer_id);

-- 2. Create Large Fact Table using DISTSTYLE KEY colocated on customer_id
CREATE TABLE fact_online_sales (
    sales_id        BIGINT NOT NULL,
    order_date      DATE NOT NULL,
    customer_id     BIGINT NOT NULL,
    product_id      INT NOT NULL,
    store_id        INT NOT NULL,
    quantity        INT NOT NULL,
    total_amount    NUMERIC(12, 2) NOT NULL,
    PRIMARY KEY (sales_id),
    FOREIGN KEY (customer_id) REFERENCES dim_customer(customer_id)
)
DISTSTYLE KEY
DISTKEY (customer_id)
COMPOUND SORTKEY (order_date, customer_id);

-- 3. Run EXPLAIN to verify Colocated Join execution plan (DS_DIST_NONE)
EXPLAIN
SELECT 
    c.account_tier,
    SUM(s.total_amount) AS tier_revenue
FROM fact_online_sales s
JOIN dim_customer c ON s.customer_id = c.customer_id
WHERE s.order_date >= '2026-01-01'
GROUP BY c.account_tier;