Pipeline

A ClickHouse schema for shred-decoded transactions

ClickHouse never updates a row in place, which suits data that changes status over time: write a newer version of the row and let the table engine keep the one that matters. This page builds that table for transactions decoded from shreds.

To store Solana transactions in ClickHouse while their outcome is still unknown, use a ReplacingMergeTree table ordered by signature, with a status rank as the version column. Every status change is a new insert. Background merges keep the row with the highest rank, and queries read with FINAL to see one row per transaction even before a merge.

Table design#

Column or settingChoiceWhy
ORDER BY signaturethe sort key, and so the deduplication keythe signature never changes between versions of a row; anything that can change must stay out of the sort key
status_rankthe version column of ReplacingMergeTree(status_rank)seen 0, dropped 1, failed 2, confirmed 2: a late duplicate seen can never beat an outcome, and a later check can still turn dropped into confirmed or failed
statusan Enum8 of seen, dropped, failed, confirmedreadable values, stored as one byte
slot, entry_index, tx_indexplain integer columnsneeded for queries, kept out of the sort key
updated_atinsert timewhen this version of the row was written
version, programs, fee_payer, raw_base64the decoded fields and the original bytesthe raw bytes let you decode again when your decoder improves
no PARTITION BYdeliberatemerges and FINAL only collapse rows inside one partition

The ranks follow the same forward-only rule as provisional rows: dropped sits below the two final outcomes so that a later check can still turn a dropped row into confirmed or failed, never the reverse. When a transaction counts as dropped is defined on the reconcile page.

Steps#

  1. Create the table once, with the signature as the only sort key and the status rank as the version.
  2. Insert decoded records in batches, straight from your decoder or from a Kafka topic. ClickHouse prefers few large inserts to many small ones.
  3. When the reconcile job settles a transaction, insert a new row for it with the new status and its higher rank. Never run an UPDATE.
  4. Read with FINAL whenever a result must count each transaction once.

clickhouse.ts#

The tested sink, unchanged. It runs on Node 24 with @clickhouse/client 1.25.0 against ClickHouse server 25.8.33.6.

clickhouse.ts
// ClickHouse sink. ReplacingMergeTree keeps one row per ORDER BY key
// (signature) after background merges, choosing the row with the highest
// version column. Using a status rank as the version means a late duplicate
// "seen" row can never overwrite a "confirmed" or "failed" row.
import { createClient } from "@clickhouse/client";
import type { Status, TxRecord } from "./records.ts";
export const ch = createClient({
url: process.env.CLICKHOUSE_URL ?? "http://127.0.0.1:18323",
username: process.env.CLICKHOUSE_USER ?? "sse",
password: process.env.CLICKHOUSE_PASSWORD ?? "sse-throwaway",
database: process.env.CLICKHOUSE_DB ?? "sse",
});
// dropped ranks above seen but below the final outcomes: a later check can
// still turn a "dropped" guess into confirmed or failed, never the reverse.
export const STATUS_RANK: Record<Status, number> = { seen: 0, dropped: 1, failed: 2, confirmed: 2 };
// No PARTITION BY on purpose: merges and FINAL only collapse rows inside one
// partition, so partitioning by anything that can differ between two copies
// of the same signature would leave duplicates behind.
export async function createTable(table = "solana_tx"): Promise<void> {
await ch.command({
query: `
CREATE TABLE IF NOT EXISTS ${table} (
signature String,
slot UInt64,
entry_index UInt32,
tx_index UInt32,
version LowCardinality(String),
fee_payer String,
programs Array(String),
status Enum8('seen' = 0, 'dropped' = 1, 'failed' = 2, 'confirmed' = 3),
status_rank UInt8,
raw_base64 String,
updated_at DateTime64(3) DEFAULT now64(3)
)
ENGINE = ReplacingMergeTree(status_rank)
ORDER BY signature`,
});
}
// One INSERT per batch: ClickHouse prefers few large inserts over many small ones.
export async function insertBatch(records: TxRecord[], table = "solana_tx"): Promise<void> {
await ch.insert({
table,
format: "JSONEachRow",
values: records.map((r) => ({ ...r, status_rank: STATUS_RANK[r.status] })),
});
}
// FINAL collapses duplicates at query time, so results are correct even
// before the background merge has run.
export async function countByStatus(table = "solana_tx"): Promise<Record<string, number>> {
const rs = await ch.query({
query: `SELECT status, count() AS n FROM ${table} FINAL GROUP BY status`,
format: "JSONEachRow",
});
const rows = await rs.json<{ status: string; n: string }>();
return Object.fromEntries(rows.map((r) => [r.status, Number(r.n)]));
}

Results from the fixture run#

The test inserts the decoded records of two real mainnet blocks twice, with every fifth record repeated inside each batch, then changes two statuses by inserting newer rows.

CheckResult on 18 November 2026
Rows counted without FINAL, before any merge1,562
Rows counted with FINAL781, one per transaction
One record moved to confirmed and one to failed by a new insertread back as confirmed and failed
A late seen row inserted after the confirmed oneignored: the transaction still reads as confirmed
Status counts with FINAL at the end779 seen, 1 confirmed, 1 failed

Duplicates inside a single insert block already collapsed on insert in this run; copies across separate inserts only collapse at merge time or under FINAL. The transactions are real; their entry_index values come from a locally built shred fixture, since RPC does not expose entries.

Partitioning and the sort key#

ReplacingMergeTree removes duplicates only among rows that share the whole sort key, and only inside one partition. Two consequences follow for shred data.

First, keep the sort key to values that never change between versions of a row. The signature qualifies. A slot can differ between two sightings of the same transaction if it shows up in a block that is later skipped and lands again elsewhere, so putting the slot in the sort key would keep both rows forever.

Second, think twice before partitioning by slot range or by day. If two versions of one transaction fall into different partitions, no merge will ever bring them together. The example uses no partitioning at all. If your table grows to the point where you need partitions, choose a scheme where every version of a signature always lands in the same one, and read with FINAL regardless.

The alternative to FINAL is argMax(status, status_rank) grouped by signature. Both return one status per transaction; measure both on your own data before choosing. The argMax form is not part of the tested example; only FINAL is.

Where this table design falls short#

ClickHouse is built for scans and aggregates, not for answering "what is the status of this one transaction" over and over for single rows, and FINAL adds work to every query. If your application mostly looks up single transactions and updates them, an operational database fits better; ClickHouse or Postgres weighs the two. And every row here is only as good as the reconcile job behind it: without it, the table holds transactions that were seen and never settled.

Next in the pipeline#

The Postgres version of the same sink uses a primary key and forward-only updates instead of versions. Statuses come from reconciling with RPC. For a query this table makes easy, see live DEX volume before confirmation, and the learn page on signatures as IDs explains why the signature can carry the whole sort key. Raw input comes from a SolanaShreds plan.

Sources#

Raw shreds on your own server

Ultra Low Latency Shreds is $200 and Standard Shreds is $100. You pay in crypto, tell us which IPv4 address and UDP port should receive the stream, and we wire it up by hand.