Schema conventions

How the Solana Historical Data Indexer schema is put together: shared columns, raw base units, partition keys, retention windows, and the padding and naming gotchas that bite first.

// updated 2026-08-22

The Solana database holds 21 tables and 463 columns of decoded DEX activity. This page covers what is true across all of them; the per-table column lists live in the Table reference.

NOTE

Read this page before your first query. Four conventions - fixed-width padding, raw base units, per-table partition keys, and per-table retention - explain most of the surprises people hit in week one.

Shared columns

Almost every table carries the same transaction envelope. Use it for joins, time-series aggregation, and wallet-level work.

Column	Type	What it is
`block_time`	DateTime	UTC timestamp of the block
`block_date_utc`	Date	UTC date, and on most tables the partition key
`slot`	UInt32 / UInt64	Solana slot number
`tx_idx`	UInt16 / UInt32	Transaction index within the block
`signature`	String / FixedString(128)	Transaction signature, base58
`fee_payer`	String	Wallet that paid the fee
`fee`	UInt64	Fee actually paid, in lamports
`provided_gas_fee`	UInt64	Compute-unit price the transaction offered
`provided_gas_limit`	UInt64	Compute-unit limit requested
`consumed_gas`	UInt64	Compute units actually burned
`parent_program`	String	Program that invoked this instruction through CPI

(slot, tx_idx) identifies a transaction inside a block, and together with signature it is what you join on across tables.

TIP

parent_program is the aggregator fingerprint. A swap routed through Jupiter, a bot, or any other program carries that program's address here, while a direct call to the DEX does not. It is the cheapest way to split organic flow from routed flow.

Four gotchas

1. Fixed-width columns are null-padded

Older tables store mints, wallets, and signatures as FixedString(48) or FixedString(128). ClickHouse pads the unused bytes with \0, so a raw value will not compare equal to a plain base58 string, and grouping by it produces keys with invisible trailing bytes.

Strip the padding on both sides of any comparison or join:

SELECT replaceAll(toString(mint), '\0', '') AS mint_clean,
       count() AS launches
FROM pumpfun_token_creation
WHERE block_time >= now() - INTERVAL 1 DAY
GROUP BY mint_clean

Newer tables use plain String and need no cleanup. The Table reference shows the exact type per column.

2. Every amount is a raw base unit

Token and SOL amounts are integers in the smallest unit - lamports for SOL, and the mint's own base unit for SPL tokens. Nothing is pre-divided by decimals, and nothing is converted to USD. Divide by 1e9 for SOL; for a token, apply that mint's decimals.

Fee-rate columns follow the same rule: lp_fee_basis_points and protocol_fee_basis_points are basis points, not fractions.

3. The partition key is not the same on every table

Most tables partition on block_date_utc, but not all:

Partition key	Tables
`block_date_utc`	most swap and migration tables
`block_date`	`meteora_swaps`
`(block_date_utc, failed)`	`pumpfun_v2_swaps`
`toYYYYMM(block_time)`	`pumpfun_amm_admin_set_coin_creator`
`toYYYYMMDD(block_time)`	`pumpfun_creator_fee_distributions`
`toYYYYMMDD(toDateTime(entry_timestamp))`	`tx_timestamps`
none	`pumpfun_token_creation`, `pumpfun_all_swaps`, `raydium_launchpad_token_creation`, `solana_blocks`, `max_caps`

WARNING

Filter on the partition column, and do it in PREWHERE. A swap query with no partition filter reads the whole table - on pumpswap_all_swaps that is 1.6 TB and 5 billion rows. PREWHERE block_date_utc >= today() - 7 cuts it to the parts you need before any other column is read.

4. Some columns only carry data from a certain date

Columns added after a table went live are marked (populated since ...) in the Table reference. The column exists on every row, but rows older than that date hold the type default - an empty string or a zero, not a NULL. Filter on the date range as well as the column value, or old rows will quietly look like real zeros.

Retention

Retention is per table. Nothing is a single global window.

Window	Tables
14 days	`tx_timestamps`, `jito_tips`
31 days	`token_transfers`, `sol_top_ups`
90 days	`meteora_swaps`, `raydium_all_swaps`, `raydium_cpmm_swaps`
1 year	`pumpswap_all_swaps`, `pfamm_migrations`, `meteora_dynamic_bonding_swaps`, all `raydium_launchpad_*`
Full history	`pumpfun_all_swaps`, `pumpfun_v2_swaps`, `pumpfun_token_creation`, `pumpfun_creator_fee_distributions`, `pumpfun_amm_admin_set_coin_creator`, `solana_blocks`, `max_caps`

TIP

Need a longer window than the table keeps? Extended retention and one-off exports are arranged per customer - message @supanode_tgs before you build a pipeline that assumes the data will still be there.

Coverage and latency

History depth

The deepest table, pumpfun_token_creation, starts 17 January 2024. Every table's own window is listed in the Table reference.

Validation latency

~15 seconds between block confirmation and the row being queryable. Confirmed transactions only - never processed-level data that can revert.

Storage engine

Setting	Value
Database	ClickHouse, `default` database
Engine	`MergeTree` family
Partitioning	daily on the table's date column (see above)
Sort order	`(slot, tx_idx)` on transaction tables
Nanosecond timing	separate `tx_timestamps` table, joined on `(slot, tx_idx)`