Skip to main content
In this lesson we explain how production databases handle sharding for you, how to pick the right system, and when you should shard at all. We cover two broad families (relational vs NoSQL), show how common systems perform routing and resharding, and give guidelines for selecting a shard key. Why this matters: sharding affects query performance, operational complexity, and whether joins and cross-shard transactions are practical. Understanding each system’s sharding model helps you design the right data model and choose where to optimize: at the database layer, the application, or via caching.

​
Two database families: Relational vs NoSQL

Relational databases (MySQL, Postgres) store data in tables with fixed columns and explicit relationships (for example: Users, Chirps, Likes). They excel at joins and strict correctness guarantees and are queried with SQL:
Relational systems are typically built around a single primary machine by default and do not split a single table across machines without an additional layer that handles splitting and routing. NoSQL databases (Cassandra, DynamoDB, MongoDB) were designed from the start to distribute data across many machines. To achieve horizontal scale they commonly relax cross-shard joins or strict transactional guarantees (or provide them only in limited or slower ways), because enforcing those guarantees across machines requires cross-network coordination. The key practical difference: relational databases usually rely on an external sharding layer, while many NoSQL systems have distribution built in. Example of normalized relational rows (for clarity):

​
Relational systems: sharding via an extra layer

When you need SQL joins and relational guarantees at scale, you typically add a routing/resharding layer that presents a single SQL surface to the application and handles query routing and online resharding.

​
Vitess (MySQL)

Vitess is a popular sharding/proxy layer originally built at YouTube (used by Slack and other large services). The application talks to Vitess as if it were a single MySQL instance; Vitess routes queries to the correct shard and handles online resharding for you.
A minimal architecture diagram showing an app at the top, a Vitess routing layer in the middle, and a MySQL database at the bottom with arrows indicating request flow. The Vitess logo and name appear prominently.
When you add a shard, Vitess performs online resharding using the same four general steps most systems use: copy data, catch up with concurrent writes, cut over traffic to the new shard, then delete the old copy.
A dark-themed architecture diagram showing an "App" connecting through a routing layer labeled "VITESS" to five database shards (Shard 0–4). The top shows a four-step process with step 2 "Catch Up" highlighted, and Shard 2 and Shard 4 visually emphasized.
Behind Vitess, each shard is a standard MySQL instance; the difference is Vitess hides shard topology and migration from the application.

​
Citus (Postgres)

Citus is a Postgres extension that exposes a single Postgres surface and distributes queries to the nodes that hold the relevant rows. If you shard related tables by the same key (for example user_id), the relevant rows for a user (profile, posts, likes) can be co-located on one worker node so joins remain local and fast.
A diagram of the Citus distributed database architecture showing an app talking to a routing layer that shards data across multiple PostgreSQL nodes (Shard 0–3). A small country_codes table (IN, US, GB) is shown as an example of a tiny, denormalized table.
Citus supports copying small lookup/reference tables (e.g., country_codes) to every worker node. This is a standard optimization: replicate small, read-heavy tables to avoid cross-node joins.
Vitess and Citus preserve SQL and relational semantics while handling routing and online data movement for you. You still get joins and stronger transactional guarantees (compared to many NoSQL systems) if you design your sharding keys correctly.

​
NoSQL systems: distribution built in

NoSQL systems typically embed the sharding logic inside the database and trade some cross-shard semantics for scalability and simpler operational models.

​
Cassandra

Cassandra is a peer-to-peer distributed database: any node can accept a client request. The receiving node acts as a coordinator: it consults a consistent-hashing ring to determine which nodes own the data and forwards the request to them. There is no single primary machine.
A dark-themed diagram titled "Cassandra" showing a user labeled "Chris" connecting through an app to a ring of database nodes, with several nodes highlighted (41) and arrows indicating data flow.
Cassandra maps each physical node to multiple points on the ring using virtual nodes (vnodes). This makes rebalancing smoother because adding a new machine takes many small ranges from many existing nodes rather than one large contiguous range.
A dark-themed diagram showing Cassandra's consistent-hashing ring with orange dots around a circle labeled "Virtual Nodes." To the right are stacked database icons representing machines, with a dashed outline highlighting a "New Machine."
Cassandra does not support cross-shard joins or multi-row transactions across arbitrary partitions in the way relational databases do. Data modeling is therefore often denormalized: duplicate the data needed for efficient local reads rather than relying on joins at query time.
A dark-themed diagram titled "Cassandra" showing two separate data stores: on the left a "users" table with user_id 18 (Leo) connected to "Leo's Data," and on the right a "likes" table listing likes (including Martin and Leo) connected to "Chris's Data."
Operationally, Cassandra is commonly self-hosted and operated by the team using it; you manage the machines, repair, compaction, and topology changes.

​
DynamoDB

DynamoDB is a fully managed key-value / document store. Amazon runs the machines, handles partitioning, rebalancing, and splits. Your main responsibility is selecting the partition key (DynamoDB’s shard key).
A stylized DynamoDB diagram showing an app routing requests to multiple partitions with "user_id" as the partition key. One partition is highlighted (labeled "Lena 500") with read/write throughput bars marked 3,000 reads/s and 1,000 writes/s.
Important constraints:
  • DynamoDB automatically splits and rebalances partitions as data grows.
  • A single partition key value cannot be split across physical partitions; a very hot key (a single popular user) can become a throughput hotspot.
  • Practical per-key limits (approximate for small items): ~3,000 small reads/s and ~1,000 small writes/s. Exact limits depend on item size and provisioned vs on-demand mode.
Design for per-partition limits. If one user or key becomes extremely popular (a “hot key”), you must mitigate with caching, request fanout, or by changing your data model (for example, adding time-based suffixes to keys or sharding at the application level).
DynamoDB also does not support joins; denormalization and single-table design patterns are common.

​
MongoDB

MongoDB stores semi-structured documents and supports sharding via a router process (mongos). Data is split into ranges of shard key values called chunks. When chunks become imbalanced the balancer moves them between shards using the copy → catch-up → cut-over → delete lifecycle. Queries that include the shard key are targeted to a single shard:
Queries that do not include the shard key require a scatter-gather: the router asks every shard and merges the results, which is less efficient:
Scatter-gather queries are supported, but they can be slow at scale because they touch all shards.

​
Quick comparison

​
Choosing a database and a shard key

Consider three main factors:
  • Query patterns to optimize: which queries must be low latency and single-shard?
  • Required cross-shard consistency and joins: do you need relational transactions and joins, or can you denormalize?
  • Operational model: do you prefer fully managed (DynamoDB, Atlas) or self-hosted control (Cassandra, Vitess/Citus)?
Best practices:
  • Pick a shard key that makes the most common queries targeted (single-shard).
  • Co-locate related tables on the same shard key in relational sharded systems (Vitess/Citus) to keep joins local.
  • If you use a managed system (DynamoDB), design for per-key throughput limits and avoid hot keys.
  • For NoSQL systems, adopt denormalized data models where joins would require scatter-gather behavior.
If your workload requires frequent multi-table joins and strong transactional guarantees, favor relational sharding layers (Vitess/Citus). If you need horizontal scale with simpler operational overhead or are building a high-throughput key-value service, consider NoSQL solutions but design for denormalization and hot-key mitigation.

​
Summary

  • Relational systems (MySQL/Postgres) preserve SQL joins and stronger correctness; sharding is commonly provided by an external layer (Vitess for MySQL, Citus for Postgres).
  • Cassandra and DynamoDB embed distribution: Cassandra is peer-to-peer with vnodes and coordinator routing; DynamoDB is fully managed and requires careful partition-key design.
  • MongoDB shards by range and uses a router (mongos) with chunk migrations that follow the same online-migration steps.
  • Choosing a database and shard key is always a tradeoff among query patterns, operational preferences, and the level of cross-shard semantics you need.

Watch Video