Amazon Redshift Architecture Guide: Distribution, Sort Keys, and Spectrum

Amazon Redshift is a columnar, MPP (massively parallel processing) data warehouse that distributes data across compute nodes and parallelises query execution. Unlike serverless warehouses (BigQuery, Snowflake) where storage and compute are managed transparently, Redshift requires explicit design decisions at table creation — distribution key, sort key, and compression encoding — that have significant and lasting impact on query performance. This guide covers those decisions, Redshift Spectrum for querying external data, and the design mistakes that cause most Redshift performance problems.

How Redshift distributes data

Redshift stores data across a cluster of compute nodes. Each table's rows are distributed across those nodes according to a distribution style. Query performance depends on whether the data a query needs is co-located on the same node or requires shuffling across the network — the primary driver of Redshift query time.

**EVEN distribution**: Rows are distributed uniformly across all nodes using round-robin assignment. Every node has approximately the same number of rows. Appropriate for tables that are never joined to other tables, and for smaller tables where even distribution reduces skew. Not appropriate for large tables that are frequently joined — joins against an EVEN-distributed table require data movement.

**KEY distribution**: All rows with the same distribution key value are placed on the same node. When two tables are joined on their distribution keys, the matching rows are co-located — no data movement required during the join. This is the most impactful optimisation available in Redshift. Choose distribution keys that are used in frequent, expensive joins: a customer_id on a customer dimension that is joined to an orders fact table, for example.

**ALL distribution**: The entire table is copied to every node. No data movement for any join to this table, regardless of what it is joined on. Appropriate for small dimension tables (under 5GB) that are frequently joined to large fact tables. For large tables, ALL distribution is impractical — the storage overhead is too significant.

**AUTO distribution**: Redshift chooses the distribution style automatically based on table size. Small tables get ALL distribution; larger tables get EVEN distribution. Reasonable default when you do not have specific join patterns to optimise against.

Sort keys

Sort keys determine the physical order in which rows are stored on disk within each node's slice. Redshift stores column statistics (min and max values) for each 1MB block. When a query includes a filter on a sort key column, Redshift uses block-level zone maps to skip blocks that cannot contain matching rows — reducing I/O significantly.

**Compound sort key**: Defines a sort order by multiple columns, in precedence order (primary sort column, then secondary, etc.). Queries filtering on the primary sort key column skip the most blocks. Queries filtering only on secondary sort key columns skip fewer. Compound sort keys are the standard choice for most tables: partition_date as the primary, another frequently-filtered column as secondary.

**Interleaved sort key**: Gives equal weight to all sort key columns — each column is weighted equally in the sort, enabling block skipping for any of the sort key columns. The trade-off is higher VACUUM cost (resorting after inserts is more expensive) and higher maintenance overhead. Only worthwhile if your query patterns genuinely vary which sort key column is most important across different queries.

**Choosing sort keys**: Sort on the date or timestamp column used in the most frequent WHERE clause filters — this is almost always correct for fact tables. For dimension tables, sort on the primary key if it is frequently joined.

Compression encodings

Redshift stores each column independently and applies compression encoding to each column separately. Compression reduces storage and, more importantly, I/O — compressed data means fewer disk reads per query. The ANALYZE COMPRESSION command suggests encodings for each column based on the data distribution.

Best practice: run ANALYZE COMPRESSION on tables loaded with production data and apply the suggested encodings when recreating the table. For new tables, the AUTO encoding (set at column or table level) uses machine learning to choose appropriate encodings. Manual encoding selection is rarely worth the effort compared to AUTO or ANALYZE COMPRESSION suggestions.

Redshift Spectrum

Redshift Spectrum allows Redshift to query data files directly in S3 — Parquet, ORC, CSV — without loading them into Redshift managed storage. The query is pushed down to the Spectrum layer, which runs distributed across independent Spectrum compute resources, and results are returned to the Redshift cluster.

Spectrum is appropriate for:

- Large historical data (years of event data) that is rarely queried — avoid Redshift managed storage cost for cold data

- Data that is also consumed by other services (EMR, Athena) — keeping it in S3 in open formats serves all consumers

- Querying raw or semi-structured data before deciding how to load it

Spectrum performance is slower than querying native Redshift tables — the Spectrum layer adds latency and the external data lacks Redshift's compression and sort key optimisations. For frequently queried data, load it into Redshift native storage.

External schemas in Redshift point to a Glue Data Catalog database, which maps table definitions to S3 locations. Once an external schema is defined, you query Spectrum tables with standard SQL alongside native Redshift tables.

Redshift Serverless

Redshift Serverless (released in 2022) eliminates cluster management — you do not provision nodes, choose node types, or manage cluster scaling. Compute capacity is measured in Redshift Processing Units (RPUs) and scales automatically. Billing is per RPU-second.

Redshift Serverless uses the same SQL surface as provisioned Redshift but removes distribution key and sort key optimisations — because the cluster configuration is dynamic, the distribution and sort metadata that optimises query routing in provisioned clusters does not apply in the same way. For many workloads, Serverless is simpler to operate and adequately performant. For workloads with complex, high-volume joins that benefit from distribution key co-location, provisioned Redshift with explicit distribution may still be preferable.

Common design mistakes

**Wrong distribution key**: The most impactful mistake. Choosing a distribution key that is not used in joins (or using EVEN for large joined tables) forces Redshift to redistribute data at query time. A fact table with a customer_id distribution key that is joined to a customer dimension on customer_id — both distributed on customer_id — requires zero data movement. The same join with the fact table on EVEN distribution requires shipping one table's matching rows across the network.

**Ignoring sort key on date columns**: A billion-row fact table with no sort key, queried with WHERE event_date > '2026-01-01', scans the entire table. The same table sorted on event_date skips all blocks with dates outside the filter range.

**Not running VACUUM**: Redshift uses MVCC for updates and deletes — old row versions are logically deleted but remain on disk until VACUUM reclaims the space. Tables with frequent updates or deletes accumulate dead rows that degrade performance and storage. Run VACUUM regularly on frequently-modified tables, or enable auto-vacuum (on by default in Redshift Serverless).

For the broader data warehouse comparison context, see snowflake vs redshift and data warehouse design. Our cloud engineering practice optimises Redshift environments and migrates organisations between warehouse platforms — book a free architecture review.

A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.