Modeling on-chain data so your queries do not melt

The query was fine in testing and fell over in production. It joined three event tables, scanned a few million rows, and took thirty seconds to answer a question the UI needed in under one. The fix was not a better database or more hardware. It was on-chain data modeling, the boring decision about what tables exist and what shape the rows take, made before a single event is processed. That decision sets the ceiling for how fast everything downstream can ever be.

Raw events are not your data model

The mistake I see most often is treating the event log as the schema. You define a table per event, dump every emitted log into it, and call that your database. It feels honest because it mirrors the chain exactly. It also means every meaningful question becomes an expensive aggregation over raw history, computed fresh on every request.

Events are an input, not an answer. A Transfer event tells you one movement happened. The thing your app actually wants is a balance, which is the sum of every movement for an account up to now. If you store only the events, you recompute that sum constantly. If you maintain the balance as you go, you read it in a single lookup. Same data, completely different performance story.

Model the questions, not the chain

Before I write a schema I list the questions the product will ask. What is this account's balance. What are the last twenty trades on this pair. What is the total value locked in this pool right now. Each of those is a read pattern, and the schema should make the common ones cheap even if that costs more work at write time. Indexers are write-heavy in bulk and read-heavy in bursts, so paying at write time to make reads instant is almost always the right trade.

This usually means keeping both raw event tables and derived state tables. The raw tables are your audit trail and your rebuild source. The derived tables are what the application reads. Keeping both feels redundant until the first time you need to fix a bug by replaying events into a corrected derived table, at which point you are very glad you kept the raw history.

Entities that earn their keep

When I design the derived layer I think in entities that match how the protocol behaves, not how the contracts are split. A single logical entity like a position or a pool might be touched by events from several contracts. Modeling it as one row that several handlers update beats scattering its state across one table per contract and joining them back together at read time.

• Keep a current-state row per entity (account balance, pool reserves, position size) and update it in place as events arrive
• Keep an append-only history table when the product needs a timeline, but never make it the only source of current state
• Store amounts as strings or wide numeric types, token values overflow normal integers fast
• Denormalize the fields you filter and sort on most, a little duplication beats a join on the hot path

That last point matters more than it sounds. The expensive part of most on-chain queries is not reading rows, it is joining and aggregating them. If the row you need already holds the fields you filter by, the query is a straight index lookup.

Index for the access pattern, not the column

People add an index to a column and assume the database will be fast. Indexes serve queries, not columns. If your hot query filters by pool and orders by block number, a composite index on (pool, block number) turns it into a quick range read, while two separate single-column indexes leave the database doing more work than it should. I look at the actual query, then build the index that matches it, then check the query plan to confirm the database agrees with me.

With both The Graph and Subsquid you have control over this, more so with Subsquid since you own the Postgres schema directly. I use that control. A schema that maps cleanly to the product's read patterns is the difference between a dashboard that loads instantly and one that spins.

Plan for the rebuild

Every indexer gets rebuilt. A contract you missed, a bug in a handler, a new field you need from history. The schema that survives this is the one where raw events are kept intact and derived state can be regenerated from them deterministically. If your derived tables are the only copy of the truth, a single handler bug can permanently corrupt data you can never recover, because the chain moved on and your wrong numbers are all you have left.

So model for two readers. The application, which wants fast current answers, and future you, who will need to rebuild the whole thing without losing anything. Get both right and your queries stay quick no matter how long the chain runs.