Redshift Taught Me That Storage Format Matters More Than Query Logic

· 5 min read
data-engineeringredshiftaws

A straightforward aggregation — average order value by region over the past year — ran for over a minute on a few million rows. The SQL was fine. The problem was how Redshift stores data.

Redshift is a columnar database. Each column is stored separately on disk. A query for AVG(revenue) reads only the revenue column — customer names, addresses, and timestamps remain untouched. That is the fundamental advantage of columnar storage for analytics: dramatically less data is read to answer most questions.

Sort keys determine the physical order of rows on disk. When queries consistently filter by date, sorting on a timestamp column allows Redshift to skip entire blocks outside the filter range via zone maps. Distribution styles determine which node holds which data. Co-locating a fact table and its dimension table on the same distribution key means joins happen locally — no network shuffling. These two settings often matter more than any query rewrite.

Choosing the right sort key

Redshift supports two types: compound (the default) and interleaved. Compound sort keys are ordered — ideal when filter columns follow a consistent sequence. Interleaved sort keys weight each column equally, which helps when different queries filter on unrelated columns. In practice, compound sort keys are almost always the right choice: lower maintenance cost during loads and VACUUM, and sufficient performance when a dominant filter pattern exists.

The selection process should follow actual query patterns:

  • Query stl_query and stl_plan_info in Redshift’s system tables to identify columns most frequently used in WHERE and JOIN clauses.
  • Prioritize columns in range filters (e.g. WHERE event_date BETWEEN ... AND ...) — these benefit most from zone map skipping.
  • Low-cardinality columns like status or region make poor sort keys on their own because they lack the granularity for effective block skipping.
  • For multi-column access patterns, define a compound sort key in filter order: SORTKEY (event_date, region). The first column defines the primary sort; subsequent columns only matter within each first-column value.
  • Interleaved sort keys should be reserved for genuinely independent filter columns with no dominant access pattern, given their higher maintenance cost and slower vacuum operations.

After setting a sort key, verify the benefit. Check svv_diskinfo for skew, and confirm via EXPLAIN that scans are narrow rather than full-table. The system view stl_alert_event_log surfaces cases where queries scan more data than necessary — look for alerts like “Scan has high skew” or “Nested loop join”.

Compression is a related advantage. Values within a single column tend to be similar — numbers in the same range, strings from the same domain — so columnar storage compresses well. Redshift applies compression encodings automatically, and a columnar table can be a fraction of the size of its row-based equivalent. Less data on disk means less data to scan.

Choosing the right compression encoding

Redshift provides ANALYZE COMPRESSION to find the optimal encoding per column based on actual data:

ANALYZE COMPRESSION my_table;

This returns a row per column with the recommended encoding (e.g. AZ64, ZSTD, DELTA32K) and the estimated compression ratio. Representative data should be loaded first — the recommendations are only as good as the sample.

Key points on compression selection:

  • AZ64 — default for many numeric columns; best balance of compression ratio and scan speed. Designed specifically for Redshift, it outperforms older encodings like DELTA and RUNLENGTH for most numeric data.
  • ZSTD — the standard for VARCHAR and CHAR columns. It adapts to the data distribution and rarely underperforms more specialized encodings.
  • DELTA (DELTA32K, DELTA64K) — effective on integers with a predictable progression, such as surrogate keys or timestamps stored as epoch seconds.
  • BYTEDICT and LZO — older encodings still in documentation but rarely optimal. ANALYZE COMPRESSION recommendations should take precedence.
  • Sort key columns should use RAW encoding (no compression). Compressed sort key columns require decompression before zone map evaluation, adding overhead. Uncompressed sort key columns make zone map checks nearly free.
  • Compression ratio alone is not a reliable proxy for query speed — some encodings decompress faster than others. ANALYZE COMPRESSION accounts for this trade-off.

Applying new encodings requires recreating the table — Redshift does not support changing encodings on existing columns in place. The standard pattern: create a new table with target encodings, load data via INSERT INTO ... SELECT, then drop and rename. The Redshift console can automate this through modify-table-encoding, but the underlying operation remains a full table rewrite.

The trade-off: columnar storage performs poorly for row-level operations. Single-row inserts are slow because each column must be updated independently. Redshift recommends bulk loading via COPY commands rather than incremental inserts. OLTP it is not.

This understanding shifted my approach to pipeline design — from optimizing query syntax to thinking about how data lands on disk. The storage format is the foundation; everything else builds on top of it.

All articles