CONCEPT Cited by 1 source

Shard key volatility¶

Shard key volatility is how often the value in the shard-key column changes for a given row. An immutable shard key (user_id, org_id, file_id — identifiers set once at row creation) is zero-volatility; a mutable shard key (step_count, status, region) is high-volatility. The second of the three shard-key selection criteria named by Ben Dicken (Source: sources/2026-04-21-planetscale-database-sharding).

Why it matters¶

Every sharding strategy — range, hash, lookup — derives the shard from the current value of the shard-key column. When that value changes, the row must physically migrate across shards to stay consistent with the routing function, even though it's the same logical row.

Dicken's framing:

"Any time the value in a column of a shard key is changed, it may need to be moved from one shard to another in order to maintain the integrity of our sharding strategy. Consider again the choice between using step_count or user_id as a shard key. A step_count is a column that may be volatile. Throughout a day, the step count will change as the user continues to walk … Each time a change occurs, the database cluster has to re-evaluate which shard the row belongs on. This means a single row may end up moving around to different shards over time." (Source: sources/2026-04-21-planetscale-database-sharding)

A row that migrates on every update is effectively running a small snapshot+catchup on every write — the cluster pays inter-shard network cost, write-amplification, and transactional complexity on every update to a volatile shard key.

Worst case: unbounded migration¶

step_count is mutable but bounded — it grows a few hundred per minute. More dangerous are shard keys tied to state machines:

order_status (pending → paid → shipped → delivered) — every transition migrates the row.
user_region — user moves from us-east to eu-west once, but every row they create drags with them.
team_id — a team moving between orgs drags every team-scoped row (the canonical "half their data was missing" failure mode noted by Figma in concepts/horizontal-sharding).

If the shard key is any state-machine field, every row transitions N times where N is the state count — N × row count total migrations over the dataset lifetime.

Immutable shard keys are zero-migration¶

user_id is set at user creation and never changes. Once a row lands on its shard under hash(user_id), it stays there for life. Row migration only happens on resharding (operator-initiated; deliberate; supported by cutover-swap patterns), not on every write.

This is why Dicken's recommendation is unambiguous: "Always take time to consider the volatility of a column before selecting it as a shard key."

Two practical rules¶

Prefer immutable identifier columns — user_id, file_id, org_id, account_id, tenant_id, device_id. Set once at row creation, never rewritten by application logic.
Never shard on state-machine columns — anything that transitions under normal use (status, phase, tier, state). These generate constant migration load and force transactional row-move logic into the hot path.

Pairs with the other two shard-key criteria¶

Volatility is the second of three criteria. The other two are cardinality (many distinct values, uniformly distributed) and query-pattern alignment (dominant query predicate includes the shard key). user_id scores well on all three; step_count scores badly on volatility and on query-pattern (queries are usually "all steps for a user", not "steps with count = X").

Seen in¶

sources/2026-04-21-planetscale-database-sharding — canonical naming of volatility as the second shard-key criterion; step_count vs user_id worked example; explicit framing "a single row may end up moving around to different shards over time."