← All writing

Written October 2026, looking back at Systems18 min read

ClickHouse or RocksDB?

When our trade and candle history outgrew TimescaleDB, ClickHouse looked like the obvious next step. I argued the case for RocksDB anyway, to make sure "obvious" meant "right". Here's how the comparison came out.

From Print.World · Aug 2025 – Present

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?

AppendWrites are inserts in time order, almost never updates
RangeReads are time ranges: a chart, a history, a window
AggregateMany reads roll up: PnL, volume, holders, top traders
SharedMany services read it, not one process
  • 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.

Sizes matter more than adjectives here, so these are the numbers the design was sized against, read from TimescaleDB's own chunk metadata rather than a count(*) that would have scanned the table:

2.32BSolana trade rows, history back to December 2024
1M+Trade rows per hour at peak
3.1 TBThat one table in Postgres, data plus indexes
2.1 TBOf which B-tree indexes

Two things in those numbers drove the design. First, two thirds of the footprint was indexes, not data. Second, a single busy day was around 27 million rows, so anything that touches "a day" of trades is a real scan, not a lookup.

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

The clearest symptom was product-visible. About fifteen trade read paths carried timestamp > NOW() - INTERVAL '90 days'. Nothing was ever deleted; the bound was a cost cap, because an all-time read for one token over 2.32 billion rows and 2 TB of B-tree indexes was too expensive to allow. The user-facing result was that an old token's trade history looked empty. Removing that bound became a primary goal of the migration, not a side effect of it.

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.

How each engine writesRocksDB optimizes for fast keyed writes and lookups. ClickHouse optimizes for scanning a few columns across a huge number of rows.

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. At a peak of over a million trades an hour, a second full keyspace is a second million key writes an hour, each of which leveled compaction then rewrites again as it moves down the tree.

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.

RocksDBClickHouse
Ingest throughputExcellentExcellent, in batches
Point lookup latencyExcellentAcceptable (reads at least one 8,192-row granule)
Range scans and aggregatesHand-written per questionSQL, vectorized, columnar
New access patternsNew keyspace, more writesUsually a new query or view
Shared by many servicesBuild a service around itIt is the service
Ad-hoc debuggingOnly through the appSQL console
Updates and deletesCheap to write; tombstones slow scans until compactionExpensive, 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. The trades tables are ordered by (token, pool, timestamp, tx_hash, tx_index), with only the (token, pool, timestamp) prefix as the primary key. Leading with the token and then time means a chart query reads one contiguous, compressed range per data part.

That sparse index is where the 2 TB of B-tree indexes went. A rough size, with the inputs shown: the stage copy of trades_sol held 1.78 billion rows, which is about 217,000 granules (1.78B / 8,192). At roughly 100 bytes per index entry (two base58 addresses of about 44 characters each plus an 8-byte timestamp), that's on the order of 20 MB of primary index, small enough to live in memory, against terabytes of per-row B-tree entries in Postgres.

Columns also compress far better than rows, because a column holds one kind of value, sorted next to its neighbours. Timestamps use DoubleDelta before ZSTD, and low-variety strings like the DEX name use LowCardinality, which stores a dictionary index instead of the string. On a 472-million-row copy of real Solana trades the compression ratio was 3.24, and the per-row footprint lands far below Postgres:

Bytes on disk per trade rowDerived: on-disk size divided by row count. Postgres is the production table (1,010 GB of data plus 2,095 GB of indexes over 2.32B rows). ClickHouse is the stage table (218 GiB over 1.78B rows) and a 472M-row benchmark copy (54.6 GiB). Different row sets, so read it as an order of magnitude, not a precise ratio.
Show data
storeBytes per row
Postgres, data + indexes1,338
Postgres, data only435
ClickHouse, stage132
ClickHouse, benchmark copy124

So roughly 3x less than Postgres's heap alone, and about 10x less once the indexes are counted. Smaller data is also faster data: fewer bytes per granule means fewer bytes to read and decompress per query.

What it measured. On that 472M-row copy (an isolated 4 vCPU, 30 GiB ARM instance, warm cache), USDC, with about 4 million trades, is a good stress case for a "chart" read:

Token reads on 472M real trade rows (ms, warm)One isolated 4 vCPU instance, warm cache, single runs. The full-history read scans the token's ~3.9M rows; the last-hour read touches only the newest monthly partition.
Show data
qLatency
Count one token's trades5ms
Last hour, all tokens25ms
Last 50 trades of USDC39ms
Full USDC history66ms

The full-history read touched 503 of 57,836 granules (0.87%) and processed about 60 million rows a second on four cores (3.93M rows in 66ms). That's the vectorized part paying off: the engine runs each function over a block of thousands of values from one column at a time, instead of row by row.

The "last 50 trades" read is where the gain is smaller than it looks. It still read all 4.1 million of USDC's rows to return 50. That's consistent with the sort key: inside one token, rows are ordered by pool first and then time, so "newest across all pools" isn't a prefix of the sort order, and ClickHouse can't stop early. It's 39ms, which is fine, but it's a full read of the token, not a seek. If that query ever dominated, the fix would be a key that puts time before pool, at the cost of the per-pool chart reads.

Where the sparse index loses: small tokens. The index prunes granules within a part, but it can't skip a part. Token addresses are effectively random strings, so every part's range of tokens spans the whole keyspace, and the binary search in every part lands on the one granule where the token would be, whether or not it's there. On the stage table (about 45 parts at the time), a token with 64 trades living in one part read 41 granules, about 336,000 rows, to aggregate 64. A bloom_filter skip index on token fixes exactly that: it answers "is this token in this part at all?" and drops the parts that hold nothing.

Reading a small token: before and after part skipping (ms)15 short-lived tokens, 2 runs each, query cache off, on the stage table. The 'after' case simulates perfect part skipping with a _part predicate, so it is the upper bound of what the bloom filter can give. Rows read fell from 106,804 to 7,287 per query.
  • Every part
  • One part only
Show data
metricEvery partOne part only
CPU39ms12ms
Wall27ms12ms
p90 wall45ms13ms

That's 14.7x fewer rows read but only 3.2x less CPU, because a query has fixed costs (parsing, planning, opening each part's index) that don't shrink with the rows. CPU was the number that mattered: the stage box is 4 vCPU, so per-query CPU is what sets how many of these reads it can serve at once.

The wallet path. A token-first sort key does nothing for "this wallet, last 30 days", which becomes a full scan. That's the same problem I held against RocksDB, and the fix has the same shape: a second copy of the data ordered by (wallet, timestamp). The first draft used a projection, which ClickHouse maintains on every insert. I dropped it for two reasons. A full second ordering of 2.32 billion rows roughly doubles storage. And projections don't compose with FINAL, the query modifier that collapses not-yet-merged duplicates, so a deduplicated read would silently fall back to the base table.

What the read patterns actually needed was narrower. Wallet reads come from two populations: our own users, and wallets someone has chosen to track. Each gets a small mirror table ordered by (user_address, timestamp, ...), written by the same dual-write fan-out as the main table. The Postgres version of the users' mirror was about 73 MB, against 3.1 TB for the main table. Any other wallet is a rare, cold read, served from the main table through a bloom_filter on user_address. So the honest version of my RocksDB argument is: ClickHouse doesn't remove the "second copy" problem, it lets you pay for the second copy only for the rows that need it.

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.

Partitions across the full historyCandles: about 4.5 years of history. Daily partitions meant about 1,640 partitions; monthly brought it to about 55, growing by 12 a year.
Show data
schemePartitions
Daily partitions1,640
Monthly partitions55.00

Removing the 90-day bound made daily partitions actively hostile rather than just untidy. An all-time read for a token first seen eight months ago would open about 240 daily partitions to find a handful of rows.

The tempting alternative was to partition by token instead, say cityHash64(token) % 16, on the theory that a token read would then only open one partition. We tested that rather than argued it: three copies of the same 472M rows, identical in everything except PARTITION BY.

MonthlycityHash64(token) % 16First character of token
Granules read, one-token query503494477
Last 50 trades of a token39ms41ms43ms
Parts created per mixed insert batchabout 1about 16about 56
"Last hour" query25ms, 271k rows95ms, 1.24M rows150ms, 5.78M rows
Parts at rest30189358
Compression ratio3.243.243.25

Token partitioning saved nothing on token reads, because the sort key had already found the same granules. It cost on everything else. Every insert batch holds many tokens, so it opens a new part in every partition it touches: 16x or 56x the parts per insert. Fifty small batches produced 3,778 parts under the prefix scheme, and ClickHouse's default parts_to_throw_insert of 3,000 means sustained ingest at that rate eventually gets inserts rejected with "too many parts". And time-range reads lose locality, since the last hour is now scattered across every partition. This matches ClickHouse's own guidance: put the client identifier first in ORDER BY, not in PARTITION BY.

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. Decimal64(10) holds 18 significant digits with 10 after the point, which leaves a ceiling of about 10^8 and a resolution of 10^-10. Measured against the real 9.58M-row candle set, that clipped both ends: 201,931 bars overflowed (volume reached 4.16 × 10^17) and 6,536 bars of very small tokens rounded to zero (prices went down to 9 × 10^-18). The source columns in Postgres were already 8-byte doubles, so Float64 is not a loss of precision relative to the source, only a refusal to narrow its range. 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.

Deduplicate with a version, not a constraint. Postgres deduplicated trades with a primary key and ON CONFLICT DO NOTHING. ClickHouse has no unique constraint, so every table is a ReplacingMergeTree, which keeps one row per sort key, the one with the highest version column, when parts merge. The version is the trick that makes the migration safe: backfilled rows carry the event's own time as their version, and live dual-written rows carry the insert time. Wherever the two overlap on the same trade, the live row wins, and re-running a backfill reproduces identical versions that collapse cleanly. Replacement only happens at merge time, so reads that must not double-count (PnL, holder counts) either deduplicate at query time with argMax and GROUP BY or pay for FINAL. All versions of a trade share a timestamp and so a monthly partition, which lets FINAL work partition by partition instead of across the whole table.

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, and the ClickHouse write runs after the Postgres write succeeds and never throws into it, so a ClickHouse failure can't touch the existing path.
  2. Step 2Backfill in time windowsHistory is copied in bounded windows, so no single statement runs long enough to hit a timeout. The flag goes on first, so live rows are already landing; the backfill fills everything before that, and ReplacingMergeTree collapses the overlap.
  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. Each ClickHouse read has its own client-side timeout (8 seconds by default, tunable live), so a slow read falls back instead of hanging the caller.
  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.

The backfill is a query, not a pipeline. ClickHouse can read Postgres directly through its postgresql() table function, so each backfill is one INSERT ... SELECT run on the ClickHouse box: no export, no intermediate files, no extra service. The reads are plain SELECTs, which under Postgres MVCC never block the live writers. Candles, at about 9.6 million rows, copied in a single streamed pass of roughly 40 seconds. Trades could not, and the reason is a good example of a mechanism you have to check rather than assume:

  • TimescaleDB enforces a 3-minute statement_timeout, and a full-table read has to decompress every compressed chunk, so one pass over 2.32 billion rows can't finish inside it.
  • ClickHouse does push a WHERE timestamp predicate down to Postgres, provided the filter sits directly on the raw column inside a subquery, with all casts in the outer select. TimescaleDB turns that predicate into chunk exclusion, so a one-day window read takes about 5 seconds instead of a whole-table scan.
  • Window size follows density. Older history is sparse enough for daily windows; a dense recent day of around 27 million rows would hit the timeout, so those periods run hourly.
  • Each finished window is recorded, a failed window is logged and skipped rather than aborting the run, and a re-run resumes where it stopped. Because of the version column, re-running a window is always safe.

One sizing rule came out of this: a ReplacingMergeTree backfill temporarily inflates disk by 2 to 3x, because duplicate rows sit in unmerged parts until background merges catch up. The disk has to be sized for the merged footprint plus that transient, and the backfill now checks free space and stops itself (resumably) before it can fill the volume.

What it costs to run, and where it stops scaling

The production ClickHouse box is a single 8 vCPU, 64 GiB instance (an r7g.2xlarge); per-query memory is capped at 12 GiB so one heavy query can't take the server down with it. A closed month of trades is about 150 GiB compressed, and the product promise is now full history, so retention is handled by tiering rather than deletion: every trades table carries TTL toStartOfMonth(timestamp) + INTERVAL 2 MONTH TO VOLUME 'cold', which moves each month to S3 once it's two months old. The tables and the SQL stay the same, and a 100 GiB local cache keeps recently read S3 data on disk. Moving four closed months took production from 81% to 30% of its 1 TB volume.

Tiering has its own cost, and it shows up in the tail. A move reads the local volume as fast as it can, and that competes with queries: uncapped, production p95 went from 27ms to 68ms and p99 from 44ms to 490ms. So S3 writes are capped at 60 MiB/s, which also paces the monthly move of about 150 GiB over roughly 45 minutes instead of a burst.

At 10x the volume, storage isn't the wall; CPU on a single node is. The 4 vCPU stage box already runs hot under per-token reads, which is why the bloom filter work was about CPU per query rather than rows. The next steps, in order, would be read replicas for the API's read paths, and then sharding across nodes by cityHash64(token). That's the same hash the partitioning test rejected, and the difference matters: partitioning by token on one node multiplies parts without saving reads, while sharding by token across nodes puts each token's contiguous range on one machine and divides both the data and the CPU.

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.