March 10, 20256 min read

Designing Scalable Database Schemas for Real-Time Apps

MongoDBDatabase DesignArchitecture

Schema design can make or break your application's performance at scale. Here's what I learned building the chess platform's database layer.

The Requirements

Store player match history (potentially millions of records)

Track active rooms with real-time state

Maintain player profiles with ELO ratings

Handle concurrent reads/writes without conflicts

Schema Decisions

Embedding vs. Referencing

For active game state, I embedded the board position directly in the room document. This eliminates joins during the hot path — every move only requires a single document update.

Indexing Strategy

Compound indexes on (playerId, timestamp) for match history queries. Partial indexes on (status: "active") for room lookups.

State Persistence

Automated routines snapshot game state every 10 moves, protecting against data loss from abrupt disconnects.

Performance Results

Room lookup: < 5ms average

Match history pagination: < 20ms for 1000+ games

Zero data loss across 10,000+ test disconnections