---
title: "Redshift vs ClickHouse for analytics workloads"
excerpt: "Redshift is a batch MPP warehouse. ClickHouse® is columnar OLAP for real-time analytics. Compare workloads, cost, and when teams use both."
authors: "Tinybird"
categories: "AI Resources"
createdOn: "2026-08-11 00:00:00"
publishedOn: "2026-08-11 00:00:00"
updatedOn: "2026-08-11 00:00:00"
status: "published"
---

Your Redshift cluster handles the finance mart fine. Then product asks for a usage dashboard refreshed every minute. Engineering wants an HTTP API over raw events. Concurrency scaling kicks in for the third time this week. WLM queues show the BI team and the API Lambda fighting for the same slice.

That is the usual moment teams compare **Redshift vs ClickHouse{% sup %}®{% /sup %}**. Not because Redshift "failed," but because the workload changed from batch reporting to continuous serving.

Redshift is an MPP columnar warehouse optimized for curated dimensional models and scheduled SQL. ClickHouse is an OLAP engine optimized for append-heavy event data, high-ingest pipelines, and aggregation queries that must return in sub-second time at billion-row scale.

## Architecture at a glance

| Dimension | Amazon Redshift | ClickHouse |
| --- | --- | --- |
| Primary pattern | Batch ETL → star schema → BI | Stream/batch events → wide tables → SQL APIs |
| Storage layout | Distribution keys + sort keys on MPP nodes | MergeTree partitions + sort keys per table |
| Ingest | COPY from S3, scheduled loads | Kafka, HTTP, S3, continuous inserts |
| Typical latency | Seconds to minutes | Sub-second on tuned queries |
| Concurrency model | WLM queues, concurrency scaling credits | Workload-specific tuning |
| Sweet spot | Finance, BI, governed marts | Product analytics, logs, metrics, ops |

Redshift coordinates a leader node and compute slices. ClickHouse shards/replicas scale horizontally but the developer experience is closer to "one very fast analytical database per cluster."

## Workload fit in practice

**Stay on Redshift when:**

- Dashboards refresh on hourly or daily schedules
- Data is modeled in conformed dimensions and facts before query
- AWS-native BI (QuickSight, Spectrum to S3 lake) is the main consumer
- Finance and ops accept minutes of load latency after ETL

**Add ClickHouse when:**

- Events must be queryable within seconds of production
- APIs or embedded analytics hit the warehouse directly
- Queries scan raw high-cardinality events (session IDs, trace IDs, URLs)
- Concurrency scaling costs track user growth faster than data growth

For [Amazon Redshift alternatives](https://www.tinybird.co/blog/amazon-redshift-alternatives), the trigger is usually **serving latency**, not SQL expressiveness.

## Cost dynamics teams miss

**Redshift:** provisioned nodes or Serverless RPU, RA3 managed storage, concurrency scaling, Redshift Spectrum scans. You pay for cluster uptime and burst concurrency even when dashboards are idle.

**ClickHouse:** self-hosted hardware, ClickHouse Cloud consumption, or [Tinybird](https://www.tinybird.co/) platform tiers. Cost tracks storage + query CPU for analytical scans.

Hidden Redshift tax for real-time features:

- Lambda + API Gateway in front of slow queries
- ElastiCache for result caching
- Extra WLM tuning cycles
- Duplicate marts (one for BI, one "fast" for product)

Hidden ClickHouse tax:

- Operating merges, parts, and TTL yourself (unless managed)
- Building auth/API layers (unless using Tinybird Pipes)

## Hybrid reference architecture

Most large AWS shops land here:

```text
Apps / mobile / web
  → Kinesis / Kafka / Firehose
  → ClickHouse or Tinybird (hot path: APIs, ops dashboards)

Curated aggregates (hourly/daily)
  → S3 parquet
  → Redshift COPY (cold path: finance, executive BI, compliance)
```

Redshift keeps governed metrics with signed-off definitions. ClickHouse keeps raw and semi-raw events for exploratory SQL and product features that cannot wait for ETL.

Spectrum lets Redshift query S3 lake data without loading it. That helps **batch** exploration. It does not replace millisecond APIs over fresh events.

## Same question, two engines

**Product dashboard:** error rate by service, last 24 hours, refresh every 30s.

ClickHouse on raw events:

```sql
SELECT
    toStartOfMinute(event_time) AS minute,
    service_name,
    countIf(status = 'error') / count() AS error_rate
FROM app_events
WHERE event_time >= now() - INTERVAL 24 HOUR
GROUP BY minute, service_name
ORDER BY minute;
```

Redshift after nightly ETL into `fact_events`:

```sql
SELECT
    date_trunc('minute', event_ts) AS minute,
    service_name,
    sum(CASE WHEN status = 'error' THEN 1 ELSE 0 END)::float / count(*) AS error_rate
FROM fact_events
WHERE event_ts >= dateadd(hour, -24, getdate())
GROUP BY 1, 2
ORDER BY 1;
```

Both run SQL. The difference is **freshness**, **concurrency**, and **cost per refresh** when 500 users open the same chart.

**Concurrency math:** 500 users × 30s refresh × 24h dashboard = 1,440,000 query opportunities per day on Redshift if each panel hits the warehouse. Even with result caching, cache miss storms during incidents trigger concurrency scaling. ClickHouse serves the same pattern from pre-aggregated rollups with millisecond scans when sort keys match filters.

**Distribution and sort keys in Redshift:** A fact table with `DISTKEY(user_id)` optimizes joins to a user dimension but hurts queries that aggregate across all users by time. ClickHouse has no distribution key; you pick one sort order per table. Teams migrating often create **two ClickHouse tables** from one Redshift fact: one sorted by `(tenant_id, event_time)` for API queries, one by `(event_time, service_name)` for ops dashboards.

## WLM queues and concurrency scaling

Redshift Workload Management (WLM) assigns queries to queues with memory and concurrency slots. BI dashboards, ETL jobs, and ad hoc analyst SQL share those slots. When product puts an API in front of Redshift, every page load becomes a warehouse query competing with the nightly `COPY`.

Typical symptoms:

- `STL_WLM_QUERY` shows long queue times during business hours
- Concurrency scaling activates repeatedly (extra RPU charges)
- Teams add `LIMIT 1000` everywhere to avoid timeouts
- Materialized views refresh on schedules that still miss product SLOs

ClickHouse does not use WLM, but you still tune `max_threads`, merge settings, and materialized views. The difference is query latency on append-heavy tables is usually milliseconds to low seconds without a queue layer designed for batch BI.

**Rule of thumb:** if more than 20% of Redshift spend is concurrency scaling driven by interactive/API traffic, you are using a batch warehouse as a serving engine.

## Data modeling differences

Redshift dimensional modeling (facts, dimensions, distribution keys, sort keys) optimizes joins across curated tables loaded in bulk. ClickHouse favors denormalized wide event tables with sort keys aligned to filter columns (`event_time`, `tenant_id`, `service_name`).

| Pattern | Redshift | ClickHouse |
| --- | --- | --- |
| Raw events | Staging → ETL → facts | Direct insert into MergeTree |
| Deduplication | `MERGE` in ETL job | `ReplacingMergeTree` or post-query `argMax` |
| Late-arriving data | Reload partition in mart | Insert with version column; MV recomputes |
| Cross-table joins | Star schema strength | Possible but avoid on hot path |

Teams that succeed with both engines **do not mirror Redshift star schemas in ClickHouse**. They model events for analytical scans and export hourly aggregates back to Redshift for finance.

## Migration playbook (serving workloads only)

Do not "lift and shift" the whole warehouse. Move **serving** tables first.

1. **Inventory queries** with p95 > 2s or API timeouts
2. **Tag tables** as raw events vs curated marts
3. **Stream new events** to ClickHouse (Kinesis → Kafka → HTTP)
4. **Backfill** from S3 with partitioned INSERT jobs
5. **Rebuild rollups** as materialized views matching dashboard filters
6. **Point APIs** to ClickHouse/Tinybird endpoints
7. **Leave Redshift** marts for finance until parity checks pass

Parity check example:

```sql
-- ClickHouse
SELECT toDate(event_time) AS d, count() FROM app_events WHERE d = today() - 1 GROUP BY d;

-- Redshift
SELECT event_date, count(*) FROM fact_events WHERE event_date = current_date - 1 GROUP BY 1;
```

Counts should match within known ETL lag bounds.

### Backfill from S3 parquet

Most teams already land events in S3 for Redshift `COPY`. Reuse that path for ClickHouse backfill:

```sql
INSERT INTO app_events
SELECT *
FROM s3(
    'https://your-bucket/events/year=2025/month=07/*.parquet',
    'Parquet',
    'event_time DateTime64(3), user_id String, service_name String, status String, duration_ms Float64'
);
```

Partition backfill jobs by month to limit failure blast radius. Run during off-peak hours; throttle insert rate to avoid merge storms. After backfill, switch streaming ingest to Kafka or HTTP for new events only.

### Materialized views on the hot path

Rebuild dashboard aggregations as ClickHouse materialized views **before** cutting traffic:

```sql
CREATE TABLE app_events_1m
(
    minute        DateTime,
    service_name  LowCardinality(String),
    errors        UInt64,
    total         UInt64
)
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(minute)
ORDER BY (service_name, minute);

CREATE MATERIALIZED VIEW app_events_1m_mv TO app_events_1m AS
SELECT
    toStartOfMinute(event_time) AS minute,
    service_name,
    countIf(status = 'error') AS errors,
    count() AS total
FROM app_events
GROUP BY minute, service_name;
```

Point APIs and live dashboards at `app_events_1m`. Redshift continues serving finance from `fact_events` until hourly exports match.

## When not to migrate

- Compliance requires all metrics in a signed Redshift mart
- Workloads are exclusively batch with no near-term API needs
- Team has no capacity to operate a second engine and will not use managed ClickHouse
- Data volume fits comfortably in Redshift with acceptable queue times

ClickHouse is not a replacement for governed enterprise BI. It is an acceleration layer for event-heavy serving.

## RA3, provisioned, and Serverless cost levers

**RA3 nodes** separate compute and managed storage. You pay for compute hours plus storage beyond the free tier per node. Good when data grows faster than query concurrency.

**DC2 nodes** tie storage to compute. Cheaper at small scale; painful when you outgrow local SSD.

**Redshift Serverless** bills in RPU-hours with base capacity and max limits. Useful when BI concurrency spikes unpredictably. Less predictable at steady high load than right-sized provisioned clusters.

**Concurrency scaling** charges extra when WLM queues overflow. This line item is the canary for "we are using Redshift as a serving engine." Track it separately from base cluster cost.

ClickHouse cost models differ: self-hosted capex/opex, ClickHouse Cloud consumption, or Tinybird platform tiers tied to compute usage. There is no WLM queue tax, but you pay for merge CPU, storage, and egress unless managed.

## Kinesis and Firehose into the hybrid stack

AWS-native ingest often looks like:

```text
Application → Kinesis Data Streams / Firehose
  → S3 (parquet) → Redshift COPY (batch path)
  → Kafka → ClickHouse Kafka engine (hot path)
  → Tinybird Kafka connector (hot path, managed)
```

Firehose to S3 + Redshift `COPY` fits hourly finance loads. It does not meet sub-minute product dashboard SLOs. Split paths at the stream: critical product events to ClickHouse; curated aggregates to S3 for Redshift.

For [real-time streaming architectures](https://www.tinybird.co/blog/real-time-streaming-data-architectures-that-scale), document freshness SLAs per path. Finance may accept 6-hour lag; product APIs may require <30 seconds.

## Spectrum: what it solves and what it does not

Redshift Spectrum queries external tables on S3/Glue without loading data into Redshift tables. Strong for:

- Ad hoc analyst exploration on lake data
- Joining curated Redshift dimensions to cold lake facts
- Reducing duplicate loads when data already lives in S3

Weak for:

- Sub-second API latency over fresh events
- High-cardinality scans (user IDs, session IDs) without partition pruning
- Replacing pre-aggregated rollups for dashboard refresh

Spectrum charges per TB scanned. A poorly partitioned lake query can cost more than loading the partition into ClickHouse once and serving from rollups.

## Tinybird in a Redshift + ClickHouse stack

Real-time APIs on Redshift commonly look like:

```text
Redshift SQL → Lambda → API Gateway → ElastiCache (optional) → client
```

Each hop adds latency, cost, and failure modes. WLM queue depth becomes an API SLO problem.

[Tinybird](https://www.tinybird.co/) collapses storage + SQL + HTTP:

1. Ingest via [Events API](https://www.tinybird.co/docs/ingest/events-api) or [Kafka connector](https://www.tinybird.co/docs/forward/ingest-data/connectors/kafka)
2. Write SQL Pipes with parameterized time ranges and tenant filters
3. Publish sub-second HTTP endpoints consumed by apps and Grafana
4. Optionally export hourly aggregates to S3 for Redshift BI marts

Redshift remains the batch BI system of record. Tinybird handles the hot analytical path.

Canva processes 3.6 PB per month on Tinybird with 54 ms p99 query latency, per [Tinybird's product page](https://www.tinybird.co/product/managed-clickhouse). Resend processes 100 TB per month with 62 ms p90 query latency without relying on cache, per [Resend's customer story](https://www.tinybird.co/customer-stories/resend).

## Serving API anti-pattern on Redshift

Teams that expose Redshift to product APIs usually build:

```text
Redshift SQL → Lambda → API Gateway → ElastiCache (optional) → client
```

Problems that show up in production:

- **WLM queue depth** becomes an API p99 problem. Lambda timeouts correlate with BI dashboard load, not app traffic alone.
- **ElastiCache** masks stale data. Cache invalidation logic duplicates business rules already in SQL.
- **Connection pooling** across Lambda invocations is fragile. Too few connections → queue; too many → cluster overload.
- **Cost** stacks: Redshift compute + concurrency scaling + Lambda + API Gateway + cache cluster.

ClickHouse or Tinybird collapses the serving path to storage + SQL + HTTP. Same query patterns, no WLM between your API and the data.

## Workload inventory before migration

Tag every heavy Redshift query before choosing an engine:

| Tag | Example | Target engine |
| --- | --- | --- |
| `batch_bi` | Executive revenue dashboard, daily refresh | Redshift |
| `serving_api` | Usage API, embedded customer chart | ClickHouse |
| `ops_realtime` | Error rate last 15 minutes | ClickHouse |
| `compliance` | Signed-off finance mart | Redshift |
| `exploratory` | Analyst SQL on raw events | ClickHouse |

Run `STL_QUERY` and `SVL_QLOG` reports for queries with p95 > 5s or frequent concurrency scaling triggers. Migrate `serving_api` and `ops_realtime` first. Leave `batch_bi` and `compliance` on Redshift until aggregate parity checks pass.

## Decision checklist

| Question | If yes → lean |
| --- | --- |
| Must data be queryable within seconds of creation? | ClickHouse |
| Are APIs/customer dashboards hitting the warehouse? | ClickHouse |
| Is workload mostly scheduled BI on curated stars? | Redshift |
| Do you need arbitrary SQL on raw events at billions of rows? | ClickHouse |
| Is AWS-only governance the top constraint? | Redshift (+ Spectrum) |

## What the choice comes down to

Redshift wins governed batch analytics on AWS. ClickHouse wins continuous ingest, high-cardinality event SQL, and sub-second serving at scale.

The question is not which database is "better." It is which workloads belong on a batch warehouse versus an OLAP engine built for [real-time analytics](https://www.tinybird.co/blog/real-time-analytics-a-definitive-guide), and whether you need both.

## Frequently Asked Questions (FAQs)

### Can ClickHouse replace Redshift entirely?

Rarely in mature AWS data teams. ClickHouse replaces the **serving layer** for events and metrics. Finance, compliance, and executive BI often stay on governed Redshift marts fed by hourly aggregates from the hot path.

### Does Redshift Spectrum close the real-time gap?

Spectrum queries S3 without loading data into Redshift. Useful for ad hoc lake exploration and batch reporting on parquet. It does not give sub-second APIs over events that arrived seconds ago. Spectrum scan cost and latency scale with data scanned, not with pre-aggregated rollups.

### What about Redshift Serverless?

Serverless removes cluster sizing toil but WLM and batch-oriented ingest remain. It helps variable BI workloads with unpredictable concurrency. It does not turn Redshift into an event streaming database or remove per-RPU cost when APIs hammer the warehouse during business hours.

### How do teams sync metrics between both systems?

Common pattern: ClickHouse materialized views export hourly parquet to S3; Redshift `COPY` loads `fact_usage_hourly`. Product uses ClickHouse for live dashboards; finance uses Redshift for signed-off numbers. Document ETL lag bounds in the parity SLA (often 1–24 hours for finance marts).

### How do distribution keys in Redshift map to ClickHouse sort keys?

Redshift `DISTKEY` spreads joins across slices. ClickHouse has no distribution key; co-located joins depend on sort key alignment and denormalization. Do not copy Redshift star schemas into ClickHouse. Model wide event tables with sort keys matching filter columns (`tenant_id`, `event_time`, `service_name`).

### Is Tinybird just hosted ClickHouse?

Tinybird runs native ClickHouse with managed ingestion ([Events API](https://www.tinybird.co/docs/ingest/events-api), [Kafka connector](https://www.tinybird.co/docs/forward/ingest-data/connectors/kafka)), SQL Pipes as HTTP endpoints, [Branches](https://www.tinybird.co/product/branches) for staging, and workspace monitoring. Per [Tinybird's product page](https://www.tinybird.co/product/managed-clickhouse), it includes schema iteration, automatic scaling, and SOC 2 Type II certification. You get ClickHouse performance without operating merges, shards, and API auth yourself.

### When should we stay on Redshift only?

When all consumers are batch BI, data freshness of hours is acceptable, AWS-native governance is mandatory, and no product API hits the warehouse directly. Adding ClickHouse without a serving workload is extra operational surface with no user-visible win.

### Can we run both engines indefinitely?

Yes. Hybrid is the stable end state for many AWS data teams: ClickHouse for hot serving, Redshift for governed batch BI. The failure mode is duplicating **definitions** (two different error-rate formulas) without documenting which is canonical for which audience.

{% cta
  title="Real-time analytics beyond Redshift latency"
  text="Tinybird is managed ClickHouse with streaming ingestion and sub-second SQL APIs. Move serving workloads off batch warehouse queues."
  button={href: "https://cloud.tinybird.co/signup", target: "_blank", text: "Try Tinybird free"}
/%}