T25 · Blockchain Data Infrastructure

A Real-World Problem

Your indexer has run reliably since T17: no missing blocks, rollback support, and repeatable runs. The application database holds tens of millions of transfer events.

Operations asks its first question: “How many active addresses and how much fee revenue did our protocol have in each of the past twelve months?”

You write a SQL query and press Enter. Forty minutes later it finishes, just as production alerts fire: the query saturated database I/O and frontend requests began timing out.

You add indexes; the second run is faster. Then comes “Can you separate new and returning users?” Another half hour.

The third request is “Can we see twenty-four months?”

The database has only the most recent six months. Disk grew too fast, so three months ago you scheduled deletion of old data.

All three questions seem so routine that operations expects a couple of clicks. Your system answers none well.

The indexer is not poorly written; it does its own job well. A system optimized to retrieve one address's current balance and one optimized to scan a billion rows for an aggregate differ in storage format and rebuild strategy.

This chapter builds the second.

Thought Experiment

Start with a paper ledger.

Each page holds one transaction with thirty fields: date, counterparty, amount, purpose, handler, invoice number, department, project code, and so on.

Question one: “How much was Zhang San's payment yesterday?” Find the page, read it, and answer. One-page access is cheap.

Question two: “Total monthly spending last year.” You must turn every page for a year. Only date and amount matter, but pages bind the fields together. You cannot turn just those two columns; all thirty fields pass through your hands.

Now rebind the ledger: copy every date onto one roll, every amount onto another, one roll per field. For question two, read only two rolls rather than thirty. Values of the same type compress well, so even moving the data gets cheaper.

Question one becomes harder: to reconstruct Zhang San's complete record, pull one entry from each of thirty rolls.

That is row versus column storage. Neither is universally better; each fits a different question.

But a second thought experiment is harder.

The ledger is not written all at once. It grows every day, and occasionally you must replace all entries for a day last month because of a chain reorg, a misparsed field, or an upgrade changing an event's meaning.

The problem is no longer merely fast lookup:

How can you rebuild one day so that its result exactly matches rebuilding the entire history from day one?

Only then can a warehouse support reconciliation, reporting, and settlement. Otherwise it is just a faster cache.

Your Decision

You must answer operations' three questions. Choose an approach.

What Happens

Compare the choices:

The fourth column dominates during an incident, outweighing the others together.

People ask only two fundamental questions of analytics:

How was this number calculated?

If I calculate it again, do I get the same number?

The first is the metric definition; the second is replayability. Neither is primarily a query problem. Both are ETL problems.

That is the surprise: SQL itself can be learned quickly. The difficult work is explaining each row's origin, rebuilding partitions independently, and obtaining the same result each time.

You have already solved half of it. T17's checkpoints, idempotency, range table, and rollback reappear here in another form.

Build a Model

Three layers: Raw, Decoded, Curated

Do not combine “fetch data from the chain” and “compute business metrics” in one step. Use at least three layers:

The last column explains layering. Pay the costliest step only once.

Common corrections include “we parsed a field wrong” and “an upgrade changed an event's meaning.” If RPC feeds metrics directly, either correction requires refetching chain history, perhaps for days and subject to provider limits. With original Raw bytes retained, you rerun decoding locally in minutes.

Raw is insurance against the certainty that you will someday discover a decoding mistake. Its storage cost is small beside a full refetch.

Partitioning: the choice that shapes everything

Partitions physically divide data into files so queries read only relevant pieces, a feature called partition pruning.

Blockchain data offers two natural keys: block height and time. Which should you use?

Both, with different roles:

Physical partition key  →  date derived from block time (UTC)
Sort within partition   →  block height, then log_index

Business questions are by time (“last month,” “Q3”), so dates make pruning useful. Writes, deduplication, and rebuilds use block heights, so ordering by height lets the engine skip ranges and gives each partition an exact recorded height span.

A directory layout:

warehouse/
  raw_logs/
    dt=2024-03-01/part-0000.parquet
    dt=2024-03-02/part-0000.parquet
  decoded_transfers/
    dt=2024-03-01/part-0000.parquet
  curated_daily_metrics/
    dt=2024-03-01/part-0000.parquet

Two easy mistakes:

First, derive the partition key from block time, never now(). An ingestion-time key puts the same chain data into different partitions today and tomorrow, breaking replay. T16 stated the rule for fact tables: time comes from the block header, not your server.

Second, do not partition too finely. A daily file of tens or hundreds of megabytes is comfortable. Hourly partitioning can create tens of thousands of tiny files whose metadata cost erases the columnar benefit: the small-file problem.

Idempotency: a partition is the atomic write unit

T17 used database transactions and keys. Filesystems lack database transactions but offer a workable substitute: atomic rename.

1. Write the entire partition into a temporary directory
   warehouse/decoded_transfers/_tmp_2024-03-01_run42/
2. After writing and validation, atomically rename it to the published path
   → warehouse/decoded_transfers/dt=2024-03-01/
3. Update the manifest: height span, row count, checksum, decoder version

The rule from T17 appears in a new form:

A partition either exists completely or does not exist at all. Never publish half a partition.

A crash during step 1 leaves a temporary directory for the next run to clear; the published path stays intact. A crash after step 2 but before manifest update causes a rebuild. That is harmless because a repeat produces identical bytes.

Rename first, update the manifest second. Reverse them and you get “manifest says it exists, but files do not,” the same error as advancing T17's checkpoint before storing data.

Manifest: the pipeline's source of truth

Without a manifest, a warehouse is merely a pile of files and nobody can say whether a month is complete.

create table warehouse_manifest (
  dataset        text    not null,      -- raw_logs / decoded_transfers / ...
  chain_id       integer not null,
  dt             date    not null,      -- Partition key
  from_block     bigint  not null,      -- Height span actually covered
  to_block       bigint  not null,
  row_count      bigint  not null,
  checksum       text    not null,      -- Content fingerprint to compare reruns
  decoder_version text   not null,      -- Decoder code version; crucial for lineage
  status         text    not null,      -- pending / running / done / failed
  attempts       integer not null default 0,
  built_at       timestamptz not null default now(),
  primary key (dataset, chain_id, dt)
);

It answers four questions nobody asks in calm periods and everyone asks during an incident:

-- 1. Are any dates missing?
select dt from generate_series($from::date, $to::date, '1 day') g(dt)
 where not exists (
   select 1 from warehouse_manifest m
    where m.dataset = 'decoded_transfers' and m.chain_id = $chain
      and m.dt = g.dt and m.status = 'done'
 );

-- 2. Are height spans continuous between adjacent dates?
select a.dt, a.to_block, b.dt, b.from_block
  from warehouse_manifest a
  join warehouse_manifest b
    on b.dataset = a.dataset and b.chain_id = a.chain_id
   and b.dt = a.dt + 1
 where b.from_block <> a.to_block + 1;

-- 3. Which partitions use an old decoder version and need rebuilding?
select dt from warehouse_manifest
 where dataset = 'decoded_transfers' and decoder_version <> $current;

-- 4. Did content change on replay? Compare checksums by partition

Query 1 is T17's range table in another form: “complete” must be answerable with SQL, not inferred from a progress bar. Query 3 realizes the value of layers.

Reorgs: the warehouse should lag

Columnar files are immutable. How do you handle the chain-head reorgs from T17?

Keep the chain head out of the warehouse.

Application database (OLTP) ← follows the head, serves real time, handles rollback
        │
        │ Move only rows with block_number <= safeHeight
        ↓
Data warehouse (OLAP)        ← trails by blocks; published files are immutable

safeHeight is the same safe boundary used by the T17 outbox. Lagging by minutes or hours is a design choice, not a flaw.

Reorg handling becomes simple: the warehouse contains no data that needs rollback. It cannot answer “what happened one second ago”; the application database and T18's real-time channel serve that question.

Two systems offer two freshness levels and two consistency guarantees. Do not force both into one system.

If you must rewrite a published partition, perhaps after finding a decoder error from three months ago, regenerate it, atomically replace it, update the manifest, and mark dependent downstream partitions pending. That is why manifest records decoder_version.

Curated layer: definitions belong next to code

At this layer, technical questions give way to definitions.

“Monthly active addresses” has at least six interpretations:

- Count only transaction initiators, or both sides of a transfer?
- Count failed transactions?
- Count contract addresses?
- For aggregator routes, count the aggregator or the end user?
- One person with five addresses: one or five?
- Use UTC or a local timezone?

Two answers to each generate dozens of legitimate metrics with the same label.

Keep a definition comment for every curated table in the same file as its SQL. A separate document will drift away from the code within months.

-- daily_active_addresses
-- Definition (finalized 2024-03):
--   1. Count logs from successful transactions only
--   2. Count and deduplicate both transfer from and to
--   3. Exclude the zero address used for mint and burn counterparties
--   4. Include known contract addresses, but flag them separately
--   5. Use UTC calendar days
create table curated_daily_active (
  chain_id   integer not null,
  dt         date    not null,
  contract   bytea   not null,
  addresses  bigint  not null,
  tx_count   bigint  not null,
  primary key (chain_id, dt, contract)
);

A useful observation: when two people report different numbers for one metric, the usual cause is different definitions, not bad arithmetic. Put the definition beside the SQL and a long argument becomes a short comparison.

A complete example

Aggregate monthly metrics from decoded data:

-- Monthly active addresses and fees over 12 months
-- Partition pruning uses dt, so only these months' files are scanned
with e as (
  select dt, from_addr, to_addr, amount, gas_used, gas_price
    from decoded_transfers
   where chain_id = 1
     and contract = $contract
     and dt >= date '2024-01-01'
     and dt <  date '2025-01-01'
),
addrs as (
  select date_trunc('month', dt) as m, from_addr as addr from e
  union all
  select date_trunc('month', dt) as m, to_addr   as addr from e
)
select m,
       count(distinct addr)                      as active_addresses,
       sum(gas_used * gas_price) / 1e18          as fees_native
  from addrs
  join e on date_trunc('month', e.dt) = addrs.m
 group by m
 order by m;

Watch where dt >= ... and dt < .... It determines whether the engine scans twelve months of files or the full archive, which creates the order-of-magnitude difference.

If you instead write where date_trunc('month', dt) = ... or wrap dt in any function, partition pruning may fail and the engine scans everything. Fast queries often depend on leaving the partition key bare in the filter.

What It Is Called

Hands-on

Check 1: Rebuild any one daily partition; its checksum stays the same
Check 2: No holes in the manifest; adjacent height spans are continuous
Check 3: Aggregates match direct calculations from raw logs row by row

raw_logs/dt=YYYY-MM-DD/part-0000.parquet

Three reruns, three checksums; each must exactly match its original

AI Lab

Step one:
Here is my decoded_transfers schema and partition layout (partition by dt,
sort by block_number within each). Write a query for monthly active addresses
and total fees over the last 12 months. Use partition pruning and state how
many partitions it should scan.

Step two:
List every metric-definition assumption you made. Write each as:
"I assumed X; if the actual rule is Y, the result changes in this way."
Give at least six.

Step three:
The contract actually upgraded last June. After the upgrade the same event
has an extra field and a different topic0. What happens to your query around
June, and how should it change?

Real-World Case

Change One Variable

Takeaway Questions

Chapter Check