SysPad › Lessons › database lessons › Redshift

How Redshift works

Columnar MPP data warehouse for analytics. Built for a modest number of heavy queries, not high-RPS OLTP - throughput is bounded by concurrency slots. ra3 nodes separate compute from managed storage.

Redshift is built to answer one question fast: "across billions of rows, what’s the total / average / trend?" It does that by storing data in columns, not rows, and splitting every query across many machines at once. It’s a terrible transactional database and a brilliant analytical one, and knowing why is the whole point.

What you will learn

Why does storing data by column make analytics 10–100x cheaper to read?
How does "massively parallel processing" turn one big query into many small ones?
Why is Redshift the wrong place to run your app’s per-user reads and writes?
What do distribution keys and sort keys actually change?

Columns, not rows

Analytics usually touches a few columns across a huge number of rows ("sum revenue by month"). Redshift stores each column together, so a query reads only the columns it needs and skips the rest entirely.

Because a column holds similar values, it also compresses extremely well, so there’s even less to read from disk.

Massively parallel processing

A Redshift cluster has a leader node that plans queries and compute nodes (each divided into slices) that do the scanning. Your data is spread across the slices, so every slice works on its piece at the same time.

One enormous scan becomes hundreds of small parallel scans whose partial results are combined. That parallelism is how Redshift chews through terabytes quickly.

⚓ This is the engine behind dashboards, BI tools, and big aggregate reports.

Great at analytics, awful at transactions

All of this is tuned for OLAP (analytical) workloads: big scans, big aggregations, mostly-read. It is not built for OLTP: lots of tiny single-row reads and writes, like an app serving user requests.

Distribution and sort keys

Two choices shape performance. The distribution style (KEY, EVEN, ALL) decides how rows spread across slices, get it right and joins happen locally instead of shuffling data between nodes. The sort key orders data on disk so Redshift can skip blocks that can’t match a filter.

What about RA3 nodes, Serverless, and Spectrum?

RA3 nodes use managed storage so you scale compute and storage separately. Redshift Serverless removes cluster sizing entirely and bills for what you run. Spectrum lets you query data sitting in S3 directly, without loading it first, useful for a data lake alongside the warehouse.

Flashcards

Why is SUM(revenue) over a billion rows so cheap in Redshift?: Columnar storage: it reads only the revenue column (not whole rows), and that column compresses well, so far less data is read from disk.
What turns one huge Redshift scan into many fast ones?: MPP: data is spread across compute-node slices that all scan in parallel, coordinated by the leader node.
Your app does thousands of single-row inserts/updates per second. Redshift?: No. That’s OLTP; columnar storage makes tiny row writes/lookups expensive. Use RDS/Aurora/DynamoDB and load into Redshift for analysis.
Two big tables are joined constantly and Redshift keeps shuffling data between nodes.: Set a matching distribution KEY on the join column so matching rows live on the same slice, making the join local instead of a network shuffle.
You want to query raw data in S3 without loading it into the warehouse first.: Use Redshift Spectrum, it queries S3 data in place, alongside your warehouse tables.

Sources

Open the SysPad canvas · Official Redshift documentation