[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