Amazon Athena vs Amazon Redshift
Both run SQL over large datasets, but they sit at different points on the trade between query-time cost and up-front investment in loading and modelling data.
1. Architecture and processing model
Section titled “1. Architecture and processing model”Athena
- Serverless query service, built on the Presto/Trino lineage
- No infrastructure to provision
- Billed per query, on the volume of data scanned
Redshift
- A data warehouse, available in two forms
- Provisioned clusters: you choose node types and counts and manage the cluster; billing is per second while the cluster is not paused
- Redshift Serverless: no cluster or node concept at all — capacity is provisioned and scaled automatically, billed in RPU-hours on a per-second basis with a 60-second minimum
The serverless option removes most of the management-overhead argument that used to separate the two services. What remains is the storage and cost model below.
2. Data storage
Section titled “2. Data storage”Athena queries data where it lives in S3. Nothing is loaded, nothing is copied, and the data stays in whatever format it was written in.
Redshift stores data in its own managed columnar storage, so data must be loaded before it can be queried. Redshift Spectrum (on RA3 and DC2 clusters) and the integrated data lake query engine (on Redshift Serverless and RG clusters) let it read S3 data without loading, at the cost of the performance that loading buys.
3. Use cases
Section titled “3. Use cases”Athena — ad-hoc queries, intermittent analysis, exploration of a new dataset, log analysis, lightweight ETL.
Redshift — repeated complex queries, scheduled reporting, heavy transformation, BI workloads with many concurrent users, enterprise data warehousing.
4. Performance
Section titled “4. Performance”Athena performance depends almost entirely on how the data is organised in S3. Columnar formats (Parquet, ORC), sensible file sizes and partitioning on the columns queries filter by make the difference between seconds and minutes.
Redshift is optimised for repeated queries against modelled data. It uses distribution styles, sort keys, zone maps and compression encodings, and maintains table statistics for the query planner. Note that Redshift has no secondary indexes — sort keys and zone maps do the work that indexes do elsewhere.
5. Cost model
Section titled “5. Cost model”Athena — per terabyte scanned, no standing cost. Cost tracks how much data each query touches, which makes partitioning and columnar formats a cost lever as well as a speed one.
Redshift — per second of compute. A provisioned cluster costs money whenever it is running and unpaused, which is predictable for steady workloads and wasteful for sporadic ones. Redshift Serverless bills only while queries run, which narrows that gap. Storage is billed separately from compute on RG and RA3 nodes and on Serverless.
6. Query optimisation
Section titled “6. Query optimisation”Athena — few knobs. Optimisation is data-layout work: partitioning, file format, file size, compression.
Redshift — distribution styles, sort keys, compression encodings, vacuum and analyze operations, materialised views.
7. Concurrency
Section titled “7. Concurrency”Athena — concurrent query limits apply per account and Region; suited to lower concurrency.
Redshift — designed for high concurrency, with workload management (WLM) and concurrency scaling on provisioned clusters, and automatic scaling on Serverless.
8. Data freshness
Section titled “8. Data freshness”Athena — reads whatever is in S3 right now; no load lag.
Redshift — data is as fresh as the last load, unless read through Spectrum or the data lake query engine.
9. Integration
Section titled “9. Integration”Athena — native S3 integration, uses the Glue Data Catalog, straightforward to call from Lambda.
Redshift — broad AWS integration, Spectrum for S3 data, mature ETL and BI tool support, data sharing between warehouses and accounts.
Choosing
Section titled “Choosing”Choose Athena when queries are ad-hoc or unpredictable, the data already lives in S3, concurrency is modest, and you would rather not own a warehouse.
Choose Redshift when queries are complex and repeated, workloads are predictable enough to model for, concurrency is high, or you need warehouse features such as materialised views and data sharing. If the objection to Redshift was cluster management, evaluate Redshift Serverless before ruling it out.