CockroachDB 21.2 removes interleaving and makes schema conversion an upgrade requirement

Keeping a customer's orders beside the customer in storage can make a related-data lookup cheaper. It can also make a scan of customers traverse order data it did not ask for. CockroachDB's interleaved tables accepted that physical coupling, and the project's deprecation guidance identifies its costs as reasons to remove the feature.

The 21.2 release entry dates the release to November 16. Interleaved tables and indexes must be converted before completing this upgrade. The implementation enforces that requirement through a cluster-version migration check.

I find the removal interesting because it concerns a storage optimization expressed in the schema. The SQL relationship between customers and orders can remain useful after the physical interleaving is removed. Separating those concerns lets the engine simplify storage behavior without requiring the application to discard its relational model.

Interleaving changes the key structure

The 21.1 interleaving guide explains the original mechanism. A child table's primary key begins with the entire primary key of its parent. That shared prefix lets the child data be placed immediately after the corresponding parent key in the key-value store.

For customers and orders, the child's customer column comes first in the orders primary key. Several orders for one customer can then appear beside that customer's entry. The guide describes related data as more likely to stay in the same key-value range.

The prefix is a storage relationship. The guide separately recommends a foreign key to enforce the logical relationship between parent and child. Interleaving alone does not replace that constraint.

A hierarchy can contain several levels. Orders can be children of customers while packages are children of orders. The physical arrangement then connects the tables through nested key prefixes.

The customer prefix has two jobs in this arrangement. It identifies the logical customer associated with an order, and it positions the order inside the parent's storage hierarchy. The foreign key enforces the former relationship independently of the latter. Removing interleaving can therefore preserve the same customer identifier and its constraint.

A conversion that retains those columns does not require orders to become unrelated to customers. It changes how the engine arranges their keys. This distinction is useful when explaining the migration to application developers who see the same logical relationships in their queries.

That design favors operations aligned with the hierarchy. Reading one customer's related records can benefit when they occupy the same range. The cost appears when an operation needs only part of the structure or changes one table independently.

A parent scan encounters the children

The guide says scans of objects with interleaved children are much slower because they traverse the parent and its children. A physical layout intended to help related lookups therefore imposes work on scans that do not need the related data.

This is a workload-dependent tradeoff. A schema relationship by itself does not establish that most queries want parent and child rows together. A frequently scanned parent can pay for a much larger child collection even when the query's requested columns belong only to the parent.

The deprecation guidance also identifies schema-change overhead. Adding or removing a column can require rewriting the entire interleaved hierarchy for the relevant tables and their parents or children. The storage coupling expands the work caused by a logical change.

These costs explain why a local optimization can become a general maintenance burden. The initial choice affects later queries and migrations beyond the request that motivated it. The guide says internal benchmarks found the performance benefits limited to a small number of cases, without providing a universal numerical result in that description.

Interleaving was deprecated in 20.2 and disabled by default in 21.1 through sql.defaults.interleaved_tables.enabled. Those earlier steps gave existing users a migration period. Version 21.2 turns that earlier direction into a requirement for completing the upgrade.

The upgrade checks the remaining objects

The 21.2.0 migration implementation queries whether crdb_internal.interleaved contains any rows. If it does, the check returns an error saying that interleaved tables are no longer supported and directing the operator to drop or uninterleave the listed objects.

The migration registry registers this work under EnsureNoInterleavedTables. It supplies the check as a precondition and calls the same check from the migration itself.

That structure makes the compatibility boundary explicit. The migration does not attempt to choose a new schema layout for the user. It requires the incompatible objects to have been handled before it can complete.

The query also supplies an inspection point. A user can inspect the same internal relation named by the error to identify remaining objects. This connects the failed version transition to schema state instead of leaving the operator with an unexplained finalization failure.

It is therefore insufficient merely to stop creating new interleaved tables. The upgrade check concerns existing objects. A default that disables new use does not remove physical arrangements already present in the cluster.

Convert tables from the bottom of the hierarchy

The 21.1 guide documents conversion through ALTER PRIMARY KEY. The statement repeats the existing primary-key columns but omits INTERLEAVE IN PARENT. That preserves the logical key selection while replacing the interleaved physical arrangement.

Only a child that is not itself a parent can be converted at that point. A multilevel hierarchy therefore needs conversion from its leaves upward. For the documented customers, orders and packages example, packages comes before orders.

The guide's commands preserve each existing key column sequence. The quoted order column is part of the example schema, not an instruction to rename a column.

ALTER TABLE packages
ALTER PRIMARY KEY USING COLUMNS (customer, "order", id);

ALTER TABLE orders
ALTER PRIMARY KEY USING COLUMNS (customer, id);

The guide describes ALTER PRIMARY KEY as an online schema change. Online execution addresses service continuity while the database performs the conversion. It does not imply that the storage operation has no resource cost or that its dependencies can be ignored.

SHOW CREATE TABLE provides a way to inspect the resulting definition. The guide's before-and-after examples retain foreign-key constraints while removing the interleaving clause. They also show additional unique indexes in the converted definitions.

That output is useful because the intended end state is more specific than successful command submission. The physical interleaving should be absent while the required relational constraints and key columns remain. The documented examples expose both aspects of the change.

Secondary indexes need replacement

The migration path differs for interleaved secondary indexes. The guide says they cannot be converted to non-interleaved indexes. Their existing definitions must be dropped and new indexes created without the interleaving clause.

A table conversion and an index replacement therefore cannot be treated as one interchangeable operation. The inventory needs to distinguish primary-key interleaving from secondary-index interleaving. Each has its own documented response.

That distinction also matters for application queries. Removing an interleaving clause from a table does not establish that every secondary index has the intended replacement definition. The upgrade gate addresses remaining incompatible state, while the schema owner chooses which valid indexes the workload still needs.

I prefer a migration requirement that exposes those objects before finalization. Automatically dropping a secondary index would require the database to assume the application no longer needs it. Requiring deliberate replacement keeps that schema decision with the person responsible for the workload.

The release improves observation around schema choices

The November 16 release announcement adds index-usage statistics and longer-lived statement and transaction statistics. Those capabilities concern understanding a workload after choosing its schema.

Index usage can help identify indexes that impose write overhead without serving observed reads. Persistent statement history can extend the period available for inspecting workload behavior. Neither feature establishes that removing every rarely read index is correct, since the intended queries still define its purpose.

The announcement also adds expression indexes and more control over session settings. Those are separate capabilities, rather than direct replacements for interleaving. They address query and configuration needs without recreating the removed storage hierarchy.

The useful end state for an affected upgrade is a schema without interleaved physical objects and with its required relational definitions intact. The conversion order handles hierarchy dependencies, secondary indexes receive explicit replacements where needed, and the migration check verifies that no incompatible interleaved objects remain when the cluster advances its version.