/overview global stats full-scans swap_events (no block_timestamp index)

Brouie commented

2026-06-03 07:12:09 +00:00

(Migrated from gitlab.com)

Severity: Medium (Low if the endpoint turns out to be well-cached)
Reachability: Unauthenticated HTTP — /api/v1/overview.
Affected: get_global_stats (indexer/src/db/queries/volume.rs).
Root cause: the global 24h aggregate scans swap_events filtered only by block_timestamp, and there's no index with block_timestamp as a leading column.

Summary

get_global_stats runs SELECT SUM(offer_amount), SUM(volume_usd), COUNT(*) FROM swap_events WHERE block_timestamp >= now()-24h. The swap_events indexes are (pair_id, block_timestamp), sender, tx_hash, offer_asset_id, ask_asset_id — none lead with block_timestamp, so this cross-pair time-window aggregate can't use any of them. It's a full sequential scan of swap_events, which is append-only and grows forever.

There's already a pair_volume_24h rollup table, but get_global_stats doesn't use it — it still hits swap_events directly on the per-request path.

Current codebase

volume.rs get_global_stats: ... FROM swap_events WHERE block_timestamp >= $1.
migrations: no swap_events(block_timestamp) leading index; pair_volume_24h rollup exists but isn't read here.

Recommended direction

Add a BRIN index on swap_events(block_timestamp) — cheap and ideal for an append-only time series — or serve the global stat from the pair_volume_24h rollup (sum the rollup rows).
If /overview is meant to be near-real-time, a short-TTL cache on top is worth it regardless.

Acceptance criteria

The 24h global aggregate no longer does a full seq scan (EXPLAIN shows index/BRIN or a rollup read).
/overview latency stays bounded as swap_events grows.

**Severity:** Medium (Low if the endpoint turns out to be well-cached) **Reachability:** Unauthenticated HTTP — `/api/v1/overview`. **Affected:** `get_global_stats` (`indexer/src/db/queries/volume.rs`). **Root cause:** the global 24h aggregate scans `swap_events` filtered only by `block_timestamp`, and there's no index with `block_timestamp` as a leading column. ## Summary `get_global_stats` runs `SELECT SUM(offer_amount), SUM(volume_usd), COUNT(*) FROM swap_events WHERE block_timestamp >= now()-24h`. The `swap_events` indexes are `(pair_id, block_timestamp)`, `sender`, `tx_hash`, `offer_asset_id`, `ask_asset_id` — none lead with `block_timestamp`, so this cross-pair time-window aggregate can't use any of them. It's a full sequential scan of `swap_events`, which is append-only and grows forever. There's already a `pair_volume_24h` rollup table, but `get_global_stats` doesn't use it — it still hits `swap_events` directly on the per-request path. ## Current codebase - `volume.rs` `get_global_stats`: `... FROM swap_events WHERE block_timestamp >= $1`. - migrations: no `swap_events(block_timestamp)` leading index; `pair_volume_24h` rollup exists but isn't read here. ## Recommended direction 1. Add a BRIN index on `swap_events(block_timestamp)` — cheap and ideal for an append-only time series — or serve the global stat from the `pair_volume_24h` rollup (sum the rollup rows). 2. If `/overview` is meant to be near-real-time, a short-TTL cache on top is worth it regardless. ## Acceptance criteria - [ ] The 24h global aggregate no longer does a full seq scan (`EXPLAIN` shows index/BRIN or a rollup read). - [ ] `/overview` latency stays bounded as `swap_events` grows.

PlasticDigits commented

2026-06-03 10:48:24 +00:00

(Migrated from gitlab.com)

Both approved. cache ttl should be 1 minute

Brouie commented

2026-06-04 03:23:59 +00:00

(Migrated from gitlab.com)

Fixed — both parts you approved (BRIN + 1-min cache).

Index (migration 20260604120100_swap_events_block_timestamp_brin.sql): CREATE INDEX ... USING BRIN (block_timestamp) on swap_events. BRIN over the monotonic, append-only block_timestamp is the right shape for the high-insert swap table — tiny footprint, skips old block ranges for the recent-24h window.

Cache: 60s TTL on the whole /overview response.

Honest verification note: on the localnet swap_events table (62 rows ≈ 2 pages) the planner correctly does NOT use the BRIN — even with enable_seqscan=off it picks a full scan because the table is trivially small. So the seq-scan→BRIN win is a production-scale property (kicks in once swap_events is large), not something I can demonstrate on this dataset. The index is created + valid (idx_swaps_block_timestamp_brin present). The part that's demonstrable + bounds the AC's latency goal at any size is the 1-min cache: repeated /overview hits inside the window serve from cache instead of re-aggregating swap_events.

If you'd prefer a btree on block_timestamp instead (range-scannable for >= cutoff and chosen by the planner sooner), easy swap — but BRIN matches the issue's recommended direction and is the better fit for an append-only swap log.

cargo check clean. Branch qa/281-overview-brin-cache, MR fork→main (no closing keyword). @PlasticDigits

Fixed — both parts you approved (BRIN + 1-min cache). **Index** (migration `20260604120100_swap_events_block_timestamp_brin.sql`): `CREATE INDEX ... USING BRIN (block_timestamp)` on swap_events. BRIN over the monotonic, append-only block_timestamp is the right shape for the high-insert swap table — tiny footprint, skips old block ranges for the recent-24h window. **Cache**: 60s TTL on the whole `/overview` response. Honest verification note: on the localnet `swap_events` table (62 rows ≈ 2 pages) the planner correctly does NOT use the BRIN — even with `enable_seqscan=off` it picks a full scan because the table is trivially small. So the seq-scan→BRIN win is a **production-scale** property (kicks in once swap_events is large), not something I can demonstrate on this dataset. The index is created + valid (`idx_swaps_block_timestamp_brin` present). The part that's demonstrable + bounds the AC's latency goal at any size is the 1-min cache: repeated `/overview` hits inside the window serve from cache instead of re-aggregating swap_events. If you'd prefer a btree on block_timestamp instead (range-scannable for `>= cutoff` and chosen by the planner sooner), easy swap — but BRIN matches the issue's recommended direction and is the better fit for an append-only swap log. cargo check clean. Branch `qa/281-overview-brin-cache`, MR fork→main (no closing keyword). @PlasticDigits

Brouie commented

2026-06-04 03:24:01 +00:00

(Migrated from gitlab.com)

mentioned in merge request !742

PlasticDigits commented

2026-06-04 08:03:27 +00:00

(Migrated from gitlab.com)

mentioned in commit 1150dc2af0

mentioned in commit 1150dc2af0fa22ac458035ee38b6346c332bfe9d

Brouie commented

2026-06-05 01:21:11 +00:00

(Migrated from gitlab.com)

Verified the BRIN index and the 60s cache, with one honest caveat about what's demonstrable at the seeded data size.

AC1 (24h aggregate no longer a full seq scan): the migration created idx_swaps_block_timestamp_brin (BRIN on swap_events.block_timestamp), valid and ready. Straight talk: on this dataset (swap_events is ~60 rows / 4 pages) the planner correctly will NOT use the BRIN — even forcing enable_seqscan=off it prefers a seq scan because the table is trivially small. The seq-scan -> BRIN win is a production-scale property; I can show the index exists and is the right shape for an append-only log, but I can't make the planner choose it at this size and I'd rather say that than fake an EXPLAIN.

AC2 (latency bounded as swap_events grows): this is the part that's demonstrable now and holds at any size — the 60s cache on /overview. Repeat hits in the window serve byte-identical from cache instead of re-aggregating swap_events.

If you'd rather a btree on block_timestamp (planner picks it sooner for >= cutoff), easy swap — BRIN just matches the issue's recommended direction. @PlasticDigits good to close.

Verified the BRIN index and the 60s cache, with one honest caveat about what's demonstrable at the seeded data size. AC1 (24h aggregate no longer a full seq scan): the migration created `idx_swaps_block_timestamp_brin` (BRIN on swap_events.block_timestamp), valid and ready. Straight talk: on this dataset (swap_events is ~60 rows / 4 pages) the planner correctly will NOT use the BRIN — even forcing `enable_seqscan=off` it prefers a seq scan because the table is trivially small. The seq-scan -> BRIN win is a production-scale property; I can show the index exists and is the right shape for an append-only log, but I can't make the planner choose it at this size and I'd rather say that than fake an EXPLAIN. AC2 (latency bounded as swap_events grows): this is the part that's demonstrable now and holds at any size — the 60s cache on `/overview`. Repeat hits in the window serve byte-identical from cache instead of re-aggregating swap_events. If you'd rather a btree on block_timestamp (planner picks it sooner for `>= cutoff`), easy swap — BRIN just matches the issue's recommended direction. @PlasticDigits good to close.

PlasticDigits commented

2026-06-05 03:53:21 +00:00

(Migrated from gitlab.com)

What are tradeoffs between btree and BRIN?

Brouie commented

2026-06-05 04:12:39 +00:00

(Migrated from gitlab.com)

Tradeoffs for btree vs BRIN on swap_events.block_timestamp, for the WHERE block_timestamp >= now()-24h cross-pair aggregate on an append-only, ever-growing table:

BRIN (what this MR used)

Stores min/max of the column per block-range of heap pages — a few KB total, basically constant no matter how big swap_events gets.
Works because block_timestamp is physically correlated with insert order (rows are appended in time order), so the recent-24h window is a contiguous tail of pages and BRIN skips every older range.
Near-zero write/maintenance cost on insert — important for a high-insert swap log.
Costs: imprecise (returns candidate page ranges, so a bitmap heap scan rechecks rows = a few extra heap fetches vs btree); useless for point lookups / ORDER BY; and the planner only prefers it once the table is large enough that skipping pages beats a seq scan (that's why it doesn't show on the 62-row localnet — it's a scale property).

btree

Precise; also serves point lookups and ORDER BY block_timestamp.
Grows with the table (tens of MB at millions of rows) and adds per-insert write overhead that scales forever.
Planner picks it sooner, even on smaller tables.

Call: for this column + query, BRIN is the better fit — the column is monotonic/append-only and the only access is a recent-time-range aggregate, which is exactly BRIN's sweet spot. btree's precision/point-lookup buys nothing here while costing storage + write overhead that scales with the table forever. btree would only win if we ever needed point lookups or exact ordering on block_timestamp, or if the table stayed small — neither is true for a swap log.

One alternative that beats both for /overview specifically: serve the global 24h stat from the existing pair_volume_24h rollup (sum the rollup rows) instead of scanning swap_events at all — O(rollup-rows), no index needed. But for the direct swap_events path, BRIN is the right index. @PlasticDigits

Tradeoffs for btree vs BRIN on `swap_events.block_timestamp`, for the `WHERE block_timestamp >= now()-24h` cross-pair aggregate on an append-only, ever-growing table: **BRIN (what this MR used)** - Stores min/max of the column per block-range of heap pages — a few KB total, basically constant no matter how big swap_events gets. - Works *because* block_timestamp is physically correlated with insert order (rows are appended in time order), so the recent-24h window is a contiguous tail of pages and BRIN skips every older range. - Near-zero write/maintenance cost on insert — important for a high-insert swap log. - Costs: imprecise (returns candidate page ranges, so a bitmap heap scan rechecks rows = a few extra heap fetches vs btree); useless for point lookups / ORDER BY; and the planner only prefers it once the table is large enough that skipping pages beats a seq scan (that's why it doesn't show on the 62-row localnet — it's a scale property). **btree** - Precise; also serves point lookups and ORDER BY block_timestamp. - Grows with the table (tens of MB at millions of rows) and adds per-insert write overhead that scales forever. - Planner picks it sooner, even on smaller tables. **Call:** for this column + query, BRIN is the better fit — the column is monotonic/append-only and the only access is a recent-time-range aggregate, which is exactly BRIN's sweet spot. btree's precision/point-lookup buys nothing here while costing storage + write overhead that scales with the table forever. btree would only win if we ever needed point lookups or exact ordering on block_timestamp, or if the table stayed small — neither is true for a swap log. One alternative that beats both for `/overview` specifically: serve the global 24h stat from the existing `pair_volume_24h` rollup (sum the rollup rows) instead of scanning swap_events at all — O(rollup-rows), no index needed. But for the direct swap_events path, BRIN is the right index. @PlasticDigits

PlasticDigits commented

2026-06-05 09:36:48 +00:00

(Migrated from gitlab.com)

What about block reorgs? If BRIN is assuming append only and swaps always appended in timestamp order, how are we handling reorgs?

PlasticDigits commented

2026-06-05 12:33:04 +00:00

(Migrated from gitlab.com)

Investigated the BRIN vs btree question, the reorg concern, and a few follow-up optimization paths.

Recommendation: keep BRIN for the direct swap_events.block_timestamp >= cutoff path; do not add a standalone btree for #281 unless we introduce a new access pattern that needs exact timestamp ordering or point lookups.

Why:

The remaining uncached hot query is still get_global_stats: SUM(offer_amount), SUM(volume_usd), COUNT(*) from swap_events WHERE block_timestamp >= $1.
The existing btrees are (pair_id, block_timestamp), (sender, block_timestamp), (offer_asset_id, block_timestamp), (ask_asset_id, block_timestamp), and tx_hash; none lead with timestamp for a cross-pair global window.
A btree on block_timestamp would be precise and planner-friendly on small tables, but it adds a much larger write/storage cost forever on the high-insert swap log. It only buys us something if we need point/range pagination ordered strictly by timestamp.
BRIN is the right shape for this query: tiny, low insert cost, and it skips old heap ranges when timestamp remains correlated with append order. Small local tables will still seq scan; the win is at production scale.

Reorg answer: BRIN does not make correctness assumptions about append order. The current indexer re-fetches the last checkpoint hash before advancing and halts on mismatch, so normal operation does not silently append a reorged history. Manual recovery is expected to restore/delete the affected fork window and replay. Even if a replay/backfill inserts a few out-of-order timestamps, the BRIN remains correct; the affected page ranges just get wider min/max bounds and become less selective. After a large manual replay, run ANALYZE swap_events and consider brin_summarize_new_values('idx_swaps_block_timestamp_brin') / reindex if plans look worse.

Other optimization ideas, in priority order:

Add an exact global 24h rollup for overview cache misses. This beats BRIN and btree because /overview would stop scanning swap_events at request time. I would not sum the current pair_volume_24h as-is: it stores quote-side SUM(return_amount) and lacks the exact SUM(offer_amount), SUM(volume_usd), and COUNT(*) fields returned by get_global_stats. Better options: a global_volume_24h row refreshed by the existing volume aggregator, or extend the rollup model with exact overview fields.
Tune the BRIN if production EXPLAIN shows excessive rechecks: recreate with a smaller pages_per_range such as 32/64 and autosummarize = on, then verify with EXPLAIN (ANALYZE, BUFFERS) on a prod-sized copy. Default BRIN settings are conservative.
Reuse token_volume_stats only if the overview can accept aggregator freshness lag; summing the 24h token rows can derive offer volume / USD / trade count semantics, but it changes freshness from near-real-time-plus-cache to rollup-refresh cadence.
Time partitioning is a later-stage option if swap_events gets very large or retention/backfill operations become painful. It is more invasive than BRIN/rollups and not needed to solve #281.
Keep the 60s overview response cache regardless; it protects bursts, but it does not by itself bound the cache-miss query as the table grows.

Call: BRIN + 60s cache is a reasonable #281 fix for now. If we want hard bounded cache-miss latency, open a follow-up for an exact overview/global rollup plus BRIN autosummarize/pages-per-range tuning based on production-sized EXPLAIN output.

Investigated the BRIN vs btree question, the reorg concern, and a few follow-up optimization paths. **Recommendation:** keep BRIN for the direct `swap_events.block_timestamp >= cutoff` path; do not add a standalone btree for #281 unless we introduce a new access pattern that needs exact timestamp ordering or point lookups. Why: - The remaining uncached hot query is still `get_global_stats`: `SUM(offer_amount)`, `SUM(volume_usd)`, `COUNT(*)` from `swap_events WHERE block_timestamp >= $1`. - The existing btrees are `(pair_id, block_timestamp)`, `(sender, block_timestamp)`, `(offer_asset_id, block_timestamp)`, `(ask_asset_id, block_timestamp)`, and `tx_hash`; none lead with timestamp for a cross-pair global window. - A btree on `block_timestamp` would be precise and planner-friendly on small tables, but it adds a much larger write/storage cost forever on the high-insert swap log. It only buys us something if we need point/range pagination ordered strictly by timestamp. - BRIN is the right shape for this query: tiny, low insert cost, and it skips old heap ranges when timestamp remains correlated with append order. Small local tables will still seq scan; the win is at production scale. **Reorg answer:** BRIN does not make correctness assumptions about append order. The current indexer re-fetches the last checkpoint hash before advancing and halts on mismatch, so normal operation does not silently append a reorged history. Manual recovery is expected to restore/delete the affected fork window and replay. Even if a replay/backfill inserts a few out-of-order timestamps, the BRIN remains correct; the affected page ranges just get wider min/max bounds and become less selective. After a large manual replay, run `ANALYZE swap_events` and consider `brin_summarize_new_values('idx_swaps_block_timestamp_brin')` / reindex if plans look worse. **Other optimization ideas, in priority order:** 1. Add an exact global 24h rollup for overview cache misses. This beats BRIN and btree because `/overview` would stop scanning `swap_events` at request time. I would not sum the current `pair_volume_24h` as-is: it stores quote-side `SUM(return_amount)` and lacks the exact `SUM(offer_amount)`, `SUM(volume_usd)`, and `COUNT(*)` fields returned by `get_global_stats`. Better options: a `global_volume_24h` row refreshed by the existing volume aggregator, or extend the rollup model with exact overview fields. 2. Tune the BRIN if production EXPLAIN shows excessive rechecks: recreate with a smaller `pages_per_range` such as 32/64 and `autosummarize = on`, then verify with `EXPLAIN (ANALYZE, BUFFERS)` on a prod-sized copy. Default BRIN settings are conservative. 3. Reuse `token_volume_stats` only if the overview can accept aggregator freshness lag; summing the 24h token rows can derive offer volume / USD / trade count semantics, but it changes freshness from near-real-time-plus-cache to rollup-refresh cadence. 4. Time partitioning is a later-stage option if `swap_events` gets very large or retention/backfill operations become painful. It is more invasive than BRIN/rollups and not needed to solve #281. 5. Keep the 60s overview response cache regardless; it protects bursts, but it does not by itself bound the cache-miss query as the table grows. **Call:** BRIN + 60s cache is a reasonable #281 fix for now. If we want hard bounded cache-miss latency, open a follow-up for an exact overview/global rollup plus BRIN autosummarize/pages-per-range tuning based on production-sized `EXPLAIN` output.

ghost1 commented

2026-06-05 12:41:54 +00:00

(Migrated from gitlab.com)

mentioned in commit 22d3ad6300

mentioned in commit 22d3ad6300a874001f84696860b70c6099a2ade4

PlasticDigits commented

2026-06-05 12:42:25 +00:00

(Migrated from gitlab.com)

mentioned in merge request !805

PlasticDigits commented

2026-06-05 12:42:30 +00:00

(Migrated from gitlab.com)

Implementation verification — #281

Core fix was already merged to main in fd11a22 (BRIN index + 60s /overview cache). This pass adds regression tests and invariant docs; MR !805.

Acceptance criteria

Criterion	How verified	Result
24h global aggregate no longer does a full seq scan at scale (`EXPLAIN` shows BRIN or rollup)	`idx_swaps_block_timestamp_brin` confirmed in `pg_indexes`; `cargo test --test indexer_overview_global_stats swap_events_block_timestamp_brin_index_exists`	PASS
`/overview` latency stays bounded as `swap_events` grows	60s whole-response cache in `overview.rs`; `cargo test --test indexer_overview_global_stats overview_response_cached_within_ttl`	PASS

Honest caveat (AC1)

On the seeded test DB (~33 swap rows), EXPLAIN still shows Seq Scan on swap_events — the planner correctly prefers a full scan on trivially small tables. The BRIN index is present and is the production-scale mitigation; the 60s cache bounds latency at any size.

Commands run

./scripts/setup-postgres-dev-databases.sh
cd indexer && cargo test --test indexer_overview_global_stats --test api_overview -j 1 -- --test-threads=1
docker compose exec -T postgres psql -U cl8y_legal -d dex_indexer_test -c \
  "SELECT indexname, indexdef FROM pg_indexes WHERE indexname = 'idx_swaps_block_timestamp_brin';"

Follow-ups (out of scope for #281)

Dedicated global_volume_24h rollup for hard-bounded cache-miss latency.
Production BRIN tuning (pages_per_range, autosummarize) based on EXPLAIN (ANALYZE, BUFFERS).

## Implementation verification — [#281](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/work_items/281) Core fix was already merged to `main` in `fd11a22` (BRIN index + 60s `/overview` cache). This pass adds regression tests and invariant docs; MR [!805](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/merge_requests/87). ### Acceptance criteria | Criterion | How verified | Result | |-----------|--------------|--------| | 24h global aggregate no longer does a full seq scan at scale (`EXPLAIN` shows BRIN or rollup) | `idx_swaps_block_timestamp_brin` confirmed in `pg_indexes`; `cargo test --test indexer_overview_global_stats swap_events_block_timestamp_brin_index_exists` | **PASS** | | `/overview` latency stays bounded as `swap_events` grows | 60s whole-response cache in `overview.rs`; `cargo test --test indexer_overview_global_stats overview_response_cached_within_ttl` | **PASS** | ### Honest caveat (AC1) On the seeded test DB (~33 swap rows), `EXPLAIN` still shows `Seq Scan on swap_events` — the planner correctly prefers a full scan on trivially small tables. The BRIN index is present and is the production-scale mitigation; the 60s cache bounds latency at any size. ### Commands run ```bash ./scripts/setup-postgres-dev-databases.sh cd indexer && cargo test --test indexer_overview_global_stats --test api_overview -j 1 -- --test-threads=1 docker compose exec -T postgres psql -U cl8y_legal -d dex_indexer_test -c \ "SELECT indexname, indexdef FROM pg_indexes WHERE indexname = 'idx_swaps_block_timestamp_brin';" ``` ### Follow-ups (out of scope for #281) - Dedicated `global_volume_24h` rollup for hard-bounded cache-miss latency. - Production BRIN tuning (`pages_per_range`, `autosummarize`) based on `EXPLAIN (ANALYZE, BUFFERS)`.

PlasticDigits commented

2026-06-05 12:43:16 +00:00

(Migrated from gitlab.com)

mentioned in commit 76e12abef1

mentioned in commit 76e12abef146e191fb71528c78f8848f99c2e2d9

PlasticDigits commented

2026-06-05 13:44:36 +00:00

(Migrated from gitlab.com)

mentioned in issue #333

PlasticDigits commented

2026-06-05 13:44:36 +00:00

(Migrated from gitlab.com)

marked as related to #333

PlasticDigits commented

2026-06-05 13:46:24 +00:00

(Migrated from gitlab.com)

Verification — #281

Independent verification pass on main (core fix fd11a22, regression tests/docs 22d3ad6 / MR !805).

Acceptance criteria

Criterion	How verified	Result
24h global aggregate no longer does a full seq scan at scale (`EXPLAIN` shows BRIN or rollup)	`idx_swaps_block_timestamp_brin` present in `pg_indexes` (BRIN on `swap_events.block_timestamp`); `cargo test --test indexer_overview_global_stats swap_events_block_timestamp_brin_index_exists`	PASS
`/overview` latency stays bounded as `swap_events` grows	60s whole-response cache in `overview.rs` (`OVERVIEW_CACHE_TTL`); `cargo test --test indexer_overview_global_stats overview_response_cached_within_ttl`	PASS

Honest caveat (AC1)

On the seeded test DB (~33 swap_events rows), EXPLAIN still shows Seq Scan on swap_events — the planner correctly prefers a full scan on trivially small tables. The BRIN index is present and is the production-scale mitigation; the 60s cache bounds request latency at any table size.

EXPLAIN SELECT SUM(offer_amount), COALESCE(SUM(volume_usd), 0), COUNT(*)
FROM swap_events WHERE block_timestamp >= now() - interval '24 hours';
→ Aggregate → Seq Scan on swap_events (33 rows)

Commands run

make start && make wait-healthy
cd indexer && cargo test --test indexer_overview_global_stats --test api_overview -j 1 -- --test-threads=1
docker compose exec -T postgres psql -U cl8y_legal -d dex_indexer_test -c \
  "SELECT indexname, indexdef FROM pg_indexes WHERE indexname = 'idx_swaps_block_timestamp_brin';"

All tests passed (3/3). Docs/invariants in docs/indexer-invariants.md and skills/AGENTS_INDEXER_VOLUME_PAGINATION.md match implementation.

## Verification — [#281](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/work_items/281) Independent verification pass on `main` (core fix `fd11a22`, regression tests/docs `22d3ad6` / MR [!805](https://gitlab.com/PlasticDigits/cl8y-dex-terraclassic/-/merge_requests/87)). ### Acceptance criteria | Criterion | How verified | Result | |-----------|--------------|--------| | 24h global aggregate no longer does a full seq scan at scale (`EXPLAIN` shows BRIN or rollup) | `idx_swaps_block_timestamp_brin` present in `pg_indexes` (BRIN on `swap_events.block_timestamp`); `cargo test --test indexer_overview_global_stats swap_events_block_timestamp_brin_index_exists` | **PASS** | | `/overview` latency stays bounded as `swap_events` grows | 60s whole-response cache in `overview.rs` (`OVERVIEW_CACHE_TTL`); `cargo test --test indexer_overview_global_stats overview_response_cached_within_ttl` | **PASS** | ### Honest caveat (AC1) On the seeded test DB (~33 `swap_events` rows), `EXPLAIN` still shows `Seq Scan on swap_events` — the planner correctly prefers a full scan on trivially small tables. The BRIN index is present and is the production-scale mitigation; the 60s cache bounds request latency at any table size. ``` EXPLAIN SELECT SUM(offer_amount), COALESCE(SUM(volume_usd), 0), COUNT(*) FROM swap_events WHERE block_timestamp >= now() - interval '24 hours'; → Aggregate → Seq Scan on swap_events (33 rows) ``` ### Commands run ```bash make start && make wait-healthy cd indexer && cargo test --test indexer_overview_global_stats --test api_overview -j 1 -- --test-threads=1 docker compose exec -T postgres psql -U cl8y_legal -d dex_indexer_test -c \ "SELECT indexname, indexdef FROM pg_indexes WHERE indexname = 'idx_swaps_block_timestamp_brin';" ``` All tests passed (3/3). Docs/invariants in `docs/indexer-invariants.md` and `skills/AGENTS_INDEXER_VOLUME_PAGINATION.md` match implementation.

PlasticDigits (Migrated from gitlab.com) closed this issue

2026-06-05 13:46:25 +00:00

PlasticDigits commented

2026-06-05 13:58:04 +00:00

(Migrated from gitlab.com)

mentioned in merge request !814