Amazon Redshift overview

Amazon Redshift is a fully managed data warehouse designed for online analytical processing. It is PostgreSQL-derived — it speaks a close dialect of PostgreSQL SQL and works with standard JDBC and ODBC drivers, so most business intelligence tools connect to it without special handling — but the storage engine underneath is entirely different.

Why it is fast at analytics

Columnar storage. Rows are stored column by column rather than row by row. A query that aggregates three columns of a hundred-column table reads only those three columns from disk. Values within a column are also similar to each other, which compresses far better than whole rows do, so less data is read in the first place.

Massively parallel processing. A query is distributed across compute nodes and, on each node, across slices, each working on its own portion of the data. Adding capacity adds parallelism.

Result caching and automatic workload management. Repeated queries can be answered from cache, and the service allocates memory and concurrency between queries without a manual queue configuration.

The corollary is the thing to remember about Redshift: it is optimised for reading a lot and writing in bulk. Single-row inserts are expensive. Load with COPY from S3 in large batches, and treat frequent small writes as a sign the data belongs in an OLTP database instead.

Deployment models

Redshift Serverless requires no cluster. You set a base capacity in Redshift Processing Units — one RPU provides 16 GB of memory, and the default base is 128 RPUs — and the warehouse scales with the workload, learning from it over time. Usage limits can be set to bound spend. It creates a recovery point automatically every 30 minutes, retained for 24 hours, from which a snapshot can be taken for longer retention. This is the usual starting point for new warehouses and for workloads that are intermittent.

Provisioned clusters are the alternative when the workload is steady enough to size deliberately and to buy reserved capacity against, or when a feature only available on a cluster is required. Managed storage separates the warehouse’s storage from its compute so that the two can be scaled independently, and Graviton-based instance families have replaced the earlier generations as the current default.

Snapshots can be restored from a provisioned cluster to Serverless and vice versa, so the choice is not permanent.

Redshift Spectrum

Spectrum runs Redshift SQL directly against data in Amazon S3, without loading it into the warehouse first.

• It makes a data lake queryable through the same SQL and the same tools as the warehouse.
• It shortens the path from data arriving to a question being answered, because there is no ETL step in between.
• It joins S3 data against tables held inside Redshift, which is the practical difference between Spectrum and Amazon Athena: Athena queries S3 on its own, Spectrum queries S3 alongside warehouse tables.

The usual pattern is that S3 holds the raw and infrequently queried history — transaction logs, sensor readings, clickstreams, third-party feeds — while Redshift holds the curated, frequently queried subset, and Spectrum spans the two.

Enhanced VPC routing

Enhanced VPC routing forces COPY and UNLOAD traffic through your VPC rather than over the public AWS network, so that it is subject to your security groups, network ACLs, VPC endpoints and flow logs. Turn it on where data movement has to be auditable or confined to private networking; note that it then requires the VPC to have a route to S3, normally through a gateway endpoint.

Zero-ETL integrations

Redshift can now receive data continuously from operational sources — Aurora, RDS, DynamoDB, streaming services and some third-party applications — without a pipeline being built and maintained for each. Where one of these integrations covers the source, it removes a whole category of ETL code, which is worth checking for before writing a Glue job.

About the name

Two explanations circulate: an astronomical reference to the redshift of receding galaxies, and a joke about shifting away from Oracle’s red branding. AWS has never settled it, and both are repeated often enough to be worth knowing about but not worth asserting.