Skip to content

PLANETSCALE 2024-07-10

Read original ↗

PlanetScale — Dealing with large tables

Summary

Ben Dicken (PlanetScale, 2024-07-10) publishes the canonical three-rung scaling ladder for a single fast-growing table: vertical scaling → vertical sharding → horizontal sharding. The post is pedagogical but architecturally substantive: it names the pattern that most production databases actually have — "hundreds or even thousands of individual tables … [where] only a small subset … grow very large, into the hundreds of gigabytes or terabytes for a single table" — and walks the concrete Vitess commands (vtctldclient MoveTables, vtctldclient ApplyVSchema, vtctldclient Reshard) for each transition. The load-bearing framing:

"Vertical scaling, vertical sharding, and horizontal sharding are useful techniques for scaling to handle large tables. Each technique is useful for different phases of the growth of a table. Typically, you scale your entire database vertically, then shard vertically, then use horizontal sharding for ultimate scalability of large workloads."

The post's worked example is MuscleMaker — a workout-tracking app with user, exercise, and exercise_log tables. Only exercise_log grows into the billions-of-rows regime ("several million rows per day … many billions or even trillions of rows") with a large hot-row set ("many users will want to search through their historical exercise data, meaning old rows will likely be queried and aggregated on a regular basis"). The non-uniform-growth assumption — one table dominates — is the structural reason the three-rung ladder exists: scale the whole cluster first (cheap, affects everything), then isolate just the hot table (vertical sharding preserves existing query shapes for the small tables), then horizontally shard just the hot table (avoids sharding the low-cardinality reference tables like exercise).

The vertical scaling rung names three substrate-dependent costs: (1) bare metal requires installing disks + migrating data; (2) EC2/cloud-VM requires volume snapshot → resize → OS partition expansion ("you can do the resize online without taking down your server"); (3) managed services (like PlanetScale) auto-grow storage. The rung's ceiling is economic: "physical machines and cloud VMs can become prohibitively expensive as you start to reach machines that have hundreds of gigabytes of RAM and many CPU cores. Even with a large machine, memory contention can become a problem."

The vertical sharding rung is canonicalised as "moving large tables onto separate servers." In Vitess terms: split the musclemaker keyspace into musclemaker_main (user + exercise, downsized to 16 vCPU / 32 GB RAM) and musclemaker_log (exercise_log, 32 vCPU / 64 GB RAM / 4 TB). The mechanics are vtctldclient MoveTables … create (copies tables into their new keyspaces, hours-long for multi-TB tables, all traffic still routed to the original keyspace during the copy) + vtctldclient MoveTables … switchtraffic (atomic cutover to the new keyspaces). The downsize of the main keyspace is named as an explicit part of the rung: the hot table isn't just moved, the cold table's server is also right-sized.

The horizontal sharding rung is canonicalised as spreading a single table's rows across many shards via a shard key, with Vitess supporting multiple strategies ("one of the most common of these being hashing"). Dicken names the log_id vs user_id shard-key choice as the canonical teaching example: hashing by log_id distributes data evenly but "the logs for any given user will be spread out across all shards … This will be terrible for performance" — making "all logs for user X" (the dominant query) a cross-shard query. Hashing by user_id instead routes every per-user query to a single shard. The mechanical sequence is walked end-to-end: (1) create a vitess_sequence-typed table for ID generation (exercise_log_id_sequence) + ApplyVSchema to register the sequence; (2) ApplyVSchema to declare the sharded table's Primary Vindex (user_id → hash) + the sequence binding for log_id; (3) ALTER TABLE … change log_id log_id bigint not null to remove MySQL's auto_increment (Vitess now owns ID generation); (4) provision four new servers named by Vitess's shard-range convention -40 / 40-80 / 80-c0 / c0-; (5) vtctldclient Reshard … create --source-shards '0' --target-shards '-40,40-80,80-c0,c0-'; (6) Reshard … switchtraffic.

The four benefits of horizontal sharding are canonicalised as: (1) write throughput scales because no single primary handles all writes ("whenever write performance is bottlenecking, we can upsize shards or add new shards"); (2) backup parallelism — the table backs up on every shard concurrently; (3) failure isolation — any single shard outage affects only a fraction of the table; (4) cost — "in some instances, running many small cloud VM instances is more affordable than a single, top-of-the-line instance." Closing architectural claim: "Vitess also has support for resharding an already-sharded table. This means that as the data size and I/O workload continues to grow, we can expand out to using more and more shards. We can also downsize or use less shards if demand decreases … Horizontal sharding gives us infinite options for scaling."

Key takeaways

  • The three-rung scaling ladder is the canonical wiki framing for a single large table. Vertical scaling → vertical sharding → horizontal sharding. Cheap to expensive, uniform to targeted. The rungs are sequential because each preserves more of the original query surface than the next: vertical scaling changes nothing at the SQL layer; vertical sharding changes which keyspace the app connects to (one app-level change); horizontal sharding adds shard-key semantics to every query on the sharded table. The non-uniform growth assumption — "only a small subset of these tables grow very large" — is the structural reason to climb the ladder one rung at a time and only as far as needed.

  • Vertical sharding is the structurally right intermediate rung when one table dominates. "Vertical sharding is a fancy phrase with a simple meaning — moving large tables onto separate servers." Don't shard everything horizontally when one table is the problem: split the hot table into its own keyspace, downsize the rest. The musclemaker_log / musclemaker_main split is canonical: the log keyspace gets 32 vCPU / 64 GB RAM / 4 TB to handle the growth; the main keyspace is downsized to 16 vCPU / 32 GB RAM once the heavy table is gone. Canonical new isolate-fastest-growing-table-to-own-keyspace pattern.

  • MoveTables is the canonical data-motion primitive for the vertical-sharding rung. Two-phase: (1) vtctldclient MoveTables --workflow musclemakerflow create --target-keyspace musclemaker_log --source-keyspace musclemaker --tables "exercise_log" — "with large tables, these two steps will take a while (hours). While this is happening, all production traffic will still be routed to [the source keyspace]." (2) vtctldclient MoveTables --workflow musclemakerflow --target-keyspace musclemaker_log switchtraffic — cutover is separable from copy; traffic-switch is fast even when copy is slow. This is the same primitive sources/2026-02-16-planetscale-zero-downtime-migrations-at-petabyte-scale canonicalises at petabyte scale — here disclosed at the pedagogical altitude with the raw vtctldclient commands.

  • The shard-key choice collapses or explodes the dominant query. Dicken's canonical teaching example: sharding exercise_log on hash(log_id) "provides a (roughly) even distribution of data across all of the shards. There is a problem with this though: the logs for any given user will be spread out across all shards." Sharding on hash(user_id) instead routes every "show me this user's logs" query to a single shard. "Each user_id will produce the same hash, and thus get sent to the same server." This is the same three-criterion framing as sources/2026-04-21-planetscale-database-sharding (cardinality + volatility + query-pattern alignment), reduced here to the third criterion applied to the log_id vs user_id choice on a log-style table.

  • Horizontal-sharding setup needs a sequence table, a vschema update, and an auto_increment removal. Four-step Vitess sequence concretised: (1) create table if not exists exercise_log_id_sequence(id int, next_id bigint, cache bigint, primary key(id)) comment 'vitess_sequence'; insert into exercise_log_id_sequence(id, next_id, cache) values(0, 1000, 100); — Vitess-owned ID generation table; (2) ApplyVSchema to register the sequence table as type sequence and the sharded table's Primary Vindex as hash on user_id bound to the sequence for log_id; (3) alter table exercise_log change log_id log_id bigint not null — "Assigning IDs has shifted to be Vitess' responsibility instead of MySQL." The auto_increment-removal step is the canonical signal that ID generation has moved from the MySQL primary to Vitess — necessary because a sharded table can no longer rely on a single primary to vend monotonic IDs.

  • Vitess shard-range naming -40 / 40-80 / 80-c0 / c0- is the addressing convention. Each shard owns a contiguous range of keyspace_id values expressed as hex bounds. The four-shard layout splits the binary(8) keyspace at 0x40, 0x80, 0xc0. New shards are added by splitting a range in half; this is what Reshard does. The --source-shards '0' --target-shards '-40,40-80,80-c0,c0-' command form canonicalises the fan-out addressing.

  • Reshard is the canonical primitive for the horizontal-sharding rung. Same shape as MoveTables: create (create --source-shards '0' --target-shards '-40,40-80,80-c0,c0-') then switch (switchtraffic). The key property: "Vitess also has support for resharding an already-sharded table" — the rung is not one-shot. Four shards today, eight tomorrow, sixteen a year from now; rung stays the same primitive. Canonical new reshard-online-via-vreplication pattern composing VReplication + routing-rule swap into a repeatable shape.

  • Four structural benefits of horizontal sharding over vertical scaling. (1) Write throughput — no single primary bottleneck; "whenever write performance is bottlenecking, we can upsize shards or add new shards." (2) Backup speed — "instead of backing up a single table on 1 machine, the table is divided up across many shards. Therefore, taking backups of the table can happen in parallel, vastly speeding up overall backup time." (3) Failure isolation — "any node that goes down only affects a subset of the table, not the whole data set." (4) Cost — "in some instances, running many small cloud VM instances is more affordable than a single, top-of-the-line instance." The first three are structural (a sharded table is these things by construction); the fourth is workload-dependent (whether N × small-instance-price < 1 × large-instance-price depends on the instance family).

Systems, concepts, patterns surfaced

Operational numbers

  • MuscleMaker exercise_log growth regime: "5-10 new rows in the exercise_log each time they work out … at least several million rows per day … quickly grow to a total size of many billions or even trillions of rows" — the non-uniform-growth worked example.
  • Vertical-sharding keyspace sizing: musclemaker_log = 32 vCPU / 64 GB RAM / 4 TB disk; musclemaker_main = 16 vCPU / 32 GB RAM (downsized from 32/64/4TB).
  • MoveTables copy phase: "these two steps will take a while (hours)" for multi-TB tables. Cutover is separately invoked.
  • Horizontal-sharding shard sizing: four shards × 16 vCPU / 32 GB RAM / 2 TB each — aggregate 64 vCPU / 128 GB RAM / 8 TB vs the previous single 32/64/4TB primary.
  • Vitess sequence table init: insert into exercise_log_id_sequence(id, next_id, cache) values(0, 1000, 100); — cache 100 IDs per allocation, start at 1000.

Caveats

  • No production latency / throughput numbers. The post is pedagogical — no Slack-style "2 ms average query latency" reference (which sources/2026-04-21-planetscale-database-sharding does provide), no MoveTables copy-rate disclosure, no Reshard cutover duration. Dicken's "hours" framing for the MoveTables copy is qualitative.
  • Vertical-scaling ceiling is asserted, not quantified. "Hundreds of gigabytes of RAM and many CPU cores" is the hand-wavy threshold; the actual economic cross-over between upsize-further and vertical-shard is workload-dependent and not disclosed.
  • The four-benefit list omits the cost of cross-shard queries. Horizontal sharding's write-throughput / backup / failure-isolation benefits apply only when the shard key is aligned with the dominant query; cross-shard queries erode all four. sources/2026-04-21-planetscale-database-sharding names this explicitly; this post does not.
  • Replicas mentioned but not disclosed. "Note that typically we'd also have replica nodes set up for every MySQL instances in a keyspace. These are omitted in this article for simplicity, but it is best practice to use replicas for high availability and disaster recovery." — out of scope here.
  • The post predates Vitess 21. Originally 2024-07-10, re-fetched 2026-04-21. vtctldclient commands used here are compatible with the Vitess 21 Reshard / MoveTables surface (sources/2026-04-21-planetscale-announcing-vitess-21).

Source

Last updated · 347 distilled / 1,201 read