Database sharding на собеседовании Data Engineer

Зачем sharding

Single DB упирается в limits — storage, connections, write throughput. Sharding — divide data across multiple servers.

Users 1-1000      → Shard 1
Users 1001-2000   → Shard 2
Users 2001-3000   → Shard 3

Hash sharding

shard_id = hash(user_id) % N_shards

Pros: Equal distribution.

Cons:

• Range queries cross all shards.
• Resharding hard (change N → most data moves).
• Nearby users в different shards (no locality).

Consistent hashing. Mitigates resharding pain — only 1/N data moves при добавлении shard.

Range sharding

Users 1-1000      → Shard 1
Users 1001-2000   → Shard 2

Pros:

• Range queries fast (within shard).
• Locality.

Cons:

• Hotspots (новые users — все на last shard).
• Manual balancing.

Geographic / directory sharding

Geographic. EU users → EU shard. US users → US shard.

Directory. Lookup table — для каждого user → which shard.

Pros: flexibility, tenant isolation. Cons: lookup overhead.

Hotspots

Когда один shard takes > N/total_shards traffic.

Causes:

• Celebrity user (millions followers — что-то shared).
• Time-based partitioning + always writing to current.
• Skewed key distribution.

Mitigation:

• Salt key (user_id_random_suffix).
• Read replicas hot shard.
• Move hot data на dedicated shard.

Resharding

Самая сложная операция distributed system.

Steps:

1. New shards added (empty).
2. Background — copy data к new locations.
3. Dual-write window (write old + new).
4. Read switches к new.
5. Old data deprecated.

Tools:

• Vitess (YouTube). MySQL sharding.
• Citus. Postgres extension.
• MongoDB sharded clusters. Native.

В РФ: чаще приложение-уровень custom sharding.

Связанные темы

• Транзакции и MVCC для DE
• Greenplum на собесе DE
• Партиционирование таблиц для DE
• WAL и репликация для DE
• Подготовка к собесу Data Engineer

FAQ

Это официальная информация?

Нет. Статья основана на индустриальном опыте (Vitess, MongoDB).

Тренируйте Data Engineering — откройте тренажёр с 1500+ вопросами для собесов.