Blockchain Indexes: How Onchain Indexing Pipelines Work and How to Build One

What a blockchain index actually is

A blockchain index is a secondary data store derived from a chain. The chain itself is an append-only ledger optimized for consensus and verification, not for search. It can return a block by number or a transaction by hash, but it cannot natively answer questions like "list every transfer to this address in the last 30 days" or "sum the fees paid by this contract this week." A blockchain index solves that by continuously reading blocks, decoding transactions, logs, and state changes, and writing normalized rows into a database with the right keys and secondary indexes.

The term is overloaded. In traditional finance, a blockchain index often means a benchmark basket such as a crypto price index. In engineering, it means the indexing pipeline and the database it produces. This article covers the engineering meaning. If you are looking for a market benchmark, that is a different product category and a different data source.

The core promise is latency and expressiveness: once data is in a relational or document store, you can filter, aggregate, join, and paginate with ordinary query tools instead of scanning the chain. The core cost is that the index is a copy, so it can lag, drift, or be wrong after a reorg unless you design for it.

• Source of truth: the chain, read through an RPC endpoint or a node you operate.
• Derived artifact: the index database, which is rebuildable but not authoritative.
• Contract: every indexed row should be traceable to a block number and block hash.

Assumptions and prerequisites

The procedure below assumes you have an RPC endpoint that supports the JSON-RPC methods your chain uses (for EVM chains, at minimum eth_getBlockByNumber, eth_getLogs, and eth_getBlockReceipts or equivalent), a database such as PostgreSQL, and a process supervisor or scheduler. You do not need a full archive node to start, but historical backfill beyond the node's pruning window requires archive access or a provider that serves historical state.

You also need to decide your consistency target before writing code. A dashboard that can tolerate a few minutes of lag has very different requirements from a balance service that must never show a stale value. Write that target down, because it determines confirmation depth, reorg handling, and whether you need idempotent upserts.

For endpoint selection and provider comparison, use the RPC Assistant rather than hardcoding a public URL. See the <a href="/en/api-service">API service overview</a> for how managed RPC access fits into an indexing architecture, and the <a href="/en/networks">supported networks list</a> to confirm the chain you are indexing is available.

The ETL mental model behind every indexer

Almost every blockchain indexer is an extract-transform-load pipeline with a cursor. Extract reads a block and its receipts or logs. Transform decodes ABI-encoded events, normalizes addresses to lowercase, converts token amounts using decimals, and derives entities such as transfers, swaps, or positions. Load writes rows and advances the cursor in the same transaction so the cursor never runs ahead of the data.

The cursor is the most important piece of state. It should record the last fully processed block number and hash, not just a number, because a hash lets you detect that the chain reorganized underneath you. A cursor that only stores a height will silently continue on a forked chain after a reorg and produce an index that looks healthy but is wrong.

Streaming and indexing are complementary. A stream gives you low-latency events for real-time reactions; an index gives you durable, queryable history. Many production systems run both: the stream triggers notifications, and the indexer remains the system of record for reads.

• Extract: block header, transactions, receipts, and logs for a height.
• Transform: decode, normalize, and derive domain entities.
• Load: upsert rows and advance the cursor atomically.
• Verify: compare index output against direct RPC reads at a known height.

Designing a reorg-safe schema

Reorgs are normal on most chains, especially at low confirmation depth. Your schema should make rollback cheap. The simplest robust pattern is to store block_number and block_hash on every derived row, keep a canonical_blocks table, and delete or mark rows whose block_hash no longer matches the canonical chain when you detect a mismatch.

A second pattern is a staging area: write new blocks into a pending table, promote them to canonical after N confirmations, and drop pending rows on reorg. This costs more storage but makes the canonical tables append-only and easier to reason about. Choose based on how expensive a wrong read is for your application.

Regardless of pattern, make writes idempotent. Use a unique key such as (tx_hash, log_index) for event rows and upsert on conflict. Idempotency lets you safely replay a range after a failure without duplicating data.

CREATE TABLE canonical_blocks (
  number BIGINT PRIMARY KEY,
  hash TEXT NOT NULL,
  parent_hash TEXT NOT NULL,
  indexed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE transfers (
  tx_hash TEXT NOT NULL,
  log_index INT NOT NULL,
  block_number BIGINT NOT NULL,
  block_hash TEXT NOT NULL,
  token TEXT NOT NULL,
  from_addr TEXT NOT NULL,
  to_addr TEXT NOT NULL,
  amount NUMERIC NOT NULL,
  PRIMARY KEY (tx_hash, log_index)
);

CREATE INDEX transfers_to_idx ON transfers (to_addr, block_number DESC);
CREATE INDEX transfers_from_idx ON transfers (from_addr, block_number DESC);

Running a minimal indexer against an RPC endpoint

The script below is a deliberately small EVM indexer loop. It fetches a block, pulls logs for that height, decodes ERC-20 Transfer events, upserts them, and advances the cursor in one transaction. Replace RPC_URL with your endpoint and adjust the start block. It is not production-hardened, but it demonstrates the cursor and idempotency pattern you should keep as you scale.

Run it with a database URL in the environment. The first run backfills from START_BLOCK; subsequent runs resume from the stored cursor. Because the upsert is keyed on (tx_hash, log_index), replaying a range is safe.

import os, json, psycopg2, requests
from web3 import Web3

RPC_URL = os.environ["RPC_URL"]
DB_URL = os.environ["DB_URL"]
START_BLOCK = int(os.environ.get("START_BLOCK", "0"))
TRANSFER_TOPIC = "0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef"

w3 = Web3(Web3.HTTPProvider(RPC_URL))
conn = psycopg2.connect(DB_URL)

def get_cursor(cur):
    cur.execute("SELECT number FROM canonical_blocks ORDER BY number DESC LIMIT 1")
    row = cur.fetchone()
    return row[0] if row else START_BLOCK - 1

def index_block(cur, number):
    block = w3.eth.get_block(number, full_transactions=False)
    logs = w3.eth.get_logs({"fromBlock": number, "toBlock": number, "topics": [TRANSFER_TOPIC]})
    cur.execute(
        "INSERT INTO canonical_blocks (number, hash, parent_hash) VALUES (%s,%s,%s) "
        "ON CONFLICT (number) DO UPDATE SET hash = EXCLUDED.hash",
        (block.number, block.hash.hex(), block.parentHash.hex()),
    )
    for log in logs:
        if len(log["topics"]) != 3:
            continue
        cur.execute(
            "INSERT INTO transfers (tx_hash, log_index, block_number, block_hash, token, from_addr, to_addr, amount) "
            "VALUES (%s,%s,%s,%s,%s,%s,%s,%s) ON CONFLICT (tx_hash, log_index) DO NOTHING",
            (log["transactionHash"].hex(), log["logIndex"], number, block.hash.hex(),
             log["address"].lower(),
             "0x" + log["topics"][1].hex()[-40:],
             "0x" + log["topics"][2].hex()[-40:],
             int.from_bytes(log["data"], "big")),
        )

with conn, conn.cursor() as cur:
    cursor = get_cursor(cur)
    head = w3.eth.block_number
    for n in range(cursor + 1, head + 1):
        index_block(cur, n)
        if n % 100 == 0:
            print(f"indexed through {n} of {head}")

Verifying the index against the chain

Never trust an indexer you have not reconciled. Pick a recent block height, query the chain directly for a known fact, and compare it to the index. A reproducible verification procedure is more valuable than any single spot check because it can be rerun after every schema or decoder change.

A practical method: choose three heights (one recent, one mid-history, one at your start block), fetch the block hash from RPC, and confirm the index stores the same hash. Then pick one address with activity at each height and compare the transfer count and summed amount from the index against a direct eth_getLogs scan for that address. Record the results in a table so regressions are visible.

The table below is a template. Fill in the measured values from your own run; do not copy numbers from another environment, because block heights, token decimals, and address activity differ per chain and per deployment.

• Row 1: block hash at height H from RPC vs. canonical_blocks.
• Row 2: transfer count for address A at height H from eth_getLogs vs. transfers table.
• Row 3: summed amount for address A, with decimals applied consistently.
• Row 4: cursor lag, measured as head minus last indexed block at query time.

| Check | Height | RPC value | Index value | Match |
|---|---|---|---|---|
| Block hash | H1 | <fill> | <fill> | <yes/no> |
| Transfer count for A | H1 | <fill> | <fill> | <yes/no> |
| Summed amount for A | H1 | <fill> | <fill> | <yes/no> |
| Cursor lag (blocks) | head | n/a | <fill> | n/a |

Troubleshooting drift, decode failures, and lag

The three most common failure modes are cursor drift after a reorg, silent decode failures, and unbounded lag. Drift shows up as a block hash mismatch between the index and RPC at the same height. Detect it by comparing the stored parent_hash chain on every new block; if the new block's parent does not match your stored hash, roll back to the last common ancestor and reindex forward.

Decode failures are usually silent because a malformed log is skipped rather than raised. Add a counter for skipped logs and alert when it moves. Common causes are non-standard token ABIs, proxy contracts emitting unexpected topics, and logs with fewer topics than your decoder expects.

Lag is a throughput problem, not a correctness problem, but it becomes a correctness problem if your application reads stale data without knowing it. Expose the cursor height as a metric and have read paths check it. If lag grows without bound, batch log requests by range, parallelize independent block ranges with a single writer, and consider a managed indexer for chains where your node's log limits are restrictive.

• Hash mismatch at height H: rewind to last matching ancestor, replay.
• Skipped-log counter rising: inspect raw topics and ABI version.
• Lag climbing: increase batch size, reduce per-block RPC calls, or shard by range.
• Duplicate rows: confirm the unique key includes log_index, not just tx_hash.

Self-hosted versus managed indexing: the real tradeoff

Self-hosting gives you control over schema, retention, and decode logic, and it keeps sensitive query patterns inside your perimeter. The cost is operational: you own backfill time, reorg logic, node reliability, and schema migrations. For a single chain and a narrow set of entities, self-hosting is often the cheapest path.

Managed indexing shifts the pipeline to a provider that handles ingestion, reorgs, and often a hosted query API. That reduces engineering load but introduces vendor-specific query languages, retention limits, and pricing tied to query volume or indexed rows. The decision usually hinges on how differentiated your decode logic is: if your index is a commodity view of transfers and swaps, managed is attractive; if it encodes proprietary scoring or joins against internal data, self-hosting wins.

Whichever you choose, keep the RPC layer separable. A dedicated node or a managed endpoint should be swappable without rewriting the indexer. See <a href="/en/dedicated-node">dedicated node options</a> for workloads where consistent log throughput matters, and <a href="/en/pricing/rpc">RPC pricing</a> to model request volume for backfill versus steady-state ingestion.

Sizing, retention, and cost considerations

Index size grows with the number of events you persist, not with chain size. A transfers-only index is far smaller than one that stores every log and trace. Estimate rows per block from a sample range, multiply by your retention window, and add index overhead; secondary indexes on address columns can dominate storage on high-activity tokens.

Retention is a product decision. Many teams keep hot rows for 90 days and archive older partitions to cold storage while keeping aggregate tables. Partitioning by block range makes both retention and reindexing cheaper, because you can drop and rebuild a partition without touching the rest of the table.

Backfill cost is dominated by RPC request count, not wall-clock time. Batching logs by range and avoiding per-transaction receipt calls can cut request volume by an order of magnitude. Measure requests per thousand blocks before and after optimization rather than assuming.

Next steps and related reading

Start with one entity, one chain, and a verification table you can rerun. Once the cursor, reorg rollback, and reconciliation checks are stable, expand the decoder and add partitions. If you need a concrete endpoint recommendation for a specific chain, ask the RPC Assistant; this page intentionally avoids naming providers because the right choice depends on your chain, region, and query mix.

For broader context on node operations, read the <a href="/en/learn/blockchain-node-hosting-guide">blockchain node hosting guide</a>. If you are comparing endpoint strategies for the ingestion layer, the <a href="/en/learn/rpc-endpoints-guide">RPC endpoints guide</a> and the <a href="/en/learn/multi-chain-rpc-endpoints-guide">multi-chain RPC endpoints guide</a> cover failover and multi-chain patterns. For a provider-neutral starting point on managed access, see <a href="/en/api-service">the API service</a>.

• Ship the cursor and reconciliation checks before adding more entities.
• Add reorg rollback tests with a synthetic fork in staging.
• Measure requests per thousand blocks and lag before scaling out.
• Revisit managed indexing once your decode logic stabilizes.