Pipelines · November 18, 2026 · 7 min read

Write it twice, store it once: idempotent ingestion for chain data

A shred pipeline sees the same transaction more than once: after restarts, replays and late recovery. Choose keys and upserts so that every repeat lands on the row it already wrote.

Idempotent writes make blockchain data safe to reprocess: writing the same transaction twice leaves one row. Key transactions on the first signature, instructions on signature plus instruction index, and let a later write only move status forward. Then a restart, a replayed capture or a late shred refreshes rows rather than adding copies.

Choosing natural keys: signature, slot, instruction index#

A raw feed gives you no row ids, so the keys have to come from the data. Three fields carry the weight, and the question is which combinations you key on.

RowKeyWhy
Transactionfirst signatureThe first signature identifies a transaction everywhere on Solana, so it is the one value every source agrees on
Transaction seen in a slotfirst signature, slotUse this only if you need to keep each fork appearance as its own row
Instructionfirst signature, instruction indexInstructions have no identity of their own; their position inside the transaction is stable
Decoded actionfirst signature, action number, rule versionLets you reprocess with new rules without overwriting the old interpretation
Slot summaryslotOne row per slot, rewritten as the slot fills in

Whether the slot belongs in the key needs its own decision. The same signed transaction can show up in two forks, so it can arrive with two different slots. Keyed on signature alone, you hold one row and update its slot; keyed on signature and slot, you hold one row per appearance and let reconciliation mark the abandoned one as dropped. The first is simpler for reports. The second is honest about what you saw.

Status only moves forward#

Idempotence is easy for facts that never change, such as the signature, the account keys and the instruction bytes. It is harder for the one field that does change: status. A shred pipeline writes rows as provisional and later marks them confirmed, failed or dropped once it checks the outcome.

Give the statuses a rank, and let an upsert overwrite status only when the incoming rank is equal or higher. Provisional ranks lowest, dropped ranks next, and confirmed and failed rank highest, so that a later check can still turn a dropped row into an outcome but nothing can turn an outcome back. A replay of an old capture then writes "provisional" again and the database ignores it, because the stored row already holds a final status.

Upsert patterns per store#

PostgreSQL#

INSERT ... ON CONFLICT turns a duplicate key into an update, and its optional WHERE clause lets the update apply only when the new status is not a step backwards.

upsert.sql
INSERT INTO transactions (signature, slot, status, status_rank, fee_payer)
VALUES ($1, $2, $3, $4, $5)
ON CONFLICT (signature) DO UPDATE
SET slot = EXCLUDED.slot,
status = EXCLUDED.status,
status_rank = EXCLUDED.status_rank
WHERE transactions.status_rank <= EXCLUDED.status_rank;

ClickHouse#

ClickHouse does not update rows in place. A ReplacingMergeTree table keeps, for each sorting key, the row with the highest version during background merges. Use the status rank as the version column and sort by signature. Until a merge runs, duplicates can exist on disk, so queries that need one row per key read with FINAL or aggregate with argMax.

Kafka#

Use the signature as the message key. Every event for one transaction then lands in the same partition in the order you produced it, and a compacted topic keeps the latest value for each key. Consumers must still be idempotent themselves, because compaction runs in the background and a consumer can read both versions before it does.

Replays after restarts and reconnects#

Repeats come from more places than you might expect:

  • the decoder restarts and reprocesses the last few slots it was unsure it had committed;
  • you replay a capture to rebuild a table after a schema change;
  • a slot's missing shreds are recovered late, so its transactions are emitted a second time along with the new ones;
  • a reconciliation job and the live writer both touch the same row.

You do not need exactly once delivery from your own pipeline if every write is idempotent. At least once, with idempotent writes, gives the same final table and is far easier to build.

Testing idempotency#

  1. Record a capture of raw traffic and keep it as a fixture.
  2. Run the full pipeline over it into an empty database, then take row counts and a checksum of each table ordered by key.
  3. Run the same capture through again without clearing anything. Counts and checksums must not change.
  4. Run reconciliation, which moves rows to final statuses, then replay the capture a third time. Every final status must survive.
  5. Kill the writer halfway through a run, restart it, and confirm the final counts match step 2.

What idempotent writes do not fix#

Upserts stop duplicates. They do not recover data you never received: a slot whose shreds were lost and never recovered is simply missing, and replaying it changes nothing. They also cannot make a provisional row trustworthy. The row is still an observation of a signed request, and its outcome only arrives through reconciliation against confirmed data.

Signatures as transaction IDs explains why the first signature makes a good key and when the same one appears twice. For the full storage patterns, see the Postgres setup and the Kafka topic design; for transaction-level duplicate handling, see deduplicating transactions. The status model these writes rely on is in provisional rows. When you are ready to test against real traffic, a Standard Shreds destination is enough.

When writes pass through a message bus first, the Kafka, NATS and Redis Streams scorecard compares how each one replaces a record by key.

Sources#

Raw shreds on your own server

Ultra Low Latency Shreds is $200 and Standard Shreds is $100. You pay in crypto, tell us which IPv4 address and UDP port should receive the stream, and we wire it up by hand.

Read nextProvisional rows: storing what you saw before the chain agreed