Pipeline

Postgres for shred-decoded transactions: provisional, then confirmed

A transaction row in Postgres starts as seen and moves forward once, to confirmed, failed or dropped. With the signature as the primary key and a forward-only update, you can load the same capture twice, or rerun the reconcile job, without corrupting anything.

To store Solana transactions in Postgres from a shred decoder, make the first signature the primary key, insert with ON CONFLICT DO NOTHING, and keep a status column that starts at seen. Updates may only move it forward: seen to dropped, confirmed or failed, and dropped to confirmed or failed. Replays and reruns then change nothing they should not.

Schema#

ColumnTypePurpose
signaturetext PRIMARY KEYthe transaction ID, and the reason a second insert of the same transaction does nothing
slot, entry_index, tx_indexbigint, integer, integerwhere the decoder saw it; useful for range queries, never part of the key
versiontextlegacy, 0 or 1
fee_payer, programstext, text[]the fields most queries start from
statustext with a CHECK constraintseen, confirmed, failed or dropped, default seen
raw_base64textthe original transaction bytes, so you can decode again later
first_seen, updated_attimestamptzwhen the row arrived and when its status last moved

The decoded fields sit in ordinary columns, so queries never parse JSON, and the raw bytes are kept so you can decode again when your decoder improves.

Steps#

  1. Create the table and the slot index once.
  2. Load decoded records in batches with ON CONFLICT DO NOTHING on the signature.
  3. When the reconcile job settles transactions, apply the new statuses with an UPDATE that only moves forward.
  4. Only the reconcile job marks a row dropped, under the rule on the reconcile page. Keep or archive dropped rows as your retention policy says.

postgres.ts#

The tested sink, unchanged. It runs on Node 24 with pg 8.23.1 against PostgreSQL 17.11.

postgres.ts
// Postgres sink. The signature is the primary key, so loading the same
// transaction twice is a no-op (ON CONFLICT DO NOTHING). The status column
// starts at "seen" and is moved forward later by reconcile.
import pg from "pg";
import type { Status, TxRecord } from "./records.ts";
export const pool = new pg.Pool({
connectionString: process.env.PG_URL ?? "postgres://sse:sse-throwaway@127.0.0.1:18354/sse",
});
export async function createSchema(table = "solana_tx"): Promise<void> {
await pool.query(`
CREATE TABLE IF NOT EXISTS ${table} (
signature text PRIMARY KEY,
slot bigint NOT NULL,
entry_index integer NOT NULL,
tx_index integer NOT NULL,
version text NOT NULL,
fee_payer text NOT NULL,
programs text[] NOT NULL,
status text NOT NULL DEFAULT 'seen'
CHECK (status IN ('seen', 'confirmed', 'failed', 'dropped')),
raw_base64 text NOT NULL,
first_seen timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS ${table}_slot_idx ON ${table} (slot);`);
}
// The whole batch goes in as one jsonb parameter, which avoids the 65535
// bind parameter limit of a wide VALUES list. DO NOTHING (unlike DO UPDATE)
// also accepts the same signature twice inside one batch.
export async function insertBatch(records: TxRecord[], table = "solana_tx"): Promise<number> {
const res = await pool.query(
`INSERT INTO ${table} (signature, slot, entry_index, tx_index, version, fee_payer, programs, raw_base64)
SELECT signature, slot, entry_index, tx_index, version, fee_payer, programs, raw_base64
FROM jsonb_to_recordset($1::jsonb) AS r(signature text, slot bigint, entry_index int, tx_index int,
version text, fee_payer text, programs text[], raw_base64 text)
ON CONFLICT (signature) DO NOTHING`,
[JSON.stringify(records)],
);
return res.rowCount ?? 0; // rows actually inserted, duplicates excluded
}
// Moves status forward only: seen -> anything, dropped -> confirmed/failed.
// A final outcome is never overwritten, so replays and retries are safe.
export async function applyStatuses(
updates: { signature: string; status: Status }[],
table = "solana_tx",
): Promise<number> {
const res = await pool.query(
`UPDATE ${table} t SET status = u.status, updated_at = now()
FROM jsonb_to_recordset($1::jsonb) AS u(signature text, status text)
WHERE t.signature = u.signature
AND ((t.status = 'seen' AND u.status IN ('dropped', 'confirmed', 'failed'))
OR (t.status = 'dropped' AND u.status IN ('confirmed', 'failed')))`,
[JSON.stringify(updates)],
);
return res.rowCount ?? 0;
}

What the fixture test proved#

Here the same two-block fixture went in twice, each load carrying a repeat of every fifth record, followed by a set of allowed and forbidden status moves.

CheckResult on 18 November 2026
Rows inserted by the first load781, one per transaction; the in-batch repeats were skipped
Rows inserted by the second, identical load0
Rows and distinct signatures in the table781 and 781
seen to confirmed and seen to failedboth applied
confirmed to dropped and failed to seen, attempted afterwardsboth refused: 0 rows changed
The programs array after a round tripidentical to the input

The transactions are real; their entry_index values come from a locally built shred fixture, because RPC does not expose entries.

Loading in batches: jsonb or COPY#

The example sends a whole batch as one jsonb parameter and expands it with jsonb_to_recordset. That keeps the query to a single bind parameter, so it never runs into the protocol's limit on parameters per statement, and ON CONFLICT still applies row by row.

COPY is the other common route for large loads. It does not take an ON CONFLICT clause, so the usual pattern is to COPY into a temporary staging table and then run the same INSERT ... SELECT ... ON CONFLICT (signature) DO NOTHING from the staging table into the real one. The COPY variant is not part of the tested example; if you adopt it, run the same twice-loaded fixture check against it.

Indexes for common queries#

The primary key already serves lookups by signature, which is what the reconcile job and most application code do. The example adds one index on slot for range scans such as "everything decoded in the last thousand slots". Beyond that, index for your own queries: a fee payer index for wallet views, or a GIN index on programs if you often ask which transactions touched a program. A partial index on status = 'seen' keeps the reconcile job's work queue small, since settled rows drop out of it.

When Postgres is the wrong home#

Postgres is a good operational store for statuses and single-transaction lookups. It is a poor fit for scanning every transaction of the last month to aggregate volumes; a column store handles that far better, which is why the ClickHouse page exists and why ClickHouse or Postgres is worth reading before you pick. Neither database can tell you a transaction's outcome; that always comes from reconciliation.

Keep going#

The reasoning behind write-twice-store-once is in idempotent writes for chain data, and the status model comes from provisional rows. The deduplication page covers repeats before they reach the database, and why the signature makes a good key covers the primary key choice. To start receiving shreds, order a plan.

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.