Lab 2 — Read a Real Production Indexer — Tempo's tidx

Question

Tempo's tidx is a production-grade indexer built on Reth's ExEx (Execution Extension) API. Read the real source — see how production code handles reorgs, schema migrations, and backfill. Three thousand lines of Rust you can use as a template.

Principle (minimum model)

• ExEx = the Reth-native indexing API. A subscriber that receives Committed / ChainReorged / Reverted notifications as Reth syncs. Tightly integrated with the node's state.
• Three notification variants. Committed (new blocks) / ChainReorged (reorg happened, undo old + apply new) / Reverted (rollback, undo last N blocks). Production indexers must handle all three.
• tidx architecture. ExEx receiver → handler dispatch (per event type) → batched writes to Postgres → schema-migration manager. ~3K lines of Rust.
• Reorg handling pattern. On ChainReorged, undo the old chain's effects (delete rows matching old block numbers) → apply the new chain. Idempotent writes.
• Schema migrations. Tempo uses refinery for SQL migrations; the indexer runs migrations before starting subscription. Forward-only; never modify deployed migrations.
• Backfill mode. When starting fresh, replay history from a known block. ExEx exposes a backfill API; production indexers always have a backfill mode.
• What carries over. The Committed/ChainReorged/Reverted handling pattern is universal to any chain indexer. Even non-Postgres backends (ClickHouse / DuckDB) use the same handler dispatch.

Worked example + steps

Read a Real Production Indexer — Tempo's tidx

Etherscan and Dune are indexers. Their architectures are not public. tidx is — Tempo's production indexer for its EVM L1, open source, in active use. This lesson walks you through it. You'll see what they had to choose, what they got right, and what trade-offs are visible only by reading the source.

Open the repo. Read it. We'll go through it together.

📌 Why this is the right starting point. Every "build an indexer" tutorial pretends one database is enough. Real production hits a wall the moment two query shapes coexist: "show me the last 10 transfers from address X" (point lookup — PostgreSQL wins) and "show me daily transfer volume for the past year" (range scan — ClickHouse wins). tidx writes to both backends in parallel and routes queries to whichever fits. That dual-storage decision is the whole lesson.

Acceptance criteria

The lesson is complete when these tests pass (full code at the end in §Test gate):

1. replays_committed_then_reverted — apply N..N+5 then revert N+3..N+5; the derived state in PG matches the golden reference at every height.
2. idempotent_under_replay — feeding the same notification twice is a no-op (crash-recovery scenario).

Test-first reading. The walkthrough below explains the dual-sink architecture and Notification::ChainCommitted / ChainReverted shapes you'll need to construct fixture inputs for these tests.

The OLTP vs OLAP design tension, concretely

A row store like PostgreSQL keeps each transfer's columns adjacent on disk. To answer "last 10 transfers from 0xAlice," the planner uses an index on from, jumps to ~10 disk pages, returns. Microseconds.

A column store like ClickHouse keeps each column contiguous. To answer "daily volume for the past year," it scans the value and block_timestamp columns — only those two — across millions of rows, then aggregates. Also microseconds, because it never touched the other twelve columns of each row.

Ask each store the other's question and you die:

• "Daily volume for the past year" on PostgreSQL: read every row of every page that contains a Transfer in that range. Disk-bound. Tens of seconds to minutes on a real dataset.
• "Last 10 transfers from 0xAlice" on ClickHouse: ClickHouse has no point-lookup index; it scans. Wasted IO when the answer is 10 rows.

tidx's resolution: write to both, route on read. Same chain data lands in both engines; the HTTP API picks the engine based on the query (or an explicit ?engine= override).

Step 1: The Dual Sink — one chain reader, two writes

Open src/sync/sink.rs. The whole "write twice" abstraction is here in ~120 lines:

#[derive(Clone)]
pub struct SinkSet {
    pool: Pool,                 // PostgreSQL (always present)
    ch: Option<ClickHouseSink>, // ClickHouse (optional)
}

impl SinkSet {
    pub async fn write_all(
        &self,
        blocks: &[BlockRow], txs: &[TxRow],
        logs: &[LogRow], receipts: &[ReceiptRow],
    ) -> Result<()> {
        if let Some(ch) = &self.ch {
            tokio::try_join!(
                writer::write_batch(&self.pool, blocks, txs, logs, receipts),
                ch.write_blocks(blocks),
                ch.write_txs(txs),
                ch.write_logs(logs),
                ch.write_receipts(receipts),
            )?;
        } else {
            writer::write_batch(&self.pool, blocks, txs, logs, receipts).await?;
        }
        Ok(())
    }
}

Walk:

• tokio::try_join! — PG and CH writes are concurrent, not sequential. Wall-clock cost is max(pg, ch), not pg + ch. On a healthy node, both finish in single-digit ms.
• PG is a single transaction; CH is four direct inserts. PostgreSQL's write_batch puts all four tables (blocks/txs/logs/receipts) in one transaction so a crash leaves nothing partial. ClickHouse doesn't do multi-table transactions — it's append-only, so partial-batch on crash is the chunk-retry path's job (src/sync/ch_sink.rs, the CH_MAX_RETRIES loop).
• CH is Option-al. If you don't configure ClickHouse, tidx degrades cleanly to PG-only. The OLAP queries just stop being available.

(Answer: yes, briefly — try_join! returns when both succeed, but between the two completing, a reader hitting CH first will see stale state. tidx accepts this; the ClickHouse backfill cursor — ch_backfill_block in sync_state — exists precisely to repair these gaps after the fact.)

Step 2: The Sync Engine — one fetcher, fanned out

Open src/sync/engine.rs. The engine reads the chain once and hands the result to SinkSet::write_all. Read the struct fields:

pub struct SyncEngine {
    throttled_pool: ThrottledPool,
    sinks: SinkSet,             // ← fan-out lives here
    realtime_rpc: RpcClient,    // ← separate RPC client for tip-following
    backfill_rpc: RpcClient,    // ← separate RPC client for gap-filling
    chain_id: u64,
    ...
}

Two RPC clients, not one. Why? Realtime sync (following the chain head) has tight latency budgets; backfill (filling old gaps) is bandwidth-greedy. If they shared one connection-limited pool, a slow backfill would starve realtime and the node would visibly lag. Separate clients = separate concurrency budgets. The constants at the top of the file: REALTIME_RPC_CONCURRENCY = 4, BACKFILL_RPC_CONCURRENCY = 8.

🔍 Find in repo. Same file, find backfill_first and trust_rpc. Read 30 seconds each. In your own words: what does backfill_first change about node startup behavior, and what does trust_rpc opt out of?

Step 3: Schemas — same data, two shapes

This is where the OLTP/OLAP duality stops being an abstraction. Open both side by side:

• PostgreSQL: db/blocks.sql, txs.sql, logs.sql, receipts.sql
• ClickHouse: db/clickhouse/blocks.sql, txs.sql, logs.sql, receipts.sql

Same columns. Different table engines, different ordering keys, different indexes. PG uses btree indexes on (tx_hash), (block_num), (from), (to) — every column you'd point-lookup on. CH uses MergeTree-family engines sorted by (block_num, ...) — the natural physical layout for time-range scans.

The columns themselves are picked to be JOIN-free for the common questions. The txs table carries block_timestamp denormalized. So does logs. So does receipts. A relational purist would normalize that; a real production indexer learns it always needs a timestamp for any analytics query and pays the bytes.

🔍 Find in repo. Open both db/logs.sql and db/clickhouse/logs.sql. Identify one column or index that exists in one and not the other. In your own words: what query class does each version optimize for?

Step 4: Lazy event decoding — the killer design choice

Most indexers (Subgraph, Goldsky, OpenZeppelin Defender) demand you pre-register every event you want to index. You declare your ABI up front, the indexer creates a typed table per event, and only those events are decoded at write time.

tidx doesn't. Look at the logs schema — it stores raw bytes: selector BYTEA, topics BYTEA[], data BYTEA. No per-event tables. No pre-registration. Decoding happens at query time, by passing the ABI signature as a CTE generator.

Open src/query/parser.rs. EventSignature::parse takes a string like Transfer(address indexed from, address indexed to, uint256 value) and:

1. Parses ABI param types
2. Computes topic0 = keccak256("Transfer(address,address,uint256)")
3. Returns an EventSignature the router uses to synthesize a CTE that filters logs by selector = topic0 and projects decoded fields as named columns

The user's SQL then references the event name as a table:

tidx query \
  --signature "Transfer(address indexed from, address indexed to, uint256 value)" \
  "SELECT * FROM Transfer WHERE from = '\\xAlice...' LIMIT 10"

What this costs: storing every log on every contract, ever, even ones you'll never query. ~5–10× more raw bytes than a Subgraph-style "only index these contracts" approach.

What this buys: a question you didn't pre-register is answerable. A new token launches; you query it from minute one. A new event signature shows up; you decode it without backfilling. The trade-off is "spend disk to keep the question space open" — and disk is cheap.

Step 5: Query routing — engine selection

Open src/query/router.rs. The whole engine-selection contract:

pub enum QueryEngine {
    ClickHouse,  // OLAP
    Postgres,    // OLTP
}

Two engines. The HTTP API (/query) takes an optional ?engine= parameter; if omitted, the router picks. Examples from the README:

• SELECT * FROM blocks WHERE num = 12345 → point lookup → PG.
• SELECT type, COUNT(*) FROM txs GROUP BY type → aggregation → CH.

The honest version: the routing rules are heuristics, not magic. Production users override with ?engine=clickhouse when they know what they want. The engine separation is the architectural commitment; the auto-routing is convenience on top.

Step 6: Materialized views — pre-computed analytics on CH

Open src/api/views.rs. ClickHouse exposes "materialized views" — auto-updated aggregations that compute on insert, not on read. tidx ships an HTTP API to manage them:

curl -X POST "https://tidx.example.com/views" -d '{
  "chainId": 4217,
  "name": "top_holders",
  "sql": "SELECT token, holder, sum(balance) AS balance
          FROM token_balances GROUP BY token, holder HAVING balance > 0",
  "orderBy": ["token", "holder"]
}'

What CH does on POST /views: creates a target table analytics_4217.top_holders with an inferred schema, creates a materialized view top_holders_mv that auto-populates on inserts to the source, then backfills existing data from the source query. From then on, every new insert into the source incrementally updates the view. SELECT * FROM top_holders is now an indexed lookup, not a scan.

Note the authorization: POST and DELETE require connection from a trusted IP (configured via trusted_cidrs, typically Tailscale). The pattern: read APIs are public; write/admin APIs are CIDR-gated.

🔍 Find in repo. In views.rs, find require_admin_mutation. Note that it requires both a trusted IP and an x-tidx-admin: 1 header. Why both? (Defense in depth — IP can be spoofed inside a misconfigured network; the header is a cheap second check.)

Step 7: Sync architecture — Realtime + Gap Sync

The README's Sync Architecture section diagrams two concurrent loops:

• Realtime follows the chain head, maintains ~0 lag.
• Gap Sync detects discontinuities, fills them from most recent to earliest.

Gap sync is recent-first by design: users querying recent data should see it land first; old history backfills underneath. The sync_state table tracks four block numbers — head_num, tip_num, synced_num, backfill_num — and they each move under different conditions. Open db/sync_state.sql and read the column comments; the four-cursor design is what lets the two loops coexist without stepping on each other.

Step 8: From reading to writing — your own indexer

If you wanted to ship an indexer for MegaETH, a custom OP-stack rollup, or your own chain, what does adopting tidx look like? Two paths:

Adopt as-is. If your chain speaks Ethereum JSON-RPC, change one field — rpc_url — in config.toml and run tidx up. tidx's tables are chain-agnostic; chain_id is a column, not a schema decision.

Fork. If your chain has features tidx's schema doesn't model — Tempo-style fee payers, custom precompile traces, sponsored-tx fields — fork three things:

1. db/*.sql and db/clickhouse/*.sql — add columns for your chain's extras.
2. src/sync/decoder.rs — extract the extra fields from each block / tx / receipt.
3. src/types.rs — extend the *Row structs that flow from decoder to sinks.

What you almost never fork: the sync engine, the dual sink, the query router, the views API. Those are the architecture; everything else is data definition.

tidx vs Subgraph / Goldsky — the honest comparison

Neither is strictly better. tidx is the choice when you treat indexed chain data as your database (and the OLAP scans matter); managed services are the choice when "an API on top of chain data" is sufficient and ops capacity is the constraint.

Recall checklist

Before moving on, confirm without scrolling:

1. Name a query class PostgreSQL kills on; name a query class ClickHouse kills on.
2. Where in the codebase do PG and CH writes fan out from a single sync step? (File + function.)
3. Why does tidx use two separate RPC clients in the sync engine?
4. Why can tidx skip event pre-registration that Subgraph requires? (Where does ABI decoding happen?)
5. What does a materialized view buy you that an ad-hoc GROUP BY query doesn't?
6. If you forked tidx for a new chain's custom tx field, name the three files you'd touch.

If you stumbled on 3, 4, or 6, re-read Steps 2, 4, and 8 before the next lesson.

Drill

1. Map the dual sink. Open sink.rs. Trace what happens if writer::write_batch (PG) succeeds but ch.write_blocks fails inside try_join!. Does the PG transaction roll back? What does the next sync iteration do? (30 min)
2. Find the routing rule. Open src/query/router.rs and the surrounding mod.rs. Identify exactly how a query with no ?engine= parameter gets routed. Is it always PG? Always CH? Heuristic? Write the rule in one sentence. (45 min)
3. Add a column to both schemas. Pick one — say, a per-tx l1_origin field for an L2 use case. Add it to db/txs.sql, db/clickhouse/txs.sql, the TxRow struct in src/types.rs, and the decoder. cargo build it. (2 hours)
4. Define a materialized view. Pick a real analytics question (top 100 senders by tx count, daily-active-addresses, top-volume tokens). Write the POST /views body. Confirm the schema CH infers. (1 hour)
5. Run it against a public chain. tidx init, point rpc_url at a free Tempo or testnet endpoint, tidx up, watch tidx status --watch until realtime catches up. Query it. (1 hour)

Finish drill 5 and you have a running tidx instance indexing a real chain with both engines online.

Test gate

Per Test gate — every app in this tier ships with passing tests, this lesson's minimum gate is fixture-chain replay: feed a known sequence of Notification::ChainCommitted and Notification::ChainReverted into your indexer's processing function, then assert the derived state in PG (and CH if you wired it) matches a golden reference exactly.

The reorg case is non-negotiable: a sink-into-PG indexer that handles only ChainCommitted corrupts derived state on every reorg. Your gate must prove the ChainReverted path actually reverses the writes.

// tests/fixture_replay.rs
use reth_exex::ExExNotification;

#[tokio::test]
async fn replays_committed_then_reverted() {
    let pg = test_pg_pool().await;            // ephemeral schema per test
    let mut indexer = Indexer::new(pg.clone());

    // Apply blocks N..N+5
    for n in N..N+5 {
        indexer.handle(committed_fixture(n)).await.unwrap();
    }
    assert_eq!(pg.tx_count_at_block(N+4).await, GOLDEN_TX_COUNT);

    // Reorg over N+3..N+5
    indexer.handle(reverted_fixture(N+3..=N+5)).await.unwrap();
    assert_eq!(pg.tx_count_at_block(N+2).await, GOLDEN_TX_COUNT_AT_N2);
    assert_eq!(pg.tx_count_at_block(N+5).await, 0, "reorged blocks must vanish");
}

#[tokio::test]
async fn idempotent_under_replay() {
    // Same notification twice must be a no-op (crash-recovery scenario)
}

The fixture lives under tests/fixtures/ as a serialized ExExNotification (use reth's test helpers or capture from a real node once and pin). The lesson is not complete until both tests are green and CI runs them on every push.

📺 Further watching

GhEhzE9SFqY | Alexey Shekhirin — Using Reth Execution Extensions for next generation indexing (Devcon 2024)

🧭 Where you are now in the stack: you've shipped a database-layer application — OLTP + OLAP dual-storage design read off tidx's source, with the fixture-chain-replay test gate locking in reorg handling. The same shape Snowflake and ClickHouse + Postgres combinations solve, applied to chain data. Next lesson moves to the networking layer: a server-side RPC extension via extend_rpc_modules.

Summary (3 lines)

• tidx = Tempo's production indexer on Reth ExEx, ~3K lines of Rust. ExEx receiver → per-event handler dispatch → batched Postgres writes + refinery schema migrations.
• Three notification variants (Committed / ChainReorged / Reverted) — production indexers must handle all three. Idempotent writes; backfill mode is standard.
• The handler-dispatch pattern transfers to any chain indexer regardless of backend (Postgres / ClickHouse / DuckDB). Next: custom RPC endpoint.