Building Real-Time OHLC Candlestick Charts with ClickHouse

OHLC stands for Open, High, Low, Close - the four price points that define a candlestick on a financial chart. If you've used any trading platform you'll be familiar with them. Each candle represents a time period, with the body showing where the price opened and closed, and the wicks showing the extreme high and low for that window. Green means the price went up, red means it went down.

What's interesting from a data engineering perspective is that these charts are typically interactive - you can switch between 1-minute, 5-minute, 15-minute, hourly, daily granularities and the chart updates instantly. Platforms like Binance do this with pre-aggregation, maintaining separate datasets for each resolution so they're not crunching through millions of raw ticks on every request.

ClickHouse handles this pattern extremely well, and in this post I'll walk through exactly how to implement it.

The Demo

Here's a walkthrough of the implementation, including the schema, materialized views, and a demo app built on top:

The Architecture

The approach is straightforward. We maintain a base trades table that holds every raw tick - symbol, price, volume, and timestamp with millisecond precision. On top of that, we create a set of pre-aggregated OHLC tables at different resolutions: 5 minutes, 10 minutes, 15 minutes, 30 minutes, 1 hour, 3 hours, and 24 hours. Materialized views populate each of these automatically on every insert into the trades table.

When the UI requests chart data at a given granularity, it queries the appropriate pre-aggregated table directly rather than touching the raw data. The result is consistently fast queries regardless of how much historical data is sitting in the base table.

The Base Table

The trades table is a standard MergeTree with millisecond timestamp precision:

CREATE TABLE trades
(
    symbol      LowCardinality(String),
    price       Float64,
    volume      Float64,
    trade_time  DateTime64(3)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(trade_time)
ORDER BY (symbol, trade_time);

Using LowCardinality(String) for the symbol column is worth noting - for a field with a small number of distinct values like ticker symbols, it gives better compression and faster filtering.

The OHLC Aggregation Tables

Each pre-aggregated table uses AggregatingMergeTree, which is where things get interesting. Rather than storing plain values, the columns hold intermediate aggregate states that can be merged together later. This is what allows ClickHouse to correctly combine partial aggregations across multiple parts.

Here's the 5-minute table as an example:

CREATE TABLE ohlc_5m
(
    symbol      LowCardinality(String),
    bucket      DateTime,
    open        AggregateFunction(argMin, Float64, DateTime64(3)),
    high        AggregateFunction(max, Float64),
    low         AggregateFunction(min, Float64),
    close       AggregateFunction(argMax, Float64, DateTime64(3)),
    volume      AggregateFunction(sum, Float64),
    trade_count AggregateFunction(count)
)
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(bucket)
ORDER BY (symbol, bucket);

For high and low this is straightforward - we use max and min state. For open and close we need the price at the earliest and latest timestamp within the bucket, which is where argMin and argMax come in. The second type parameter is the timestamp we're argmin/argmax-ing over, so the engine knows which price corresponds to which time.

Materialized Views

A materialized view populates each OHLC table automatically on every insert. For the 5-minute table:

CREATE MATERIALIZED VIEW mv_ohlc_5m TO ohlc_5m AS
SELECT
    symbol,
    toStartOfFiveMinutes(trade_time)    AS bucket,
    argMinState(price, trade_time)      AS open,
    maxState(price)                     AS high,
    minState(price)                     AS low,
    argMaxState(price, trade_time)      AS close,
    sumState(volume)                    AS volume,
    countState()                        AS trade_count
FROM trades
GROUP BY symbol, bucket;

The toStartOfFiveMinutes() call rounds the timestamp down to the start of the containing 5-minute window. Two trades at 12:17 and 12:19 both land in the 12:15 bucket. The AggregatingMergeTree then merges rows with the same key, combining the partial states correctly.

For each additional resolution you just repeat the pattern with a different bucketing function - toStartOfFifteenMinutes, toStartOfHour, toStartOfDay, or toStartOfInterval(trade_time, INTERVAL 10 MINUTE) for anything without a dedicated function.

All the materialized views point directly at the trades table. You can also daisy-chain them - a 15-minute MV that reads from the 5-minute table rather than raw trades - but in practice the compute is equivalent and I've found direct views simpler to reason about.

Querying the Data

When reading from an AggregatingMergeTree table, you must use the *Merge combinator functions to finalise the partial states. Forgetting this is a common gotcha:

SELECT
    symbol,
    bucket,
    argMinMerge(open)       AS open,
    maxMerge(high)          AS high,
    minMerge(low)           AS low,
    argMaxMerge(close)      AS close,
    sumMerge(volume)        AS volume,
    countMerge(trade_count) AS trade_count
FROM ohlc_5m
WHERE symbol = 'BTC/USD'
GROUP BY symbol, bucket
ORDER BY bucket;

The GROUP BY is required because ClickHouse may not have merged all parts yet - the Merge combinators need to aggregate across whatever partial states exist on disk.

Data Reduction in Practice

To give a sense of how effective this is, I inserted around 1,300 BTC/USD trades spanning three days. Here's the row count across each table:

The daily table has 3 rows covering 3 days of data. When the UI requests a daily chart, it's querying a table with a handful of rows, which is why the granularity switching feels instant even with large amounts of underlying data.

Understanding AggregatingMergeTree

If this is your first time using AggregatingMergeTree, it's worth spending time understanding the State and Merge combinator pattern before building on it. The key thing to internalise is that the values stored in the table aren't final - they're serialised intermediate states. Writing to these tables without the *State functions and reading without the *Merge functions will give you wrong results, usually silently.

The ClickHouse docs cover this well, and once it clicks it opens up a lot of patterns for efficient real-time aggregation.

Full Code

For reference, here's the complete schema and sample data:

-- ============================================================================
-- ClickHouse OHLC Aggregation Pipeline
-- ============================================================================

-- 0. Clean slate
DROP TABLE IF EXISTS mv_ohlc_24h;
DROP TABLE IF EXISTS mv_ohlc_3h;
DROP TABLE IF EXISTS mv_ohlc_1h;
DROP TABLE IF EXISTS mv_ohlc_30m;
DROP TABLE IF EXISTS mv_ohlc_15m;
DROP TABLE IF EXISTS mv_ohlc_10m;
DROP TABLE IF EXISTS mv_ohlc_5m;
DROP TABLE IF EXISTS ohlc_24h;
DROP TABLE IF EXISTS ohlc_3h;
DROP TABLE IF EXISTS ohlc_1h;
DROP TABLE IF EXISTS ohlc_30m;
DROP TABLE IF EXISTS ohlc_15m;
DROP TABLE IF EXISTS ohlc_10m;
DROP TABLE IF EXISTS ohlc_5m;
DROP TABLE IF EXISTS trades;

-- 1. Base table
CREATE TABLE trades
(
    symbol      LowCardinality(String),
    price       Float64,
    volume      Float64,
    trade_time  DateTime64(3)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(trade_time)
ORDER BY (symbol, trade_time)
SETTINGS index_granularity = 8192;

-- 2. 5-minute OHLC
CREATE TABLE ohlc_5m
(
    symbol      LowCardinality(String),
    bucket      DateTime,
    open        AggregateFunction(argMin, Float64, DateTime64(3)),
    high        AggregateFunction(max, Float64),
    low         AggregateFunction(min, Float64),
    close       AggregateFunction(argMax, Float64, DateTime64(3)),
    volume      AggregateFunction(sum, Float64),
    trade_count AggregateFunction(count)
)
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(bucket)
ORDER BY (symbol, bucket);

CREATE MATERIALIZED VIEW mv_ohlc_5m TO ohlc_5m AS
SELECT
    symbol,
    toStartOfFiveMinutes(trade_time)    AS bucket,
    argMinState(price, trade_time)      AS open,
    maxState(price)                     AS high,
    minState(price)                     AS low,
    argMaxState(price, trade_time)      AS close,
    sumState(volume)                    AS volume,
    countState()                        AS trade_count
FROM trades
GROUP BY symbol, bucket;

-- 3. 10-minute OHLC
CREATE TABLE ohlc_10m
(
    symbol      LowCardinality(String),
    bucket      DateTime,
    open        AggregateFunction(argMin, Float64, DateTime64(3)),
    high        AggregateFunction(max, Float64),
    low         AggregateFunction(min, Float64),
    close       AggregateFunction(argMax, Float64, DateTime64(3)),
    volume      AggregateFunction(sum, Float64),
    trade_count AggregateFunction(count)
)
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(bucket)
ORDER BY (symbol, bucket);

CREATE MATERIALIZED VIEW mv_ohlc_10m TO ohlc_10m AS
SELECT
    symbol,
    toStartOfInterval(trade_time, INTERVAL 10 MINUTE) AS bucket,
    argMinState(price, trade_time)      AS open,
    maxState(price)                     AS high,
    minState(price)                     AS low,
    argMaxState(price, trade_time)      AS close,
    sumState(volume)                    AS volume,
    countState()                        AS trade_count
FROM trades
GROUP BY symbol, bucket;

-- 4. 15-minute OHLC
CREATE TABLE ohlc_15m
(
    symbol      LowCardinality(String),
    bucket      DateTime,
    open        AggregateFunction(argMin, Float64, DateTime64(3)),
    high        AggregateFunction(max, Float64),
    low         AggregateFunction(min, Float64),
    close       AggregateFunction(argMax, Float64, DateTime64(3)),
    volume      AggregateFunction(sum, Float64),
    trade_count AggregateFunction(count)
)
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(bucket)
ORDER BY (symbol, bucket);

CREATE MATERIALIZED VIEW mv_ohlc_15m TO ohlc_15m AS
SELECT
    symbol,
    toStartOfFifteenMinutes(trade_time) AS bucket,
    argMinState(price, trade_time)      AS open,
    maxState(price)                     AS high,
    minState(price)                     AS low,
    argMaxState(price, trade_time)      AS close,
    sumState(volume)                    AS volume,
    countState()                        AS trade_count
FROM trades
GROUP BY symbol, bucket;

-- 5. 30-minute OHLC
CREATE TABLE ohlc_30m
(
    symbol      LowCardinality(String),
    bucket      DateTime,
    open        AggregateFunction(argMin, Float64, DateTime64(3)),
    high        AggregateFunction(max, Float64),
    low         AggregateFunction(min, Float64),
    close       AggregateFunction(argMax, Float64, DateTime64(3)),
    volume      AggregateFunction(sum, Float64),
    trade_count AggregateFunction(count)
)
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(bucket)
ORDER BY (symbol, bucket);

CREATE MATERIALIZED VIEW mv_ohlc_30m TO ohlc_30m AS
SELECT
    symbol,
    toStartOfInterval(trade_time, INTERVAL 30 MINUTE) AS bucket,
    argMinState(price, trade_time)      AS open,
    maxState(price)                     AS high,
    minState(price)                     AS low,
    argMaxState(price, trade_time)      AS close,
    sumState(volume)                    AS volume,
    countState()                        AS trade_count
FROM trades
GROUP BY symbol, bucket;

-- 6. 1-hour OHLC
CREATE TABLE ohlc_1h
(
    symbol      LowCardinality(String),
    bucket      DateTime,
    open        AggregateFunction(argMin, Float64, DateTime64(3)),
    high        AggregateFunction(max, Float64),
    low         AggregateFunction(min, Float64),
    close       AggregateFunction(argMax, Float64, DateTime64(3)),
    volume      AggregateFunction(sum, Float64),
    trade_count AggregateFunction(count)
)
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(bucket)
ORDER BY (symbol, bucket);

CREATE MATERIALIZED VIEW mv_ohlc_1h TO ohlc_1h AS
SELECT
    symbol,
    toStartOfHour(trade_time)           AS bucket,
    argMinState(price, trade_time)      AS open,
    maxState(price)                     AS high,
    minState(price)                     AS low,
    argMaxState(price, trade_time)      AS close,
    sumState(volume)                    AS volume,
    countState()                        AS trade_count
FROM trades
GROUP BY symbol, bucket;

-- 7. 3-hour OHLC
CREATE TABLE ohlc_3h
(
    symbol      LowCardinality(String),
    bucket      DateTime,
    open        AggregateFunction(argMin, Float64, DateTime64(3)),
    high        AggregateFunction(max, Float64),
    low         AggregateFunction(min, Float64),
    close       AggregateFunction(argMax, Float64, DateTime64(3)),
    volume      AggregateFunction(sum, Float64),
    trade_count AggregateFunction(count)
)
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(bucket)
ORDER BY (symbol, bucket);

CREATE MATERIALIZED VIEW mv_ohlc_3h TO ohlc_3h AS
SELECT
    symbol,
    toStartOfInterval(trade_time, INTERVAL 3 HOUR) AS bucket,
    argMinState(price, trade_time)      AS open,
    maxState(price)                     AS high,
    minState(price)                     AS low,
    argMaxState(price, trade_time)      AS close,
    sumState(volume)                    AS volume,
    countState()                        AS trade_count
FROM trades
GROUP BY symbol, bucket;

-- 8. 24-hour OHLC
CREATE TABLE ohlc_24h
(
    symbol      LowCardinality(String),
    bucket      DateTime,
    open        AggregateFunction(argMin, Float64, DateTime64(3)),
    high        AggregateFunction(max, Float64),
    low         AggregateFunction(min, Float64),
    close       AggregateFunction(argMax, Float64, DateTime64(3)),
    volume      AggregateFunction(sum, Float64),
    trade_count AggregateFunction(count)
)
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(bucket)
ORDER BY (symbol, bucket);

CREATE MATERIALIZED VIEW mv_ohlc_24h TO ohlc_24h AS
SELECT
    symbol,
    toStartOfDay(trade_time)            AS bucket,
    argMinState(price, trade_time)      AS open,
    maxState(price)                     AS high,
    minState(price)                     AS low,
    argMaxState(price, trade_time)      AS close,
    sumState(volume)                    AS volume,
    countState()                        AS trade_count
FROM trades
GROUP BY symbol, bucket;

-- 9. Verify row counts
SELECT * FROM (
    SELECT 1 as index, 'trades'   AS table_name, count() AS rows FROM trades
    UNION ALL SELECT 2, 'ohlc_5m',  count() FROM ohlc_5m
    UNION ALL SELECT 3, 'ohlc_10m', count() FROM ohlc_10m
    UNION ALL SELECT 4, 'ohlc_15m', count() FROM ohlc_15m
    UNION ALL SELECT 5, 'ohlc_30m', count() FROM ohlc_30m
    UNION ALL SELECT 6, 'ohlc_1h',  count() FROM ohlc_1h
    UNION ALL SELECT 7, 'ohlc_3h',  count() FROM ohlc_3h
    UNION ALL SELECT 8, 'ohlc_24h', count() FROM ohlc_24h
) ORDER BY index;

-- 10. Query 5-minute candles
SELECT
    symbol,
    bucket,
    argMinMerge(open)       AS open,
    maxMerge(high)          AS high,
    minMerge(low)           AS low,
    argMaxMerge(close)      AS close,
    sumMerge(volume)        AS volume,
    countMerge(trade_count) AS trade_count
FROM ohlc_5m
WHERE symbol = 'BTC/USD'
GROUP BY symbol, bucket
ORDER BY bucket;

This gives you the foundation for a real-time financial charting application backed by ClickHouse. The pattern scales well - with proper partitioning and ordering, querying millions of candles at any granularity stays fast.

If you're building something similar or need help implementing this for your use case, feel free to get in touch.