ClickHouse 与 Redshift 实时分析成本对比

All benchmark code and results are available in the CostBench repository. The stock-quotes dataset requires a separate data license, so the data itself cannot be redistributed.

To explain that result, we follow the same path as the data: preparation first, then query serving.

How ClickHouse Cloud prepares incoming data

The benchmark used a dedicated ClickHouse Cloud ingest service with two 2-CPU nodes for high availability, the smallest HA configuration that sustained the target during calibration. The shared client sent rows through asynchronous inserts built into each node.

The diagram below shows those same nodes handling ① columnar storage, ② ordering raw data, and ③ ordered pre-aggregation, plus background merges, on four CPU cores in total.

① Store in columns and ② order data: When an ingest node flushes its asynchronous-insert buffer, it sorts raw quotes by the MergeTree table’s (sym, t) key, stock symbol and event timestamp, and writes an ordered data part.

③ Order and pre-aggregate data: The same node runs the incremental MV query over the incoming block in memory, computes aggregate states, and sorts the results by the AggregatingMergeTree table’s (sym, day) key before writing an ordered part containing the summaries.

Background merges consolidate parts in both tables.

The result: raw data and pre-aggregations advance together from the same flushed insert block, without a separate refresh cycle.

How much work did the four-core ingest service handle? (click to expand) The ClickHouse writer-utilization results show ingestion continuing alongside background maintenance. CPU usage remained around 3.1 of the 4 available cores, tracked memory stayed within capacity, background merges processed several million rows per second, and the maximum active-part count per partition stayed around 100. These diagnostics describe the ClickHouse source run reused for this comparison.

Both nodes were retained for high availability, so the preparation cost includes the complete two-node deployment.

Now follow the same preparation tasks through Redshift Serverless, from the MSK stream to the streaming view and its daily summaries.

How Redshift Serverless prepares incoming data

The benchmark used Redshift streaming ingestion from Amazon MSK directly into a materialized view, without an S3 landing stage. The view stored each quote’s payload in Redshift’s SUPER data type, with symbol and event timestamp exposed as typed sort-key columns. The charts label this read path “SUPER.”

The diagram below follows ① columnar storage in the streaming view, ② background sorting, and ③ incremental refresh of the daily aggregate MV. It shows the 32-RPU writer scenario used for preparation pricing, separately from the query consumer.

① Store in columns: The producer sends quotes to MSK. Redshift’s writer consumes that topic into the quotes_streamed materialized view, with AUTO REFRESH YES. The live streaming view retains the event-level payload used by the headline drill-down queries.

② Order data: The streaming view used SORTKEY (sym, t), with DISTSTYLE EVEN to preserve scan parallelism for symbol filters. Redshift sorts in the background, so newly arrived rows can be visible before that layout work is complete.

③ Order and pre-aggregate data: A separate daily MV groups directly from the streaming view by (sym, day), with its own sort key. A controller repeatedly issues incremental REFRESH MATERIALIZED VIEW ... RESTRICT. The daily MV and a typed raw projection are sibling branches; daily summaries do not wait for the typed branch.

The result: Redshift’s streaming view, background sorting, and daily summaries advance through separate stages, so queries can encounter unfinished preparation.

Why these ingestion paths, and how were they buffered? (click to expand) The same ingestion problem:

The source produces frequent writes that must become efficient analytical storage. ClickHouse accepts them through native asynchronous inserts. Redshift’s recommended high-rate streaming path includes an MSK broker cluster plus a consuming writer workgroup; both belong in preparation cost.

Redshift’s streaming path:

The producer used 16 processes and lz4 compression. MSK provided three brokers across three availability zones, with 24 partitions and replication factor three. Redshift consumed the stream directly; no intermediate S3 load job was added.

ClickHouse’s native path:

Ordinary INSERT requests with async_insert enabled were buffered on each receiving node. An adaptive flush timeout responded to incoming traffic. Ordering and pre-aggregation ran on the ingest nodes; the dedicated ingest service shared storage with the independently sized read service.

Shared pacing:

Both adapters read the same Parquet dataset and decoded the same schema under the same target-rate model. Parallelism and client batch sizes were adapted to each destination’s API; they were not forced to be identical.

ClickHouse batches:

The client sent 3,000-row batches through asynchronous inserts. Default buffering flushed at the first of three thresholds: 100 MiB buffered, an adaptive timeout between 50 milliseconds and 1 second, or 450 queued insert queries.

Redshift delivery:

Producer acknowledgments report Kafka delivery, not completion of all downstream views. Streaming ingestion advances the raw streaming view; separate refresh transactions advance the typed projection and daily summaries.

Batch-size boundary:

Client INSERT batches and Kafka batches are transport units. They do not define the final MergeTree part, Redshift storage block, or daily-MV refresh size.

Progress and refresh evidence:

The monitor sampled SYS_STREAM_SCAN_STATES during ingestion and journaled child refreshes. These point-in-time observations were recorded while the workload ran; they were not reconstructed from a post-run view.

Incremental maintenance:

The two child MVs are defined directly on quotes_streamed and refreshed with RESTRICT. The refresh journal’s server status identifies incremental updates; labels such as Manual or Auto alone do not establish incremental versus full refresh. Each child refreshes independently.

Writer-capacity boundary:

The diagram’s 32 RPUs come from the accepted capacity scenario: 32 RPUs sustained the target in characterization with about five seconds of median streaming lag, while 16 fell behind. The long-run measurements were recorded with a 128-RPU writer. Pricing 32 RPUs does not turn those latency or freshness measurements into a 32-RPU rerun.

What this means for freshness and preparation cost

Pre-aggregation lag

ClickHouse builds summaries during inserts; Redshift refreshes them separately. The chart below follows Redshift’s Kafka event watermark to its daily aggregate MV: the underlying active observations show 46 seconds of median lag. ClickHouse’s zero line represents raw data and summaries advancing together.

The Redshift series includes the streaming-ingestion and daily-refresh path, while ClickHouse’s baseline describes raw-to-summary synchronization. Queries against Redshift’s daily MV read its last completed refresh; they do not automatically add the newer streaming rows. The serving comparison below retains that freshness behavior.

How was pre-aggregation freshness measured? (click to expand) Redshift series:

The freshness evidence uses successful daily refresh records through the active endpoint. It measures the Kafka event watermark represented in the daily MV, rather than only the interval between refresh completions. The lag sampler supplies the row-progress timeline.

Statistics and display:

The active daily-MV series contains 1,884 observations, with 46 seconds median, 53 seconds P95, and 79 seconds maximum lag. The chart applies a centered five-observation rolling median; its displayed curve reaches about 64 seconds. The first nonmonotonic lag sample and 463 post-ingestion daily refreshes are excluded.

ClickHouse baseline:

The zero line is a benchmark-semantic raw-to-summary baseline, not a separately polled source-to-query metric. The raw table and incremental MV consume the same flushed insert block. It does not claim zero ingestion delay or use the same source-watermark boundary as Redshift’s series.

ClickHouse kept raw data and summaries together; Redshift’s daily summaries trailed the Kafka event watermark by 46 seconds at the median.

Freshness is one consequence of the preparation path. The next chart accounts for the cost of running that path.

Cost of keeping incoming data query-ready

ClickHouse handles ① columnar storage, ② ordering raw data, and ③ ordered pre-aggregation inside its ingest service. Redshift handles that work in its writer, fed by a separately charged MSK cluster. The chart below compares ClickHouse’s preparation cost with the 32-RPU Redshift writer scenario plus MSK.

ClickHouse’s complete ingest service cost $28.69. Redshift’s modeled preparation path totaled $522.03: $470.02 for the writer, including streaming ingestion, sorting, and both child-MV refresh paths, plus $52.01 for MSK brokers and storage. The shared path is charged once.

ClickHouse handled the complete preparation path at 18.2× lower cost than the Redshift writer-plus-MSK scenario.

Now follow the raw data and summaries into query serving.

How ClickHouse Cloud serves queries

Ordered raw data lets drill-downs skip unrelated rows; pre-aggregations let interactive aggregations combine prepared results instead of recalculating them from individual quotes. The diagram below follows both paths through ClickHouse Cloud’s read service: one node with 16 CPUs and 64 GiB of memory.

Drill-downs: Queries prune ordered raw data in the event-level MergeTree table. Filtering by symbol, the leading column of its (sym, t) sorting key, lets the read service skip unrelated quotes.

Interactive aggregations: Queries read current pre-aggregations from the AggregatingMergeTree table, combining prepared counts, sums, minima, and maxima from a much smaller set of daily summary rows. There is no independent refresh to wait for.

The result: drill-downs prune ordered raw data; interactive aggregations read summaries kept current during ingestion.

Redshift separates serving into a consumer workgroup, reading the streaming and aggregate views through a live datashare.

How Redshift Serverless serves queries

The diagram below shows a separate 32-RPU consumer workgroup reading shared data from the writer. The live datashare exposes the streaming view and daily aggregate MV without copying them into a second serving database.

Drill-downs: The drill-down queries filter the typed sym column in quotes_streamed, then navigate and cast measure fields from the payload. Its (sym, t) sort key supports pruning, but incoming rows can be read before background sorting completes.

Interactive aggregations: Queries read the daily aggregate MV as of its last completed refresh. They do not merge newer streaming-view rows into the answer at query time, so the returned summary can trail incoming data.

The diagram’s two paths show where preparation remains visible to readers: drill-downs can encounter unsorted data, while aggregate queries can read an earlier summary snapshot.

What do Redshift’s refresh and datashare guarantees mean? (click to expand) Snapshot contract:

A query reads committed contents of the daily MV. The benchmark did not force a refresh before every query or reconstruct its answer from newer streaming rows. The measured aggregate latency therefore retains Redshift’s summary-refresh lag.

Refresh contract:

AUTO REFRESH YES drives streaming ingestion. A controller separately refreshes the downstream MVs because Redshift does not permit automatic refresh on an MV defined on another MV. RESTRICT updates only the named child from the committed parent state. This avoids stacking the typed projection’s refresh delay into daily-summary freshness.

Live-share contract:

Sharing exposes the writer’s committed objects to the reader workgroup. It separates read compute from preparation compute; it does not make those objects fresher or sort their newest rows before queries access them.

Read-path boundary:

The headline drill-down results come from the live streaming view. A typed projection was also maintained and measured as an alternative. Its refresh and storage-layout work are retained in the shared preparation scenario, even though its query executions are excluded from this article’s score.

Sizing boundary:

The reader was fixed at base=max 32 RPUs during the long run. Writer capacity is modeled separately for preparation cost. The score combines those declared cost assumptions with the recorded read-path results; it does not present them as a literal provider invoice.

The result: Redshift drill-downs can read rows before background sorting finishes; interactive aggregations can return summaries from an earlier refresh.

What this means for query performance and cost

Drill-downs over ordered raw data

ClickHouse used ORDER BY (sym, t) and ordered rows during inserts; Redshift used SORTKEY (sym, t) and relied on background sorting. D1 and D2 investigate one stock’s growing history: hourly price summaries and a risk-and-liquidity profile. The chart follows Redshift’s live streaming view, bypassing the daily aggregate MV.

ClickHouse Cloud is yellow; Redshift Serverless is orange.

How were query latencies and accumulated results compared? (click to expand) Query definitions:

Part 1 describes A1-A4 and D1-D2. The Redshift SQL preserves the symbol filter and full-history scope, with engine-specific substitutions. D1 uses window functions for first and last prices; D2 uses approximate discrete percentiles and moment calculations. Its excess-kurtosis convention differs from ClickHouse’s raw population kurtosis, so strict numerical equivalence is not asserted for that field.

Cache policy:

enable_result_cache_for_session was off. Underlying data caches were allowed to operate normally; the benchmark did not flush them between queries.

Timing:

Redshift latency uses SYS_QUERY_HISTORY.elapsed_time, converted to seconds; ClickHouse uses its recorded runner duration. Median, P99, and maximum pool unsmoothed per-query observations with 0 < raw_rows ≤ 100 billion. P99 uses linear interpolation. Accumulated runtime uses the same recorded timing basis.

Latency charts:

Each system is plotted at its own observed ingestion progress. Aggregate trends use a centered seven-observation rolling median; drill-downs use five observations, with shrinking edge windows. No outliers are removed. The curves are display smoothing; the statistics and accumulated totals use the original recorded durations.

Interactive readouts:

Values below the plots follow the displayed trends at the selected row count. Logarithmic scales keep milliseconds and seconds readable together; Linear shows absolute differences. Each chart has its own playback and row-position controls.

Accepted accumulated workload:

The selection contains 190 four-query aggregate batches and 33 two-query drill-down batches against the streaming view per system: 760 + 66 = 826 executions. ClickHouse observations are aligned monotonically to Redshift row progress through the 113,219,565,734-row reference horizon. Later post-ingestion observations are excluded.

Accumulated charts:

Query durations are added at corresponding ingestion counts as step sums, without smoothing or interpolating future executions into earlier positions. Preparation-cost lines allocate the complete-ingest total proportionally by row progress; they are not metered cost-at-time traces. The score sums query durations, not elapsed ingestion time.

ClickHouse Cloud:

• 722.5 ms median - 98.6× faster than Redshift Serverless
• 1.15 s P99 - 281× faster
• 1.18 s maximum - 378× faster

Accumulated runtime was 47.92 seconds across the 33 two-query batches. Both drill-downs stayed near or below one second for most of the plotted window.

Redshift Serverless:

• 71.27 s median
• 322.96 s P99
• 446.00 s maximum

Accumulated runtime was 89.51 minutes across the same 33 batches. Reading the growing streaming view took substantially longer, with D1’s window-based hourly summaries showing the larger latency curve.

Across this drill-down workload, ClickHouse Cloud was 112× faster overall.

Why use Redshift’s streaming view, and what about typed columns? (click to expand) Streaming view:

The streaming view keeps the payload as SUPER and promotes sym and t to typed sort-key columns. D1 and D2 filter the typed sym column, preserving pruning intent; they cast the payload’s measure fields when computing results. This is a different representation from ClickHouse’s typed raw table.

Typed alternative:

A sibling typed MV projects the payload fields into columns, with the same (sym, t) sort key and EVEN distribution. Its queries implement the same Redshift drill-down logic. Both representations were measured during the long run.

Observed outcome:

Across the active-ingestion comparison, the typed alternative accumulated 2.11× as much drill-down runtime as the streaming view. D1 was approximately equal; D2 was 4.42× slower on typed data. The primary article follows the faster measured alternative, which reads the streaming view.

Cost boundary:

The writer-plus-MSK preparation cost includes both child refreshes and is shared unchanged between the read alternatives. The score for the streaming view charges only its aggregate and drill-down query allocations, rather than adding typed query executions.

Interpretation:

The benchmark measures the complete recorded query outcome. It does not isolate how much of the gap comes from background sorting, payload navigation, query formulation, or other engine work. A typed layout cannot be assumed faster from a short characterization when the complete run shows otherwise.

Interactive aggregations over ordered pre-aggregated data

Daily pre-aggregations let A1-A4 combine prepared counts, sums, and price minima and maxima. A1-A2 filter summaries by symbol; A3-A4 read across all symbols. ClickHouse reads current summaries; Redshift reads its last completed daily-MV refresh. The chart compares aggregate latency as ingestion continues.

ClickHouse Cloud:

• 13 ms median - 34.6× faster than Redshift Serverless
• 42 ms P99 - 128× faster
• 111 ms maximum - 57.2× faster

Accumulated runtime was 10.21 seconds across the 190 four-query batches.

Redshift Serverless:

• 449.4 ms median
• 5.32 s P99
• 6.35 s maximum

Accumulated runtime was 12.05 minutes across the same 190 batches. These queries read refreshed summaries without waiting for the next update. ClickHouse served them faster while maintaining summaries during inserts; Redshift’s measured latency came with a refresh-dependent freshness boundary.

Across the interactive-aggregation workload, ClickHouse Cloud was 70.8× faster overall, with summaries maintained during inserts.

Accumulated query cost and runtime

Drill-downs account for most of Redshift’s accumulated query runtime. The charts below combine both workloads and show the reader compute allocated to that query work.

Across 826 accepted executions per system, ClickHouse Cloud accumulated 58.12 seconds of query runtime and Redshift 1.69 hours. Normalized query-serving cost was about $0.05 versus $24.97, respectively. These are costs attributed to the accepted query work, not complete provider invoices.

ClickHouse Cloud served the query workload 104.8× faster overall, with 495× lower normalized query cost.

From the field: Clay

Clay’s customers were feeling the limits of its Redshift reporting system. Credit-usage dashboards routinely timed out or took too long to load, with P95 query latency around 60 seconds.

After migrating the reporting workload to ClickHouse, Clay improved both the speed of the answers and the freshness of the data behind them. In his Open House talk, software engineer Paul Noble reports that P95 query latency fell from approximately 60 seconds to 1 second, while dashboard data freshness improved from approximately 75 seconds to 4 seconds. Watch Clay’s migration example.

Those improvements address two requirements of customer-facing analytics: recent data and answers fast enough to use interactively. Clay’s figures describe its own production migration; CostBench measures the separate Redshift Serverless workload described above.

Redshift Serverless can’t match ClickHouse Cloud for real-time analytics

While fresh data kept arriving, ClickHouse Cloud led in all three measured components:

• ① Preparation cost: 18.2× lower than the Redshift writer-plus-MSK scenario.
• ② Normalized query cost: 495× lower across the accepted query work.
• ③ Accumulated query runtime: 104.8× lower across both query workloads.

The final chart brings these results together, plotting combined preparation and normalized query cost against accumulated query runtime as fresh data arrives.

Up is lower total modeled cost; right is lower accumulated query runtime.

ClickHouse Cloud delivered 1,996× better end-to-end performance per dollar in this streaming comparison, with summaries kept current during inserts.

The difference starts in the preparation path. ClickHouse orders raw quotes and builds current summaries during inserts. Redshift’s stream, background sorting, and child-MV refreshes advance separately. Its drill-downs took much longer, and aggregate queries were slower even while reading refresh-dependent snapshots.

That is why Redshift Serverless can’t match ClickHouse Cloud for real-time analytics.