Why TraderLaunchpad Uses Both ClickHouse and Convex for Candle Data

When you open a trading chart — any chart, on any platform — the candlesticks tell you two fundamentally different stories at the same time. Every bar except the rightmost one is history. Settled. Immutable. The rightmost bar is alive. Its close price changes with every tick, its high and low shift as the market breathes, and its volume accumulates in real time.

That distinction is the entire reason TraderLaunchpad uses two databases instead of one.

The naive approach: one database for everything

The first version of TraderLaunchpad's candle storage was simple. Ingest per-tick data from the TradeLocker broker API, aggregate it into OHLCV bars, and store everything in one place. Serve the same rows to TradingView's charting library for both historical scrollback and the live updating bar.

It works until it does not.

Why ClickHouse alone is not enough

ClickHouse is spectacular at what it does. We run it on a private droplet with a MergeTree engine, partitioned by month, storing finalized 1-minute candles across seven resolution tables — candles_1m, candles_5m, candles_15m, candles_30m, candles_1h, candles_4h, candles_1d. A query like "give me the last 1,500 fifteen-minute bars for EURUSD" returns in single-digit milliseconds. For historical data, it is hard to beat.

But ClickHouse is an analytical database, not an operational one. It is built for batch inserts and fast reads over large ranges, not for updating a single row many times per second. The current in-progress candle needs to update on every tick — potentially dozens of times per second during volatile markets. Doing that in ClickHouse means one of:

1. Insert-on-every-tick: You insert a new row per tick and aggregate at read time. This works technically, but for a platform serving hundreds of instruments across multiple users, you are generating millions of rows per day of raw tick data just to power one updating bar per chart. The read-time aggregation adds latency exactly where latency matters most — on the live bar.

2. Upsert the current bar: ClickHouse's ReplacingMergeTree can handle this, but the merge is asynchronous. You write the updated bar, but reads might still return the old version until the merge runs. For a user staring at a chart, seeing a price jump backward because a merge has not completed yet is worse than not updating at all.

3. Poll the broker directly: Skip the database for the live bar and poll the broker API from the frontend. This couples every chart to a direct broker connection, breaks multi-tenant isolation, and means the frontend needs broker credentials — a non-starter.

None of these are acceptable for a product where users make trading decisions based on what the chart shows right now.

Why Convex alone is not enough

Convex is a real-time document database. When a document changes, every client subscribed to a query that reads that document gets the update instantly through a WebSocket. For the live candle problem, this is perfect: write the latest OHLCV values to a document, and every chart subscribed to that instrument sees the bar update in real time, no polling.

But Convex is not a time-series engine. It does not have columnar storage, predicate pushdown, or the ability to scan millions of rows in milliseconds. A typical trading chart loads 1,500 bars of history when you open it, and users scroll back through months or years of data. Storing all of that in Convex means:

1. Document bloat: Even compressing OHLCV to the minimum fields, a year of 1-minute candles for one instrument is over 500,000 rows. Across hundreds of instruments, this is hundreds of millions of documents.

2. No time-range indexing optimized for analytics: Convex indexes are B-tree-style, great for point lookups and small range scans. Scanning 10,000 bars in timestamp order is not what they are optimized for. ClickHouse does this in microseconds because that is literally what columnar storage was invented for.

3. Cost at scale: Document databases charge per read and per document stored. Analytical queries that scan large ranges are where columnar databases offer orders-of-magnitude cost advantages.

4. Array limits: Convex arrays max out at 8,192 elements. A single chart load of 1,500 bars is fine, but backfilling history for a new instrument — which can mean fetching 100,000+ bars from the broker — requires slicing into chunks and multiple round-trips. ClickHouse accepts a bulk insert of any size in a single HTTP request using JSONEachRow format.

The hybrid: finalized bars in ClickHouse, live bar in Convex

The architecture we settled on maps directly to how candle data actually works:

ClickHouse owns finalized history. Once a candle's time window closes — once that minute, hour, or day is over — the OHLCV values are immutable. They will never change. This is exactly the kind of data ClickHouse excels at: write once, query often, scan fast.

Convex owns the current in-progress bar. The priceLiveCandles table holds exactly one document per instrument per resolution. As ticks come in, we upsert this document. Every chart subscribed to getLiveCandle for that instrument gets the update through the reactive WebSocket — no polling, no interval timers.

// Convex: the live candle — one row per instrument, upserted on every tick
priceLiveCandles: defineTable({
  sourceKey: v.string(),
  tradableInstrumentId: v.string(),
  resolution: v.string(),
  minuteStartMs: v.number(),
  lastUpdateAt: v.number(),
  o: v.number(),
  h: v.number(),
  l: v.number(),
  c: v.number(),
  v: v.number(),
}).index("by_source_instrument_resolution", [
  "sourceKey", "tradableInstrumentId", "resolution",
])

The index is the key design decision. One document per source + instrument + resolution combination means the upsert is a single index lookup, a patch, and an instant reactive push to all subscribers. No scan, no aggregation, no merge delay.

How the data flows

Backfilling history

When a chart opens for an instrument that has never been viewed, TraderLaunchpad needs to seed ClickHouse with historical data from the broker. This runs as a Convex action:

1. Query ClickHouse for the latest timestamp we already have for this instrument
2. Fetch history from TradeLocker in time-sliced chunks (broker APIs rate-limit and paginate)
3. Bulk insert into ClickHouse using the insertCandles1mJsonEachRow action, which sends the data as a single HTTP POST with FORMAT JSONEachRow
4. ClickHouse deduplicates automatically via ReplacingMergeTree keyed on (sourceKey, tradableInstrumentId, ts)

This runs asynchronously. The user sees a loading state on the chart, then the bars appear. Subsequent visits hit the cached ClickHouse data directly — sub-millisecond.

Serving chart data

The TradingView charting library calls a datafeed with getBars(symbol, resolution, from, to). Our datafeed routes this to a Convex action that:

1. Calls ClickHouse with a parameterized SQL query scoped to the resolution table, time range, and instrument
2. Returns the OHLCV array directly to the frontend

SELECT
  toUnixTimestamp64Milli(ts) AS t,
  open AS o, high AS h, low AS l, close AS c, volume AS v
FROM candles_15m
WHERE sourceKey = {sourceKey:String}
  AND tradableInstrumentId = {tradableInstrumentId:String}
  AND ts >= fromUnixTimestamp64Milli({fromMs:Int64})
  AND ts <= fromUnixTimestamp64Milli({toMs:Int64})
ORDER BY ts DESC
LIMIT {limit:Int64}

Parameterized queries. No string interpolation. ClickHouse handles the type coercion.

Live updates

Separately, a Convex reactive query subscribes the chart component to the current live candle:

const liveCandle = useQuery(api.pricedata.getLiveCandle, {
  sourceKey: "tradelocker",
  tradableInstrumentId: instrumentId,
  resolution: "1m",
});

When new ticks arrive and the priceLiveCandles document is upserted, every chart subscribed to this query re-renders with the new OHLCV values. The TradingView library sees the bar update in its onRealtimeCallback, and the rightmost candle animates smoothly.

When the minute rolls over, two things happen:

1. The now-finalized bar is inserted into ClickHouse as part of the next ingestion cycle
2. A new live candle document is created in Convex for the new minute

The transition is seamless from the user's perspective. The bar that was updating in real time becomes part of the scrollable history.

Why the boundary matters

The split between "finalized" and "in-progress" is not an implementation detail. It reflects a genuine difference in the nature of the data:

Property	Finalized bars	Live bar
Mutability	Immutable	Constant updates
Access pattern	Range scans, aggregation	Point lookup by instrument
Latency requirement	Milliseconds acceptable	Sub-second critical
Volume per instrument	Thousands to millions	Exactly one
Update frequency	Write once	Dozens of times per second
Query pattern	"Last 1500 bars"	"Current bar for EURUSD"

Trying to serve both patterns from one database means compromising on one or the other. ClickHouse compromises on update latency. Convex compromises on scan performance. The hybrid compromises on nothing at the cost of maintaining two systems — which, in practice, is barely more complex than maintaining one.

The Convex-to-ClickHouse bridge

The glue between the two systems is a small but critical piece: Convex actions that make HTTP calls to ClickHouse. We built a launchthat-plugin-clickhouse package that exposes clickhouseSelect and clickhouseExec helpers. These handle:

• Parameterized query building with typed params
• HTTP POST to the ClickHouse HTTP interface
• Error handling and timeout configuration
• Response parsing from JSONEachRow format

ClickHouse runs on a private droplet — not exposed to the public internet. Convex actions run server-side, so they can reach the private network. The frontend never talks to ClickHouse directly. Every query goes through a Convex action, which means every query is authenticated, rate-limited, and scoped to the user's authorized instruments.

This is important. In a multi-tenant trading platform, you cannot let one user's chart query leak data from another user's broker connection. The Convex action layer enforces tenant isolation before any SQL touches ClickHouse.

What about other approaches?

TimescaleDB (Postgres extension)

TimescaleDB handles both time-series queries and single-row updates reasonably well. We considered it. The issue is that TimescaleDB is still Postgres under the hood — row-oriented storage with B-tree indexes. For the analytical query patterns we need (scanning large ranges, computing aggregates across resolutions), columnar storage wins by a significant margin. And we did not want to run Postgres when the entire operational layer was already on Convex.

Redis for the live bar

Using Redis as a pub/sub layer for live candle updates is common in trading platforms. But it introduces another moving part with its own persistence story, and we already had Convex's reactive queries — which are pub/sub with automatic persistence, type safety, and zero additional infrastructure. Adding Redis would have been solving a problem we had already solved.

ClickHouse with materialized views

ClickHouse materialized views can maintain a "latest bar" view that updates on insert. This gets closer to the hybrid model but still has the merge-delay problem for real-time reads. And it does not give you reactive push — you still need to poll or add a separate pub/sub layer.

The numbers

In production, the hybrid architecture handles:

• Seven resolution tables in ClickHouse (1m through 1d), with MergeTree engine partitioned by month
• Sub-5ms read latency for historical bar queries up to 10,000 rows
• Sub-100ms reactive updates from tick arrival to chart re-render via Convex
• Single HTTP POST bulk inserts for backfill operations (no row-by-row overhead)
• One Convex document per instrument for live state — the entire live candle cache for hundreds of instruments fits in a few hundred documents

The ClickHouse instance runs on a modest droplet. Convex scales automatically. The most expensive part of the system is the broker API rate limits, not the databases.

Lessons

Three things I would tell someone building a similar system:

1. Match the database to the data lifecycle, not the data shape. OHLCV bars look the same whether they are finalized or in-progress. The schema is identical. But their access patterns, update frequencies, and latency requirements are completely different. Same shape, different database.

2. Let your real-time layer handle real-time, and let your analytical layer handle analytics. The temptation to put everything in one database is strong because it is simpler to reason about. But the simplicity is an illusion — you end up building workarounds for the things your chosen database does not do well, and those workarounds are more complex than running two purpose-built systems.

3. The bridge between systems should be thin and boring. Our ClickHouse integration is a handful of functions that make HTTP calls with parameterized SQL. No ORM, no query builder, no abstraction layer. The SQL is visible in the code. When something goes wrong, you can copy the query into the ClickHouse client and run it directly. Boring infrastructure is reliable infrastructure.

The hybrid model is not the simplest possible architecture. But for a trading platform where users make financial decisions based on what the chart shows them, the data has to be both historically deep and instantaneously current. No single database does both well. Two purpose-built databases, with a clear boundary between them, do.