Canonical: https://solanashreds.xyz/docs/pipeline/postgres

# 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

| Column | Type | Purpose |
| --- | --- | --- |
| `signature` | `text PRIMARY KEY` | the transaction ID, and the reason a second insert of the same transaction does nothing |
| `slot`, `entry_index`, `tx_index` | `bigint`, `integer`, `integer` | where the decoder saw it; useful for range queries, never part of the key |
| `version` | `text` | legacy, 0 or 1 |
| `fee_payer`, `programs` | `text`, `text[]` | the fields most queries start from |
| `status` | `text` with a CHECK constraint | seen, confirmed, failed or dropped, default seen |
| `raw_base64` | `text` | the original transaction bytes, so you can decode again later |
| `first_seen`, `updated_at` | `timestamptz` | when 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](https://solanashreds.xyz/docs/pipeline/reconcile) 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](https://solanashreds.xyz/docs/pipeline/reconcile). 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.

```typescript file="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.

| Check | Result on 18 November 2026 |
| --- | --- |
| Rows inserted by the first load | 781, one per transaction; the in-batch repeats were skipped |
| Rows inserted by the second, identical load | 0 |
| Rows and distinct signatures in the table | 781 and 781 |
| seen to confirmed and seen to failed | both applied |
| confirmed to dropped and failed to seen, attempted afterwards | both refused: 0 rows changed |
| The `programs` array after a round trip | identical 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](https://solanashreds.xyz/docs/pipeline/clickhouse) exists and why [ClickHouse or Postgres](https://solanashreds.xyz/compare/clickhouse-vs-postgres-for-solana) is worth reading before you pick. Neither database can tell you a transaction's outcome; that always comes from [reconciliation](https://solanashreds.xyz/docs/pipeline/reconcile).

## Keep going

The reasoning behind write-twice-store-once is in [idempotent writes for chain data](https://solanashreds.xyz/blog/idempotent-writes-for-chain-data), and the status model comes from [provisional rows](https://solanashreds.xyz/blog/provisional-rows). The [deduplication page](https://solanashreds.xyz/docs/pipeline/deduplicate) covers repeats before they reach the database, and [why the signature makes a good key](https://solanashreds.xyz/learn/transaction-signatures-as-ids) covers the primary key choice. To start receiving shreds, [order a plan](https://solanashreds.xyz/docs/start/order-and-activate).
