CockroachDB 20.1 makes primary keys changeable online

A primary key can outlast the assumptions that made it a good choice. CockroachDB 20.1 introduces online primary-key changes so an existing table can adopt a different key while data remains accessible. The May 12 release notes describe the old primary index becoming a unique secondary index. The change therefore preserves an access path and uniqueness property while giving the table a new primary organization.

The release catalog records 20.1.0 as a production release on May 12. Its SQL additions also include SELECT FOR UPDATE and recursive common table expressions. These capabilities address different application needs, but they share a practical effect. More of a schema transition or data operation can be expressed within the database's documented mechanisms.

I value CockroachDB's serializable default because it gives an application a strong starting point for reasoning about concurrent work. Schema evolution still needs its own explanation. A key change affects the table's access paths and distributed storage work, even when the command is short. The useful question is what the database changes and preserves while the application continues accessing the table.

The old key remains a unique index

The ALTER PRIMARY KEY reference states that the operation may rewrite multiple indexes. It cannot run while the table is already undergoing a primary-key change or another schema change. It requires CREATE privilege on the table. Those conditions make the operation more specific than a metadata rename. For a table with a suitable id column, the command has this form:

ALTER TABLE customer_accounts ALTER PRIMARY KEY USING COLUMNS (id);

The former key becomes a unique secondary index. That helps queries that still filter on its columns, but it also preserves uniqueness for those columns. An application changing its identifier strategy should distinguish changing the primary designation from removing every constraint associated with the previous identifier. The resulting table can have both the new primary key and the old unique access path.

Partitioning needs separate attention too. The reference says the operation does not update existing partition definitions automatically, and the secondary index created from the old primary key is not partitioned. A migration that depends on data placement consequently needs to account for the resulting partition configuration, not simply observe that the new primary key exists.

Online changes are staged work

The schema-change description available before this release explains how schema versions overlap during a transition. A background job progresses through steps and backfills underlying data while the cluster serves application reads and writes. The final schema becomes available after the necessary transition work completes.

That mechanism explains the word online. Application access continues while the database performs the transformation. It does not imply that the transformation consumes no resources or that a primary-key rewrite is equivalent to changing a label in the catalog. The work still has to read and construct the required representations.

I prefer this form of schema evolution when its physical consequences remain visible. A migration can state which key becomes primary and which previous index survives. That gives application and database reviewers a shared object to assess, including queries that continue using the old key. It avoids describing availability during the operation as proof that the resulting access paths are unchanged.

Index selection still follows the workload

The 20.1 index documentation describes how key order affects range traffic. With a sequential index key, new writes can repeatedly append at one end of the keyspace. Splitting a range cannot distribute that incoming traffic if all new values continue arriving beyond the split. More ranges alone do not guarantee that the current writes spread across them.

The release includes experimental hash-sharded indexes for this case. They distribute sequential traffic across hashed index prefixes, with a read-performance tradeoff. The experimental status matters when choosing a new primary organization. A key migration can change which access pattern is primary, but the desired distribution and read behaviour still need to be stated.

The same index documentation warns against retaining unnecessary secondary indexes because they add write work and consume memory. That makes the old key's preservation an intentional part of the migration. It may support important queries and constraints, or it may represent a property the application no longer needs. Those are different resulting schemas, despite both beginning with a request to change the primary key.

Locking can order contending transactions

The SELECT FOR UPDATE reference describes locking selected rows within a transaction. A second transaction attempting the same locking read waits for the first to finish, then sees the value left by that transaction. The feature requires SELECT and UPDATE privileges on the relevant tables. For an existing counter row, a transaction can identify its intended contention point before changing it:

BEGIN;
SELECT value FROM counters WHERE id = 1 FOR UPDATE;
UPDATE counters SET value = value + 1 WHERE id = 1;
COMMIT;

The purpose is to order access to a row that several transactions need to update. It changes when competing work waits, which can reduce retries for the contention pattern described in the release notes. It does not change the application's requirement to handle transaction outcomes according to the database's serializable model.

That distinction is useful for an application performing a read, making a decision and then writing. Locking identifies the row whose concurrent changes matter to that sequence. The application still determines what calculation follows the read and whether the transaction should commit. The database provides an ordering mechanism for the selected row, rather than an application policy for the operation as a whole.

Retry handling remains part of the client contract

The transaction documentation distinguishes automatic retries from cases requiring client intervention. Once results have reached the client, the database cannot necessarily replay an interactive transaction without involving that client. A retryable failure uses SQLSTATE 40001, and client handling may need to rerun the transaction's statements.

SELECT FOR UPDATE can reduce particular contention retries, but it should not be interpreted as a guarantee that every multi-statement transaction completes on its first attempt. A transaction may access other rows or encounter another reason that serializable execution requires retrying. The mechanism and the error-handling contract operate at different levels.

For a reviewer, that means locating the transaction boundary around the business operation. A locking read belongs inside that boundary, and a retry belongs around the work the database requires repeated. Merely adding the clause to one SELECT does not describe how the application responds if the transaction eventually returns a retryable error.

Recursive queries keep their iteration in SQL

The common table expression documentation describes recursive CTEs with an initial query followed by UNION ALL and a recursive query. The initial result seeds a working table. Each iteration uses the current working rows, contributes its results and replaces the working table until an iteration returns nothing. A small example generates a bounded sequence:

WITH RECURSIVE sequence_values (n) AS (
    VALUES (1)
    UNION ALL
    SELECT n + 1 FROM sequence_values WHERE n < 5
)
SELECT n FROM sequence_values;

The predicate supplies the termination condition. In this release the documented recursive form uses UNION ALL, and the PostgreSQL variant using UNION is unsupported. That is a specific compatibility limit despite the familiar SQL syntax. An application porting an iterative query needs to preserve its termination and duplicate-handling assumptions within the form CockroachDB implements.

The working-table model is also useful for reading a recursive query. Its recursive reference represents the rows from the current iteration, rather than a request for the client to issue another SQL statement. The outer query consumes the accumulated result. This lets the reader distinguish the initial conditions from the relationship that advances the computation.

In the example, the next value depends on the current n, and the predicate excludes further expansion after five. A traversal over parent and child identifiers would need the corresponding relationship expressed in its recursive query. Moving iteration into SQL makes that relationship reviewable as part of the statement, without supplying a termination rule automatically for arbitrary application data.

For hierarchical application data, keeping the traversal in a query can remove a sequence of client-directed iterations. The query still needs an explicit relationship defining the next rows and a condition that ends expansion. Recursive CTE support makes that operation expressible within SQL, while primary-key changes and row locking address separate decisions about how the same application's data is organized and updated.