[Backend] Database Read Replicas & Connection Pooling Optimization with PgBouncer

[blurbeast]

Overview & Background

As event volume and active stream queries grow, database connection limits and read query contention can degrade API latency. Setting up explicit read/write splitting in Prisma along with connection pooling optimization ensures backend responsiveness during high-traffic streaming volume.

---

Technical Specification & Architecture

1. Read/Write Splitting with Prisma:
   • Configure Prisma read-replica extension (@prisma/extension-read-replicas) in backend/src/lib/prisma.ts.
   • Direct write mutations (indexer worker, user creations) to primary DB instance.
   • Route high-volume read endpoints (/streams, /events, /summary) to read replicas.
2. Connection Pool Tuning:
   • Tune pg.Pool parameters in backend/src/lib/pg-pool.ts:
     • Max connections per container: 20
     • Idle timeout: 10,000ms
     • Connection timeout: 2,000ms
3. Docker Compose & Production Setup:
   • Include PgBouncer sidecar configuration in docker-compose.yml.

---

Target Files

• backend/src/lib/prisma.ts
• backend/src/lib/pg-pool.ts
• docker-compose.yml

---

Acceptance Criteria

• Prisma routes read queries to read replica when DATABASE_READ_REPLICA_URL is configured.
• Eliminates connection exhaustion under load tests (1,000 concurrent requests).
• Graceful fallback to primary DB when replica is unavailable.

[jchinedu]

let me work on this issue

[Ranjeet2063]

Hi @maintainers, I am ready to resolve this!

Execution Plan for [Backend] Database Read Replicas & Connection Pooling Optimization with PgBouncer:

• Backend Implementation: Structure [Backend] Database Read Replicas & Connection Pooling Optimization with PgBouncer with strict input validation, idempotent handlers, and proper error codes.
• Integration Tests: Provide automated tests covering both valid payloads and failure scenarios.

Why assign me?

• Proven Proof-of-Work: #1 contributor on Mova Store (28 merged Web3 PRs) and official ElizaOS contributor.
• Fast Turnaround: Dedicated to shipping a tested, production-grade PR within 12–24 hours.
• Zero Maintainer Burden: Self-documenting code with green CI and tests included so you don't spend time debugging.

Ready to deliver within 12–24h—please feel free to assign it to me!