Architecture Review: Realtime Collaborative Editor
An architecture for multi-user document editing where users see each other's changes in near real-time. PostgreSQL provides durable state persistence, Redis coordinates ephemeral session state and pub/sub for live change propagation, and connection pooling protects the database from WebSocket-induced connection churn.
Evidence Confidence
Limited
moderate
Executive Summary
Realtime Collaborative Editor: weak operational readiness (72% evidence confidence). 1 architectural strength identified, 1 operational risk to manage. Primary concern: Connection Pool Exhaustion. Requires Expert Only operational maturity.
Readiness Rationale
Overall weak readiness across 8 dimensions. Weak: consistency, team maturity. Strong: operational, migration, failure recovery.
Key Concerns
- !Connection Pool Exhaustion
Key Strengths
- +A connection pool bounds the total database connections an application can open, preventing connection storms during traffic…
8
Assessments
3
Tradeoffs
6
Sections
10
Recommendations
Readiness Assessments
8Architectural Tradeoffs
3Recommendations
10Monitor: Connection Pool Exhaustion
risk_monitoringAll database connections in the pool are in use; new requests queue and then time out, causing cascading latency and errors across all dependent services.
Affects 1 node. (Redis). 1 mitigation identified
Implement: Monitor connection pressure signals
observabilitySeed 'Connection Pool Pressure Under Load' identifies 4 metrics relevant to connection_exhaustion. Execution preview confirms this risk manifests under modelled load.
Metrics to instrument: active_connections, connection_wait_time_ms, p95_latency_ms
Short-polling API with version-based conflict detection → WebSocket + Redis pub/sub live propagation
migration_planningTrigger: User-visible edit conflicts > 5% of sessions; poll interval causes noticeable latency in collaborative sessions. Migrate from 'Short-polling API with version-based conflict detection' to 'WebSocket + Redis pub/sub live propagation'. Short-polling is a valid starting point for small-scale collaboration. The migration to WebSocket is straightforward in concept but adds operational surface that should not be underestimated.
WebSocket infrastructure more complex to operate than HTTP API; Reconnection logic must handle temporary disconnects gracefully
Last-write-wins conflict resolution → Operational transformation (OT) or CRDT-based conflict resolution
migration_planningTrigger: Data loss complaints from users editing simultaneously; conflict rate measurably degrading user experience. Migrate from 'Last-write-wins conflict resolution' to 'Operational transformation (OT) or CRDT-based conflict resolution'. Automerge, Yjs, and similar open-source CRDT libraries are the practical path. Building OT or CRDT from scratch is rarely justified outside specialized contexts.
OT and CRDT implementations are notoriously complex to implement correctly; Existing data format may be incompatible with CRDT encoding
Prepare runbook for: Burst Traffic Cold Cache Stampede
simulation_preparednessSimulation demonstrates critical degradation of redis, postgresql
Without a runbook, recovery from this failure mode will be ad-hoc
Prepare runbook for: Connection Pool Exhaustion with Horizontal User Scale
simulation_preparednessSimulation demonstrates critical degradation of postgresql
Without a runbook, recovery from this failure mode will be ad-hoc
Plan evolution: Single Cache Layer → Distributed Cache
evolution_planningEvolution from Single Redis Node / Sentinel Cluster → Distributed Redis Cluster (Consistent Hash Ring)
Migration complexity: medium. Rollback: complex.
Plan evolution: Direct DB Queries → CQRS Read Models
evolution_planningEvolution from Unified Read/Write Database → CQRS with Separate Read Projections
Migration complexity: high. Rollback: complex.
Monitor threshold: Tier 1: WebSocket Connection Ceiling
scaling_monitoringSignal: Server memory growing with active connections; file descriptor limits approached; new WebSocket connections refused
Bottleneck: WebSocket server process connection limit or OS file descriptor ceiling. Evolution: Increase file descriptor limits (ulimit); move to dedicated WebSocket server tier; implement connection multiplexing (multiple documents per connection where safe)
Monitor threshold: Tier 2: Database Write Contention
scaling_monitoringSignal: Consecutive writes to the same document causing lock contention; write latency rising; auto-save batching queue depth increasing
Bottleneck: High-frequency auto-save operations conflicting at the document row level; row-level locking under concurrent user edits . Evolution: Move to operational transformation or CRDT-based conflict resolution; batch writes and resolve conflicts in-process before database commit; consider append-only event log for document operations
Scaling Pressure Signals
8Server memory growing with active connections; file descriptor limits approached; new WebSocket connections refused
Threshold
Tier 1: WebSocket Connection Ceiling
Likely Bottleneck
WebSocket server process connection limit or OS file descriptor ceiling
Recommended Evolution
Increase file descriptor limits (ulimit); move to dedicated WebSocket server tier; implement connection multiplexing (multiple documents per connection where safe)
Consecutive writes to the same document causing lock contention; write latency rising; auto-save batching queue depth increasing
Threshold
Tier 2: Database Write Contention
Likely Bottleneck
High-frequency auto-save operations conflicting at the document row level; row-level locking under concurrent user edits
Recommended Evolution
Move to operational transformation or CRDT-based conflict resolution; batch writes and resolve conflicts in-process before database commit; consider append-only event log for document operations
Redis memory growing; high number of active pub/sub channels per Redis instance; SUBSCRIBE/UNSUBSCRIBE operations becoming significant overhead
Threshold
Tier 3: Redis Channel Explosion
Likely Bottleneck
One pub/sub channel per active document multiplied by active users
Recommended Evolution
Shard Redis pub/sub by document range; implement channel expiry; consider dedicated messaging tier (e.g. Ably, Pusher) for very high session counts
Server memory growing with active connections; file descriptor limits approached; new WebSocket connections refused
Threshold
Escalation trigger: WebSocket server process connection limit or OS file descriptor ceiling
Likely Bottleneck
Tier 1: WebSocket Connection Ceiling
Recommended Evolution
Monitor: active_connections, connection_wait_time_ms, p95_latency_ms
Consecutive writes to the same document causing lock contention; write latency rising; auto-save batching queue depth increasing
Threshold
Escalation trigger: High-frequency auto-save operations conflicting at the document row level; row-level locking under concurrent user edits
Likely Bottleneck
Tier 2: Database Write Contention
Recommended Evolution
Monitor: active_connections, connection_wait_time_ms, p95_latency_ms
Redis memory growing; high number of active pub/sub channels per Redis instance; SUBSCRIBE/UNSUBSCRIBE operations becoming significant overhead
Threshold
Escalation trigger: One pub/sub channel per active document multiplied by active users
Likely Bottleneck
Tier 3: Redis Channel Explosion
Recommended Evolution
Monitor: active_connections, connection_wait_time_ms, p95_latency_ms
Connection pool at 80% utilisation : approaching saturation (16 of 20)
Threshold
Escalation trigger: Value reaches 15.0 (current critical threshold for active connections)
Likely Bottleneck
Rising active connections
Recommended Evolution
Monitor: active_connections, connection_wait_time_ms, p95_latency_ms
Connection wait time 83ms: exceeds 50ms warning threshold
Threshold
Escalation trigger: Value reaches 50.0 (current critical threshold for connection wait time ms)
Likely Bottleneck
Rising connection wait time ms
Recommended Evolution
Monitor: connection_wait_time_ms, active_connections, p95_latency_ms
Migration Readiness
12Migration Stages
2Short-polling API with version-based conflict detection → WebSocket + Redis pub/sub live propagation
infoMigration trigger: User-visible edit conflicts > 5% of sessions; poll interval causes noticeable latency in collaborative sessions
Last-write-wins conflict resolution → Operational transformation (OT) or CRDT-based conflict resolution
infoMigration trigger: Data loss complaints from users editing simultaneously; conflict rate measurably degrading user experience
Risks
10WebSocket infrastructure more complex to operate than HTTP A
warningWebSocket infrastructure more complex to operate than HTTP API
Reconnection logic must handle temporary disconnects gracefu
warningReconnection logic must handle temporary disconnects gracefully
OT and CRDT implementations are notoriously complex to imple
warningOT and CRDT implementations are notoriously complex to implement correctly
Existing data format may be incompatible with CRDT encoding
warningProjection lag creates a read-after-write window where users
criticalProjection lag creates a read-after-write window where users see stale data after their own writes. Mitigation: Route immediate post-write reads to the write store (session-scoped write token); accept eventual consistency only for non-user-initiated reads
↗ direct-db-to-cqrs
Projection rebuild after schema change can take hours or day
criticalProjection rebuild after schema change can take hours or days on large datasets. Mitigation: Design blue/green projection deployment: build new projection in parallel before switching traffic; test rebuild time in staging
↗ direct-db-to-cqrs
Missing partition for current time window causes all INSERTs
criticalMissing partition for current time window causes all INSERTs to fail with 'no partition of relation found'. Mitigation: Create partitions 7-30 days in advance; alert when next partition does not exist before its time window opens
↗ postgresql-to-partitioned
Historical data migration batch failures can leave partial d
criticalHistorical data migration batch failures can leave partial data in partitioned table. Mitigation: Validate row counts and checksums per partition before dropping old table; keep old table for 30+ days after cutover
↗ postgresql-to-partitioned
Read-after-write violations are invisible to monitoring but
criticalRead-after-write violations are invisible to monitoring but visible to users: 'my change disappeared'. Mitigation: Track write LSN per user session; route reads to primary until replica confirms that LSN; accept primary load increase
↗ single-region-to-multi-region
Replica promotion during primary region failure requires man
criticalReplica promotion during primary region failure requires manual intervention and causes data loss if replication lag is high. Mitigation: Document and test failover runbook quarterly; set maximum acceptable replication lag before automatic failover is blocked
↗ single-region-to-multi-region
Review Sections
6Referenced Intelligence