Sharding

Replication scales reads, but all writes go to one primary. When one server cannot keep up with writes or data no longer fits on disk (tens of terabytes), data is spread across several databases, each holding its part.

Before sharding, exhaust simpler steps: a bigger server (a modern machine with 64+ cores and a terabyte of RAM handles a lot), indexes, a cache, replicas, archiving old data. Sharding adds complexity for good.

The key decides which shard a row lands on. For ShortLesson a logical key is user_id or school_id: all data of one user or one school sits together, and most queries hit one shard.

Hash sharding (hash(user_id) % N) spreads data evenly, but range queries become expensive. Range sharding (by date or alphabet) is handy for ranges but creates hot shards: all new writes land in the last one.

A bad key creates a «hot» shard: shard by course_id, have one course with a million students, and that one shard becomes the bottleneck.

Joins and transactions across shards are either impossible or very expensive. Global reports have to be gathered from every shard or from a separate analytics store.

To add servers without moving data en masse, you create many logical shards (hundreds or thousands) and place them on fewer physical databases. In 2021 Notion split PostgreSQL into 480 logical shards on 32 physical databases. Ready made options: Citus for PostgreSQL, Vitess for MySQL, or distributed databases like CockroachDB that shard on their own.