feat: Table partitioning for high-volume participant/event/audit tables

[joelpeace48-cell]

Background & current state

High-volume tables (participants, indexed events, audit logs) grow unbounded across many campaigns. Large monolithic tables degrade query latency and bloat indexes. No partitioning exists.

Goal

Partition high-volume tables (by campaign_id and/or time range) so hot queries use partition pruning and old data can be archived/dropped cheaply.

Technical design

• Postgres declarative partitioning: PARTITION BY LIST (campaign_id) or RANGE (created_at) for events/audit; automated partition creation for new ranges.
• Migrations to convert existing tables (create partitioned table, backfill, swap) with zero/low downtime.
• Validate plans with EXPLAIN ANALYZE (pruning confirmed).

Edge cases

• Existing data migration → online backfill + swap; document downtime.
• Cross-partition queries → ensure indexes support them.
• Partition explosion (too many campaigns) → use hash sub-partitioning or time-based for events.
• Retention: drop old time partitions instead of DELETE.

Task breakdown

• Choose partition keys per table; design migration.
• Migrations (create/backfill/swap) + auto-partition creation.
• Retention via partition drop.
• EXPLAIN-based pruning tests.

Acceptance criteria

• Hot queries use partition pruning (EXPLAIN confirms).
• Migration round-trips without data loss.

Testing & verification

• EXPLAIN ANALYZE assertions in tests; migration up/down round-trip.

Out of scope

• Replicas (NEW-034).

Dependencies / related

• Complements NEW-024 (events), NEW-034.

Difficulty: medium · Effort: M · infra + performance

[Kami-no-san]

@Kami-no-san has applied to work on this issue as part of the Stellar Wave Program's 6th wave.

Good day Maintainer,

i can implement a partitioning for high volume user data(participant/event/audit tables) to make sure user data are stored and can be cached cheaply.

ETA 3hrs - 4.5hrs

ℹ️ Repo Maintainers: To accept this application, review their application or assign @Kami-no-san to this issue.

[theo372001]

@theo372001 has applied to work on this issue as part of the Stellar Wave Program's 6th wave.

Hello maintainer please I would like to have a chance to fix this issue.

ℹ️ Repo Maintainers: To accept this application, review their application or assign @theo372001 to this issue.

[devjayy43]

@devjayy43 has applied to work on this issue as part of the Stellar Wave Program's 6th wave.

I would like to work on this. please assign

ℹ️ Repo Maintainers: To accept this application, review their application or assign @devjayy43 to this issue.

[drips-wave]

Congratulations, @devjayy43! 🎉 Your application was accepted by the repo's maintainers, and the issue is due on June 30, 2026.

🧑‍💻 @devjayy43: Please resolve the issue such that the repo's maintainers have enough time to review your contribution before the due date. You'll earn Points for completing the issue on-time, which will make you eligible for a share of the Stellar Wave Program's reward pool.

Warning

When opening a PR, please link it to this issue to ensure it gets tracked accurately. Points are awarded when this issue is marked as completed by the maintainer.

🤠 Repo maintainers: Please keep an eye on the contributor's progress and review their work before the due date. You can manage this issue, including adjusting its complexity and points, here.

🌊 Happy Wave 🌊