Put live lookups and the pending to settled status changes in Postgres, where an upsert updates one row in place. Put large scans and aggregates in ClickHouse, where a ReplacingMergeTree table keeps the newest version of each transaction after merges. Many Solana pipelines run both, with Postgres for serving and ClickHouse for analysis.
The verdict, by side#
ClickHouse#
Pick it for volume charts, wallet histories across months, research queries over every transaction and anything that reads many rows and few columns. Model status changes as new row versions and let merges keep the latest.
Postgres#
Pick it for what an application asks one row at a time: is this signature known, what is its status, which alerts are pending. In-place upserts and transactions make the provisional pattern straightforward.
The update problem in chain data#
A transaction decoded from shreds starts life without an outcome. Seconds later it is confirmed, found to have failed, or dropped because its slot was skipped (E4 tech facts X-04). That means every row is written at least twice, and a table built only for appends will hold both versions unless you plan for it.
Postgres handles this with INSERT ... ON CONFLICT DO UPDATE: the second write replaces the status in the existing row. ClickHouse is built for appends, so the usual approach is a ReplacingMergeTree table with a version column. Both versions are stored at first, background merges keep only the highest version per key, and a query that must be exact reads with FINAL or groups by the key.
Workload by workload#
A live alert lookup#
Postgres. A point lookup by signature with an index is its home ground, and the answer reflects the latest status as soon as the upsert commits.
Moving a row from provisional to settled#
Postgres for simplicity; ClickHouse when the same rows also feed analytics and you accept merge-time replacement. Make the status move only forward in both, so a late provisional event never overwrites a settled one.
A volume aggregate by hour or pool#
ClickHouse. Columnar storage reads only the amount and time columns, which suits sums over millions of swaps.
A wallet's full history#
ClickHouse for long histories scanned in one go; Postgres if the history is short and served page by page in an app.
Research scans over raw fields#
ClickHouse, or files in Parquet next to it. Ad hoc questions over every row are what columnar engines are for.
Where TimescaleDB fits#
If you want to stay inside Postgres and your data is mostly time-ordered, TimescaleDB's hypertables partition a table by time while keeping SQL, and upserts are possible as long as the time column is part of the unique key. It is a middle path, not a replacement for a columnar engine on very large scans.
Running both#
A common split: the decoder writes provisional rows to Postgres, the reconcile loop upserts the outcome there, and a periodic job copies settled rows into ClickHouse. Analysts never see a provisional row, and the application never runs a scan that hurts it. Now that Solana runs 200 ms slots, batch those writes by slot rather than one by one; 200ms slots and your database weighs the options. The build or buy comparison lists the other parts such a pipeline needs.
Schema starting points#
| Column | Postgres | ClickHouse |
|---|---|---|
| Key | First signature, primary key | First signature in the ORDER BY |
| Slot and position | Integer columns, indexed for range queries | Plain columns; keep the sort key to values that never change between versions, such as the signature |
| Status | Text or small integer, forward-only by rule | Small integer status plus a version column for replacement |
| Updates | ON CONFLICT DO UPDATE | A new row with a higher version |
| Bulk load | COPY | Batched inserts |
The Postgres schema guide and ClickHouse schema guide turn these rows into tested DDL.
Database questions#
Can ClickHouse update a transaction's status?#
Not in place in the usual design. You insert a newer version, and ReplacingMergeTree keeps the latest after merges; exact reads before a merge need FINAL or a GROUP BY.
Is Postgres fast enough for Solana data?#
For serving and status tracking of the transactions you care about, it fits well. For scans across months of transactions, a columnar engine is the better tool.
Where do provisional rows belong?#
In whichever table your application reads first, usually Postgres. Keep them out of analytics tables until settled.
When neither database is the bottleneck#
If your receiver loses packets or your decoder falls behind, no storage choice will recover the missing transactions, because a raw feed never resends them. Fix intake first. And if you only need settled history, a provider's historical API or block requests may save you from running a database at all.
Fill the tables#
Both databases start from the same decoded rows. See plans and prices for a raw feed, and read provisional rows before you design the status column.
Sources#
- ClickHouse docs: ReplacingMergeTree (version column, deduplication at merge time, FINAL) (checked 2026-11-18)
- PostgreSQL docs: INSERT ... ON CONFLICT (checked 2026-11-18)
- PostgreSQL docs: COPY (checked 2026-11-18)
- TimescaleDB docs: hypertables (checked 2026-11-18)
- Solana improvement document 0525, shorter slots (checked 2026-11-18)
- SIMD-0326 Alpenglow: skipped slots and certificates (checked 2026-11-18)
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.