ClickHouse or RocksDB?

At Print.World, every swap on every chain we support becomes a row: the trade itself, the candles it updates, the user's own trade history, the PnL and holder views built on top. For a long time all of that lived in TimescaleDB, Postgres with time-series extensions. This summer I moved candles and trades to ClickHouse.

ClickHouse was the obvious answer. I've learned to be suspicious of obvious answers, so before committing to the move I made myself argue the other side seriously: what if we'd gone with an embedded key-value store like RocksDB instead? Plenty of trading systems are built on exactly that. This post is that argument, and why ClickHouse wins for this workload.

#Start with the workload, not the database

The mistake I try hardest to avoid with storage decisions is starting from a technology. So first, what does this data actually do?

• Writes arrive continuously from our trade pipeline, in roughly time order. Rows are effectively immutable once written.
• Reads are almost always "this token, this interval, this time range" for charts, or "this wallet, last N days" for history.
• Aggregates matter as much as raw rows: PnL across a wallet's trades, holder counts, volume per interval, leaderboards.
• Consumers are many: the terminal's APIs, background jobs, dashboards, and the people debugging all of it.

#Why TimescaleDB was straining

TimescaleDB did a lot right for a long time. But underneath it's a row-oriented database, and our tables had grown into the territory where that shows:

• recent, uncompressed chunks are plain Postgres rows, so aggregates over them read whole rows; compressed chunks do store columns separately, but every query paid to decompress them and then processed rows one at a time, with no vectorized execution
• the compressed hypertables were large enough that any query without a tight time filter had to visit a lot of chunks
• the tables were big enough to make backfills, schema changes, and long-range queries something you planned around

None of these were emergencies. They were the kind of friction that tells you the shape of your data has outgrown the shape of your database.

#The case for RocksDB

I tried to make this the strongest version of the argument, not a strawman.

RocksDB is an embedded log-structured merge tree (LSM). Writes go to an in-memory table and a write-ahead log, get flushed to sorted files, and are compacted in the background. You get very high write throughput, and fast point lookups and prefix scans over sorted keys.

For candles, you could key by (token, interval, timestamp) and read a chart with one prefix scan. Ingest would be fast, latency per lookup would be excellent, and there's no network hop because it lives inside your process. It's a genuinely good fit for some of what we do.

#Where RocksDB loses, for us

The case falls apart when you follow the workload past the first query.

1. Every new question is new code. RocksDB has no query engine. A chart is a prefix scan, fine. But "this wallet's realized PnL over 30 days", "holders of this token", or "volume per hour across all tokens" each means writing, testing, and maintaining a scan-and-aggregate routine by hand. ClickHouse answers those with SQL, vectorized across columns.

2. Every access path is another copy of the data. Data sorted by token is fast to read by token. To read by wallet, you need a second keyspace sorted by wallet, written on every insert. Each new access pattern multiplies writes and the number of places the data can disagree.

3. Embedded means one process. RocksDB lives inside a single service. Our data is read by many services and by people. Making it shared means building a service around it: an API, replication, failover, backups. At that point you've started building a database.

4. Operations and debugging. With ClickHouse, anyone on the team can open a SQL console and ask the data a question during an incident. With a custom key layout, only the code that wrote it knows how to read it.

	RocksDB	ClickHouse
Ingest throughput	Excellent	Excellent, in batches
Point lookup latency	Excellent	Acceptable (reads at least one 8,192-row granule)
Range scans and aggregates	Hand-written per question	SQL, vectorized, columnar
New access patterns	New keyspace, more writes	Usually a new query or view
Shared by many services	Build a service around it	It is the service
Ad-hoc debugging	Only through the app	SQL console
Updates and deletes	Cheap to write; tombstones slow scans until compaction	Expensive, avoid

The one row that cuts the other way is updates and deletes, where ClickHouse is weak. That's acceptable precisely because this data is append-only. If it weren't, this would be a different post.

#Designing it so it actually wins

Choosing ClickHouse is the easy part. A few design choices decide whether it pays off.

Sort key is the access path. ClickHouse's MergeTree stores data sorted by its ORDER BY key, and that sort order decides which reads are cheap. Leading with the token and then time means a chart query reads one contiguous, compressed range.

That also means ClickHouse has the same problem I held against RocksDB: a token-first sort key does nothing for "this wallet, last 30 days", which becomes a full scan. The fix has the same shape, a second copy of the data ordered by (wallet, timestamp), as a projection or a table fed by a materialized view. The real difference is who keeps the copies consistent: ClickHouse maintains them on every insert, instead of application code writing two keyspaces and hoping they agree.

Partition granularity matters more than it looks. In ClickHouse, partitions are a data-management tool (dropping or expiring old months), not a query accelerator; the sort key does that job. Daily partitions over years of history mean thousands of partitions and many small parts to merge, and any single insert that spans more than 100 days hits max_partitions_per_insert_block. Monthly partitions keep both under control.

Show data

scheme	Partitions
Daily partitions	1,640
Monthly partitions	55.00

Pick numeric types for the extremes, not the median. Token amounts on-chain span an enormous range, with some volumes around 10^17 in base units. The decimal precision first chosen overflowed on those extremes. Wider types exist (UInt256, Decimal256), but they're slower to aggregate, so I stored chart-facing amounts as Float64. That gives about 16 significant digits: not exact at 10^17, and floating-point sums can differ slightly between runs. That's fine for charts, which is exactly why exact amounts stay in the transactional system of record.

Let ClickHouse do the rollups, carefully. Candles at larger intervals are aggregates of smaller ones, which is what materialized views are for, with two catches I designed around. A ClickHouse materialized view is an insert trigger: it runs on each inserted block and never sees rows that existed before it was created, so a backfill has to populate the rollup table explicitly. And correct candles need an AggregatingMergeTree with argMin/argMax states for open and close, because rows stay partially aggregated until background merges run, so reads must finish the aggregation with -Merge combinators.

#How I moved it without a big bang

I ran the migration in a pattern I'd reach for again for any storage change, because it never asks you to trust the new system before it has earned it:

1. Step 1Dual-write behind a flagEvery new row goes to both TimescaleDB and ClickHouse. Reads still come from TimescaleDB. The flag defaults off.
2. Step 2Backfill in time windowsHistory is copied in bounded windows, so no single statement runs long enough to hit a timeout.
3. Step 3Prove parityA monitor samples the same queries against both stores and compares. On staging, the candle backfill matched exactly across 9,588,676 bars.
4. Step 4Cut reads over behind a flagReads move to ClickHouse with an automatic fallback to TimescaleDB, and repeated failures raise an alert instead of failing over silently.
5. Step 5Retire the old pathOnly once reads have been stable is TimescaleDB removed from the read path for that table.

The order is the point: at every step, the old system is still the source of truth until the new one has proven itself on real data.

#Where I would use RocksDB

None of this makes RocksDB a bad choice. It's the wrong choice for shared, queryable history. For durable state inside a single hot-path process, like a local order book snapshot, a dedup set that has to survive restarts, or per-key state in a stream processor, it's the first thing I'd reach for. Fast keyed writes, local reads, no network hop.