ClickHouse Migration from Redshift: What I Learned Moving 20TB of Data

I was five months into a migration that should have taken six weeks. Our Redshift cluster was choking on 200M daily events. Query times were spiking to 30 seconds. The CFO was asking hard questions.

Here's the hard truth: Moving from Redshift to ClickHouse isn't just a database swap. It's a fundamental shift in how you think about data. I've done this three times now. Each time taught me something I wish I'd known upfront.

What is ClickHouse migration from Redshift? It's the process of transferring your analytics workload from Amazon's columnar data warehouse to ClickHouse's column-oriented OLAP database. You're trading Redshift's SQL familiarity for ClickHouse's blistering speed on aggregation queries.

This guide covers the exact steps I used. The gotchas that burned me. The migration patterns that actually work at scale.

Most people think these are interchangeable. They're wrong.

Redshift is a full SQL database with mature ACID compliance. ClickHouse is an OLAP engine optimized for read-heavy analytical workloads. They share columnar storage. Everything else diverges.

The fundamental differences:

1. Storage architecture: Redshift uses a shared-nothing architecture with leader and compute nodes. ClickHouse uses a shared-disk model with separate compute and storage. ClickHouse scales reads horizontally with ease. Redshift requires cluster resizing.

2. Query execution: Redshift compiles SQL to C++ code. ClickHouse uses vectorized execution. This makes ClickHouse 5-100x faster on aggregation queries.

3. Data ingestion: Redshift expects batch inserts through COPY commands. ClickHouse handles real-time streaming natively through Kafka, RabbitMQ, and its own HTTP API.

In my experience, the migration fails when teams try to treat ClickHouse like a drop-in Redshift replacement. The SQL dialects look similar. They are not.

A concrete example: UPDATE behavior

Redshift supports standard UPDATE statements. ClickHouse does not. You get INSERT with DEDUPLICATION or the ReplacingMergeTree engine.

-- Redshift: Standard UPDATE
UPDATE orders 
SET status = 'shipped' 
WHERE order_id = 12345;

-- ClickHouse: You need ALTER with UPDATE mutation
ALTER TABLE orders 
UPDATE status = 'shipped' 
WHERE order_id = 12345;
-- Note: This creates a mutation, not an in-place update

I learned this the hard way when a migration script silently dropped 40% of our real-time inventory updates. The data looked correct. It was two days stale.

Switching to ClickHouse unlocked capabilities Redshift couldn't touch.

Speed on analytical queries

We had a dashboard showing 30-day rolling revenue by product category. Redshift took 45 seconds. ClickHouse completed the same query in 300 milliseconds. No indexes, no partitions, no pre-aggregation.

According to a 2024 benchmark by ClickHouse, ClickHouse outperforms Redshift by 2-10x on standard analytical queries. The gap widens with complex GROUP BY operations.

Real-time data ingestion

Redshift's COPY command loads data batch-style. You schedule it every 5 minutes. ClickHouse accepts data streams from Kafka natively.

-- ClickHouse Kafka engine table
CREATE TABLE kafka_events_queue (
    event_type String,
    timestamp DateTime,
    user_id UInt64,
    payload String
) ENGINE = Kafka
SETTINGS kafka_broker_list = 'broker1:9092',
         kafka_topic_list = 'user_events',
         kafka_group_name = 'clickhouse_consumer',
         kafka_format = 'JSONEachRow';

This eliminated our ETL pipeline entirely. Events land in ClickHouse within seconds of production.

Storage compression

ClickHouse's columnar compression is aggressive. I've seen 5-10x compression ratios on real-world datasets. Our 8TB Redshift footprint compressed to 800GB in ClickHouse.

According to Altinity's 2023 comparison, ClickHouse typically achieves 2-3x better compression than Redshift for similar data types.

Cost reduction

Redshift's pricing is compute-inclusive. You pay for nodes regardless of usage. ClickHouse separates compute and storage. We reduced our data infrastructure costs by 60% after migration.

Here's the exact migration pipeline I built. Three nodes. Twenty terabytes. Zero downtime.

Phase 1: Schema conversion

Redshift and ClickHouse share SQL similarities. But data types differ critically.

Redshift Type	ClickHouse Type	Notes
BIGINT	Int64	Direct match
VARCHAR(255)	String	Variable
TIMESTAMP	DateTime	Watch timezone handling
DOUBLE PRECISION	Float64	Direct match
GEOMETRY	Not supported	Use Tuple(Float64, Float64)

The biggest trap: ClickHouse's DateTime is timezone-naive by default. Redshift stores UTC with timezone awareness. I lost three days debugging a time-offset bug in revenue reporting.

-- Redshift timestamp
CREATE TABLE orders (
    order_id BIGINT,
    created_at TIMESTAMP,
    amount DECIMAL(10,2)
);

-- ClickHouse equivalent
CREATE TABLE orders (
    order_id Int64,
    created_at DateTime('UTC'),  -- Explicit timezone
    amount Decimal(10,2)
) ENGINE = MergeTree()
ORDER BY (created_at, order_id);

Phase 2: Data export from Redshift

UNLOAD to S3 in parallel. This is critical for speed.

UNLOAD ('SELECT * FROM orders')
TO 's3://bucket/orders/'
IAM_ROLE 'arn:aws:iam::123456789012:role/MyRedshiftRole'
PARALLEL TRUE
GZIP
DELIMITER '|';

The PARALLEL TRUE flag writes multiple files. Each file corresponds to a Redshift slice. This parallelizes your export.

Phase 3: Data import to ClickHouse

Use ClickHouse's native INSERT from S3. Skip intermediate processing.

-- Direct S3 import into ClickHouse
INSERT INTO orders 
SELECT * 
FROM s3('https://s3.amazonaws.com/bucket/orders/*.gz',
         'AWS_ACCESS_KEY',
         'AWS_SECRET_KEY',
         'TSV')
SETTINGS input_format_allow_errors_ratio = 0.01,
         input_format_allow_errors_num = 100;

I learned to set input_format_allow_errors_ratio early. One malformed row in a million can stop the entire ingestion. Allow 1% error tolerance during migration.

Phase 4: Validation

Run identical queries on both systems. Compare row counts. Check date boundaries.

-- Validation query
SELECT 
    date_trunc('day', timestamp) as day,
    count(*) as row_count,
    sum(revenue) as total_revenue
FROM orders
WHERE timestamp >= '2024-01-01'
  AND timestamp < '2024-02-01'
GROUP BY day
ORDER BY day;

I used this approach with a 0.1% tolerance threshold. Any discrepancy over 0.1% triggered an audit.

Start with read-only workloads

Don't migrate your entire stack at once. Begin with dashboards and analytical reports. Keep Redshift as the source of truth for write operations.

I've found that running dual systems for 4-6 weeks catches migration bugs you can't find in testing. Real users exercise edge cases your test suite misses.

Right-size your ClickHouse cluster

ClickHouse memory is the bottleneck. Each query thread requires memory for intermediate results.

Rule of thumb: 1 GB of RAM per 100 GB of data for MergeTree tables. Double that if you use materialized views or aggregating states.

Data Size	ClickHouse Nodes	RAM per Node	Storage
1 TB	2	32 GB	500 GB NVMe
10 TB	4	64 GB	2 TB NVMe
50 TB	8	128 GB	8 TB NVMe

According to ClickHouse's official deployment guide, over-provisioning RAM is cheaper than dealing with OOM crashes during peak loads.

Use materialized views for common queries

ClickHouse materialized views are trigger-based. They update synchronously with inserts. This is vastly different from Redshift's lazy materialized views.

-- ClickHouse materialized view
CREATE MATERIALIZED VIEW daily_revenue_mv
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(day)
ORDER BY (day, product_category)
AS SELECT
    toDate(timestamp) as day,
    product_category,
    sum(revenue) as daily_revenue
FROM orders
GROUP BY day, product_category;

This view updates automatically. Queries against it run in milliseconds.

Plan for schema evolution

ClickHouse is less flexible with ALTER TABLE than Redshift. Adding columns to MergeTree tables creates new parts. Too many columns degrade performance.

Design your schema for 6-12 months upfront. Add 20% extra columns as "buffer slots" you can repurpose later.

ClickHouse migration from Redshift isn't for everyone. Here's where it shines and where it struggles.

Choose ClickHouse when:

• Your queries are analytical aggregations (SUM, COUNT, AVG with GROUP BY)
• You ingest real-time data streams
• You need sub-second query response on billions of rows
• Your storage costs are rising faster than compute costs

Stick with Redshift when:

• You need complex JOINs across many tables
• Your workload is mixed OLTP/OLAP
• You require full ACID compliance for reporting
• Your team is deeply invested in Redshift-specific features (Spectrum, stored procedures)

According to Posthog's 2024 migration analysis, they saw 4x faster queries and 3x lower costs after switching. But they also spent 6 months rewriting 40% of their SQL queries.

The trade-off is real: ClickHouse trades SQL compatibility for speed. Every query you write in Redshift needs auditing. Some work as-is. Others require complete rewrites.

Every migration hits problems. Here's what I've faced.

Challenge 1: JOIN performance

ClickHouse JOINs are single-threaded. Large table JOINs can be slower than Redshift.

-- Slow ClickHouse JOIN
SELECT *
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'completed';

-- Faster alternative: Denormalization
SELECT *
FROM orders o
WHERE o.status = 'completed'
-- Pre-join user data into orders table during ingestion

I fixed this by denormalizing critical JOINs before migration. My orders table now includes user_name, user_email, and user_segment directly.

Challenge 2: Mutation latency

ClickHouse mutations (UPDATE/DELETE) are async. They create new parts. Then they merge these asynchronously.

-- This runs immediately but the mutation is async
ALTER TABLE orders
UPDATE status = 'cancelled'
WHERE order_id = 12345;

-- Wait for mutation to complete
SELECT *
FROM system.mutations
WHERE table = 'orders'
  AND is_done = 0;
-- Blocks until mutation finishes

For real-time updates, I switched to ReplacingMergeTree with versioning. This avoids mutations entirely.

Challenge 3: Timezone headaches

Redshift stores TIMESTAMP WITH TIME ZONE internally as UTC. ClickHouse's DateTime is timezone-naive unless you specify it.

-- ClickHouse with timezone support
CREATE TABLE events (
    event_time DateTime('America/New_York'),
    event_type String
) ENGINE = MergeTree()
ORDER BY event_time;

-- Convert to UTC for consistency
SELECT toTimeZone(event_time, 'UTC') as utc_time
FROM events;

I now store all timestamps as DateTime('UTC') and convert at query time. This matches Redshift's behavior.

Will my Redshift SQL queries work in ClickHouse?

No. ClickHouse supports a subset of SQL. Complex JOINs, window functions, and subqueries often need rewriting. Plan for 40-60% query modification rate.

How long does a ClickHouse migration from Redshift take?

For 10TB, expect 4-8 weeks. Schema conversion takes 1-2 weeks. Data transfer takes 2-3 days. Query rewriting takes 3-6 weeks.

Can I run both Redshift and ClickHouse simultaneously?

Yes. We ran dual systems for 6 weeks. Redshift handled writes. ClickHouse served reads. A CDC pipeline kept both in sync.

What happens to my existing ETL pipelines?

Most ETL tools support ClickHouse. Airbyte, Fivetran, and custom Python scripts work. But you'll need to adapt data types and timezone handling.

How does pricing compare?

ClickHouse is typically 40-60% cheaper for analytical workloads. Compute costs are lower. Storage costs are lower due to better compression.

Is ClickHouse production-ready?

Yes. ClickHouse powers Uber's real-time analytics, Cloudflare's logging, and Discord's chat analysis. It handles 1B+ rows per second in production.

Do I need a dedicated DBA?

ClickHouse is simpler to operate than Redshift. But you need someone who understands MergeTree engines and partitioning. Budget for 1-2 weeks of learning.

Can I migrate with zero downtime?

Yes. Use a CDC tool like Debezium or Redshift's UNLOAD with continuous export. Cut over during a maintenance window for the final sync.

ClickHouse migration from Redshift delivers real benefits: faster queries, lower costs, real-time ingestion. But it's not a weekend project.

Start with a small workload. Validate everything. Plan for query rewrites.

Here's my recommended timeline:

1. Week 1-2: Schema conversion and test queries
2. Week 3-4: Data export and import, validation
3. Week 5-6: Query rewriting and dashboard updates
4. Week 7-8: Cutover and monitoring

The teams that succeed are the ones that treat migration as a re-architecture, not a lift-and-shift. ClickHouse is different. Embrace the differences rather than fighting them.

If you're considering this migration, my one piece of advice: spend more time on schema design than you think you need. Get that right, and everything else becomes manageable.

---

Nishaant Dixit: Founder of SIVARO. Building data infrastructure and production AI systems since 2018. Built systems processing 200K events/sec. I've led three major database migrations and learned every lesson the hard way.

Connect on LinkedIn: https://www.linkedin.com/in/nishaant-veer-dixit

1. ClickHouse Official Performance Benchmarks
2. Altinity ClickHouse vs Redshift Comparison
3. Posthog Migration Analysis
4. ClickHouse Deployment Guide
5. Redshift vs ClickHouse on BenchANT

---

Originally published at https://sivaro.in/articles/clickhouse-migration-from-redshift-what-i-learned-moving.