Amazon Redshift

Overview

Amazon Redshift is a managed data warehouse on Amazon Web Services. It stores tables in a columnar format and spreads both data and query work across many machines that run in parallel, a design called massively parallel processing (MPP). You connect with standard SQL tools and drivers, and its SQL dialect is derived from PostgreSQL, so much of it feels familiar.

Redshift comes in two forms. Provisioned clusters let you choose a node type and number of nodes and pay for them while they run. Redshift Serverless removes that choice: you set a base capacity and it scales up and down automatically, charging for the compute used. Both forms can load data from Amazon S3, query files in S3 directly, share data with other Redshift warehouses and integrate with other AWS services.

A helpful analogy is a large exam marked by a team. One head examiner splits the papers into piles, hands a pile to each marker and adds up their totals at the end. The head examiner is Redshift's leader node, the markers are compute node slices, and the piles are distributed data. The more evenly the piles are split, the sooner everyone finishes.

What it is

Where it came from

AWS announced Redshift as a limited preview at its re:Invent conference in November 2012 and made it generally available on 15 February 2013. It was built using technology licensed from ParAccel, an MPP columnar database. Redshift was among the first widely used cloud data warehouses, and its pricing made warehouse-scale analytics affordable to many smaller teams.

Important later milestones include Redshift Spectrum for querying S3 data in April 2017, concurrency scaling in March 2019 and RA3 nodes with managed storage, announced in December 2019, which separated storage from compute. Data sharing and Redshift ML became generally available in 2021. Redshift Serverless became generally available in July 2022. Zero-ETL integrations, which replicate data from AWS operational databases such as Aurora and DynamoDB into Redshift automatically, became available from late 2023 and through 2024.

How it works

Storing data

Each table's columns are stored separately in 1 MB blocks, compressed with encodings chosen automatically or by you, such as AZ64 or ZSTD. For each block Redshift keeps zone maps, the minimum and maximum values, so queries can skip blocks that cannot match a filter. The sort key decides the physical order of rows. Sorting on a date column, for example, makes date range filters very selective.

The distribution style decides which slice holds each row. With KEY distribution, rows with the same key value go to the same slice, so joins on that key need no data movement. With ALL, a full copy of a small table lives on every node. EVEN spreads rows round-robin. AUTO lets Redshift choose and adjust over time.

Running a query

1. A client sends SQL over JDBC, ODBC or the Data API.
2. The leader node parses and optimises the query and compiles it into steps.
3. Each slice scans its local blocks, using zone maps to skip unneeded ones, and performs joins and partial aggregations.
4. Where joined tables are not co-located, data is redistributed or broadcast between nodes.
5. The leader node combines partial results and returns the answer.

On RA3 nodes and Serverless, data lives in managed storage backed by S3, with frequently used data cached on fast local SSDs. This lets you size compute for performance and pay for storage separately.

Loading data

The COPY command loads files from S3 in parallel, with each slice reading different files. Splitting input into many files of similar size, ideally a multiple of the number of slices, keeps all slices busy.

COPY sales
FROM 's3://example-bucket/sales/2026/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftLoad'
FORMAT AS PARQUET;

Key concepts and terms

A worked example

This scenario is illustrative. A logistics company wants daily reporting on 3 billion shipment events and a 2 million-row customer table, used by about 150 dashboard users.

1. The team chooses Redshift Serverless with a modest base capacity because usage peaks in office hours and is low at night.
2. The events table uses customer_id as the distribution key and event_date as the sort key. The customers table is also distributed by customer_id, so the common join runs locally on each slice.
3. Each night a pipeline writes Parquet files to S3 and runs COPY. Files are split into similar sizes so all slices load in parallel.
4. A typical dashboard query filters the last 14 days. Because the table is sorted by date, zone maps let Redshift skip most blocks.
5. Older events beyond two years stay in S3 as Parquet and are queried through Spectrum external tables when needed, which keeps warehouse storage smaller.
6. A materialised view of daily totals per region refreshes after each load, and dashboards read it instead of the raw events.

Strengths and limitations

Strengths:

• Mature MPP columnar engine with good performance on large analytical queries.
• Choice of provisioned clusters or Serverless.
• Deep integration with S3, IAM, Glue, Kinesis, Aurora and other AWS services.
• Spectrum and data sharing extend the warehouse without copying data.
• PostgreSQL-like SQL and wide BI tool support.

Limitations:

• Good performance on provisioned clusters depends on sensible distribution and sort keys.
• Not designed for high volumes of small transactional writes or single-row updates.
• Concurrency for very many simultaneous queries may need scaling features and tuning.
• Ties analytics closely to AWS.
• Some PostgreSQL features and data types are not supported.

How it compares with alternatives

Practical guidance and best practices

1. Start with AUTO distribution and sort keys, then tune keys for your largest tables based on real query patterns.
2. Distribute large tables that are often joined on the same column by that column.
3. Use a date or timestamp sort key for tables queried by time ranges.
4. Load with COPY from many similar-sized files rather than row-by-row INSERT statements.
5. Prefer RA3 nodes or Serverless so storage and compute scale separately.
6. Use materialised views for repeated dashboard aggregations.
7. Set up workload management queues or priorities so heavy ETL does not starve dashboards.
8. Keep cold data in S3 as Parquet and query it through Spectrum when needed.

Common mistakes

• Choosing a distribution key with very uneven values, causing data skew and slow slices.
• Loading data with many small inserts instead of bulk COPY.
• Ignoring the advice of system views about missing statistics or unsorted tables.
• Over-provisioning a cluster that sits idle most of the day when Serverless would fit better.
• Pulling huge result sets into BI tools instead of aggregating in the warehouse.
• Treating Redshift as an operational database for an application.

Frequently asked questions

What is the difference between provisioned Redshift and Redshift Serverless?

Provisioned clusters run on node types and counts that you choose and pay for while they run. Serverless scales compute automatically within limits you set and charges for the capacity used, measured in RPU-hours. Serverless suits variable or unpredictable workloads; provisioned clusters can be cheaper for steady, heavy use.

Does Redshift separate storage and compute?

Yes, with RA3 nodes and Serverless, which use Redshift Managed Storage. Older dense compute nodes keep data on local disks attached to the nodes.

What is Redshift Spectrum?

A feature that lets Redshift query files in S3, such as Parquet, through external tables without loading them. It is useful for large, infrequently queried data that you want to keep in the data lake.

Is Redshift based on PostgreSQL?

Its SQL dialect and client protocol derive from PostgreSQL, so many PostgreSQL tools and drivers work. Its storage and execution engine are very different, and some PostgreSQL features are not supported.

Can Redshift receive data in near real time?

Yes. It can ingest from Amazon Kinesis Data Streams and Amazon MSK through streaming ingestion into materialised views, and zero-ETL integrations replicate changes from some AWS databases.

Do I still need to run VACUUM?

Redshift runs automatic vacuum and analyse operations in the background for many cases. After very large deletes or updates you may still run them manually to restore sort order and reclaim space faster.

Glossary

• AZ64: a Redshift compression encoding for numeric and date types.
• Columnar storage: storing each column's values together on disk.
• Data skew: uneven distribution of rows that overloads some slices.
• External table: a table definition over files in S3.
• Materialised view: a stored query result that can be refreshed.
• Managed storage: S3-backed storage used by RA3 and Serverless.
• Zero-ETL: managed replication from operational databases into Redshift.
• Concurrency scaling: temporary extra clusters for bursts of queries.

Summary

Amazon Redshift is AWS's columnar MPP data warehouse. It spreads data and work across slices, uses sort keys and zone maps to skip data, and loads in parallel from S3. RA3 nodes and Serverless separate storage from compute, while Spectrum, data sharing and zero-ETL connect it to the wider AWS data platform. Good results on provisioned clusters depend on sensible distribution and sort keys and on bulk loading. Compare it with BigQuery, Snowflake and Amazon Athena when choosing a warehouse, and with a lakehouse design if open formats matter most.

Related

It belongs to Query engines and warehouses in my stack, under Big data. Others in the same group: