Visual overview
Redshift consumes and analyzes large datasets; S3 commonly complements the warehouse as durable analytical object storage.
Technical reference
Redshift physical design is easiest to remember by separating parallel execution, data placement, scan ordering, and compression.
A leader coordinates work that compute resources execute in parallel across portions of the data.
Queries can read the required columns without loading complete rows, reducing analytical scan I/O.
Distribution controls where rows are placed across compute slices and affects parallel balance and data movement.
Sort order lets the optimizer skip blocks/ranges that cannot satisfy predicates when queries align with the key.
Encodings reduce storage and I/O; Redshift can automatically choose encodings in supported workflows.
Redshift is built for analytical SQL
Amazon Redshift is a data warehouse designed for analytical queries over large datasets. Although it exposes SQL and relational tables, its execution and storage model is optimized for operations such as scanning many rows, aggregating, joining large datasets, and producing analytical results. That is a different goal from an OLTP database that prioritizes large numbers of small record-level transactions with very low per-request latency.
Provisioned Redshift architecture uses massively parallel processing (MPP): a leader node coordinates query plans and compute nodes execute pieces of work in parallel. Compute-node resources are divided into slices that operate on portions of data. Redshift Serverless abstracts cluster provisioning, but the analytical principles—parallel work, columnar data, and workload-aware table design—remain relevant.
Columnar storage reduces analytical I/O
Redshift stores table data by column rather than laying out every full row together. Analytical queries often read only a subset of columns across many rows, so columnar layout reduces the data that must be read. Columns also tend to contain similar data values, which can make compression more effective. Together, reduced I/O and compression are central reasons columnar storage fits warehouse workloads.
Redshift supports compression encodings for columns and can automate many physical-design choices. Compression should be evaluated as part of data loading and table design because poor encoding can increase storage and I/O. The goal is not to memorize every encoding but to understand why a warehouse engine can optimize each column according to its data characteristics.
Distribution and sort strategy control parallel work
Data distribution determines how table rows are placed across compute resources. A good distribution strategy can colocate data used in joins or spread work evenly so slices participate in parallel. Redshift can use automatic distribution, and manual styles exist for workloads that require explicit control. A poor distribution key can create data skew, leaving some slices with far more work than others and reducing the benefit of MPP.
Sort keys influence how rows are ordered on storage. When query predicates align with sort order, Redshift can skip ranges that cannot match instead of scanning all data. Distribution and sorting solve different problems: distribution determines where data lives across parallel workers, while sorting influences how efficiently each worker can restrict the data it must scan.
- MPP divides analytical work across compute resources so large operations can execute in parallel.
- Columnar storage reduces I/O when queries need selected columns across many rows.
- Distribution affects data placement and parallel join/work balance.
- Sort keys help the optimizer avoid scanning irrelevant row ranges for matching predicates.
- Compression encodings reduce storage and I/O when selected appropriately.
Key takeaways
- 01
Redshift is an analytical data warehouse, not a drop-in OLTP database merely because it supports SQL.
- 02
Columnar storage and compression reduce the I/O required by analytical queries.
- 03
MPP divides query work across compute resources and benefits from balanced data distribution.
- 04
Distribution style and sort keys solve different physical-design problems.
- 05
Use warehouse architecture for large scans and aggregations while operational databases continue serving transactional application paths.
Official AWS sources
Use these primary AWS resources for the source material behind this article and for deeper reference.