CONCEPT Cited by 1 source

List partitioning¶

List partitioning is a MySQL partitioning strategy that assigns rows to partitions based on membership in a fixed, pre-declared set of discrete values for the partition-key column. Each partition declares its value set with VALUES IN (v1, v2, ...); rows whose key value is not in any partition's set are rejected at insert time with an error.

The `CREATE TABLE` shape¶

Morrison's library_books worked example (partitioning by author):

CREATE TABLE library_books (
    ...
    author VARCHAR(255),
    ...
)
PARTITION BY LIST (author) (
    PARTITION p0 VALUES IN ('William Shakespeare', 'Jane Austen', 'George Orwell'),
    PARTITION p1 VALUES IN ('J.K. Rowling', 'Agatha Christie', 'Stephen King'),
    PARTITION p2 VALUES IN ('J.R.R. Tolkien', 'Gabriel García Márquez', 'Toni Morrison'),
    PARTITION p3 VALUES IN ('Haruki Murakami', 'Neil Gaiman', 'Chimamanda Ngozi Adichie')
);

(Source: sources/2026-04-21-planetscale-what-is-mysql-partitioning)

When it fits¶

Closed-world enums — country_code when the app only accepts a fixed list; status columns with a small finite state space; region / tier / tenant_class columns where adding a new value is a schema event.
Value-grouping — when you want partitions to correspond to "this set of tenants" rather than "this range of IDs"; e.g. grouping five largest tenants into one dedicated partition.
Pruning on equality predicates — WHERE author = 'Jane Austen' prunes to exactly p0.

The open-world failure mode¶

Load-bearing caveat verbatim: "One of the downsides, however, is that attempting to insert a row with an invalid value will cause an error." (Source: sources/2026-04-21-planetscale-what-is-mysql-partitioning)

This makes LIST partitioning open-world-unfriendly. Every new value the application wants to insert requires either:

An ALTER TABLE ... ADD PARTITION (...VALUES IN (new_value)) — schema change on a production table.
An ALTER TABLE ... REORGANIZE PARTITION expanding an existing partition's value set.

On a Vitess / sharded setup where adding a new tenant or country is a routine runtime event, this is operationally intolerable. The failure mode is why LIST partitioning is rare outside strictly closed-world domains.

A partial mitigation in MySQL 8.0.26+ is a DEFAULT partition catching all values not matched by any explicit set — Morrison's post predates or omits this feature.

Distinction from key partitioning¶

Axis	LIST partitioning	KEY partitioning
Who decides mapping	Operator (explicit value sets)	MySQL (hash function on primary / unique key)
Open-world tolerance	Rejects unknown values at insert	Accepts any value, hashes to some partition
Pruning on equality	Yes — `WHERE k = v` → one partition	Yes — `WHERE k = v` → one partition via same hash
Pruning on range	No — set membership doesn't imply order	No
Balance	Operator-controlled (and error-prone)	MySQL-controlled (hash-uniform)

See concepts/key-partitioning.

LIST is for domains where the operator wants explicit control over "which value lands where"; KEY is for operators who just want even distribution without caring which partition holds what.

Distinction from lookup sharding¶

LIST partitioning at the same-server altitude resembles lookup sharding at the cross-server altitude — both use an explicit value-to-destination map. But LIST's map is stored in table metadata and processed by the MySQL planner; lookup sharding's map is an external directory service the query router consults. LIST is same-server, declarative, and an ALTER TABLE away from change; lookup sharding is cross-server and the map change is a coordinated workflow.

Seen in¶

sources/2026-04-21-planetscale-what-is-mysql-partitioning — Brian Morrison II (PlanetScale, 2023-10-10) canonicalises the LIST strategy on a library_books author example across four named-author groups; names the load-bearing "invalid value causes an error" failure mode as the strategy's defining operational constraint.