How CryptoHouse Delivers Free Real‑Time Blockchain Analytics with ClickHouse and Goldsky

Demand for Blockchain Analytics

Blockchains process thousands of transactions per second, and understanding their state is crucial for investors and developers. SQL is a natural language for analysis, but it poses two challenges: converting blockchain entities to a row‑oriented format and finding a database that can handle high‑throughput, multi‑byte data while supporting ad‑hoc queries.

ClickHouse as the Standard for Blockchain Analytics

ClickHouse, an open‑source OLAP database with a columnar design and highly parallel execution engine, is well‑suited for storing blockchain data. Its ability to run fast queries over terabytes of data has led companies such as Goldsky and Nansen to adopt it as the core of their analytics services.

Building a Public Service

ClickHouse maintainers have a history of building public demos on large datasets, such as ClickPy for Python package downloads and adsb.exposed for flight data. Leveraging this experience, they created CryptoHouse to provide real‑time, free blockchain analysis, moving away from the typical asynchronous query model of existing services.

Integrating Goldsky

Goldsky supplies real‑time streaming of Solana (and other) blockchain events in a structured format that can be written directly to ClickHouse. After discussions with Goldsky’s CTO Jeff Ling, the team incorporated Goldsky’s Mirror data‑stream platform to ingest 3,000‑4,000 Solana transactions per second, feeding them through a pipeline that extracts blocks, transactions, token transfers, and account changes.

name: clickhouse-partnership-solana
sources:
  blocks:
    dataset_name: solana.edge_blocks
    type: dataset
    version: 1.0.0
transforms:
  blocks_transform:
    sql: >
      SELECT hash as block_hash, `timestamp` AS block_timestamp, height, leader, leader_reward, previous_block_hash, slot, transaction_count 
      FROM blocks 
    primary_key: block_timestamp, slot, block_hash
sinks:
  solana_blocks_sink:
    type: clickhouse
    table: blocks
    secret_name: CLICKHOUSE_PARTNERSHIP_SOLANA
    from: blocks_transform

Data‑Engineering Challenges

The pipeline converts each transaction into additional datasets (e.g., token transfers, account changes) and optimizes the schema for common queries. To handle the volume, the team uses a ReplacingMergeTree engine with a Null table and a Materialized View to transform JSON payloads into ClickHouse tuples, achieving ingestion speeds close to 500 k rows/second and edge‑node write rates of about 6 k rows/second.

CREATE TABLE solana.stage_tokens (
    `block_slot` Int64,
    `block_hash` String,
    `block_timestamp` DateTime64(6),
    `tx_signature` String,
    `retrieval_timestamp` DateTime64(6),
    `is_nft` Bool,
    `mint` String,
    `update_authority` String,
    `name` String,
    `symbol` String,
    `uri` String,
    `seller_fee_basis_points` Decimal(38,9),
    `creators` String,
    `primary_sale_happened` Bool,
    `is_mutable` Bool
) ENGINE = Null;

CREATE MATERIALIZED VIEW solana.stage_tokens_mv TO solana.tokens (
    `block_slot` Int64,
    `block_hash` String,
    `block_timestamp` DateTime64(6),
    `tx_signature` String,
    `retrieval_timestamp` DateTime64(6),
    `is_nft` Bool,
    `mint` String,
    `update_authority` String,
    `name` String,
    `symbol` String,
    `uri` String,
    `seller_fee_basis_points` Decimal(38,9),
    `creators` Array(Tuple(String, UInt8, Int64)),
    `primary_sale_happened` Bool,
    `is_mutable` Bool
) AS SELECT
    block_slot, block_hash, block_timestamp, tx_signature, retrieval_timestamp,
    is_nft, mint, update_authority, name, symbol, uri, seller_fee_basis_points,
    arrayMap(x -> (x.1, (x.2) = 1, x.3), CAST(creators, 'Array(Tuple(String, Int8, Int64))')) AS creators,
    primary_sale_happened, is_mutable
FROM solana.stage_tokens;

ClickHouse Challenges

Ensuring Fair Use

Even though the largest Solana table holds about 500 TiB of transaction data, the service must prevent any single query from exhausting memory or CPU. The team enforces quotas limiting scans to 10 billion rows, query runtime to 60 seconds, and a maximum of 60 queries per user per hour.

SELECT `table`,
       formatReadableSize(sum(data_compressed_bytes)) AS compressed_size,
       formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size,
       round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio
FROM system.parts
WHERE (database = 'solana') AND active
GROUP BY `table`
ORDER BY sum(data_compressed_bytes) DESC;

Accelerating Queries with Materialized Views

Heavy queries that scan billions of rows are accelerated by pre‑computing aggregates in Materialized Views. For example, a view that calculates daily fees aggregates data at insert time, allowing a query that would normally scan ~2 billion rows to finish in 2 seconds.

SELECT toStartOfDay(block_timestamp) AS day,
       avg(fee / 1e9) AS avg_fee_sol,
       sum(fee / 1e9) AS fee_sol
FROM solana.transactions_non_voting
WHERE block_timestamp > today() - INTERVAL 1 MONTH
GROUP BY day
ORDER BY day DESC;

The view can be queried instantly:

SELECT day,
       avgMerge(avg_fee_sol) AS avg,
       sumMerge(fee_sol) AS fee_sol
FROM solana.daily_fees_by_day
WHERE day > today() - INTERVAL 1 MONTH
GROUP BY day
ORDER BY day DESC;

Data Deduplication

Goldsky guarantees at‑least‑once delivery, which can produce duplicate events. CryptoHouse uses the ReplacingMergeTree engine, deduplicating rows that share the same block_timestamp and slot keys. The process runs asynchronously, and occasional temporary inaccuracies are acceptable given the low duplicate rate.

Using ClickHouse Cloud

The service runs on ClickHouse Cloud, separating storage and compute. This enables independent scaling of CPU and memory, unlimited object‑storage capacity, and cost‑effective operation. Query caching, added to the open‑source version earlier this year, further improves performance.

Building the User Interface

A simple UI lets users write, share, and visualize queries. Multi‑dimensional charts are powered by e‑charts. Queries are stored only in browser storage, not persisted on the server.

Query Tips

Use Materialized Views. They shift computation to insert time, reducing rows read at query time. Many views store intermediate aggregates using the AggregateFunction type, requiring -Merge functions in queries.

Apply date filters on the base tables. Materialized Views aggregate by day; for finer granularity (e.g., hourly), query the underlying tables, which contain billions of rows, with appropriate date filters to stay within quota limits.

If Users Need More…

CryptoHouse is designed for community use, not for commercial products that require large query volumes. Users needing higher quotas or dedicated performance can contact Goldsky for a private ClickHouse instance.

Conclusion

CryptoHouse is now publicly available, providing free, real‑time blockchain analytics for Solana and Ethereum. The blog covers technical details, and a developer‑focused session with Goldsky will be held at the September Solana Breakpoint conference.