How We Feed OHLC Candles to TradingView from ClickHouse® DB

A trader opens a chart. ~4,000 price events per second are flowing through the pipeline. The chart needs months of candles at the resolution they pick, and it needs them now, not in two seconds. Then it needs live ticks on top of that, with no visible gap between the last stored candle and the current price.

We built this using cascading AggregatingMergeTree tables that pre-compute candles at four resolutions (1m, 5m, 1h, 1d), served through TradingView's UDF API, with live prices streamed in parallel through Cloudflare Durable Objects. A full chart load returns in under 200ms because ClickHouse never touches a raw event.

This is part of the ClickHouse Deep Dive series. The previous post covered AggregatingMergeTree as a reference: what it is, how it works, and when to use it. This article shows how we actually used it in production under massive workloads. Including real queries and the full pipeline.

OHLC candles: what they are and why they're hard

Each candle covers a time window and captures four prices (open, high, low, close) plus volume. That's the format every charting library expects, TradingView included.

The engineering problem is computing these from a raw event stream at multiple resolutions simultaneously (1m, 5m, 1h, 1d), keeping all of them updated as events arrive at ~4,000/second, and serving them fast enough that a chart load feels instant even when requesting months of history. Recomputing from raw events on every request is not even an option at this throughput.

Design decisions

Both pipelines share four design decisions worth explaining up front.

Why OHLC and volume are separate table families

The first instinct is to put OHLCV in one table. One row per candle, all five fields together. That's how TradingView consumes it. If your prices come from trades (the common case in spot markets), you can do exactly that: one MV from the trades table writes all five columns. But in this exchange, prices come from an oracle feed, and volume comes from executed trades. Two different sources. You might think: two MVs writing into the same AggregatingMergeTree, one from raw_prices (for OHLC), one from raw_trades (for volume). The "multiple MVs into one target" pattern is well-documented. Why not here?

The problem is the columns you don't have data for. The price MV can write high, low, open, and close, and in this case, sumSimpleState(0) adds zero for the volume, fine. But going the other direction, the trade MV writing volume would need to write something for high and low. minSimpleState(0) corrupts your low price (0 is lower than any real price). maxSimpleState(0) is wrong for high. And argMinState/argMaxState for open/close need a real (price, timestamp) pair. There's no valid "empty" state you can write for those columns.

So the architecture is two parallel cascades joined at query time. Not optimal on read time, and two pipelines to maintain. Not the preferred solution, but the architecturally feasible one, since Nullable fields are not optimal in ClickHouse.

SimpleAggregateFunction vs AggregateFunction

SimpleAggregateFunction stores a plain scalar. It works for operations that are associative: max(max(a), max(b)) = max(a, b). The max of two maxes is always the max. Same for min, same for sum. ClickHouse just compares two numbers during background merges. Less storage, faster merges, and you can read the value directly on SELECT without a -Merge combinator.

AggregateFunction stores a binary intermediate state. You need this when the merge operation requires more than the result value. argMin tracks both a value AND the argument that produced it. To determine which price was first in an interval, you need to compare timestamps across parts. That requires the full (price, timestamp) pair stored as a binary state.

In the OHLC schema, high and low use SimpleAggregateFunction. open and close use AggregateFunction with argMin/argMax. In the volume schema, everything uses SimpleAggregateFunction since sum is associative.

The cascade pattern

Both pipelines use the same structure: 1m -> 5m -> 1h -> 1d. Four AggregatingMergeTree tables, each fed by a materialized view that reads from the level below. The 1-day table never sees a raw event.

At level 1, -State functions aggregate raw values into an intermediate state. At levels 2-4, -MergeState functions merge existing states from the level below. You never re-aggregate raw data at higher resolutions. You merge pre-computed states. That's what makes the cascade efficient. The final queries never touch raw data either. They always read from a preaggregated state, which is why the serving layer stays fast under load.

Integer division for time bucketing

The first cascade level uses toStartOfMinute(event_timestamp) for the 1-minute aggregation. Higher levels use intDiv(time, N) * N: intDiv(time, 300) * 300 rounds a unix timestamp down to the nearest 5-minute boundary. intDiv(time, 3600) * 3600 for hours. intDiv(time, 86400) * 86400 for days. Pure integer arithmetic, no date function overhead.

Ingestion: dedup before ClickHouse

Events arrive through a message queue. A consumer picks up each batch and writes it into ClickHouse source tables. If the consumer fails mid-batch, the message stays in the queue and gets retried. Standard stuff so far.

The problem is duplicates. Queue retries, pipeline restarts, upstream replays. For OHLC, duplicates are harmless: max is still max, argMin on the same price and timestamp pair produces the same result. Idempotent by nature.

Volume is different. sum is not idempotent. A duplicate trade means double-counted volume, and by the time ReplacingMergeTree merges the duplicate away, the materialized view has already fired and added the extra amount to the aggregate. You can't undo that.

So we deduplicate trades before they reach ClickHouse. The ingestion pipeline runs a Redis hot cache: each incoming event is checked against recently processed events using SET NX. Three states: ready (process it), already-processed (skip), in-process (another worker has it). The dedup window is one day. Generous, but we'd rather waste Redis memory than double-count a trade.

Raw data: two ReplacingMergeTree source tables

Raw data lands in two separate ReplacingMergeTree tables. The first one stores the price events:

CREATE TABLE raw_prices
(
    event_timestamp DateTime64(3),
    asset_id        String,
    price_usd       Float64,
    ingestion_ts    DateTime DEFAULT now()
)
ENGINE = ReplacingMergeTree()
PARTITION BY toYYYYMM(event_timestamp)
ORDER BY (asset_id, event_timestamp)

The second stores individual trade events:

CREATE TABLE raw_trades
(
    event_timestamp DateTime64(3),
    market_id       String,
    asset_in        String,
    amount_in       Float64,
    asset_out       String,
    amount_out      Float64,
    ingestion_ts    DateTime DEFAULT now()
)
ENGINE = ReplacingMergeTree(ingestion_ts)
PARTITION BY toYYYYMM(event_timestamp)
ORDER BY (event_timestamp, asset_in, asset_out)

ReplacingMergeTree(ingestion_ts) uses the ingestion timestamp as a version column. If the same trade lands twice, background merges keep the row with the latest ingestion_ts for each ORDER BY key. Whether (asset_id, event_timestamp) and (event_timestamp, asset_in, asset_out) are unique enough depends on your platform's trade granularity. If two distinct trades can share the same timestamp and asset pair, you'd need a transaction ID or sequence number in the ORDER BY.

This is the second layer of defense against duplicates. If something slips past Redis, background merges will eventually clean it. But "eventually" is the keyword. The materialized views fire on insert, not on merge, so volume on the MVs will be duplicated anyway. Redis catches it before that matters. This layer ensures historical data consistency.

The OHLC pipeline

Price events flow from raw_prices through four AggregatingMergeTree tables. Each level rolls up the one below it. The schemas share the same column definitions, but the state functions change at level 2.

The 1-minute table

A materialized view watches raw_prices and populates the first aggregation level:

CREATE TABLE ohlc_1m
(
    asset_id   String,
    time       DateTime,
    high       SimpleAggregateFunction(max, Float64),
    low        SimpleAggregateFunction(min, Float64),
    open       AggregateFunction(argMin, Float64, DateTime64(3)),
    close      AggregateFunction(argMax, Float64, DateTime64(3))
)
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(time)
ORDER BY (asset_id, time)

CREATE MATERIALIZED VIEW ohlc_1m_mv TO ohlc_1m AS
SELECT
    asset_id,
    toStartOfMinute(event_timestamp) AS time,
    maxSimpleState(price_usd)        AS high,
    minSimpleState(price_usd)        AS low,
    argMinState(price_usd, event_timestamp) AS open,
    argMaxState(price_usd, event_timestamp) AS close
FROM raw_prices
WHERE price_usd > 0
GROUP BY asset_id, time

high/low store plain Float64s. open/close store binary blobs. Two column types in the same table, each chosen for its merge behavior.

The cascade

Here's the 5-minute level:

CREATE MATERIALIZED VIEW ohlc_5m_mv TO ohlc_5m AS
SELECT
    asset_id,
    intDiv(toUnixTimestamp(time), 300) * 300 AS time,
    maxSimpleState(high)        AS high,
    minSimpleState(low)         AS low,
    argMinMergeState(open)      AS open,
    argMaxMergeState(close)     AS close
FROM ohlc_1m
GROUP BY asset_id, time

Notice what changed. high and low still use maxSimpleState/minSimpleState. Because they store plain scalars, taking the max of 1-minute maxes is the same operation as taking the max of raw values.

open and close switched from argMinState to argMinMergeState. At level 1, -State creates a binary state from raw (price, timestamp) pairs. At level 2+, -MergeState merges existing binary states to determine which price had the earliest timestamp across the wider interval.

The full OHLC cascade:

Table	Source	Time bucketing	high/low	open/close
ohlc_1m	raw_prices	toStartOfMinute(event_timestamp)	maxSimpleState, minSimpleState	argMinState, argMaxState
ohlc_5m	ohlc_1m	intDiv(time, 300) * 300	maxSimpleState, minSimpleState	argMinMergeState, argMaxMergeState
ohlc_1h	ohlc_5m	intDiv(time, 3600) * 3600	maxSimpleState, minSimpleState	argMinMergeState, argMaxMergeState
ohlc_1d	ohlc_1h	intDiv(time, 86400) * 86400	maxSimpleState, minSimpleState	argMinMergeState, argMaxMergeState

The volume pipeline

Volume uses a simpler aggregate (sum), but the data path is more complex.

The 1-minute table with two MVs

Each trade has two sides: an asset going in and an asset going out. We compute volume for both sides with separate materialized views that both write into the same AggregatingMergeTree:

CREATE TABLE volume_1m
(
    asset_id   String,
    time       DateTime,
    volume     SimpleAggregateFunction(sum, Float64),
    volume_usd SimpleAggregateFunction(sum, Float64)
)
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(time)
ORDER BY (asset_id, time)

CREATE MATERIALIZED VIEW volume_in_1m_mv TO volume_1m AS
SELECT
    asset_in                              AS asset_id,
    toStartOfMinute(event_timestamp)      AS time,
    sumSimpleState(amount_in)             AS volume,
    sumSimpleState(amount_in * price_usd) AS volume_usd
FROM raw_trades
INNER JOIN last_prices USING (asset_in)
GROUP BY asset_id, time

CREATE MATERIALIZED VIEW volume_out_1m_mv TO volume_1m AS
SELECT
    asset_out                              AS asset_id,
    toStartOfMinute(event_timestamp)       AS time,
    sumSimpleState(amount_out)             AS volume,
    sumSimpleState(amount_out * price_usd) AS volume_usd
FROM raw_trades
INNER JOIN last_prices USING (asset_out)
GROUP BY asset_id, time

Two MVs, one target table. The IN-side MV aggregates amount_in, the OUT-side aggregates amount_out. Both JOIN against a last-price lookup table to convert trade amounts into USD at insert time. The engine combines sumSimpleState values automatically during background merges. Same aggregate type from both sources, no conflict.

The cascade

Same pattern as OHLC. Here's the 5-minute level:

CREATE MATERIALIZED VIEW volume_5m_mv TO volume_5m AS
SELECT
    asset_id,
    intDiv(toUnixTimestamp(time), 300) * 300 AS time,
    sumSimpleState(volume)     AS volume,
    sumSimpleState(volume_usd) AS volume_usd
FROM volume_1m
GROUP BY asset_id, time

No -MergeState anywhere. Because sum is associative and volume is a SimpleAggregateFunction storing a plain Float64, summing 1-minute sums is the same as summing raw values. sumSimpleState works at every level.

Table	Source	Time bucketing	Functions
volume_1m	raw_trades	toStartOfMinute(event_timestamp)	sumSimpleState
volume_5m	volume_1m	intDiv(time, 300) * 300	sumSimpleState
volume_1h	volume_5m	intDiv(time, 3600) * 3600	sumSimpleState
volume_1d	volume_1h	intDiv(time, 86400) * 86400	sumSimpleState

Compared to OHLC, the volume cascade is straightforward. No binary state, no -MergeState combinators, no special merge logic. Just sums all the way down.

Serving: querying + streaming

Two paths feed the chart. Historical candles from ClickHouse. Live ticks from the pipeline.

Querying: TradingView's UDF protocol

TradingView's charting library speaks UDF. The endpoint that matters is /history: it receives a symbol, a resolution, and a time range, and expects OHLCV back as parallel arrays.

The backend picks the closest cascade level that's smaller than or equal to the requested resolution. A 2-hour chart reads from ohlc_1h. A 15-minute chart reads from ohlc_5m. The key: resolutions that don't have their own table still work, because the query re-aggregates pre-computed states from the level below. A 2-hour candle is just two 1-hour states merged at read time using -Merge combinators:

SELECT
    asset_id,
    intDiv(time, 7200) * 7200 AS time,  -- 2-hour buckets from 1h data
    max(high)                  AS high,  -- SimpleAggregateFunction: plain scalar
    min(low)                   AS low,
    argMinMerge(open)          AS open,  -- AggregateFunction: merge binary states
    argMaxMerge(close)         AS close
FROM ohlc_1h
WHERE asset_id = 'USD'
  AND time >= 1710000000
  AND time <  1710086400
GROUP BY asset_id, time
ORDER BY time ASC

The backend runs a separate query against volume_1h for volume (plain SimpleAggregateFunction, no combinator needed), then merges both result sets into UDF's response format before returning it to the client.

A KV cache sits in front of ClickHouse. On each /history request, the backend checks KV first. Cache miss or stale data? It fetches only the delta from ClickHouse starting from the last cached timestamp, merges it with the stored history, and updates the cache. ClickHouse only gets hit for new candles, not the full range.

If KV goes offline, every chart load hits ClickHouse directly. A typical request for 6 months of hourly candles returns ~4,300 rows from ohlc_1h in under 200ms. Without the cascade, that same request would scan millions of raw events. It is both the performance optimization and the safety net.

Streaming: Durable Objects as WebSocket fan-out

Historical candles are pre-computed and fast to scan. But there's a 2-5 second latency between a price event happening and it becoming queryable in ClickHouse: ingestion, materialized view processing, background merges. For traders watching live markets, that gap matters.

So we run a dual path. When the pipeline processes new events, it maps each price to TradingView's streaming format and POSTs it to a /broadcast endpoint:

// Pipeline side: map price to TradingView streaming format
const payload = prices
    .map((p) =>
        JSON.stringify({
            f: 't', // format: trade
            id: marketByAsset[p.assetId], // "USD"
            p: p.priceUsd, // price
            t: Math.floor(p.eventTime / 1000), // unix timestamp
            s: 0, // size
        })
    )
    .join('\n') // NDJSON

await fetch(`${apiUrl}/tradingview/broadcast`, {
    method: 'POST',
    headers: { Authorization: `Bearer ${apiKey}` },
    body: payload,
})

A single Cloudflare Durable Object instance maintains WebSocket connections to every chart client. On broadcast, it fans out to all connected sockets.

The connections use hibernatable WebSockets: this.ctx.acceptWebSocket(server) instead of server.accept(). The DO gets evicted from memory while connections stay open. When a message arrives, the runtime recreates it. Memory usage scales with activity, not connection count.

Charts show historical candles from ClickHouse and live ticks from the pipeline at the same time. No gap between the last stored candle and real-time movement.

End-to-end architecture

This is what the system looks like end-to-end. A price event enters through a message queue, gets deduplicated in Redis, and splits into two paths. One writes to ClickHouse, where materialized views cascade through four resolution levels for both OHLC and volume. The other broadcasts it directly to connected charts via a Durable Object.

The cost structure follows the architecture. ClickHouse handles the heavy storage and compute for both pipelines. That's where most of your infrastructure spending goes. The serving layer is lightweight by comparison: a KV cache that absorbs most reads, a single Durable Object for WebSocket fan-out, and an API Worker that merges two query results into one response. Without cache, it's a different story; we were there, and we did not enjoy it.

The dual-path serving (historical from ClickHouse, live from the pipeline) adds architectural complexity but eliminates the latency gap that would otherwise exist between ingestion and queryability. For a charting product, that gap is the difference between a usable tool and a frustrating one.