Build a real-time market data app with ClickHouse and Massive

Real-time tick data applications are a classic example of real-time analytics. Like tracking user behavior in web apps or monitoring metrics from IoT devices, they involve high-frequency event streams that need to be ingested, stored, and queried with low latency.

In financial markets, the difference is the urgency. Even a few seconds of delay can turn a profitable trade into a loss. Every trade and quote update generates a new tick, and these can number in the thousands per second across multiple symbols.

ClickHouse is a strong fit for this type of workload. It handles high-frequency inserts, time-based queries, and low latency queries. Built-in compression helps reduce storage overhead, even with billions of rows per symbol. Materialized views can be used to pre-aggregate or reorganize data as it's written, optimizing query performance without needing a separate processing layer.

In this post, we'll walk through how to build a real-time tick data application using Massive (formerly Polygon.io) to access market data and ClickHouse to store and query ticks in real time. We'll put that together using Node.js for the backend operation and React for the live visualization. Let's dive in.

What is a tick? #

Before we begin, it helps to understand what a quote is and what a trade is. A quote represents the current prices at which market participants are willing to buy or sell a security. Specifically, it includes the best bid (the highest price someone is willing to pay) and the best ask (the lowest price someone is willing to sell for). These are continuously updated as new orders enter or exit the market.

A trade, on the other hand, is an actual transaction between a buyer and a seller. It occurs when someone agrees to the current ask or bid price and an order is matched and executed. Trades are recorded with the executed price, the size of the trade, and a timestamp.

Tick data typically comes in two streams. One contains quote updates, and the other contains trade executions. Both are essential for understanding market behavior, but they serve different purposes in analysis and strategy development.

Access to real-time market data #

Now we understand the type of data we're going to ingest, let's have a look at how to access it. We first need to find and subscribe to a stock market API. There are many available, the one we picked to build this demo is Massive. Choose a plan that explicitly includes both stock trades and quotes over WebSockets. A REST API key or a plan with aggregate data alone is not sufficient. Check the current channel entitlements before subscribing. 

WebSockets are essential for streaming market data because they eliminate the latency and overhead of polling REST APIs. Instead of establishing new connections for each data request and potentially missing ticks between calls, WebSockets maintain a persistent connection that pushes data the moment it's available which is critical for high-frequency market data where milliseconds matter.

Starting to ingest data using Massive API is fairly straightforward, simply establish a connection with the /stocks endpoint, authenticate using your Massive API key and start processing messages.

The following standalone Node.js diagnostic authenticates, subscribes after authentication succeeds, and logs the received events. Run it from the example directory after installing dependencies and configuring .env. Stop it before starting the full application; account connection limits can prevent multiple clients from using the same feed.

1require("dotenv").config();
2const WebSocket = require("ws");
3
4if (!process.env.MASSIVE_API_KEY) throw new Error("Set MASSIVE_API_KEY in .env");
5const symbols = (process.env.MASSIVE_SYMBOLS || "AAPL,MSFT,NVDA")
6  .split(",").map((symbol) => symbol.trim()).filter(Boolean);
7const ws = new WebSocket("wss://socket.massive.com/stocks");
8
9ws.on("open", () => {
10  ws.send(JSON.stringify({ action: "auth", params: process.env.MASSIVE_API_KEY }));
11});
12ws.on("message", (data) => {
13  const payload = JSON.parse(data.toString());
14  for (const row of payload) {
15    if (row.ev === "status") {
16      console.log(row.status, row.message);
17      if (row.status === "auth_success") {
18        ws.send(JSON.stringify({
19          action: "subscribe",
20          params: symbols.flatMap((symbol) => [`T.${symbol}`, `Q.${symbol}`]).join(","),
21        }));
22      } else if (row.status === "auth_failed") {
23        ws.close();
24      }
25    } else if (row.ev === "T" || row.ev === "Q") {
26      console.log(row);
27    }
28  }
29});
30ws.on("error", (error) => console.error(error.message));
31process.on("SIGINT", () => ws.close());

Ingesting Data into ClickHouse #

Modeling Tick Data in ClickHouse #

Tick data is relatively straightforward to model since it consists of just two event types, one for trade and another one for quote, each with a small set of mostly numeric fields. Below is the DDL for creating two separate tables. t is the SIP timestamp in milliseconds, while q is the per-symbol sequence number. Condition and indicator arrays use UInt32, since values can exceed 255. The trade schema also retains optional ds (fractional size as text) and pt (participant timestamp) fields. Missing optional fields use defaults. Current quote sizes are expressed in shares. The example uses Float64 for prices and sums the integer trade size s; use suitable decimal types and fractional-volume handling where your application requires them.

1CREATE TABLE IF NOT EXISTS quotes
2(
3    `sym` LowCardinality(String),
4    `bx` UInt8,
5    `bp` Float64,
6    `bs` UInt64,
7    `ax` UInt8,
8    `ap` Float64,
9    `as` UInt64,
10    `c` UInt8,
11    `i` Array(UInt32),
12    `t` UInt64,
13    `q` UInt64,
14    `z` Enum8('NYSE' = 1, 'AMEX' = 2, 'Nasdaq' = 3),
15    `inserted_at` UInt64 DEFAULT toUnixTimestamp64Milli(now64())
16)
17ENGINE = MergeTree
18ORDER BY (sym, t - (t % 60000));
19
20CREATE TABLE IF NOT EXISTS trades
21(
22    `sym` LowCardinality(String),
23    `i` String,
24    `x` UInt8,
25    `p` Float64,
26    `s` UInt64,
27    `ds` String DEFAULT '',
28    `pt` UInt64 DEFAULT 0,
29    `c` Array(UInt32),
30    `t` UInt64,
31    `q` UInt64,
32    `z` Enum8('NYSE' = 1, 'AMEX' = 2, 'Nasdaq' = 3),
33    `trfi` UInt64,
34    `trft` UInt64,
35    `inserted_at` UInt64 DEFAULT toUnixTimestamp64Milli(now64())
36)
37ENGINE = MergeTree
38ORDER BY (sym, t - (t % 60000));

The data volume can grow quickly.

Subscribe to a small symbol list while developing, then measure throughput before subscribing to the entire market. Choosing an effective order key is essential for performance. In this case, rows are ordered first by sym (the stock symbol), grouping all events for the same symbol. Within each symbol group, rows are ordered by t - (t % 60000), which creates 1-minute time buckets. This approach works well in our case, as we aggregate data by symbol to generate the visualization. The minute bucket groups nearby events, while queries still use the original timestamp and sequence number to determine event order. Benchmark the sorting key and filters against your workload; a time bucket alone does not guarantee efficient pruning for every query.

Ingestion strategy #

There are several ways to design an ingestion pipeline for this type of application, including using a message queue like Kafka. However, to minimize latency, it's often better to keep the system simple and push data directly from the WebSocket connection into ClickHouse when possible.

Once that setup is in place, the next step is to choose the right ingestion method. ClickHouse supports both synchronous and asynchronous inserts.

With synchronous ingestion, data is batched on the client side before being sent. The batch size should strike a balance between memory usage, latency, and system overhead. Larger batches reduce the number of insert requests and improve throughput, but they can increase memory usage and delay individual records. Smaller batches reduce memory pressure but may create more load on ClickHouse by generating too many small data parts.

With asynchronous ingestion, data is sent to ClickHouse continuously, and batching is handled internally. Incoming records are first written to an in-memory buffer, which is then flushed to storage based on configurable thresholds. This method is useful when client-side batching isn't practical, such as when data comes from many small clients.

In our case, synchronous ingestion is a better fit. Since there's only one client pushing data from the WebSocket API, batching can be managed on the client side for better control over performance and resource usage.

This compact batching class illustrates the ingestion path. Create a TickBatcher and pass incoming WebSocket frames to its handleMessage method, alongside the authentication handler above. Stop incoming messages before awaiting close(). The full example adds bounded concurrent inserts, reconnect handling, shutdown deadlines, and metrics. Both use synchronous inserts so success means ClickHouse has acknowledged the write.

1const { createClient } = require("@clickhouse/client");
2
3class TickBatcher {
4  constructor() {
5    this.client = createClient({
6      url: process.env.CLICKHOUSE_URL || "http://localhost:8123",
7      username: process.env.CLICKHOUSE_USERNAME || "default",
8      password: process.env.CLICKHOUSE_PASSWORD || "",
9      database: process.env.CLICKHOUSE_DATABASE || "default",
10      compression: { request: true, response: true },
11      clickhouse_settings: { async_insert: 0 },
12    });
13    this.batches = { trades: [], quotes: [] };
14    this.flushing = { trades: false, quotes: false };
15    this.failedRecords = 0;
16    this.droppedRecords = 0;
17    this.timer = setInterval(() => {
18      void this.flushBatch("trades");
19      void this.flushBatch("quotes");
20    }, 2000);
21  }
22
23  handleMessage(data) {
24    const payload = JSON.parse(data.toString());
25    for (const { ev, ...fields } of payload) {
26      const table = ev === "T" ? "trades" : ev === "Q" ? "quotes" : null;
27      if (!table) continue;
28      if (this.batches[table].length >= 10000) {
29        this.droppedRecords++;
30        continue;
31      }
32      this.batches[table].push(fields);
33      if (this.batches[table].length >= 1000) void this.flushBatch(table);
34    }
35  }
36
37  async flushBatch(table) {
38    if (this.flushing[table]) return;
39    this.flushing[table] = true;
40    try {
41      while (this.batches[table].length) {
42        const values = this.batches[table].splice(0, 1000);
43        try {
44          await this.client.insert({ table, values, format: "JSONEachRow" });
45        } catch (error) {
46          this.failedRecords += values.length;
47          console.error(`Insert failed for ${table}:`, error.message);
48        }
49      }
50    } finally {
51      this.flushing[table] = false;
52    }
53  }
54
55  async close() {
56    clearInterval(this.timer);
57    while (Object.values(this.flushing).some(Boolean)) {
58      await new Promise((resolve) => setTimeout(resolve, 10));
59    }
60    await Promise.all([this.flushBatch("trades"), this.flushBatch("quotes")]);
61    await this.client.close();
62  }
63}

The compact class bounds each pending table buffer at 10,000 records and counts discarded or failed records; it does not retry failed writes. For a lossless pipeline, add durable buffering, replay, and deduplication. The full demo flushes at 1,000 rows or every two seconds and runs at most four inserts concurrently.

Visualize live market data #

Once the data is stored in ClickHouse, building the visualization layer is straightforward. The main challenge lies in writing the right SQL queries. Let’s have a look on how to achieve this. We’ll focus on the queries needed to power two key visualizations. The first is a real-time table that updates continuously to show the latest trading data for a specific stock.

To build this visualization, one query is enough, the data can be formatted using ClickHouse's powerful SQL query language and custom functions.

1with 
2{syms: Array(String)} as symbols,
3toDate(now('America/New_York')) as curr_day,
4trades_info as (
5    select
6        sym,
7        argMax(p, tuple(t, q)) as last_price,
8        round(((last_price - (argMin(p, tuple(t, q)))) / nullIf(argMin(p, tuple(t, q)), 0)) * 100, 2) as change_pct,
9        sum(s) as total_volume,
10        max(t) as latest_t
11    from
12        trades
13    where
14        toDate(fromUnixTimestamp64Milli(toInt64(t), 'America/New_York')) = curr_day
15        and sym in symbols
16    group by
17        sym
18    order by
19        sym asc
20),
21quotes_info as (
22    select
23        sym,
24        argMax(bp, tuple(t, q)) as bid,
25        argMax(ap, tuple(t, q)) as ask,
26        max(t) as latest_t
27    from
28        quotes
29    where
30        toDate(fromUnixTimestamp64Milli(toInt64(t), 'America/New_York')) = curr_day
31        and sym in symbols
32    group by
33        sym
34    order by
35        sym asc
36)
37select
38    t.sym as ticker,
39    t.last_price as last,
40    q.bid as bid,
41    q.ask as ask,
42    t.change_pct as change,
43    t.total_volume as volume,
44    toUnixTimestamp64Milli(now64()) - toInt64(greatest(t.latest_t, q.latest_t)) as latency
45from
46    trades_info as t
47    left join quotes_info as q on t.sym = q.sym;

Let's break down what this query does.

First, it defines two variables: symbols, which holds the list of stock tickers to analyze, and curr_day, which captures the current date in the New York timezone.

The query then retrieves trade data, including:

• last_price: The most recent trade price, using argMax(p, tuple(t, q)) to break millisecond timestamp ties with the sequence number
• change_pct: The percentage change from the first ingested trade of the current New York calendar day, with a zero-denominator guard. This is not the official opening price or the previous close. 
• total_volume: Total integer share volume ingested for that calendar day

It also fetches quote data:

• bid: Most recent bid price using argMax(bp, tuple(t, q))
• ask: Most recent ask price using argMax(ap, tuple(t, q))

Finally, the trade and quote results are joined. The additional latency column reports the age of the latest received event in milliseconds, rather than measuring only database insertion time. Rows below are illustrative historical values. The demo does not filter trade conditions, process cancellations/corrections, or reconstruct an official exchange OHLCV series.

The second visualization we’re going to analyze is a candlestick visualization that shows the price evolution and volume for a given stock.

Let’s have a look at the SQL query to power this visualization.

1select
2toUnixTimestamp64Milli(toDateTime64(toStartOfInterval(fromUnixTimestamp64Milli(toInt64(t)), interval 2 minute), 3)) as x,
3argMin(p, tuple(t, q)) as o,
4max(p) as h,
5min(p) as l,
6argMax(p, tuple(t, q)) as c,
7sum(s) as v
8from trades
9where x > toUnixTimestamp64Milli(now64() - interval 1 hour)
10and sym = {sym: String}
11group by x order by x asc;

This query groups the last hour into two-minute buckets and computes open, high, low, close, and volume. Open and close use (t, q) to resolve trades with identical timestamps. The bucket filter excludes a partially overlapping bucket at the start of the window.

The browser calls a named-query Express endpoint; only server-defined, parameterized SQL is accepted. ClickHouse credentials remain on the server and are never placed in NEXT_PUBLIC_ variables. To visualize the query result, we use click-ui components for the table display and Chart.js for the candlestick visualization.

Scaling and practical tips #

Handling high-frequency market data in production requires more than just a fast database. The following tips and techniques help ensure your system remains performant and reliable as data volume grows.

Scaling ingestion #

When dealing with tick-level data across many symbols, sustained throughput can easily exceed tens of thousands of records per second.

To handle this there are different things to look for: 

• Use client-side batching with insert sizes optimized for your system's memory and latency constraints.
• Use compression: Compressing insert data reduces the size of the payload sent over the network, minimizing bandwidth usage and accelerating transmission. 
• Monitor the number of parts created in ClickHouse to prevent excessive merging. This blog talks about asynchronous insert, but the part creation section can be applied for a synchronous ingestion. You can also use advanced dashboards to monitor the number of data parts.
• Massive also provides performance tips to handle high volume data consumption.

Monitoring ingest latency #

As discussed earlier, having the freshest data is critical for a financial application. So it does make sense to monitor it.

You can easily calculate and track the difference between the event timestamp (when the tick occurred) and the ingestion timestamp (when it was stored). This event-to-insert delay includes feed delivery, client batching, network time, and database ingestion; it is not pure database latency. The query below measures it for the latest trade per symbol within the last hour. Signed arithmetic avoids unsigned underflow when timestamps differ unexpectedly.

1SELECT
2    sym,
3    count() AS trade_count,
4    argMax(toInt64(inserted_at) - toInt64(t), tuple(t, q)) AS ingest_latency_ms
5FROM trades
6WHERE t >= toUnixTimestamp64Milli(now64() - INTERVAL 1 HOUR)
7GROUP BY sym
8ORDER BY trade_count DESC
9LIMIT 100;

Visualizing this metric in a dashboard helps you catch slowdowns early.

Take advantage of materialized views #

Materialized views are useful when you want to pre-aggregate data as it arrives. This helps optimize specific query patterns that rely on time-based summaries. A typical example is computing OHLCV (Open, High, Low, Close, Volume) metrics for financial data at fixed intervals, such as 1-minute windows. By generating these aggregates during ingestion, you can serve results quickly without recalculating them each time.

Start by creating a destination table to store the 1-minute OHLCV aggregates. Use AggregatingMergeTree to merge aggregate states. Include every grouping dimension in the sorting key: here, symbol, tape, and minute. Open and close states use the same (t, q) ordering as the raw queries.

1CREATE TABLE trades_1min_ohlcv
2(
3    `sym` LowCardinality(String),
4    `z` Enum8('NYSE' = 1, 'AMEX' = 2, 'Nasdaq' = 3),
5    `minute_bucket_ms` UInt64,
6    `open_price_state` AggregateFunction(argMin, Float64, Tuple(UInt64, UInt64)),
7    `high_price_state` AggregateFunction(max, Float64),
8    `low_price_state` AggregateFunction(min, Float64),
9    `close_price_state` AggregateFunction(argMax, Float64, Tuple(UInt64, UInt64)),
10    `volume_state` AggregateFunction(sum, UInt64),
11    `trade_count_state` AggregateFunction(count)
12)
13ENGINE = AggregatingMergeTree
14ORDER BY (sym, z, minute_bucket_ms);

The next step is to create the materialized view.

1CREATE MATERIALIZED VIEW trades_1min_ohlcv_mv TO trades_1min_ohlcv
2AS SELECT
3    sym,
4    z,
5    intDiv(t, 60000) * 60000 AS minute_bucket_ms,
6    argMinState(p, tuple(t, q)) AS open_price_state,
7    maxState(p) AS high_price_state,
8    minState(p) AS low_price_state,
9    argMaxState(p, tuple(t, q)) AS close_price_state,
10    sumState(s) AS volume_state,
11    countState() AS trade_count_state
12FROM trades
13GROUP BY sym, z, minute_bucket_ms;

Create the destination and view before starting ingestion. The view processes new inserted blocks; it does not automatically backfill existing rows. Backfill existing data with a coordinated INSERT ... SELECT if needed, taking care not to count overlapping live data twice. The query uses the -Merge functions to combine states even before background merges finish.

To view the data, execute this query.

1-- Query the table
2SELECT
3    sym,
4    z,
5    minute_bucket_ms,
6    fromUnixTimestamp64Milli(toInt64(minute_bucket_ms)) as minute_timestamp,
7    argMinMerge(open_price_state) AS open_price,
8    maxMerge(high_price_state) AS high_price,
9    minMerge(low_price_state) AS low_price,
10    argMaxMerge(close_price_state) AS close_price,
11    sumMerge(volume_state) AS volume,
12    countMerge(trade_count_state) AS trade_count
13FROM trades_1min_ohlcv
14GROUP BY sym, z, minute_bucket_ms
15ORDER BY sym, z, minute_bucket_ms;

Run the complete example #

The example source now lives under blog-examples/stock-data-demo. Use Node.js 22 or later and an existing local or Cloud ClickHouse database.

1git clone https://github.com/ClickHouse/examples.git
2cd examples/blog-examples/stock-data-demo
3npm ci
4cp env.example .env
5# Set CLICKHOUSE_URL, CLICKHOUSE_USERNAME, CLICKHOUSE_PASSWORD,
6# CLICKHOUSE_DATABASE, and MASSIVE_API_KEY in .env.
7npm run setup
8npm run build
9npm start

Open http://localhost:34567/stocks/ for charts or http://localhost:34567/admin for ingestion controls. By default, the service subscribes to AAPL, MSFT, and NVDA. Change MASSIVE_SYMBOLS to choose other symbols; the dashboard watchlist does not alter upstream subscriptions. The server binds to loopback and its APIs have no authentication, so add access controls before exposing it publicly.

If your key lacks the required channels, the admin page reports authentication failure. Markets can also be quiet or closed on weekends and holidays. To try the dashboard without market access, use a separate ClickHouse database, leave the API key empty, and run npm run seed before npm start. This inserts explicitly synthetic prices timestamped over the last three minutes; it does not fetch real market data. Rerun the seed when those timestamps move outside the chart window.

The README covers local ClickHouse setup, development mode, configuration, and verification.

Conclusion #

In this post, we explored how to build a real-time tick data application using Massive for market data and ClickHouse for fast ingestion and querying. We covered how to stream and structure tick data, manage ingestion performance, and build efficient queries and visualizations.

In this GitHub repository, you will find a working example of this using React for the visualization layer. Use it to explore ingestion and query patterns, then add durable delivery, replay, authentication, and workload-specific tuning for a production application.