Sharding MySQL & PostgreSQL, Names Across Databases, Interview

Partitioning vs sharding, how to shard MySQL (Vitess) and PostgreSQL (Citus), what Kafka, Cassandra, MongoDB and CockroachDB call a shard, and a five-step…

In this chapter

Sharding in real databases, and in the interview

Keep learning

Overview

In real databases Partitioning vs sharding Sharding MySQL and PostgreSQL One idea, many names How they place and move data What to say Quick check

{NUMBER.get(id)}. {title}

chapters · scaling out Sharding in real databases, and in the interview The sharding chapter explained the ideas. This one shows how MySQL, PostgreSQL and other databases put them into practice, what each one calls a shard, and how to answer sharding questions in an interview. On this page Part 1 · In real databases

Partitioning vs sharding

One server. A big table is split into smaller tables (links_2025, links_2026). Joins and transactions still work. Old partitions can be dropped instantly. Many servers. The same table on each, holding different rows. Each server is a separate database. Smaller indexes per piece More CPU, memory, disk Transactions across pieces Joins across pieces App unaware of the split Both are horizontal: same columns, fewer rows per piece. Vertical partitioning splits columns: keep small hot columns (code, long_url) in one table, move big rarely-read ones (preview_html) to another. ★ The one-line difference What partitioning is actually good for, and what people wrongly expect from it Use it for Don’t expect it to Skipping whole chunks when almost every query filters on the partition column, such as a date range over a time-ordered table. Handle more writes. Every partition is on the same disk and CPU, so the write ceiling does not move. Smaller maintenance steps: rebuild, back up or archive one partition at a time. Fit more data. It is still one machine’s storage.

Sharding MySQL and PostgreSQL

Plain MySQL and PostgreSQL do not shard by themselves. PARTITION BY splits a table on one server only. To spread a table over many servers you add something: Four ways, from most work to least Who routes 1. In the application Your code 2. A sharding layer Vitess (PlanetScale runs it as a service) Citus extension: a coordinator plus worker nodes 3. Built in, with limits NDB Cluster, a separate engine that shards by primary key Partitions as foreign tables on other servers (postgres_fdw): no cross-server transactions or rebalancing The database 4. Distributed SQL, same protocol CockroachDB, YugabyteDB The database, with rebalancing and cross-shard transactions

One idea, many names

A shard is “one slice of the data that lives on one server, plus its copies”. Each database named that slice differently. You don’t need to learn the names, only recognise them. Watch the two traps marked ⚠, where the same word means something else. What each system calls a shard Name for the slice What it is there MySQL, PostgreSQL ⚠ A table split into smaller tables on the same server. Not sharding. Vitess, Citus The real cross-server slice when you shard MySQL (Vitess) or PostgreSQL (Citus). Cassandra, ScyllaDB ⚠ token range (vnode) A range of shard-key values. Chunks live on “shards”, which are whole replica sets of servers. A continuous span of keys that splits when it grows. HBase, TiDB Same idea as a range: a span of sorted keys on one server. Bigtable, YugabyteDB A span of a table’s rows, split when it grows. A range of rows that Spanner moves and splits automatically. Elasticsearch, OpenSearch An index is split into primary shards, each with replica shards. Redis Cluster hash slot “Virtual bucket”: keys hash into 1,024 vBuckets spread over servers. And “partition” in sharding has nothing to do with a network partition, a broken link between servers (CAP theorem).

How real databases place and move data

Where a key goes, and what happens when you add capacity How a key finds its shard Adding capacity Cassandra, ScyllaDB A new node takes token ranges from the others Hash of the partition key to internal partitions Splits partitions automatically as data or traffic grows A balancer splits and migrates chunks Online resharding, run by an operator Hash of the distribution column into a fixed number of shards The rebalancer moves whole shards Spanner, CockroachDB Key ranges that split automatically Part 2 · Interview

What to say

1. Prove the need data or writes past one node 2. Shard key from the main query 3. Distribution hash with a ring or many shards 4. Trade-offs the query you made expensive 5. Growth how you add shards Say it like this “At 10 billion links and 20k new links a second at peak, one Postgres primary cannot take the writes, so I would shard. Every read starts with the short code, so the code is the shard key. I hash it onto a ring of many virtual nodes so a new shard moves only its share. The trade-off is ‘my links’ by user, which I serve from a second table sharded by user_id. Popular links come from Redis, so the shards mostly see first visits.”

Remember

Shard last: data size or writes, not reads Key: many values, even spread, in the main query Hot key: cache, salt, or isolate Keep transactions on one shard MySQL/Postgres partition ≠ shard → Practise: splitting one database into shards, rebalancing, search by a non-key column, partition skew.

More courses

Jobs, linked to lessons — Browse jobs where every requirement links to the chapter that teaches it. See how ready you are, how long the gap takes to close, and which listings look stale.
Match a job post to courses — Paste a job post. Each skill is matched to a track here and put in learning order, foundations first.
React from open source with patterns — Patterns extracted from Material UI, Radix, React Aria, React Hook Form, Redux Toolkit, TanStack, and Next.js — not slides about hooks.
System Design — Core concepts first (SQL vs NoSQL, CAP, indexing, replication, sharding), then designs grouped by what carries them: the database, Redis, Kafka, or many services.
Build a real API with NestJS — One ticket-booking API, built in order: a plain Node server first, then NestJS modules, validation, a real database, and the features real clients lean on.
Microservices — Microservice patterns one diagram at a time: how to split a monolith, how services talk, and how data stays consistent without distributed transactions.
Kafka — A beginner-to-senior path through event logs, reliable delivery, production operations, and disaster recovery.
AI engineering for software engineers — Eight free reads, in order, for software engineers moving into AI: when to build an agent, how to build one, how to test it, and how the model works underneath.
Frontend system design — How a UI is sliced when it is a product, not a page: ownership of state, cache, routes, and what happens when the network lies.
TypeScript in depth — The type system as a design tool. Inference, narrowing, generics, and the errors that mean the model is wrong — not a catalog of utility types.
CSS in depth — See the box, then the page. Visual chapters from the box model through Flexbox surprises, Grid, selectors, and units, then backgrounds, shapes, and visual effects, each with a playground.
Accessibility — Foundations first (WCAG in plain words, semantic HTML, lists, alt text), then hands-on HTML labs, a full pass of WAI-ARIA 1.3, and a final fix-the-page challenge.
Docker — What an image is, why layers cache, and how containers find each other on a network.
Redis — Fifteen Redis patterns, one picture each: cache, session, lock, counter, rate limiter, queue, and when Redis is the wrong tool.
Kubernetes — Kubernetes from zero to operators: why it exists, Pods, Deployments and Services, then configuration, networking, storage, scheduling, scaling and security.