Amazon Redshift

Follow one analytical query through the warehouse

The SQL text is only the beginning. A query becomes a distributed plan whose elapsed time depends on the bytes read, rows moved, memory used, and slowest required slice.

Read an EXPLAIN plan before guessing at a fix. A large redistribution, broadcast, spill, or scan may matter more than node count. The current data-distribution guidance describes two goals: keep slice work balanced and minimize runtime data movement.

Make table layout serve the dominant query

Distribution answers where rows live. Sort design influences which blocks a predicate can skip. They solve different problems and should be evaluated separately.

• DISTSTYLE AUTO lets automatic table optimization choose and evolve a layout from observed workloads.
• EVEN spreads rows without trying to collocate a join.
• KEY places equal key values together, which can reduce one important join but can also create skew.
• ALL copies a full table to every compute node, exchanging storage and load work for local joins.
• A leading sort column helps only when common predicates constrain that column enough for block ranges to prune data.

Use the lab to compare the costs instead of treating one style as universally best.

The model uses fixed percentages to make the mechanism visible. Production evidence comes from representative data, query plans, system views, statistics, and observed automatic-optimization actions.

Redshift documents AUTO, EVEN, KEY, and ALL in its current distribution-style reference. Automatic table optimization can change eligible AUTO tables in the background; inspect those actions rather than assuming AUTO means “no ownership.”

Protect short queries from long-running work

Workload Management (WLM) controls how queries are classified, prioritized, admitted, and queued under contention. Automatic WLM adjusts concurrency and memory from query needs. Separate queues, query priorities, short-query acceleration, query-monitoring rules, and concurrency scaling are related controls, not interchangeable labels.

Three signals must stay distinct:

Change the arrivals, ETL concurrency, and policy below. The slot arithmetic is deliberately simple; it demonstrates isolation and pressure, not Redshift's internal automatic-WLM algorithm.

The official automatic WLM documentation explains queue priorities and eligible concurrency scaling. Scaling can move eligible queries out of a busy queue, but it does not repair a poor plan, unbounded workload, or missing ownership.

Observe the plan before changing capacity

Use system evidence to connect a user-visible symptom to a constrained stage:

1. Separate queue delay from execution delay.
2. Find the query class, priority, status, and repeated plan shape.
3. Measure scanned bytes, returned rows, spill, redistribution, skew, and blocked work.
4. Compare the same query against representative table statistics and data volume.
5. Change one layout, query, or workload policy at a time, then rerun the evidence.

SYS_QUERY_HISTORY provides one row per user query with accumulated fields for running and completed work. Automatic table actions are visible separately.

Load and transform data in bounded stages

Large analytical systems need explicit ownership from source to serving table.

1. 1

   Register

   Land immutable input

   Write source files with a stable batch identity, schema contract, and retention policy.

2. 2

   Ingest

   Load in parallel

   Use COPY from supported object storage formats instead of many row-by-row inserts.

3. 3

   Reconcile

   Validate before publish

   Reconcile row counts, rejected records, keys, nulls, and business totals in a staging boundary.

4. 4

   Release

   Promote one version

   Make the accepted table or partition visible and retain enough lineage to replay or roll back.

Materialized views can persist expensive results, and Spectrum can query external data in S3. Neither choice removes freshness, ownership, authorization, or cost decisions. Define who refreshes a view, how stale it may be, and whether an external table is authoritative or a serving copy.

Operate Redshift as a production data system

Use controls that expose failure instead of hiding it:

• Data correctness
  • make loads idempotent by batch or object identity;
  • reconcile source and target totals before promotion;
  • keep schema changes backward compatible across producers and consumers.
• Performance
  • inspect plans and query history before resizing;
  • monitor skew, unsorted data, stale statistics, spill, queue time, and concurrency-scaling usage;
  • test automatic and explicit designs against the same representative workload.
• Security
  • separate database roles by workload and data domain;
  • use narrowly scoped IAM roles for COPY, UNLOAD, and external access;
  • audit query and data-access paths, not only console administration.
• Recovery
  • define snapshot or recovery-point objectives for the selected deployment mode;
  • rehearse restoring data and dependent grants into an isolated environment;
  • preserve source objects and transformation lineage long enough to replay critical datasets.