Amazon Redshift

Two decisions that make a warehouse

Redshift is built on two ideas that analytical databases generally share, and understanding them explains most of its behaviour.

Data is stored by column. Rather than keeping each complete row together, the values of each column are stored together.

Data is distributed across nodes. A cluster of machines each holds part of the data and processes its share of a query in parallel.

Both follow from what analytical queries actually do.

Why columns

An analytical query summarising sales by region over three years touches perhaps three columns of a table that has fifty.

With row storage, answering it means reading every complete row, including the forty seven columns nobody asked about.

With column storage, only the three columns are read.

The reduction is substantial, and there is a second benefit. Values within a column resemble one another far more than values across a row: dates near each other, repeated category codes, amounts in a similar range. Similar values compress well, so less physical data exists to read.

For analytical work this is the difference between a query being practical and not. It is also why the same storage is a poor fit for fetching one complete record, which is what transactional databases do and why they organise data differently.

Why the keys decide everything

Distribution across nodes introduces the decision that most determines whether a Redshift cluster performs.

Each table has a distribution key, whose value decides which node holds each row.

Two consequences follow, and they are the substance of designing tables here.

Even spread keeps every node working. A distribution key with well spread values puts a similar amount of data on each node. A key with few distinct values concentrates rows on some nodes while others sit idle, and the cluster performs as if it were much smaller.

Matching distribution avoids movement. When two tables are joined on the column they are both distributed by, matching rows already sit on the same node. The join is local. When they are distributed differently, rows must travel across the network before the join can proceed.

That movement is usually the dominant cost in a slow query on a distributed warehouse. It does not appear in the SQL. It appears in the execution plan, which is why reading plans is a necessary skill here.

The sort key is the second decision. It determines physical ordering within a node, so a query filtering on the sort key reads a contiguous portion rather than scanning everything.

Neither key can be changed casually once a table holds data. These are decisions made when tables are created, and getting them wrong is the most common reason a cluster underperforms.

Reaching data that was not loaded

Data held in Amazon S3 can be queried without being loaded into the warehouse.

This is useful for two situations. Data queried occasionally does not justify the effort of loading and maintaining it in the warehouse. And data being explored can be examined before anyone decides whether it is worth loading.

Performance characteristics differ from data held in the warehouse, which the official documentation describes. The usual pattern is loading data queried frequently and querying the rest where it sits.

Fitting an AWS estate

Redshift integrates with other AWS services, which is frequently the deciding factor.

Where data already lands in S3, where processing runs on AWS services and where identity is managed there, a warehouse in the same environment involves less integration work than one elsewhere.

This is an ordinary consideration rather than a technical superiority, and it is how a great many platform decisions are actually made.

Who uses it

Redshift is used by analytics teams, data engineers and analysts working on AWS. It appears as a central analytical platform in organisations whose data and infrastructure already sit there.

Points to consider

Redshift is an AWS service and the official documentation is the reference for node types, features and pricing, which change over time.

Table design is the decision that most affects performance, and it is difficult to revisit once tables are populated. Time spent on distribution and sort keys before loading is repaid.

Cluster sizing and management have changed across the service's history, including options that reduce the operational decisions required. Current documentation rather than older material is the reliable guide.

Vacuum and maintenance operations affect performance over time as data is updated and deleted, and understanding what the current service handles automatically is part of operating it.

Getting started

The official documentation covers loading data, table design and query tuning. Creating two tables with different distribution keys, joining them and reading the execution plan makes data movement visible, which is the concept that determines whether queries perform.