From Amazon Redshift to ClickHouse

Teams leave Amazon Redshift for ClickHouse when query cost and concurrency become the pain point, provisioned Redshift clusters (or RA3 nodes plus Spectrum) bill for capacity you hold whether or not you’re querying, and heavy dashboard/observability-style workloads get expensive. ClickHouse is a column-store built for exactly that: very fast aggregations over large tables, excellent compression, and the option to self-host or use ClickHouse Cloud. The catch is that it is not a drop-in Postgres-compatible warehouse the way Redshift is, the data model and SQL differ enough that this is a real migration, not a connection-string swap. Scope it as one.

The data-model shift: keys → ORDER BY

Redshift performance comes from sort keys and distribution keys across its nodes. ClickHouse’s equivalent lever is the table engine and its ORDER BY (sorting key) plus partitioning, usually on a MergeTree engine. The translation is conceptual, not mechanical:

• A Redshift sort key → ClickHouse ORDER BY (the primary index that makes range scans fast).
• A Redshift distribution key → there’s no exact analogue; for a single ClickHouse server it’s moot, and for a cluster you design sharding/replication explicitly.
• Partitioning (e.g. by month) is set with PARTITION BY and matters for retention and pruning.

Get ORDER BY right for your common query predicates and ClickHouse will fly; get it wrong and you lose its biggest advantage. This is the single most important design decision in the migration.

Choosing the MergeTree engine

ORDER BY lives on a table engine, and the engine you pick encodes your write pattern. A plain MergeTree is the default for append-heavy fact and event tables, which is where most Redshift analytics volume lives. Reach for ReplacingMergeTree when you need last-write-wins upserts, since ClickHouse does not update rows in place cheaply, and for SummingMergeTree or AggregatingMergeTree when you want to maintain pre-aggregated rollups. Partitioning with PARTITION BY (commonly toYYYYMM on a date column) is for retention and dropping old data, not for query pruning, so keep partitions coarse and let the ORDER BY do the scan-skipping.

Moving the data

The reliable bulk path is via S3 and Parquet:

1. UNLOAD Redshift tables to S3 as Parquet (columnar, compressed, typed).
2. Ingest with ClickHouse’s s3() table function straight from the bucket, no intermediate ETL server required.

-- in Redshift
UNLOAD ('select * from events')
  TO 's3://bucket/events/' IAM_ROLE '…' FORMAT PARQUET;

-- in ClickHouse
INSERT INTO events
  SELECT * FROM s3('https://bucket.s3.amazonaws.com/events/*.parquet', Parquet);

For ongoing sync during a parallel-run period, repeat incrementally (by load date/partition) until cutover. UNLOAD writes one file per slice by default, which is fine: the s3() function reads a wildcard path and ingests the files in parallel. Landing the Parquet on S3 also leaves you a reusable staging layer, so a Trino or DuckDB reader could later query the same files without re-exporting from Redshift. Watch egress and request costs on very large unloads, and prefer bounded, restartable jobs per table or date range over one unbounded dump.

The SQL-dialect rewrite

Both speak SQL, but ClickHouse has its own functions and idioms. Expect to touch:

• Window and date/JSON functions, names and semantics differ from Redshift’s Postgres-flavoured set.
• Data types, map Redshift types to ClickHouse (VARCHAR→String, careful with Nullable, use LowCardinality(String) for low-distinct columns to win big on compression).
• DISTINCT/aggregation patterns, ClickHouse has specialised functions (uniqCombined, -If/-Merge combinators) that often replace heavier Redshift SQL.

Run the rewritten queries against ClickHouse and diff results versus Redshift on the same data before trusting them.

Validate, then cut over

• Dual-run the BI/dashboard layer against both warehouses and compare row counts, key aggregates, and latency.
• Re-point ingestion (your ETL/ELT or streaming loaders) to ClickHouse only after read parity is confirmed.
• Watch resource use, ClickHouse trades RAM for speed; size memory for your largest aggregations and set sensible max_memory_usage limits.

The cost comparison is the reason you are here, so measure it during the dual-run rather than estimating up front. Provisioned Redshift bills for capacity you hold whether or not you query it, plus egress and concurrency charges, while ClickHouse shifts spend to the compute, storage, and engineering you run yourself (or to ClickHouse Cloud). Put the removed node-hour spend against the ClickHouse infrastructure cost for the same migrated queries, and treat the delta as illustrative until the parallel run has held under realistic concurrency for a couple of weeks.

Where Redshift still wins, don’t over-migrate

Weigh the fit carefully. Redshift’s tight AWS integration (Spectrum over S3, Redshift ML, IAM, zero-ops RA3, Data Sharing) and its Postgres compatibility are real advantages for AWS-centric analytics and complex transactional-style warehousing. ClickHouse shines for high-volume, append-heavy, aggregation-first workloads (events, logs, metrics, product analytics). If your workload is the latter, the cost win is large; if it’s broad enterprise BI deeply wired into AWS, keep Redshift for that and move only the heavy aggregation workloads.

The realistic takeaway

Redshift → ClickHouse can sharply cut analytics cost for aggregation-heavy workloads, but it’s a real re-platform: redesign sort/dist keys as ORDER BY/partitioning, move data via UNLOAD-to-Parquet and the s3() function, and rewrite the SQL dialect with results diffed against Redshift. Dual-run before cutover, and migrate the workloads that fit ClickHouse rather than the whole estate. Model the removed provisioned-capacity spend against ClickHouse infra (or Cloud) in the calculator above.