Paginate EVM metadata candidate selection for high-volume chains

[DenisCarriere]

Context

The EVM metadata candidate queries do a full-chain aggregation and anti-join each iteration, then pull up to 1,000,000 contracts into a single run. That is likely workable on smaller chains but much riskier on very high-volume chains like Base and BSC.

Why this looks plausible

• services/metadata/get_contracts_by_transfers.sql, get_contracts_by_swaps.sql, and get_contracts_by_balances.sql all:
  • aggregate the entire source table,
  • anti-join against metadata and metadata_errors,
  • sort globally by timestamp,
  • and return up to 1,000,000 rows in one pass.
• This increases query cost, memory pressure, and the amount of in-flight work a single iteration must finish before the next loop begins.
• On high-volume chains, one bad batch can keep the service from rolling forward for a very long time.

Proposed fix

• Process smaller batches per iteration with a configurable batch size.
• Introduce pagination or a resumable checkpoint so the next loop can continue from the prior cursor instead of rebuilding a massive one-shot batch.
• Consider a dedicated work queue/materialized view for unseen contracts on EVM chains.
• Preserve fairness so newly-seen contracts are not starved behind a giant historical backlog.

Acceptance criteria

• Each iteration has bounded query cost and bounded runtime.
• Progress survives restarts without needing to rebuild a million-row candidate set.
• High-volume networks continue making steady forward progress under load.

Relevant code

• services/metadata/get_contracts_by_transfers.sql
• services/metadata/get_contracts_by_swaps.sql
• services/metadata/get_contracts_by_balances.sql
• services/metadata/run.ts