Redshift vs ClickHouse for analytics workloads

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^®. 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

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, 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 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:

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:

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:

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).

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:

-- 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:

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:

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:

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, 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:

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 collapses storage + SQL + HTTP:

1. Ingest via Events API or Kafka connector
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. Resend processes 100 TB per month with 62 ms p90 query latency without relying on cache, per Resend's customer story.

Serving API anti-pattern on Redshift

Teams that expose Redshift to product APIs usually build:

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:

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

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, 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, Kafka connector), SQL Pipes as HTTP endpoints, Branches for staging, and workspace monitoring. Per Tinybird's product page, 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.