Amazon Redshift Explained: The Cloud Data Warehouse

Amazon Redshift is AWS's data warehouse: a database built for analytical queries over large tables, where the question is "what were sales by region for each of the last eight quarters?" and not "what is the status of order 4417?". AWS describes it as a fully managed, petabyte-scale data warehouse service (Redshift docs: what is Amazon Redshift).

If the idea of a warehouse is new, start with data warehouse explained. This article is about how Redshift in particular works, because its design explains both its speed and its rules.

Three ideas behind the speed

The developer guide lists the components that drive performance (Redshift docs: performance). Three carry most of the weight.

Massively parallel processing. A query is not run by one machine. It is compiled, split and run by many compute nodes at once, each working on its own portion of the data.

Columnar storage. A transactional database stores each row together, which suits reading or writing one whole record. Redshift stores each column together. An analytical query that touches four columns of a hundred-column table reads only those four, which the guide says drastically reduces disk I/O.

Compression. Values in a column resemble each other, so they compress well. Less data is read from disk and more fits in memory.

Inside a cluster

The architecture has four parts (Redshift docs: system architecture):

• Leader node. Your SQL client talks only to this. It parses the query, builds an execution plan, compiles code and hands it to the compute nodes, then assembles the final result.
• Compute nodes. They run the compiled code on their share of the data and return intermediate results.
• Slices. Each compute node is divided into slices, each with a share of memory and disk. Slices work in parallel, and a table's distribution style decides which slice stores each row.
• Redshift Managed Storage. Table data lives in a separate storage tier that scales to petabytes using Amazon S3, with fast local SSD as a cache. Because storage is separate, compute and storage are sized and paid for independently.

Two consequences are worth remembering. Joining two tables is fast when matching rows are already on the same slice and slow when rows must be moved between nodes first. And filtering is fast when rows are sorted so that blocks can be skipped. Redshift distribution and sort keys covers both.

Serverless or provisioned

There are two ways to run it.

Serverless measures capacity in Redshift Processing Units. One RPU provides 16 GB of memory; the base capacity defaults to 128 RPUs and can be set as low as 4 (Redshift docs: Serverless capacity). AWS states that you do not incur compute charges when the warehouse is idle. For a learner or a small team this matters: set the base capacity low, and set a usage limit, before running anything. Prices vary by Region and change, so check the pricing page before estimating a bill.

It looks like PostgreSQL, and is not

Redshift is based on PostgreSQL, and most existing SQL clients work with it after only minimal changes. The resemblance stops at the surface. The guide warns that the storage and execution engine are completely different, and that features suited to small-scale transaction processing, such as secondary indexes and efficient single-row operations, have been omitted (Redshift docs: Redshift and PostgreSQL).

What that means in practice:

• No indexes to create. Sort keys and block metadata do that job.
• Row-at-a-time writes are slow. Load in bulk; update in batches.
• Primary and foreign keys are not enforced. They are informational, used by the planner as hints, so your pipeline must guarantee uniqueness itself (Redshift docs: table constraints).
• It is the wrong database for an application backend. It is built for few, large queries, not many tiny ones.

Getting data in and out

• Loading. The COPY command loads files from S3 in parallel and is the standard route; see loading data into Redshift with COPY.
• Querying the lake. External tables let Redshift query files in S3 without loading them, and join them with local tables; see Redshift Spectrum.
• Modelling. Star schemas remain the usual design; see data modelling in Redshift.

What Redshift looks after for you

Much of what an administrator once tuned by hand is now automatic by default: distribution style and sort key set to AUTO, column compression encodings, background sorting and space reclamation. The sensible starting position is to accept the defaults, load real data, and tune only what measurements show to be slow.

When to use it

Redshift fits when a team needs a central warehouse: data from many systems brought into one modelled schema, queried by BI tools and analysts with complex joins, every day. AWS's own guidance is to choose it for highly structured data with many joins across very large tables.

It is less suited to occasional queries over files that already sit in S3, where a query service billed per query is simpler; Athena vs Redshift compares the two.

What to learn first

1. Warehouse fundamentals: facts, dimensions, star schema.
2. Redshift's architecture, as above, and why bulk operations matter.
3. Loading with COPY from S3, and merging through a staging table.
4. Reading a query plan with EXPLAIN, and distribution and sort keys.
5. External tables over S3.
6. Access control, workload management and cost limits.

Data warehousing concepts and AWS data services including Amazon Redshift are covered in the Data Engineering course.

Sources

• What is Amazon Redshift? (Redshift Management Guide) — AWS Documentation
• Data warehouse system architecture (Redshift Database Developer Guide) — AWS Documentation
• Amazon Redshift performance (Redshift Database Developer Guide) — AWS Documentation
• Amazon Redshift and PostgreSQL (Redshift Database Developer Guide) — AWS Documentation
• Compute capacity for Amazon Redshift Serverless (Redshift Management Guide) — AWS Documentation
• Table constraints (Redshift Database Developer Guide) — AWS Documentation