Real-Time Database Change Broadcast
Design a service that broadcasts PostgreSQL changes to 10,000 concurrent subscribers per project.
Problem statement
For this exercise, assume each project owns a PostgreSQL database and clients subscribe to authorized table, operation, and row filters. A logical replication reader converts WAL changes into ordered project events and broadcasts them to as many as 10,000 concurrent WebSocket subscribers.
Clients reconnect with cursors, slow consumers are isolated, schema changes are explicit, and tenant data never crosses projects. Exclude PostgreSQL's internal replication implementation.
What to build
- Authenticate and authorize a filtered project subscription and issue a resumable cursor.
- Read and durably checkpoint logical changes in commit order with schema metadata.
- Fan out eligible changes, isolate slow clients, and resume within the supported retention window.
At scale
- Support 10,000 concurrent subscribers and 50,000 row changes per second for one hot project.
- Deliver healthy-client events at p95 below 500 ms from database commit.
- Retain replayable project events for 15 minutes and expose when a cursor is too old.
- Preserve commit order within a project while allowing independent projects to scale separately.