Redshift Performance and Optimization: What Actually Moves the Needle

Most Redshift performance problems trace back to one of four things: bad distribution, unsorted data, oversized WLM queues, or queries fighting each other for the same cluster. Here's how to diagnose and fix each.

1. Distribution keys are your first lever, not your last

A misdistributed large table forces broadcast joins or network shuffle on every query that touches it — this shows up as high "network" time in

EXPLAIN

• ▸Use KEY distribution on the column your largest fact table most frequently joins on. If
  Code

  orders

  joins to
  Code

  customers

  on
  Code

  customer_id

  in nearly every query, distribute both tables on
  Code

  customer_id

  — colocated joins avoid the shuffle entirely.
• ▸Use ALL distribution for small, frequently-joined dimension tables (under a few million rows). The full copy on every node costs storage but eliminates join-time network cost.
• ▸Avoid EVEN distribution as anything but a fallback — it's the default when nothing else fits, not a good default to leave in place.

Check for skew regularly:

SQL
SELECT "table", diststyle, skew_rows
FROM svv_table_info
WHERE skew_rows > 4
ORDER BY skew_rows DESC;

A

skew_rows

2. Sort keys determine how much data you actually scan

Redshift skips blocks that fall outside a query's filter range — but only if the sort key aligns with how you filter. The most common mistake: choosing a sort key based on what seems "important" rather than what queries actually filter on.

• ▸Compound sort keys work well when queries reliably filter on a leading column (usually a date) — this is the right default for most fact tables.
• ▸Interleaved sort keys help when multiple columns are filtered with roughly equal frequency, but they cost significantly more to maintain (
  Code

  VACUUM REINDEX

  is expensive) — reserve these for genuinely multi-predicate access patterns, not "just in case."

Monitor unsorted percentage:

SQL
SELECT "table", unsorted, stats_off
FROM svv_table_info
WHERE unsorted > 10;

Anything consistently above 10-20% unsorted means your VACUUM cadence isn't keeping up with ingestion — schedule it, don't wait for complaints.

3. VACUUM and ANALYZE are not optional maintenance

Skipping these is the single most common self-inflicted performance problem in Redshift:

• ▸
  Code

  VACUUM

  reclaims space from deleted/updated rows and re-sorts data to match the sort key. Without it, unsorted regions grow and every query scans more blocks than it needs to.
• ▸
  Code

  ANALYZE

  keeps the query planner's statistics current. Stale statistics lead to bad join order decisions — the planner might broadcast a table it thinks is small when it's actually grown large.

Automate both on a schedule that matches your ingestion pattern (nightly for daily-batch pipelines, more frequent for continuously-loading tables), rather than running them reactively after a slowdown is already reported.

4. Workload Management (WLM) queues protect your most important users

The default single-queue setup means a runaway analyst query can starve your nightly ETL, or vice versa. Set up separate queues by workload type:

SQL
-- Example: separate queue for interactive dashboard users
-- (configured via WLM console or parameter group, not pure SQL)

• ▸Give ETL its own queue with enough memory to avoid disk spilling on large joins.
• ▸Give BI/dashboard queries a queue tuned for concurrency over raw throughput — more, smaller slots rather than fewer, larger ones.
• ▸Watch
  Code

  stl_wlm_query

  for queue wait times; consistent queuing means it's time to add a queue or adjust slot allocation, not just add more cluster nodes.

5. Materialized views for repeated aggregation patterns

If the same aggregation runs repeatedly with minor filter variations (a common BI pattern), a materialized view with auto-refresh can turn a multi-second scan into a sub-second lookup:

SQL
CREATE MATERIALIZED VIEW sales.daily_totals AUTO REFRESH YES AS
SELECT order_date, SUM(amount) AS total FROM sales.orders GROUP BY order_date;

6. Right-size before you resize

Before adding nodes, check whether the actual bottleneck is distribution, sort keys, or WLM contention — throwing more nodes at a skew problem just gives the overloaded node more neighbors to be faster than, not less work to do. RA3 node types with managed storage make it cheaper to experiment with compute sizing independently of storage, so use that flexibility to test before committing to a permanent resize.

The honest diagnostic order

When a query is slow, check in this order: distribution skew, sort key alignment and unsorted percentage, WLM queue wait time, then — only after those are ruled out — whether the cluster itself is undersized. Most "we need a bigger cluster" conversations turn out to be "we need better distribution and sort keys" conversations once someone actually runs the diagnostics.