Idempotent Writes and Gap Detection: keeping an indexer correct after a drop

A dashboard query comes back empty for a block range you know was busy, and nothing in the logs says why. There are two ways that happens. A range was never processed: the worker died, the socket dropped, the batch half-committed. Or a range was processed twice, and the second pass overwrote or double-counted the first. Both leave an indexer that looks healthy from the tip. Rollback after a reorg is a different failure with its own handling, covered in handling chain reorgs in an indexer. The method for the other one is gap detection and repair: make the write safe to repeat, find the hole, fill it, prove it closed.

A gap is not a reorg

A reorg replaces blocks you already have, and it announces itself: the parent hash of the next block does not match the hash you stored, and your handler rolls back. A gap is an absence nobody told you about. The tip is present, the middle is not, and every health check that reads the tip passes.

Three things produce one. A subscription drops and the reconnect resumes from a height that is not where the last delivered block ended. A worker crashes between fetching a batch and committing it. A batch commits partially, so half its rows land.

The subscription case is worth being precise about. eth_subscribe is only available on transports that support server-initiated notifications, such as WebSocket and IPC [1], and a JSON-RPC notification is a request with no response - "not confirmable by definition" [2]. Its parameters are a kind plus an optional kind-specific argument; there is no cursor and no from-block [1]. A reconnect is therefore a new subscribe call with a new subscription ID, and nothing in the protocol tells you what arrived while you were away. Both specifications are silent on delivery rather than warning about it: they define the notification's shape and say nothing about receiving every one [2][1].

Make the write safe to repeat

An idempotent write is one where running it twice leaves the same state as running it once. That is a property of the statement and the constraint behind it, not of the caller. A worker that checks for an existing row before inserting is not idempotent: two workers can both pass the check, and the check is a race rather than a guarantee.

The key decides everything else. For an indexed row the natural key is the chain, the block number, the transaction hash and the log index together. A surrogate auto-increment id cannot serve, because the database generates it: the same block delivered twice gets two ids and two rows, and nothing in the schema objects. Put a unique constraint on the natural key and the second delivery has nowhere to go.

In PostgreSQL 18, a unique constraint plus INSERT ... ON CONFLICT DO UPDATE is the documented mechanism, and the docs name it: "This is also known as UPSERT" [3]. The clause guarantees an atomic insert-or-update outcome even under high concurrency [3].

INSERT INTO logs (chain_id, block_number, tx_hash, log_index, address, data)
VALUES ($1, $2, $3, $4, $5, $6)
ON CONFLICT (chain_id, block_number, tx_hash, log_index)
DO UPDATE SET address = EXCLUDED.address, data = EXCLUDED.data;

DO UPDATE overwrites the stored row with the proposed values, which the excluded table exposes; DO NOTHING keeps whatever landed first [3]. Which one you want depends on a question the docs do not answer: can a re-delivered block carry different contents?

One caveat bites batches. A single INSERT ... ON CONFLICT DO UPDATE statement may not affect the same existing row twice; Postgres raises a cardinality violation [4]. A batch containing the same block twice fails outright rather than collapsing. Deduplicate the batch before the statement, or split it.

Then the derived values. Anything computed from a block has to be recomputed from that block, not incremented. A counter incremented once per processed block is the classic way a replay corrupts a total: the rows are correct, the unique constraint held, and the aggregate is now double.

Detect the hole

Nothing in a log line tells you a range is missing. A worker that never started logs nothing, and a batch that rolled back logs an error you may already have triaged. Gap detection is a query against the table you wrote.

Keep a checkpoint row per chain holding the highest height that is contiguously committed - every height from your start block to that one is present. That is a different number from the highest height you have seen, and the gap between the two is the whole point. SELECT max(block_number) answers the second question. It returns the tip, the tip is present, and the check passes while the middle is empty. An indexer with a gap looks exactly like an indexer without one if the only thing you measure is the maximum.

For batched work, write a completion marker per range in the same transaction as the rows. A batch that committed half its rows and then died leaves no marker, so the range is visibly incomplete rather than invisibly partial. The marker and the data commit together or not at all.

The cheap periodic check is arithmetic: pick a window, count the distinct heights you hold inside it, and compare that count against the window's width. A shortfall is a gap, and the query names the heights that are missing rather than reporting a number. Run it against committed state. A check that counts rows still inside an uncommitted transaction reports a gap that is about to close, and you will learn to ignore it.

When the drop is a WebSocket disconnect, the reconnect path has its own timing questions; filling the gap after a WebSocket drop covers those.

Fill it without making a second one

Replay from the checkpoint, not from the tip. The checkpoint is the highest contiguously committed height, so the range to refetch starts at the first height above it that the detection query named. Starting from the tip re-processes everything you already hold - safe now that the write is idempotent, and wasteful regardless.

Bound the fill. Take the missing heights in chunks small enough that a chunk either commits with its marker or does not commit at all, and write the marker in the same transaction as the rows. A fill that can be interrupted halfway without leaving a trace has reproduced the original failure with more steps.

Re-fetch by block number rather than by replaying a subscription. The old subscription is gone, and a new one cannot be pointed at a range you missed [1].

Then verify with the check that found the hole. The fill is not finished when the writes return; it is finished when the count of distinct heights in the window matches the window's width and the checkpoint has advanced. Anything short of that has moved the hole rather than closed it.

Sizing a fill against rate limits and provider caps is a planning question, and planning an indexer backfill covers it.

What this does not fix

The method does not cover everything. The limits bound what it buys you.

It does not recover data the provider no longer serves. If the node has pruned the history for the height you need, the request fails, and no amount of retrying or upserting changes that. That is a routing problem - send the historical read to an endpoint that keeps it - not a gap problem.

It does not fix ordering. Idempotent writes make a duplicate harmless. They do not make out-of-order processing correct: if block 100's derived state depends on block 99 having been applied first, a replay that lands 100 before 99 writes a wrong row that the unique constraint will accept without complaint.

It does not detect a gap in a range you never asked for. The check compares what you hold against the window you chose. A range below your start block, or a contract you never added to the filter, is invisible to it.

And it does not replace a reorg handler. Rollback is a different mechanism with a different trigger.

Where this leaves you

The order matters more than any individual step. Key and upsert first, because every later step assumes a duplicate is harmless. Checkpoint second, because detection needs a number to compare against. Detection third, because a fill needs a target. Fill last, because a fill without a checkpoint moves the hole to a different height.

If you are adding a second worker this week, we would do the first item before it starts. Two workers processing the same block is the duplicate case, and the unique constraint is what makes it a non-event.

Read the docs

Sources

1. execution-apis — src/eth/subscribe.yaml (eth_subscribe, eth_unsubscribe) — ethereum/execution-apis (GitHub), 2026-11-08
2. JSON-RPC 2.0 Specification — Notification — JSON-RPC Working Group (jsonrpc.org), 2010-01-01
3. PostgreSQL 18 Documentation — INSERT (ON CONFLICT Clause) — PostgreSQL Global Development Group, 2026-08-13
4. PostgreSQL 18 Documentation — INSERT, ON CONFLICT DO UPDATE determinism caveat — PostgreSQL Global Development Group, 2026-08-13

This article was researched, written and edited by AI under human supervision, and checked against Rankwrite's public editorial rubric.