PublishedEngineering

ClickHouse Schema Design for Blockchain Data

Blockchain tables are wide, append-heavy and queried by block range. MergeTree with a (block_number, tx_index) order fits; JSON columns do not.

Context

The first traces schema stored the call tree as a JSON string. Queries that needed a from/to pair scanned the whole column.

Method

Raw tables are flattened. Low-cardinality strings for protocol and status. UInt256 stored as Decimal(76,0) until native wide integers are uniform across the cluster.

Findings

Partitioning by a 1,000-block range makes reorg deletes cheap. Ordering by (block_number, tx_index, log_index) makes decoder joins a merge rather than a hash.

Implications for the data model

If a field will be filtered, it is a column. If it will be displayed once, it can wait. Traces taught us that the expensive way around.