> ## Documentation Index > Fetch the complete documentation index at: https://docs.moralis.com/llms.txt > Use this file to discover all available pages before exploring further. # Swaps by Pair > Sync every DEX trade on a pool/pair, direct pool fills and aggregator-routed trades, USD-enriched in-block, keyed by pool address so a pair's trade feed is a prefix scan, into your own database. ### Question it answers > "Give me every DEX trade on pool/pair **0x…**, newest first, each row is one trade with both token legs, the side, the protocol, and a USD notional." Same trade projection as [Swaps by Token](/data-feeds/templates/markets/swaps-by-token), but keyed by the **pool/pair address** with **one row per trade** (no per-token-side unpivot). `pool_address` is the **pair address** for direct pool fills and the **aggregator address** for aggregator-routed trades. ### What you get One row per trade, keyed by `pool_address`: | Column | Description | | -------------------------------------------------------- | -------------------------------------------------------------------------- | | `pool_address` | Pair address (pool fills) or aggregator address (aggregator-routed trades) | | `token0_address`, `token1_address` | The pair's two tokens | | `amount0`, `amount1` | Raw token-unit deltas for each leg (`uint256` as text) | | `side` | `buy` · `sell`, derived from the base token's pool-balance delta | | `source_kind` | `pool_fill` (direct pool) · `aggregator` (aggregator-routed) | | `price_usd` | Bought-leg (`token1`) USD price; `NULL` with no in-block price update | | `notional_usd` | `token1Amount / 10^token1Decimals × price_usd`; `NULL` if unpriced | | `protocol` | DEX/protocol identifier | | `fee_amount`, `fee_token`, `fee_usd` | Trade fee, pool fills only (`NULL` on aggregator rows) | | `trader_address` | The trading wallet | | `block_number`, `event_ts`, `tx_hash`, `vendor_event_id` | On-chain ordering and identity | ### Source The transform reads two per-block trade arrays and projects them into one feed: `tokenSwaps` (direct pool fills, `source_kind='pool_fill'`, `pool_address = pairAddress`) · `aggregateTokenSwaps` (aggregator-routed trades, `source_kind='aggregator'`, `pool_address = aggregatorAddress`) The two branches are disjoint by construction, together they give every fill exactly once. USD values are computed **inline** from the same block's `tokenPriceUpdates` (the chronologically-last update wins on duplicate keys), no separate price join at read time. ### Destination | Destination | Table | By-pair access | | ---------------------------- | -------------------- | ---------------------------------------------------------------------------------------- | | **ClickHouse** (first-class) | `fact_swaps_by_pair` | Prefix scan on `(chain_id, pool_address, event_ts, …)`; read with `FINAL` or `sum(sign)` | | **Postgres** | `swaps` | Index lead `(pool_address, block_number DESC)` | | **MySQL** | `swaps` | Index lead `(pool_address, block_number)` | ClickHouse uses the collapsing log-table pattern (see the [templates overview](/data-feeds/templates/overview#destinations)) so chain reorganizations self-correct, the `+1/−1` reorg pair for a trade shares an identical key and collapses cleanly. The fact table's sort key is pool-first, so a pair's full trade feed is a contiguous range read. ### Full schema The complete read table this template produces, one row per trade. Keep the columns you need and drop the rest (see [Schema & flexibility](/data-feeds/templates/overview#schema--flexibility)). Raw `uint256` amounts are stored as text in ClickHouse (they exceed numeric precision); USD columns are nullable so an unpriced token leaves them empty. ```sql theme={null} CREATE TABLE recipe_swaps_by_pair.fact_swaps_by_pair ( vendor_event_id String, ingested_at DateTime64(3), chain_id UInt32, block_hash String, block_number UInt64, event_ts DateTime64(3), pool_address String, -- pair (pool_fill) or aggregator token0_address String, token1_address String, amount0 String, -- raw uint256 amount1 String, -- raw uint256 trader_address String, price_usd Nullable(String), notional_usd Nullable(String), side LowCardinality(String), -- buy | sell source_kind LowCardinality(String), -- aggregator | pool_fill protocol LowCardinality(String), fee_amount Nullable(String), fee_token Nullable(String), fee_usd Nullable(String), sign Int8 ) ENGINE = ReplicatedCollapsingMergeTree( '/clickhouse/tables/{database}/fact_swaps_by_pair', '{replica}', sign) PARTITION BY (chain_id, toYYYYMM(event_ts)) ORDER BY (chain_id, pool_address, event_ts, vendor_event_id); ``` The `sign` column drives reorg collapsing, read with `FINAL` or `sum(sign)`, never a bare `WHERE sign=1`. A single-node setup can use `CollapsingMergeTree(sign)` without the replication path. ```sql theme={null} CREATE TABLE public.swaps ( position BIGINT NOT NULL, log_index BIGINT, -- NULL for aggregator rows transaction_index BIGINT, block_number BIGINT NOT NULL, block_timestamp BIGINT NOT NULL, -- unix seconds tx_hash TEXT NOT NULL, vendor_event_id TEXT NOT NULL, trader_address TEXT NOT NULL, pool_address TEXT NOT NULL, -- pair (pool_fill) or aggregator token0_address TEXT NOT NULL, token1_address TEXT NOT NULL, amount0 NUMERIC(76, 0) NOT NULL, amount1 NUMERIC(76, 0) NOT NULL, price_usd NUMERIC(38, 18), notional_usd NUMERIC(38, 18), side TEXT NOT NULL, source_kind TEXT NOT NULL, -- aggregator | pool_fill protocol TEXT NOT NULL, fee_amount NUMERIC(76, 0), fee_token TEXT, fee_usd NUMERIC(38, 18) ); -- By-pair access (the template's primary purpose). CREATE INDEX IF NOT EXISTS swaps_pool_block_idx ON public.swaps (pool_address, block_number DESC); -- Token + trader + block-range helpers. CREATE INDEX IF NOT EXISTS swaps_token0_block_idx ON public.swaps (token0_address, block_number DESC); CREATE INDEX IF NOT EXISTS swaps_token1_block_idx ON public.swaps (token1_address, block_number DESC); CREATE INDEX IF NOT EXISTS swaps_trader_block_idx ON public.swaps (trader_address, block_number DESC); CREATE INDEX IF NOT EXISTS swaps_block_idx ON public.swaps (block_number); ``` MySQL is the same shape with `DECIMAL(38,18)` for the USD columns. Amounts use `NUMERIC(76,0)` so large raw `uint256` balances never overflow. `position` is the block-level cursor used during backfill. ### Example reads All trades on a pool/pair, newest first (ClickHouse): ```sql theme={null} SELECT event_ts, side, source_kind, token0_address, token1_address, amount0, amount1, notional_usd, protocol FROM recipe_swaps_by_pair.fact_swaps_by_pair FINAL WHERE chain_id = 1 AND pool_address = lower('0x88e6A0c2dDD26FEEb64F039a2c41296FcB3f5640') ORDER BY event_ts DESC LIMIT 50; ``` 24h USD volume per pool (sign-aware, cheaper than `FINAL`): ```sql theme={null} SELECT pool_address, sumIf(toFloat64OrZero(notional_usd), sign = 1) - sumIf(toFloat64OrZero(notional_usd), sign = -1) AS volume_usd FROM recipe_swaps_by_pair.fact_swaps_by_pair WHERE chain_id = 1 AND event_ts >= now() - INTERVAL 1 DAY GROUP BY pool_address ORDER BY volume_usd DESC LIMIT 25; ``` Postgres / MySQL: ```sql theme={null} SELECT block_number, side, source_kind, token0_address, token1_address, amount0, amount1, notional_usd, protocol FROM swaps WHERE pool_address = lower('0x88e6...') ORDER BY block_number DESC LIMIT 50; ``` ### Modes Shipped defaults: **ClickHouse `hybrid`** (backfill → realtime), **Postgres / MySQL `historical`** (one-shot backfill). For live/reorg-safe ingestion use ClickHouse, see the [overview](/data-feeds/templates/overview#modes). The realtime reorg path needs a single-column `UNIQUE` on the position column, but `position` is block-level (many trades per block), so array-expanded trade rows can only carry a composite unique. Run realtime/hybrid on **ClickHouse** (the log table corrects reorgs per-block via the collapsing companion table); the Postgres / MySQL configs are intended for `historical` backfill. ### Multichain The template is chain-parametrized, point it at any supported EVM chain or Solana. On Solana, the same `logIndex` can be assigned to multiple events in one instruction, so for production the pool-fill event identity is widened with `(token0_address, token1_address, token0_amount)` to keep rows distinct; the trade feed it produces is identical in shape. ### How much data does this consume? This template's exact selection has not been measured end to end yet. The closest measured scope: on **Solana mainnet**, a continuous real-time feed of `tokenSwaps` plus `tokenPriceUpdates` (plus the negligible `launchpadEvents` slice) measured **\~250 GB** in a typical month and **\~330 GB** in a busy one (measured 2026-08-04, 30 sampled partitions, `metered` delivery, uncompressed bytes). This template additionally reads `aggregateTokenSwaps`, so expect a somewhat larger figure than that band. Three caveats: * **Real-time only.** A historical backfill is billed separately and is usually the larger number; its size depends entirely on how far back you go. * **Solana only.** Volume concentrates hard per chain, so this figure does not extrapolate to other chains. * **Filtering does not reduce it.** Restricting to specific pairs or tokens happens after the data is delivered. To reduce cost, narrow the **block range**, not the entity set. If you want your exact scope measured before you commit to a plan, [reach out](/data-feeds/early-access) and we will probe it against the live feed rather than estimate it. ### Fidelity gaps * **`price_usd` / `notional_usd` / `fee_usd` are `NULL`** when the relevant token had no in-block `tokenPriceUpdate` (illiquid or brand-new tokens). The template enriches only from same-block price updates, there is no cross-block carry-forward. * **`fee_*` is populated for pool fills only.** Aggregator-routed trades carry no per-fill fee, so `fee_amount` / `fee_token` / `fee_usd` are `NULL` on `source_kind='aggregator'` rows. * **One trade can appear under two `pool_address` values** when it is observed both as a direct pool fill (the pair address) and as part of an aggregator route (the aggregator address). These are distinct events with distinct `vendor_event_id`s by design, the two source arrays are disjoint per event. ## Migrating from the REST API This template supplies the volume leg of the token-pairs REST endpoint. That endpoint composes a pool registry with live numbers, so there is **no 1:1 replacement**, you land the pieces and join them in one view. Fidelity labels: **exact** = straight from the chain, **calculated** = derived from real DEX trades (very close, compare with a tolerance), **add yourself** = off-chain signal not in the feed. ### GET /erc20/:token\_address/pairs Lists every DEX pool that trades a token, with price, liquidity, and 24h volume per pool. The pool list itself comes through exactly from a purpose-built registry; the live figures are computed from the same onchain trades the old endpoint uses. This endpoint needs a **combination of templates**, not one. The pool list comes from the `pairAddressLookup` registry template (`pair_address_lookup_observations`, one row per pool with exchange, protocol, fee, and both tokens); this template's `swaps` table supplies the 24h volume; add [Pair Reserves](/data-feeds/templates/markets/pair-reserves) for liquidity, [Token Prices](/data-feeds/templates/token/token-prices) for `usd_price` and the 24h change, and [Token Metadata](/data-feeds/templates/token/token-metadata) for symbols and `pair_label`, then join them in one view. | `/erc20/:token_address/pairs` | Data Feeds | Fidelity | | ------------------------------------------------------------ | --------------------------------------------- | ------------ | | `pair_address`, `exchange_address` | `pair_address_lookup_observations.*` | exact | | `exchange_name` | `.protocol` | exact | | `base_token`, `quote_token` | `.token0_address` / `.token1_address` | exact | | `pair_label`, `token_name`, `token_symbol`, `token_decimals` | `token_metadata` / registry, derive the label | exact | | `usd_price`, `usd_price_24hr*` | `token_price_updates` | calculated | | `liquidity_usd`, `liquidity_skew` | `pair_reserves` × price | calculated | | `volume_24h_usd`, `volume_24h_native` | `sum(swaps.notional_usd)` over 24h | calculated | | `inactive_pair` | no trades in the window | calculated | | `exchange_logo`, `token_logo` | add yourself | add yourself | The composed view (Postgres, query it with `WHERE base_token = $1 OR quote_token = $1 ORDER BY liquidity_usd DESC NULLS LAST`): ```sql theme={null} CREATE VIEW token_pairs AS SELECT r.pair_address, r.exchange_address, r.protocol AS exchange_name, m0.symbol || '/' || m1.symbol AS pair_label, r.token0_address AS base_token, r.token1_address AS quote_token, p0.usd_price, (res.reserve0 / power(10, r.token0_decimals) * p0.usd_price) + (res.reserve1 / power(10, r.token1_decimals) * p1.usd_price) AS liquidity_usd, v.volume_24h_usd, (v.volume_24h_usd IS NULL) AS inactive_pair FROM pair_address_lookup_observations r LEFT JOIN token_metadata m0 ON m0.token_address = r.token0_address LEFT JOIN token_metadata m1 ON m1.token_address = r.token1_address LEFT JOIN pair_reserves res ON res.pair_address = r.pair_address LEFT JOIN LATERAL (SELECT usd_price FROM token_price_updates WHERE token_address = r.token0_address ORDER BY block_number DESC LIMIT 1) p0 ON true LEFT JOIN LATERAL (SELECT usd_price FROM token_price_updates WHERE token_address = r.token1_address ORDER BY block_number DESC LIMIT 1) p1 ON true LEFT JOIN LATERAL (SELECT sum(notional_usd) AS volume_24h_usd FROM swaps WHERE pool_address = r.pair_address AND block_timestamp >= extract(epoch FROM now()) - 86400) v ON true; ``` **Gotchas** * A token trades in **many** pools (fee tiers, DEXes), list them by matching either side of the registry, and sort by liquidity to surface the meaningful ones. * Backfill is mixed: the pool list needs history far enough back to see each pool's deployment (full history for completeness), while price, liquidity, and 24h volume only need `realtime` plus a 24-hour window. See [History & backfill](/data-feeds/concepts/history-and-backfill). * The volume join keys on the **pair address**, so it matches this template's `pool_fill` rows only, aggregator-routed fills carry the aggregator's address in `pool_address` (see [Fidelity gaps](#fidelity-gaps)). * The live figures track the old endpoint closely but come from a pricing computation, compare with a tolerance, not bit-for-bit. * `exchange_logo` and `token_logo` are off-chain images, layer them in from your own source keyed by address, or drop them. ### Related The same trade projection, keyed by token with one row per token side. Roll a pair's trades into candlesticks for charting. The use case this per-pair trade feed powers. Pool-level volume and trade flow for token analytics.