Backend: `raw_events` staging table grows unbounded — no TTL, no partitioning, sequential scan on cursor advance

[devJaja]

Summary

The raw_events table created in ensureSchema() (services/indexer/src/index.ts) has no TTL, no retention policy, and no partitioning. Every Soroban event ever observed is stored forever. At Stellar mainnet throughput (~100 events/ledger, ~1 ledger/5s), the table accumulates ~1.7 million rows/day. After ~6 months the table exceeds 300 M rows, sequential scans on the processed_at IS NULL filter degrade, and the UPDATE raw_events SET processed_at = NOW() WHERE ledger_sequence = $1 AND event_index = $2 loop — one UPDATE per event inside the commit transaction — causes severe write amplification.

Root Cause

// index.ts — ensureSchema()
CREATE TABLE IF NOT EXISTS raw_events (
  id              BIGSERIAL   NOT NULL,
  ledger_sequence BIGINT      NOT NULL,
  event_index     INT         NOT NULL,
  -- ...
  processed_at    TIMESTAMPTZ   -- NULL until pipeline stamps it
)

Issues:

1. No processed_at index → full table scan to find unprocessed rows after a crash.
2. N individual UPDATE statements inside the batch transaction (one per event) instead of a single bulk update.
3. No partition pruning — the entire historical table is scanned on every gap-detection query.
4. No archival or eviction of rows where processed_at IS NOT NULL and ledger_sequence < cursor - retention_window.

Impact

• Table bloat degrades indexer throughput non-linearly over time.
• Crash recovery (re-scan for unprocessed rows) becomes increasingly slow.
• PostgreSQL VACUUM struggles to reclaim space from the high UPDATE churn on processed_at.

Fix

1. Partition by ledger range (monthly or by 1M-ledger buckets) so old partitions can be dropped atomically.
2. Bulk update processed_at in a single statement:

   UPDATE raw_events SET processed_at = NOW()
   WHERE (ledger_sequence, event_index) = ANY($1::record[])

3. Add index CREATE INDEX ON raw_events (processed_at) WHERE processed_at IS NULL.
4. Retention policy: a scheduled job (or partition drop) removes fully-processed rows older than a configurable retention window (default 30 days).
5. Document the migration path for existing deployments that already have a monolithic raw_events table.

Acceptance Criteria

• raw_events is range-partitioned by ledger sequence (monthly cadence or equivalent).
• Partial index on processed_at IS NULL exists and is used by the EXPLAIN plan for crash-recovery queries.
• Batch processed_at stamp is a single bulk UPDATE, not N individual UPDATEs.
• A pg_cron or node-cron job drops/archives partitions older than the retention window.
• Migration script handles existing non-partitioned tables without data loss.
• Load test confirms throughput is stable at 10k events/s after 1 M rows.

[retkatmun]

Hi Maintainer,

I’m a smart contract and full-stack developer and would love to contribute to this issue. I’m an experienced contributor with hands-on expertise in Rust, Soroban, Cairo, and TypeScript.

I’m passionate about building secure, scalable blockchain solutions, with a strong emphasis on writing clean and maintainable code. Based on the scope of this issue, I’m confident I can deliver a complete solution within 24 hours of assignment.

I’d greatly appreciate the opportunity to contribute and help move this forward.

[grantfox-oss]

🦊 GrantFox — @retkatmun has been assigned to this issue as part of the Third Campaign campaign!

Next steps:

1. Open a Pull Request referencing this issue (e.g., Closes #1173)
2. Your PR will be reviewed by the Epta-Node maintainers

Good luck! Track your progress on GrantFox.