Skip to content

PLANETSCALE 2023-10-10

Read original ↗

PlanetScale — What is MySQL partitioning?

Summary

Brian Morrison II's 2023-10-10 pedagogy post defines MySQL partitioning as the native MySQL feature that splits an individual table into several logical units — called partitions — stored separately on disk, with a partitioning function deciding which partition each row lives in based on column values. The post walks three out-of-the-box partitioning strategies with full CREATE TABLE examples on a library_books schema: RANGE (numerical ranges keyed on publication_year with MINVALUE/MAXVALUE), LIST (fixed value sets keyed on author, with rejection-on-unknown-value as the failure mode), and KEY (MySQL-managed hash on the primary/unique key with PARTITIONS 4 count). Then names three benefits (query pruning, parallel cross-partition scans across storage devices, faster per-partition maintenance — TRUNCATE PARTITION, per-partition backup, per-partition index rebuild) and three drawbacks (restricted column types per strategy — e.g. ENUM rejected, access-pattern dependency for balanced partitions, no horizontal fault tolerance because it's still one server). Closes with the load-bearing partitioning-vs-sharding distinction: partitioning happens on the same server (natively MySQL, scales vertically) while sharding splits across different servers (requires external mechanisms, scales horizontally) — and the vendor position that PlanetScale does not support MySQL partitioning because Vitess-based horizontal sharding already solves the underlying performance and availability problem with cross-server fault isolation.

Key takeaways

  • Partitioning's unit of granularity is one table, and the resulting physical units are partitions stored separately on disk — "When you partition a table in MySQL, the table is split up into several logical units known as partitions, which are stored separately on disk. When data is written to the table, a partitioning function will be used by MySQL to decide which partition to store the data in. The value for one or more columns in a given row is used for this sorting process." (Source: this post). This is the structural property that distinguishes partitioning from sharding on the same-server axis: the partitions share the host machine's RAM, CPU, and fate.

  • RANGE partitioning uses numerical ranges declared per partition with VALUES LESS THAN (N), bracketed by MINVALUE / MAXVALUE for the outermost ends. Worked example: library_books partitioned on publication_year with p0 < 2001, p1 < 2011, p2 < 2021, p3 < MAXVALUE. Best for monotonic time-like columns where the distribution is predictable; the worked query "all books published in 2007" is the partition-pruning poster child — the planner only scans p1. (Source: this post)

  • LIST partitioning uses fixed value sets declared per partition with VALUES IN (v1, v2, ...) — best when you know the exact set of values upfront. Canonical failure mode: "attempting to insert a row with an invalid value will cause an error." This makes LIST partitioning open-world-unfriendly — adding a new author requires an ALTER TABLE before inserts succeed. Worked library_books example partitions on author across four named-author groups. (Source: this post)

  • KEY partitioning lets MySQL hash the primary / unique key and spread rows across a specified number of partitions (PARTITIONS 4). "An internal algorithm will be used to determine how to best sort the data evenly across a specified number of partitions." Zero-configuration path for operators who want partitioning's maintenance and scan-parallelism benefits without declaring ranges or value sets — MySQL-managed and strategy-opaque. (Source: this post)

  • Partitioning's three first-order benefits are (1) pruning, (2) cross-partition parallelism, (3) faster per-partition maintenance — verbatim: "If you wanted to query all books published in 2007, MySQL would only need to scan p1 and not the entire table to find the requested data. If you need to query across partitions, these operations can be performed in parallel, which can be even faster if the partitions are stored across multiple storage devices." and "truncating all of the data in a partition is faster than performing a DELETE ... WHERE statement on the same data. Backup operations can also be performed on individual partitions, which can reduce the load on the overall server. Rebuilding indexes or reclaiming unused space can also be done per partition instead of on the entire table." (Source: this post)

  • Partitioning's three first-order drawbacks are (1) column-type restrictions per strategy, (2) access-pattern sensitivity, (3) single-server fault domain — "Depending on the partitioning strategy you want to use, you may be limited on the available data types for columns you want to partition on. For instance, ENUMs are not permitted on most strategies. Additionally, you need to properly understand your access patterns as unbalanced partitions can limit the performance gains you'd get from partitioning in the first place." Plus the closing caveat: "Since the focus is put on a single server, it still creates a single point of failure should something catastrophic happen to your MySQL server." (Source: this post)

  • Partitioning is scaling vertically; sharding is scaling horizontally — load-bearing vocabulary anchor: "The key differences are that partitioning occurs on the same server and is supported by MySQL natively, whereas sharding a database splits tables across different servers and requires external mechanisms to achieve this. Since partitioning involves one server, it is considered scaling vertically, whereas sharding is scaling horizontally." This is the one-sentence distinction the wiki has been underspecifying — partitioning is the MySQL-native same-server split; sharding is the cross-server split requiring Vitess or similar framework. (Source: this post)

  • Vendor position: PlanetScale does not support MySQL partitioning — "Because we're built with a focus on sharding and horizontal scalability, partitioning is not supported in PlanetScale. Partitioning, in reality, is a stepping stone to greater performance. Since the focus is put on a single server, it still creates a single point of failure should something catastrophic happen to your MySQL server. Because of our expertise in sharding, partitioning adds little value to what we already do." The structural argument: once Vitess is in play, partitioning's same-server pruning + parallelism + maintenance benefits are subsumed by sharding's cross-server equivalents (keyspace-level query routing, shard-parallel scans, shard-parallel backups) and the single-server-fault-domain drawback is eliminated. (Source: this post)

Systems / concepts / patterns extracted

  • MySQL — host of the native partitioning feature.
  • Vitess — the external sharding framework PlanetScale uses instead of partitioning ("Vitess handles very well using a combination of a stateless proxy which routes queries to the proper MySQL node, and a topology server to keep the entire system aware of any changes to individual nodes.").

  • PlanetScale — the managed-Vitess vendor that excludes partitioning from its supported feature surface.

  • MySQL partitioning (new) — native same-server table split.

  • Range partitioning (new) — numerical-range strategy.
  • List partitioning (new) — fixed-value-set strategy.
  • Key partitioning (new) — MySQL-managed hash strategy.
  • Partition pruning (new) — query-planner skips partitions whose range/list/hash doesn't match the WHERE predicate.
  • Partitioning vs sharding (new) — one-page disambiguation of the same-server / cross-server / MySQL-native / external-framework / vertical / horizontal distinction.
  • Horizontal sharding (extend) — adds the partitioning-is-not-sharding cross-ref.

  • Vertical scaling (extend) — partitioning named as an intra-instance vertical-scaling lever.

  • Per-partition maintenance (new) — TRUNCATE PARTITION, per-partition backup, per-partition index rebuild as cheaper alternatives to full-table operations.

Operational numbers

This post contains zero production numbers — it is a definitional / tutorial piece. No timings, no row counts, no partition cardinality figures, no IOPS numbers, no comparative benchmarks. All operational claims are directional: "faster", "reduced load", "can be even faster".

Caveats

  • Pedagogy voice, not production retrospective. Brian Morrison II's 2023-10-10 post is early in his PlanetScale-pedagogy arc (predates his 2023-11-15 replication-best-practices, 2023-11-20 three-surprising-benefits-of-sharding, 2024-01-08 isolation-levels, 2024-02-02 branching-vs-Aurora, 2024-03-19 UUID-PK posts). Architecture density ~60% on a short ~1,100-word body; worked SQL examples but no EXPLAIN trace showing partition pruning in action.
  • No coverage of HASH, LINEAR HASH, LINEAR KEY, or subpartitioning — post explicitly defers to the MySQL manual ("These are only a few select types that can be used in partitioning."). The wiki pages for key partitioning acknowledge HASH as the sibling strategy without mechanism detail from this source.
  • No discussion of partition-pruning mechanics at the planner level — the "MySQL would only need to scan p1" claim is asserted; the underlying planner-phase decision (which partitions match the predicate given the PARTITION BY RANGE function) is not walked. Contrast with sources/2026-04-21-planetscale-how-to-read-mysql-explains which walks EXPLAIN PARTITIONS output.
  • LIST partitioning's open-world failure is stated but not mitigated — post notes "attempting to insert a row with an invalid value will cause an error" but does not name DEFAULT partition (MySQL 8.0.26+) or the ALTER TABLE ... REORGANIZE PARTITION workflow for adding a value set.
  • No discussion of global vs local indexes on partitioned tables — a significant omission: InnoDB partitioned tables in MySQL 8.0 do not support global indexes, which is the single biggest constraint on partitioned-table schema design. Unique indexes must include the partition key; the post does not mention this.
  • Partition count trade-off unaddressed — more partitions = finer pruning but more open-file-handles, more metadata, worse query-planner overhead on non-pruned queries. The "PARTITIONS 4" in the KEY example is a magic number; no guidance on picking it.
  • Vendor position is load-bearing but not defended at the substrate level — PlanetScale's "partitioning adds little value to what we already do" is framed as the natural consequence of Vitess-based sharding, but the post does not engage with cases where partitioning plus sharding are complementary (e.g. time-range partitioning within a sharded table for archival storage tiering).
  • No mention of partition-level access-control or resource-isolation — some databases (not MySQL native) support per-partition quotas or access control; MySQL's native partitioning has neither, but the post does not disambiguate.
  • Brian Morrison II not at the Dicken / Noach altitude — pedagogy-canon voice. Passes the Tier-3 bar on the vocabulary-canonicalisation grounds: partitioning-vs-sharding is a distinction the wiki has been making implicitly across many pages (especially concepts/vertical-partitioning, concepts/horizontal-sharding, concepts/scaling-ladder), and this post supplies a single-source citation anchor.

Source

Last updated · 470 distilled / 1,213 read