Why is a wide-column table's primary key effectively permanent, and what does changing the key of a live table actually involve?

## The key is the layout In a relational database a primary key is a logical constraint, and the storage engine can often rebuild a table under a new key. In a wide-column store the key is **physical**: - it chooses **placement** — the partition's hash, or the key's position in a sorted, range-split keyspace; - it chooses **order** — how rows sit next to each other on disk, which is what makes range reads cheap; - and, in stores that keep data in immutable sorted files, it is baked into every file already written. Changing the key would mean moving and re-sorting every row. So stores in the family do not support altering a table's key, and they do not let you update a row's key either: a "rename" of a key is a delete of the old row plus an insert of the new one. ## Why teams end up needing it Keys are designed around the queries known at the time. Common triggers for a re-key: - a new dominant read path that needs a different order (by time instead of by user); - partitions or rows that grew without bound because the key had no time component; - a hotspot caused by a key that concentrates writes; - a change of tenancy model that needs the tenant at the front of the key. ## The migration, step by step 1. **Create the new table** (or new key layout) designed for the new read path. 2. **Dual-write**: every new write goes to both the old and the new layout, from the application or from a change stream. 3. **Backfill**: read the old table in its stored order, transform, and write into the new one. Make writes **idempotent** — the same cell written twice must be harmless — because backfill and live writes overlap, and a crashed backfill must be restartable from a checkpoint. 4. **Handle the race**: a backfill can copy a row after a live write updated it. Carrying the original cell timestamps into the new table (where the store allows it) lets the newer write win; otherwise re-read or reconcile the overlap window. 5. **Verify**: compare counts per key range, sample rows, or run both read paths in shadow and diff results. 6. **Cut over reads**, keep dual writes for a rollback window, then **stop writing** and drop the old table. ## What it costs | cost | why | |---|---| | write load | every write lands twice during the transition | | read load on the old table | the backfill scans the whole table | | storage | both copies exist until cut-over completes | | engineering time | idempotent writers, checkpoints, verification tooling | | risk | missed writes in the race window, divergence between copies | Throttle the backfill so it does not starve live traffic, and schedule it with background merge work in mind, since it writes a large volume of new files. ## The cheaper alternatives Before a full re-key, check whether the new read path can be served by an **additional table** for that path while the old one keeps serving its own; this is the same dual-write and backfill but without deleting anything. Most wide-column designs end up with several layouts of the same data for exactly this reason. ## Interview angle The point to land is that key design is a one-way door: you can walk back through it, but only by copying the whole table. Strong candidates describe dual-write, idempotent backfill, verification and cut-over, and name the race between backfill and live writes.