Scaling Patterns: Real Sharding vs a Table That Only Looks Sharded

The same idea — divide a table by a range of a key column — produces a real multi-machine system in one engine and a single-machine reorganization in the other.

MongoDB: sharding across two real machines

sh.enableSharding("dbscaling")
db.orders.createIndex({customer_id: 1})
sh.shardCollection("dbscaling.orders", {customer_id: 1})
// ... insert 200,000 documents ...
db.orders.getShardDistribution()

Shard shard1RS at shard1RS/mongo-shard1:27018
{
  data: '13.3MiB',
  docs: 200000,
  chunks: 1,
  ...
}

Everything landed on one shard — a freshly sharded collection starts as a single chunk, and the balancer only splits and moves chunks once one crosses its size threshold (128MB by default). 13MB never got there on its own.

sh.splitAt("dbscaling.orders", {customer_id: 100000})
sh.moveChunk("dbscaling.orders", {customer_id: 150000}, "shard2RS")
db.orders.getShardDistribution()

Shard shard2RS at shard2RS/mongo-shard2:27018
{ data: '6.65MiB', docs: 100000, chunks: 1, ... }
Shard shard1RS at shard1RS/mongo-shard1:27018
{ data: '6.72MiB', docs: 200000, chunks: 1, ... }
Totals
{ data: '13.38MiB', docs: 300000, chunks: 2, ... }

Read that total again: docs: 300000 — for a collection that only ever held 200,000 documents. getShardDistribution()’s per-shard counts don’t add up to reality here.

The real numbers, verified directly

db.orders.countDocuments()
db.orders.countDocuments({customer_id: {$lt: 150000}})
db.orders.countDocuments({customer_id: {$gte: 150000}})

200000
150000
50000

150000 + 50000 = 200000 — that’s the real split, matching the chunk boundary at customer_id: 150000 exactly. shard1RS genuinely holds the first 150,000 documents; shard2RS genuinely holds the last 50,000, now on physically separate storage on a separate container. getShardDistribution() is a useful summary, but not the number to trust when it disagrees with a direct count.

MySQL: PARTITION BY looks similar, stays on one server

CREATE TABLE orders (
  id INT AUTO_INCREMENT,
  customer_id INT,
  item VARCHAR(50),
  amount INT,
  PRIMARY KEY(id, customer_id)
) PARTITION BY RANGE (customer_id) (
  PARTITION p0 VALUES LESS THAN (100000),
  PARTITION p1 VALUES LESS THAN (200000),
  PARTITION p2 VALUES LESS THAN MAXVALUE
);
-- ... insert 200,000 rows ...
SELECT PARTITION_NAME, TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA='dbscaling' AND TABLE_NAME='orders';

PARTITION_NAME	TABLE_ROWS
p0	99994
p1	94842
p2	0

Same shape of bug as MongoDB’s distribution summary: 99994 + 94842 = 194836, not 200,000. information_schema.PARTITIONS.TABLE_ROWS is an estimate derived from index statistics for InnoDB tables, not a live count — both engines’ built-in “how is my data laid out” tools turn out to be approximations.

SELECT COUNT(*) FROM orders PARTITION (p0);
SELECT COUNT(*) FROM orders PARTITION (p1);

100000
100000

The real, exact counts — clean 100,000/100,000, exactly matching the 100000/200000 range boundaries. But notice what didn’t change: p0 and p1 are still two partitions of one table on one MySQL server (mysql-lesson). Nothing here moved to a second machine. Where MongoDB’s chunk move genuinely relocated data to different physical storage, MySQL’s partitioning only changed how one server organizes and queries the same disk.

What actually scales horizontally

PARTITION BY can make queries and maintenance faster on one very large table by letting MySQL skip irrelevant partitions — a real benefit, but a single-server one; every partition still competes for that one machine’s CPU, memory, and disk. MongoDB’s sharding is genuine horizontal scaling: add a third shard, and the cluster now has three machines’ worth of storage and query capacity to spread load across, with no single-server ceiling. MySQL has no native equivalent to sh.shardCollection() — real horizontal write-scaling for MySQL requires an external layer (Vitess is the common one), outside what this project covers. For sharding’s real depth on the engine that has it natively, see mongodb/sharding-concepts.md.

Practice

db.orders.countDocuments({customer_id: {$gte: 100000, $lt: 150000}})

50000

EXPLAIN SELECT * FROM orders WHERE customer_id = 250000;

id  select_type  table   partitions  type  possible_keys  key   key_len  ref   rows  filtered  Extra
1   SIMPLE       orders  p2          ALL   NULL           NULL  NULL     NULL  1     100.00    Using where

mongosh --port 27018 dbscaling --eval "db.orders.countDocuments({customer_id: 180000})"

1

Next: schema design — normalized relational tables vs. embedded documents, with the actual query-cost difference measured, not asserted.