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

Shared harness:

Both systems used the same source dataset, analytical workload, target-rate model, query schedule, and result format. Destination-specific adapters handled delivery and timing. Part 1 explains the shared methodology; the Redshift architecture records this provider’s implementation.

Dataset and preparation:

The complete NBBO dataset contains 113,219,565,734 narrow, 12-column rows. The raw-data ordering columns were (sym, t), stock symbol and event timestamp; daily summaries used (sym, day), stock symbol and UTC day, maintaining counts, sums, and price minima and maxima.

Continuous workload:

Fresh events were paced toward 1 million per second while aggregate and drill-down queries ran on their ten-minute and hourly schedules. This models data generated continuously and prepared as it arrives. Redshift’s aggregate queries read the last completed daily-MV refresh; aligning ingestion progress does not make that snapshot equally fresh to ClickHouse’s insert-time summaries.

Recommended stack and sizing:

ClickHouse Cloud used a 16-CPU, 64-GiB read node. Redshift used separate writer and reader workgroups connected by a live datashare. The reader was fixed at 32 RPUs. RPUs are provider capacity units, not an assertion of identical CPU or memory resources. MSK used three kafka.m7g.xlarge brokers, 24 partitions, replication factor three, and 500 GB of storage per broker.

① Preparation cost:

The model includes ClickHouse’s complete two-node HA ingest service and Redshift’s writer plus MSK broker and storage costs. The accepted writer-capacity scenario prices 32 active RPUs across the complete producer window. The original long run used 128 writer RPUs; subsequent characterization found that 32 sustained the target and 16 did not. The cost is modeled at 32; the recorded latency and freshness remain from the long run.

② Query cost and ③ runtime:

ClickHouse prices recorded query duration at its read-side compute rate. Redshift allocates hourly reader compute_seconds to statements in proportion to their recorded elapsed time, then prices the query allocations. Accumulated runtime sums the accepted query durations. The allocation represents query work, rather than reconstructing a complete invoice.

Target versus observed rate:

Redshift’s completed run ingested all 113,219,565,734 rows in 113,227 seconds, about 31.45 hours, averaging 999,933 rows/s. ClickHouse completed its ingest in about 36.75 hours, averaging approximately 0.86 million rows/s. The target was shared; observed progress differed. Kafka delivery, streaming-view visibility, and child-MV visibility are separate milestones.

Reporting window:

Per-query latency plots stop at 100 billion rows. Preparation cost covers the complete producer window; accumulated query cost and runtime use the accepted active-ingestion observations through the reference endpoint. Later query observations and freshness samples are excluded. The writer model also includes both child-MV refresh paths, described below.

Pricing basis:

ClickHouse Cloud uses $0.3903 per compute unit (CU) per hour. Redshift uses the checked-in eu-west-2 rate of $0.467/RPU-hour. MSK uses $0.47175 per broker-hour and $0.116 per GB-month of broker storage. The accepted full-path summary retains the model and full-precision inputs.

① Complete preparation cost:

The full-path scenario includes writer compute over the complete 113,227-second producer window, MSK broker uptime, and prorated broker storage. The writer covers streaming ingestion, background sorting, and both child-MV refreshes.

ClickHouse Cloud:

2 CUs × 36.75 hours × $0.3903/CU-hour = $28.68705, including both HA ingest nodes.

Redshift writer:

The 32-active-RPU scenario uses 32 × (113,227 ÷ 3,600) hours × $0.467/RPU-hour = $470.01785778. The long run used 128 writer RPUs; the 32-RPU cost scenario is grounded in subsequent keep-up characterization and is not original-run billing.

MSK:

Three brokers × producer uptime × $0.47175/broker-hour, plus 1,500 GB × $0.116/GB-month × uptime-hours ÷ 730, totals $52.00912922.

Shared preparation path:

Writer + MSK = $522.02698699. That complete total is charged once in this score. It is neither split between the two read alternatives nor doubled within the comparison.

② Normalized query cost:

ClickHouse prices recorded durations. Redshift allocates each hour’s reader SYS_SERVERLESS_USAGE.compute_seconds across statements that began in that hour, in proportion to statement elapsed_time; the accepted query share is priced at $0.467/RPU-hour.

ClickHouse Cloud:

8 CUs × $0.3903/CU-hour × (10.207s aggregate + 47.915s drill-down) ÷ 3,600 ≈ $0.05041, using the rounded cost-summary inputs.

Redshift aggregate work:

The committed hourly allocations assign $2.955693203 to the 760 aggregate executions. This uses recorded reader compute and statement duration, rather than a flat 32-RPU rate multiplied by each query’s wall time.

Redshift drill-down work:

The 66 executions receive $22.011782831, giving total normalized query cost of $24.967476034. Together, the two workloads receive 53.4635461 RPU-hours of allocated reader compute.

Allocation boundary:

Historical evidence fields named billed_times or billed_rpu_seconds hold hourly compute allocations; their names do not establish invoice billing. Database storage, client cross-AZ transfer, producer VM cost, discounts, and later post-ingestion observations are excluded from the headline scope. MSK’s broker storage is included in its preparation component.

③ Accumulated query runtime:

ClickHouse: 10.207s aggregate + 47.915s drill-down = 58.122s. Redshift: 722.871916s aggregate + 5,370.670031s drill-down = 6,093.541947s.

Comparison boundary:

① uses the complete producer window and declared 32-RPU writer model. ② and ③ use 190 four-query aggregate batches and 33 two-query drill-down batches against the streaming view. The writer also maintains the typed child, but typed query executions are outside this score. Latency statistics use the separate 100-billion-row window.

Final score:

ClickHouse: ($28.68705 + $0.05041) × 58.122 = 1,670.27865. Redshift: ($522.02698699 + $24.967476034) × 6,093.541947 = 3,333,133.70523. The ratio is 1,995.555×, rounded to 1,996×.

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.

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.

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.

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.

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.

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.