High-Performance Blockchain Indexing: Lessons Learned

From “it works on my node” to “we can handle mainnet”

Introduction

A blockchain indexer sounds simple on paper: read blocks from a node, parse them, put them in a database, add an API.

In reality, a serious indexer is a distributed data pipeline that must:

• ingest raw blocks and events at chain speed;
• survive reorgs and node hiccups;
• keep PostgreSQL (or similar) from melting under billions of rows;
• serve low‑latency queries for explorers, wallets, DEXs, and analytics.

Indexers exist because base nodes are terrible for ad‑hoc querying. A “blockchain indexer” is essentially a specialised ETL: extract raw blocks, transform them into structured data, and load them into a database or data service for fast queries.(Alchemy)

In this article I’ll walk through how I structure high‑performance indexers and what actually failed in production on noisy, high‑throughput chains.

1. Treat the indexer as a pipeline, not a script

I always start by drawing the system as a pipeline:

+-----------+    +-----------+    +-------------+    +-------------+
| Full node | -> | Ingestor  | -> |  Pipeline   | -> |   Database  |
| (RPC/gRPC)|    | (block /  |    | (Kafka/ES)  |    |  (Postgres) |
+-----------+    |  log feed)|    +-------------+    +-------------+
                      |                                      |
                      v                                      v
                 +---------+                         +---------------+
                 |  Reorg  |                         |  HTTP / gRPC  |
                 | handler |                         |   API layer   |
                 +---------+                         +---------------+

The important mindset shift: this is not “a daemon with a while (true) over block height”. It’s a streaming system with:

• a clear source (node),
• an internal log (Kafka / queues / append‑only tables),
• one or more processors (decoding, enrichment, projections),
• one or more sinks (SQL, time‑series, search, object storage).(GitHub)

When you treat it that way, scaling becomes a matter of:

• partitioning work,
• controlling backpressure,
• isolating hot paths,

rather than micro‑optimising one fat loop that “does everything”.

2. Ingestion: from node to durable log

The ingestion side has two non‑negotiable jobs:

1) Keep up with the chain head.
2) Persist enough information to replay or repair.

I typically split it into two stages:

Node
  -> "raw_blocks"    (binary block data, height, hash)
  -> "decoded_items" (tx, logs, UTXOs, events ...)

There are two broad patterns:

• Raw‑first: push raw blocks into a durable log (Kafka, append‑only table) and let downstream workers decode and fan‑out into per‑entity streams (transactions, logs, UTXOs). TON ETL and similar projects follow this model.(GitHub)

• Decoded‑first: do basic decoding close to the node and publish higher‑level messages (“ERC20Transfer”, “PoolSwap”, “UTxOCreated”) into topics. Frameworks like Polygon’s Chain Indexer use this for EVM chains.(GitHub)

Both can work. Two lessons held across every chain I’ve touched:

Ingestion must be I/O‑bound, not CPU‑bound. The process talking to the node should spend its time waiting on RPC/gRPC, not running heavy business logic. ABI decoding, Ordinals parsing, deep script analysis, address clustering—all of that belongs in downstream workers. Ordinals indexers are very explicit about separating “catch all blocks” from “parse inscriptions”, or they simply can’t keep up.(Hiro)

Persist enough to replay. If your node crashes, a decoder has a bug, or you add a new projection, you want to replay from a durable log (Kafka, SQS, Kinesis, append‑only table, S3), not from the live node. AWS’s guidance for explorer indexers and multiple vendor architectures make the same recommendation: treat the node as a volatile data source, not as your only copy of history.(AWS Builder)

The cost of durable logging is modest compared to the cost of a full re‑index from genesis.

3. Schema and storage: designing for billions of rows

Indexers live or die by their storage strategy.

A naïve “blocks / transactions / addresses” schema works for toy explorers. Real workloads with years of history and high‑activity chains need more deliberate design.

3.1 Narrow, hot tables for critical queries

For hot paths (latest blocks, recent transactions for an address, token transfers, swaps) I prefer narrow, purpose‑built tables:

blocks(
  chain,
  height,
  hash,
  parent_hash,
  timestamp,
  ...
)

tx(
  chain,
  hash,
  block_height,
  from_address,
  to_address,
  value,
  status,
  ...
)

address_tx(
  chain,
  address,
  tx_hash,
  block_height,
  direction,     -- in/out/self
  ...
)

This is close to what many professional explorers and indexers implement: denormalised, query‑oriented tables alongside raw block / tx storage.(Coudo AI)

The rule is simple: the 10–20 most common queries should hit one or two tables with good indexes, not 8‑way joins across a fully normalised schema.

3.2 Partitioning and time‑series thinking

All long‑lived indexers eventually behave like time‑series systems:

• data is append‑only,
• keyed by height or time,
• most queries are “recent first”.

PostgreSQL range partitioning is the usual first step:

- partition tx by (chain, block_height) or (chain, block_time)
- partition address_tx similarly
- local indexes per partition
- archive / drop old partitions when allowed

Coingecko engineers and others have written about the impact of using Postgres partitioning properly: cheaper vacuum, better index locality, and more predictable plans.(DEV Community)

Beyond that, hybrid storage is common:

- "hot" months in fast Postgres (OLTP)
- "warm" history compressed or in cheaper Postgres instances
- "cold" history in columnar / object storage (S3, warehouse)

Coindesk describes exactly this pattern for multi‑chain transaction indexing: PostgreSQL plus additional layers to keep per‑address history tractable over time.(CoinDesk Data)

The main lesson: schema and storage design are not “set and forget”. They must evolve with real data volumes and observed query patterns.

4. Query patterns: design for the 90%

The API layer is where your design gets judged.

The most popular calls are extremely predictable:

- Latest blocks (paginated by height or time)
- Transactions by address (reverse chronological)
- Token transfers by address / contract
- Contract-specific events (swaps, mints, votes)
- Basic address / contract summaries

If you scan the public APIs and docs of mainstream explorers, you’ll see the same shapes over and over.(99Bitcoins)

I design tables and indexes so these queries look boring:

-- latest blocks
INDEX ON blocks(chain, height DESC);

-- address history
INDEX ON address_tx(chain, address, block_height DESC);

-- token transfers
INDEX ON token_transfers(chain, contract, topic, block_height DESC);

Then I push “creative analytics” (multi‑year aggregations, ad‑hoc filters) into separate paths:

• a reporting database with different indexes,
• a data warehouse / OLAP system,
• offline jobs that materialise aggregates.

If you let one dashboard query on a giant, unbounded GROUP BY run on the same database that powers your explorer, you’ll eventually take down your public APIs. Keep OLTP and OLAP concerns separated.

5. Reorgs, idempotency, and correctness

Reorgs are not special incidents; they’re part of the protocol.

Every serious indexer has to survive:

• frequent short reorgs near the tip,
• rare but painful longer reorgs on some chains.

Teams building Ordinals indexers and explorers talk openly about how much engineering goes into making this boring.(Hiro)

I model canonicality explicitly:

blocks(
  chain,
  height,
  hash,
  parent_hash,
  is_canonical
)

The indexer maintains, per chain, a view of the current best tip. When a reorg happens:

1) Ask the node for the new best chain (by hash / height).
2) Find the fork point between the old and new best chains.
3) For blocks on the old tip after the fork:
     - mark is_canonical = false
     - emit "block_orphaned" events
4) For blocks on the new tip after the fork:
     - mark is_canonical = true
     - emit "block_canonical" events

Derived tables (address_tx, balances, DEX state) consume those events and apply inverse operations when they see block_orphaned. That’s where idempotent processors matter: they must be able to see the same block twice (once as canonical, once as orphaned) and end up with consistent state.

Event‑driven indexer frameworks like Open Ethereum Indexer formalise this pattern: listeners get both “added” and “removed” events and must handle both.(openethereumindexer.com)

From the user’s point of view:

• a transaction might move from “confirmed in block X” to “confirmed in block Y”;
• the high‑level state (“this address sent 1 BTC to that address”) should remain correct.

6. Scaling out: multi‑chain and multi‑tenant

A single‑chain indexer is hard but manageable. Multi‑chain and multi‑tenant is where bad decisions really hurt.

I like a per‑chain pipeline, shared platform model:

+----------------------+
|  Indexer Platform    |
|  - control plane     |
|  - shared tooling    |
|  - schema conventions|
+-----------+----------+
            |
   +--------+--------+----------------+
   |        |        |                |
   v        v        v                v
 BTC       ADA     Cosmos-Hub      EVM-L2-N
 pipeline  pipeline pipeline      pipeline

Each pipeline has:

• its own node connections and ingestion workers;
• chain‑specific decoders (EVM logs vs UTXO vs Ouroboros data);
• a schema that shares core concepts (block, tx, address, event) but allows extensions.

On the storage side you have a choice:

• Shared database Faster to set up: one large Postgres instance with everything partitioned by (tenant, chain, height). Good until one tenant or chain becomes noisy.

• Per‑tenant / per‑chain database More operationally complex, but gives performance isolation. Some high‑QPS indexer setups explicitly deploy a Postgres instance per customer or per chain, behind a common control plane.(Medium)

The pattern I’ve seen work:

- start with shared DB + strong partitioning
- as chains/tenants grow, split the heaviest ones into their own DBs
- keep tooling (migrations, schema, observability) uniform

The platform is what keeps you sane; pipelines are allowed to be chain‑specific.

7. Monitoring, backpressure, and “don’t fall behind”

Performance is not just peak throughput. It’s how the system behaves under sustained load and failure.

The minimal metrics I track:

Per chain:
  - node_tip_height
  - last_indexed_height
  - ingest_lag_blocks    = node_tip_height - last_indexed_height
  - ingest_lag_seconds   = now - timestamp(last_indexed_block)

Per stage:
  - end-to-end latency from "block seen" to "fully indexed"
  - queue depth per topic/partition
  - consumer lag (Kafka / queue metrics)

Database:
  - write latency
  - slow query counts
  - table/partition sizes
  - lock wait times

AWS and others emphasise almost exactly these metrics when describing reliable explorer/indexer deployments.(AWS Builder)

Backpressure strategy is where many indexers fail:

• When the chain spikes, you must protect ingestion and canonical storage first. If something has to degrade, make it secondary projections or heavy analytics, not basic block/tx storage.

• Stateless decode / transform stages can auto‑scale, but database writes cannot grow unbounded. You need limits on concurrent writers, batch sizes, and connections.

• For “exotic” decoders (e.g. Ordinals, NFT metadata), move them off the hot path. Let them consume from the durable log and accept that they may lag while base indexing stays caught up.(Hiro)

The game is not “never lag”. The game is “when we lag, we know it early and we have a controlled way to catch up without blowing up the database”.

8. Experience notes

Production note – BSC‑style bottleneck. On a BSC‑style chain, our first real bottleneck was not CPU; it was Postgres writes to a single tx table. Inserts, updates to derived tables, and explorer queries all contended on the same hot partition. Introducing range partitioning on block height, moving address‑centric views into dedicated tables, and feeding them via append‑only logs cut write latency dramatically. The final design looked a lot like the partitioning playbooks written up by Coingecko and others.(DEV Community)

Production note – Ordinals and “deep” decoding. An Ordinals‑like feature showed us why you never mix deep, optional parsing into the ingestion loop. When inscription volume spiked, our “decode everything in one stage” design fell tens of thousands of blocks behind. Splitting Ordinals into its own downstream pipeline, consuming from the same raw block log, restored base performance. This mirrors what public Ordinals indexer teams describe: keep the core indexer boring and offload complex interpretation.(Hiro)

Production note – multi‑chain schema sanity. By the time we added a third chain, we forced ourselves to standardise on a conceptual schema—block, tx, address, event—and attach chain‑specific details via extension tables. That discipline paid off later when we needed cross‑chain views; we weren’t stitching together three incompatible models. Multi‑chain indexer vendors advocate the same idea: one mental model, many adapters.(CoinFabrik)

Conclusion

High‑performance blockchain indexing is not about clever micro‑optimisations or exotic hardware.

It’s about:

- treating the indexer as a streaming pipeline,
- putting a durable log between the node and your storage,
- designing schemas around real query patterns, not ideals,
- using partitioning and hybrid storage to tame growth,
- handling reorgs and duplicates as routine events,
- and scaling per chain / per tenant without sharing a single choke point.

Once those foundations are in place, you can keep adding chains, protocols, and APIs without constantly re‑indexing from scratch. The node stays what it is: a slow, append‑only truth source. The indexer becomes what you need: a fast, structured view the rest of your ecosystem can safely build on.

References