Canonical: https://solanashreds.xyz/docs/pipeline/clickhouse

# 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 setting | Choice | Why |
| --- | --- | --- |
| `ORDER BY signature` | the sort key, and so the deduplication key | the signature never changes between versions of a row; anything that can change must stay out of the sort key |
| `status_rank` | the 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 |
| `status` | an `Enum8` of seen, dropped, failed, confirmed | readable values, stored as one byte |
| `slot`, `entry_index`, `tx_index` | plain integer columns | needed for queries, kept out of the sort key |
| `updated_at` | insert time | when this version of the row was written |
| `version`, `programs`, `fee_payer`, `raw_base64` | the decoded fields and the original bytes | the raw bytes let you decode again when your decoder improves |
| no `PARTITION BY` | deliberate | merges and FINAL only collapse rows inside one partition |

The ranks follow the same forward-only rule as [provisional rows](https://solanashreds.xyz/blog/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](https://solanashreds.xyz/docs/pipeline/reconcile).

## 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](https://solanashreds.xyz/docs/pipeline/kafka). ClickHouse prefers few large inserts to many small ones.
3. When the [reconcile job](https://solanashreds.xyz/docs/pipeline/reconcile) 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.

```typescript file="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.

| Check | Result on 18 November 2026 |
| --- | --- |
| Rows counted without FINAL, before any merge | 1,562 |
| Rows counted with FINAL | 781, one per transaction |
| One record moved to confirmed and one to failed by a new insert | read back as confirmed and failed |
| A late `seen` row inserted after the confirmed one | ignored: the transaction still reads as confirmed |
| Status counts with FINAL at the end | 779 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](https://solanashreds.xyz/compare/clickhouse-vs-postgres-for-solana) 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](https://solanashreds.xyz/docs/pipeline/postgres) of the same sink uses a primary key and forward-only updates instead of versions. Statuses come from [reconciling with RPC](https://solanashreds.xyz/docs/pipeline/reconcile). For a query this table makes easy, see [live DEX volume before confirmation](https://solanashreds.xyz/blog/dex-volume-before-confirmation), and the [learn page on signatures as IDs](https://solanashreds.xyz/learn/transaction-signatures-as-ids) explains why the signature can carry the whole sort key. Raw input comes from a [SolanaShreds plan](https://solanashreds.xyz/pricing).
