Redshift vs RDS in AWS

The problem this comparison solves

A SaaS company builds a dashboard for customers to see their usage analytics. They query their production RDS PostgreSQL database directly. During business hours, when customers actively use the product, the dashboard generates complex GROUP BY queries across millions of rows.

Within weeks of launching, the engineering team starts getting tickets: the application is slow. Database CPU is pegged at 100% during dashboard loads. Regular application queries — creating records, updating statuses, fetching user data — time out.

The analytics queries are starving the transactional queries of resources. This is the OLAP-on-OLTP problem. The fix is to move analytics queries off RDS and onto a system designed for them.

That system is Redshift — or at minimum, a read replica. But Redshift is the right answer at scale because of how it’s architecturally different from RDS.

Why they’re architecturally different

RDS is row-oriented. Data is stored row by row. When you insert a record, all columns of that row are written together. When you query by primary key (SELECT * FROM orders WHERE id = 12345), you read exactly one row — efficient. When you run SELECT region, SUM(revenue) FROM orders GROUP BY region over 50 million rows, the database reads every row in the orders table, accessing columns it doesn’t need because rows are stored together.

Redshift is columnar. Data is stored column by column. The revenue column is stored as a contiguous block. The region column is stored as a separate block. When you run SELECT region, SUM(revenue) FROM orders GROUP BY region, Redshift reads only the region and revenue columns — it physically doesn’t touch the other columns. For analytical queries that aggregate a few columns across millions of rows, this is dramatically faster.

Redshift also uses massively parallel processing (MPP) — queries are distributed across many compute nodes and processed in parallel. A query that takes 30 seconds on RDS might take 2 seconds on Redshift because it’s running on 10 nodes simultaneously.

Redshift vs RDS comparison

When RDS is the right database

Your application’s transactional workload. Every application that creates, updates, and retrieves records in real time belongs on RDS. User registration, order placement, payment processing, content management — these are transactional operations where RDS excels.

Analytics on small datasets. If your database is under a few GB and your analytics queries complete in under a second without affecting transactional performance, staying on RDS is fine. The complexity of an ETL pipeline to Redshift is not justified until RDS is struggling.

Ad-hoc queries over live data. If you need to query today’s transactions — not historical trends — RDS is the source of truth. Redshift receives data after it’s loaded from RDS, with some delay. For real-time operational queries, RDS is the right database.

Complex transactional workflows. Multi-table transactions, foreign key constraints, and complex referential integrity logic belongs in RDS. Redshift has limited support for these patterns.

When Redshift is the right database

Historical analytics over large datasets. Monthly revenue reports, user cohort analysis, funnel conversion rates, year-over-year comparisons — these aggregate queries over large date ranges are what Redshift is built for. On RDS at scale, these queries take minutes. On Redshift, they take seconds.

Business intelligence tools. Tableau, Looker, Metabase, and similar BI tools generate complex SQL queries with multiple GROUP BYs, window functions, and subqueries. These tools work with both RDS and Redshift, but they perform significantly better against Redshift for large datasets.

Data from multiple sources. Redshift isn’t just a copy of your RDS data — it can hold data from multiple sources: application databases, third-party APIs, event logs, CRM systems. A unified data warehouse that combines all of this into one queryable store is a core Redshift use case.

Queries over hundreds of millions or billions of rows. Once your data exceeds what RDS can scan efficiently, Redshift’s parallel processing architecture becomes essential. Redshift clusters are designed for this scale.

See Amazon Redshift overview for cluster sizing, distribution keys, and query optimization.

The standard ETL flow: RDS + Redshift together

Using both RDS and Redshift is the standard production pattern for data-mature applications. The flow:

1. Application writes to RDS — all transactional operations happen in PostgreSQL or MySQL on RDS. RDS is the operational database.
2. ETL pipeline extracts from RDS — a scheduled job (AWS Glue, custom Python, or a tool like Fivetran) reads new/changed records from RDS, typically using change data capture (CDC) via AWS DMS or log-based replication.
3. Transformed data loads into Redshift — the pipeline transforms data into a fact/dimension model optimized for analytics and loads it into Redshift using COPY commands (batch loads are much more efficient than row-by-row inserts).
4. BI tools query Redshift — dashboards, reports, and ad-hoc analytical queries hit Redshift, never the production RDS database.

This decoupling means analytics queries never touch the production database. A heavy dashboard query that scans 100 million rows in Redshift has zero impact on the application serving users.

Aurora zero-ETL integration with Redshift (an emerging AWS feature) removes the pipeline step — changes in Aurora are replicated to Redshift automatically without a separate ETL job. This is valuable if you’re on Aurora and want near-real-time analytics in Redshift without managing a pipeline.