--- title: "Build a real-time market data app with ClickHouse and Massive" date: "2026-09-07T10:57:25.845Z" author: "Lionel Palacin" category: "Engineering" excerpt: "Learn how to build a real-time financial analytics application with Massive and ClickHouse that scales to thousands of events per second." --- # 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](https://en.wikipedia.org/wiki/Tick_size), 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](https://massive.com/) (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. | sym | bx | bp | bs | ax | ap | as | c | i | t | q | z | inserted_at | |-----|-----|--------|----|-----|--------|----|---|--------|-------------|----------|-------|----------------| | SPY | 12 | 602.73 | 2 | 11 | 602.74 | 6 | 1 | [1,93] | 1749583478206 | 63152225 | NYSE | 1749583479396 | 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. | sym | i | x | p | s | c | t | q | z | trfi | trft | inserted_at | |-----|----------------|----|--------|---|---------|--------------|-------|------|------|------|------------------| | SPY | 52983525034825 | 11 | 607.26 | 1 | [12,37] | 1750842255972 | 22126 | NYSE | 0 | 0 | 1750842257842 | 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](https://massive.com/). Choose a plan that explicitly includes both stock [trades](https://massive.com/docs/websocket/stocks/trades) and [quotes](https://massive.com/docs/websocket/stocks/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](https://en.wikipedia.org/wiki/WebSocket) 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](https://massive.com/) 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. ```javascript require("dotenv").config(); const WebSocket = require("ws"); if (!process.env.MASSIVE_API_KEY) throw new Error("Set MASSIVE_API_KEY in .env"); const symbols = (process.env.MASSIVE_SYMBOLS || "AAPL,MSFT,NVDA") .split(",").map((symbol) => symbol.trim()).filter(Boolean); const ws = new WebSocket("wss://socket.massive.com/stocks"); ws.on("open", () => { ws.send(JSON.stringify({ action: "auth", params: process.env.MASSIVE_API_KEY })); }); ws.on("message", (data) => { const payload = JSON.parse(data.toString()); for (const row of payload) { if (row.ev === "status") { console.log(row.status, row.message); if (row.status === "auth_success") { ws.send(JSON.stringify({ action: "subscribe", params: symbols.flatMap((symbol) => [`T.${symbol}`, `Q.${symbol}`]).join(","), })); } else if (row.status === "auth_failed") { ws.close(); } } else if (row.ev === "T" || row.ev === "Q") { console.log(row); } } }); ws.on("error", (error) => console.error(error.message)); process.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](https://massive.com/docs/websocket/stocks/trades) and another one for [quote](https://massive.com/docs/websocket/stocks/quotes), 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. ```sql CREATE TABLE IF NOT EXISTS quotes ( `sym` LowCardinality(String), `bx` UInt8, `bp` Float64, `bs` UInt64, `ax` UInt8, `ap` Float64, `as` UInt64, `c` UInt8, `i` Array(UInt32), `t` UInt64, `q` UInt64, `z` Enum8('NYSE' = 1, 'AMEX' = 2, 'Nasdaq' = 3), `inserted_at` UInt64 DEFAULT toUnixTimestamp64Milli(now64()) ) ENGINE = MergeTree ORDER BY (sym, t - (t % 60000)); CREATE TABLE IF NOT EXISTS trades ( `sym` LowCardinality(String), `i` String, `x` UInt8, `p` Float64, `s` UInt64, `ds` String DEFAULT '', `pt` UInt64 DEFAULT 0, `c` Array(UInt32), `t` UInt64, `q` UInt64, `z` Enum8('NYSE' = 1, 'AMEX' = 2, 'Nasdaq' = 3), `trfi` UInt64, `trft` UInt64, `inserted_at` UInt64 DEFAULT toUnixTimestamp64Milli(now64()) ) ENGINE = MergeTree ORDER 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](https://clickhouse.com/docs/best-practices/choosing-a-primary-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](https://kafka.apache.org/). 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](https://clickhouse.com/docs/best-practices/selecting-an-insert-strategy). ClickHouse supports both synchronous and asynchronous inserts. With synchronous ingestion, data is batched on the client side before being sent. The [batch size](https://clickhouse.com/docs/optimize/bulk-inserts) 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. ```javascript const { createClient } = require("@clickhouse/client"); class TickBatcher { constructor() { this.client = createClient({ url: process.env.CLICKHOUSE_URL || "http://localhost:8123", username: process.env.CLICKHOUSE_USERNAME || "default", password: process.env.CLICKHOUSE_PASSWORD || "", database: process.env.CLICKHOUSE_DATABASE || "default", compression: { request: true, response: true }, clickhouse_settings: { async_insert: 0 }, }); this.batches = { trades: [], quotes: [] }; this.flushing = { trades: false, quotes: false }; this.failedRecords = 0; this.droppedRecords = 0; this.timer = setInterval(() => { void this.flushBatch("trades"); void this.flushBatch("quotes"); }, 2000); } handleMessage(data) { const payload = JSON.parse(data.toString()); for (const { ev, ...fields } of payload) { const table = ev === "T" ? "trades" : ev === "Q" ? "quotes" : null; if (!table) continue; if (this.batches[table].length >= 10000) { this.droppedRecords++; continue; } this.batches[table].push(fields); if (this.batches[table].length >= 1000) void this.flushBatch(table); } } async flushBatch(table) { if (this.flushing[table]) return; this.flushing[table] = true; try { while (this.batches[table].length) { const values = this.batches[table].splice(0, 1000); try { await this.client.insert({ table, values, format: "JSONEachRow" }); } catch (error) { this.failedRecords += values.length; console.error(`Insert failed for ${table}:`, error.message); } } } finally { this.flushing[table] = false; } } async close() { clearInterval(this.timer); while (Object.values(this.flushing).some(Boolean)) { await new Promise((resolve) => setTimeout(resolve, 10)); } await Promise.all([this.flushBatch("trades"), this.flushBatch("quotes")]); await this.client.close(); } } ``` 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. ![tick-table.png](https://clickhouse.com/uploads/tick_table_f21d502995.png) To build this visualization, one query is enough, the data can be formatted using ClickHouse's powerful SQL query language and custom functions. ```sql with {syms: Array(String)} as symbols, toDate(now('America/New_York')) as curr_day, trades_info as ( select sym, argMax(p, tuple(t, q)) as last_price, round(((last_price - (argMin(p, tuple(t, q)))) / nullIf(argMin(p, tuple(t, q)), 0)) * 100, 2) as change_pct, sum(s) as total_volume, max(t) as latest_t from trades where toDate(fromUnixTimestamp64Milli(toInt64(t), 'America/New_York')) = curr_day and sym in symbols group by sym order by sym asc ), quotes_info as ( select sym, argMax(bp, tuple(t, q)) as bid, argMax(ap, tuple(t, q)) as ask, max(t) as latest_t from quotes where toDate(fromUnixTimestamp64Milli(toInt64(t), 'America/New_York')) = curr_day and sym in symbols group by sym order by sym asc ) select t.sym as ticker, t.last_price as last, q.bid as bid, q.ask as ask, t.change_pct as change, t.total_volume as volume, toUnixTimestamp64Milli(now64()) - toInt64(greatest(t.latest_t, q.latest_t)) as latency from trades_info as t 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. | ticker | last | bid | ask | change | volume | |--------|----------|-------|--------|--------|----------| | NVDA | 151.2099 | 151.2 | 151.21 | 2.17 | 65269276 | The second visualization we’re going to analyze is a candlestick visualization that shows the price evolution and volume for a given stock. ![tick-candlestick.png](https://clickhouse.com/uploads/tick_candlestick_d9e922fa60.png) Let’s have a look at the SQL query to power this visualization. ```sql select toUnixTimestamp64Milli(toDateTime64(toStartOfInterval(fromUnixTimestamp64Milli(toInt64(t)), interval 2 minute), 3)) as x, argMin(p, tuple(t, q)) as o, max(p) as h, min(p) as l, argMax(p, tuple(t, q)) as c, sum(s) as v from trades where x > toUnixTimestamp64Milli(now64() - interval 1 hour) and sym = {sym: String} group 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](https://github.com/ClickHouse/click-ui) components for the table display and [Chart.js](https://www.chartjs.org/) 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](https://clickhouse.com/docs/best-practices/selecting-an-insert-strategy#batch-inserts-if-synchronous) with insert sizes optimized for your system's memory and latency constraints. - Use [compression](https://clickhouse.com/docs/best-practices/selecting-an-insert-strategy#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](https://clickhouse.com/blog/monitoring-asynchronous-data-inserts-in-clickhouse#part-creations) talks about asynchronous insert, but the part creation section can be applied for a synchronous ingestion. You can also use [advanced dashboards](https://clickhouse.com/docs/operations/monitoring) to [monitor](https://clickhouse.com/blog/common-issues-you-can-solve-using-advanced-monitoring-dashboards#unbatched-inserts) the number of data parts. - [Massive](https://massive.com/) also provides [performance tips](https://massive.com/docs/websocket/quickstart#performance-&-latency-considerations) 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. ```sql SELECT sym, count() AS trade_count, argMax(toInt64(inserted_at) - toInt64(t), tuple(t, q)) AS ingest_latency_ms FROM trades WHERE t >= toUnixTimestamp64Milli(now64() - INTERVAL 1 HOUR) GROUP BY sym ORDER BY trade_count DESC LIMIT 100; ``` Visualizing this metric in a dashboard helps you catch slowdowns early. ### Take advantage of materialized views [Materialized views](https://clickhouse.com/docs/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](https://en.wikipedia.org/wiki/Open-high-low-close_chart) (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. ```sql CREATE TABLE trades_1min_ohlcv ( `sym` LowCardinality(String), `z` Enum8('NYSE' = 1, 'AMEX' = 2, 'Nasdaq' = 3), `minute_bucket_ms` UInt64, `open_price_state` AggregateFunction(argMin, Float64, Tuple(UInt64, UInt64)), `high_price_state` AggregateFunction(max, Float64), `low_price_state` AggregateFunction(min, Float64), `close_price_state` AggregateFunction(argMax, Float64, Tuple(UInt64, UInt64)), `volume_state` AggregateFunction(sum, UInt64), `trade_count_state` AggregateFunction(count) ) ENGINE = AggregatingMergeTree ORDER BY (sym, z, minute_bucket_ms); ``` The next step is to create the materialized view. ```sql CREATE MATERIALIZED VIEW trades_1min_ohlcv_mv TO trades_1min_ohlcv AS SELECT sym, z, intDiv(t, 60000) * 60000 AS minute_bucket_ms, argMinState(p, tuple(t, q)) AS open_price_state, maxState(p) AS high_price_state, minState(p) AS low_price_state, argMaxState(p, tuple(t, q)) AS close_price_state, sumState(s) AS volume_state, countState() AS trade_count_state FROM trades GROUP 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. ```sql -- Query the table SELECT sym, z, minute_bucket_ms, fromUnixTimestamp64Milli(toInt64(minute_bucket_ms)) as minute_timestamp, argMinMerge(open_price_state) AS open_price, maxMerge(high_price_state) AS high_price, minMerge(low_price_state) AS low_price, argMaxMerge(close_price_state) AS close_price, sumMerge(volume_state) AS volume, countMerge(trade_count_state) AS trade_count FROM trades_1min_ohlcv GROUP BY sym, z, minute_bucket_ms ORDER BY sym, z, minute_bucket_ms; ``` ## Run the complete example The [example source](https://github.com/ClickHouse/examples/tree/main/blog-examples/stock-data-demo) now lives under `blog-examples/stock-data-demo`. Use Node.js 22 or later and an existing local or Cloud ClickHouse database. ```bash git clone https://github.com/ClickHouse/examples.git cd examples/blog-examples/stock-data-demo npm ci cp env.example .env # Set CLICKHOUSE_URL, CLICKHOUSE_USERNAME, CLICKHOUSE_PASSWORD, # CLICKHOUSE_DATABASE, and MASSIVE_API_KEY in .env. npm run setup npm run build npm 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](https://github.com/ClickHouse/examples/blob/main/blog-examples/stock-data-demo/README.md) 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](https://github.com/ClickHouse/examples/tree/main/blog-examples/stock-data-demo), 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.