How do I get blockchain data into ClickHouse?
Updated August 2026
Run a Substreams SQL sink against ClickHouse. Your module transforms and emits a Protobuf message per block — the sink takes care of writing it into your tables, backfilling history in parallel before continuing into live streaming.
ClickHouse is a natural fit for blockchain data: the workload is append-heavy, time-ordered, and analytical, which is exactly what it is built for.
Do I have to run the sink myself?
No. Hosted Sinks runs it for you — configure a package and your ClickHouse connection details on The Graph Market, and it indexes continuously with no infrastructure on your side. There is a documented walkthrough for ClickHouse Cloud.
It is in beta, and your ClickHouse instance must be reachable from the internet. Otherwise run the sink yourself as below — but you need a ClickHouse database of your own first, self-hosted or managed. Spinning one up is usually a few minutes; see the ClickHouse Cloud walkthrough for the fastest path even if you plan to run the sink yourself. The Substreams package is identical either way.
Why ClickHouse for onchain data?
Blockchain data has a particular shape — enormous volume, naturally ordered by block, queried with aggregations over time ranges. Column-oriented storage handles this far better than a row store.
The practical difference shows up in queries like daily volume by pool across two years, or per-wallet aggregates over the full history of a token. In Postgres these get slow and need careful indexing. In ClickHouse they are ordinary.
| Postgres | ClickHouse | |
|---|---|---|
| Best for | Application state, transactions | Analytics, aggregations, time-series |
| Updates | Cheap | Expensive — prefer append |
| Large aggregations | Needs tuning | Native |
| Typical use | Serving an app | Dashboards, research, reporting |
How should I design the schema?
Three decisions matter more than the rest.
Ordering key. Usually block number first, then whatever you filter on most. This drives compression and query performance more than anything else.
Table engine. MergeTree for pure append. ReplacingMergeTree when you need to correct rows — which you do, because of reorgs.
Materialised views. Pre-aggregate the rollups you query repeatedly rather than computing them each time. This is where most of ClickHouse's practical advantage comes from.
The substreams-sql agent skill covers ClickHouse-specific patterns including analytics-optimised schemas, materialised views, and time-series design — a faster route to a sensible schema than working it out from first principles.
How do reorgs work with an append-oriented store?
This is the wrinkle. ClickHouse does not want row-level updates, but reorgs require correcting data that was already written.
The usual approach is ReplacingMergeTree with a version column, so corrected rows supersede earlier ones on merge. For materialised views specifically, a delta pattern works well too: emit a signed count column — +1 on insert, -1 on removal or reorg — and aggregate with SummingMergeTree, so corrections become new rows that net out rather than in-place updates. Alternatively, delay writes by a confirmation depth — accepting some latency in exchange for writing only finalised data.
Which you choose depends on whether your dashboards need the current block or can tolerate being a few blocks behind. Most analytical workloads can.
What about very large backfills?
Historical processing runs in parallel, so the extraction side is fast. The constraint is usually ClickHouse ingest.
Tune batch sizes upward for backfill — much larger than the streaming default — and consider loading into a staging table before swapping into the table serving queries. substreams-sink-deploy documents flush tuning and the associated failure modes.
Frequently asked questions
Can I use ClickHouse Cloud? Yes. You need the connection details and write permissions; the sink does not care where it is hosted.
Should I use Postgres or ClickHouse? Postgres for application state you read and update transactionally. ClickHouse for analytics over large historical ranges. Plenty of teams run both.
Can I stream to ClickHouse and Postgres from the same module? Yes. The same module output can feed multiple sinks.
Get an API key at thegraph.market — no personal information required.
Related: How do I stream onchain data to a database? · How do I get blockchain data into Postgres? · What is blockchain ETL?