CockroachDB schema jobs under load

A CockroachDB schema job builds or removes database structure while the application continues using the table. Adding an index, for example, means creating entries for existing rows and keeping those entries correct as new writes arrive. The SQL statement starts that work; the job records its progress and outcome.

The application has a longer task. A finished column addition cannot tell an older caller how to write the new field, and a successful index build cannot establish that the indexed rule is the business rule you intended. Accepting a change needs evidence about the job, the resulting schema, the data and the callers that will depend on it.

We will follow an index build, then apply those distinctions to the shop’s price conversion and optional loyalty codes. Live schema changes and backfills establishes the general compatibility protocol; Inside CockroachDB explains how table and index entries become keys. The native observations here use CockroachDB v26.2.8 on one disposable node. The drawings explain selected orderings rather than reproduce a distributed trace.

Schema jobs and application callers

The shop is replacing decimal price with integer price_hundredths. P3’s 4.50 becomes 450, P7’s 20.00 becomes 2000 and P9’s 35.00 becomes 3500. The amount and its currency meaning stay the same. This is independent of purchase 103: its agreed lines still total 29.00, with no payment.

The first schema step adds somewhere to store the new representation:

ALTER TABLE products ADD COLUMN price_hundredths INT;

That does not ask CockroachDB to calculate price * 100. In the native three-product test, the new field was NULL on all three rows after the column addition. The engine had done the requested schema work correctly; the application migration had barely begun.

The chosen protocol first deploys bridge callers that read price and write both forms atomically. It drains old-only writers, including their in-flight transactions, before backfill. The bridge remains in force while old reads are a supported return path. CockroachDB’s handling of overlapping internal schema versions does not deploy those callers or close an old import path for you.

Keep schema statements separate from the application’s data transactions. CockroachDB does not give arbitrary mixtures of DDL and data changes the ordinary all-or-nothing guarantee. Some combinations involving a newly created table are supported, but that is not a general migration wrapper. Inspect what actually committed after an error before deciding what to retry. Also check the migration tool’s session settings: autocommit_before_ddl can commit preceding work before executing DDL. Disabling that setting does not make unsupported combinations atomic. The transaction limitations define the supported boundary.

Building an index while writes continue

A secondary index is another ordering of stored facts. A price index needs an entry for P7 at 20.00, together with P7’s primary key. Creating only the index definition would leave all existing products missing. Copying the starting rows without coordinating later writes would also be wrong: a price update during the copy could leave an entry for a price the product no longer has.

CockroachDB coordinates schema capabilities, backfill and ongoing writes before making the new index readable. Its documented backfiller uses a temporary index to capture concurrent changes, then merges them into the backfilled index. Transitions wait for the required earlier schema versions to catch up, so in-flight transactions can finish under compatible rules.

Suppose the build has copied P7’s 20.00 price entry when a catalogue update changes it to 22.00, while P9 still needs backfilling. The engine must preserve both parts of that update: remove 20.00 → P7 and add 22.00 → P7. Capturing those changes in the temporary index lets the later merge correct the copied entry. Readers must not use the new index yet: even a correct P7 entry would not supply the missing P9.

The remaining backfill supplies P9. Once merging, validation and the required transitions complete, the new index can answer reads without omitting the old rows or losing the update. Actual v26.2 jobs have additional implementation stages beyond these dependencies. The index-backfill explanation develops the interaction with timestamped data and bulk ingestion.

This is why CockroachDB does not need PostgreSQL’s separate concurrent-build procedure. CONCURRENTLY is accepted for compatibility but adds no behaviour: CockroachDB creates indexes concurrently. Choosing the key and checking the resulting query work belongs in CockroachDB query work and hot ranges.

Online work still shares processor time, storage bandwidth, space and network capacity with foreground requests. Budget for the old structure, new entries, temporary work and ongoing writes together. Watch useful request completions, latency and errors alongside job progress and free space. A backfill advancing faster while purchases fail has consumed the wrong budget. Run one production schema change at a time unless a tested plan establishes adequate headroom.

Follow the job you started

The waiting SQL client and the schema job are different things. CockroachDB emits a notice containing the job identifier while waiting for schema work. Capture that identifier with the submitted SQL, target database, operator and intended change. A client cancellation or lost connection does not establish that the job stopped.

Use the recorded identifier to inspect the exact job. This query uses a driver parameter; bind the identifier as an integer value rather than selecting whichever job happens to be newest:

SELECT job_id, job_type, description, user_name,
       status, running_status, fraction_completed, error
FROM [SHOW JOBS]
WHERE job_id = $1;

status answers where the job is in its lifecycle. running_status gives phase detail when available; fraction_completed gives reported progress, not a promise about remaining wall-clock time. A job can spend time waiting or validating after most entries have been written. Neither a large fraction nor a blank error field establishes success.

Keep retrying work distinct from terminal failure. A transient problem may lead to another execution attempt. A job in reverting is withdrawing a failed or cancelled change; it is not another forward-build phase. revert-failed means that withdrawal itself encountered a non-retryable error and requires investigation. Preserve the full error and the actual schema state before attempting a repair. The job field reference defines these states and the retry-error evidence.

Job listings are retained for a limited period. Save the relevant observations with the migration rather than depending on an old job remaining in the default listing. A terminal succeeded result establishes completion of the requested engine work. Acceptance of the application change comes later.

Pause, cancellation and cleanup

When foreground requests lose their headroom, decide whether to let the current work continue, pause it for a better window or abandon it. That decision needs service health as well as a completion fraction. Repeatedly cancelling and rebuilding an index can spend more of the same scarce resources.

For a verified job identifier, the native controls are PAUSE JOB, RESUME JOB and CANCEL JOB. A successful control request is followed by another observation of that job. pause-requested and cancel-requested describe requests awaiting handling. Polling may miss intermediate states; it must still establish the outcome before the next deployment step.

Pausing preserves work to resume. It also preserves the need for compatible callers: old reads continue, bridge writes continue, and readers do not start depending on an unfinished index or partially migrated field. A paused index backfill can retain a protected timestamp, keeping older data versions from garbage collection. Watch retained history and space while it waits; pause is not indefinite free storage. The pause guidance explains the retained-data cost.

Cancellation asks the engine to abandon a cancellable change and revert its work. It is not a universal undo command. Completed jobs are past that control boundary, and operations including drops and renames have cancellation restrictions. Check the restrictions for the operation before relying on cancellation as the escape route. A new reverse schema change, data reconciliation or recovery may be needed instead.

Reversion and physical reclamation have different end points. A failed index can be gone from the usable schema while discarded keys still occupy storage. Garbage collection has its own work and retention conditions. Do not count that space as available merely because the original build is failed or canceled, and do not expect cancelling its waiting statement to reclaim anything.

When a unique index fails

The optional loyalty-code rule is precise: assigned codes must be unique; any number of customers may be unassigned, represented by NULL. An empty string would be an assigned string value under this rule, not another spelling of NULL. The shared starting case gives Mina TEA-7 and leaves Tariq and Edda unassigned.

For this separate failure experiment, deliberately give Tariq TEA-7 too. Then attempt the stronger database rule:

CREATE UNIQUE INDEX customers_loyalty_unique
ON customers (loyalty_code);

CockroachDB checks existing data as part of creating a unique index and enforces the accepted rule on subsequent writes. Two assigned TEA-7 values cannot satisfy it. The conflicting data must be corrected under the shop’s agreed policy before a fresh build can succeed; afterwards, later assignments must preserve the same uniqueness rule.

The native v26.2.8 test rejected the duplicate build with SQLSTATE 23505. After the assumed agreed correction cleared Tariq’s mistaken assignment, a fresh build succeeded with two NULL values. An attempt to give Tariq TEA-7 then failed with 23505; assigning the unused TEA-8 succeeded, and clearing it to NULL succeeded. The application must turn that duplicate-code error into the intended user result. Retrying the unchanged conflict cannot make the code available.

A duplicate query is useful before the build:

SELECT loyalty_code, count(*)
FROM customers
WHERE loyalty_code IS NOT NULL
GROUP BY loyalty_code
HAVING count(*) > 1;

It reports the data its observation saw. It cannot reserve uniqueness against later writes. Keep code-assignment callers under a compatible policy during rollout, allow for build failure, and depend on the rule only after the engine has accepted it. Do not automatically delete one of two customer rows or invent a replacement code to make the build pass. The data correction needs the shop’s decision.

After failure, inspect both the terminal job outcome and the resulting index definition before starting again. After success, verify the actual key and its NULL policy, then exercise accepted and rejected writes. A successful unique index on the wrong combination of columns would enforce a different rule perfectly.

Accepting the new representation

The price migration needs the same distinction between completed work and accepted meaning. Continue the bridge rule while backfilling from the current stored price. For the three-product example:

UPDATE products
SET price_hundredths = (price * 100)::INT
WHERE price_hundredths IS NULL;

This small statement illustrates the transformation, not a batch size for a large catalogue. The declared domain is finite, non-negative DECIMAL(8,2), so the conversion is exact. Resolve out-of-domain inputs and stored exceptions under an agreed rule first. Preserve the original range while a return to the decimal representation is supported. The general migration article develops bounded batches, late arrivals and conditional writes.

In the separate native price test, a bridge update committed P7 at 22.00 and 2200 before backfill. A stale worker’s attempted 2000 assignment, conditioned on P7 still being 20.00 and missing its new value, affected zero rows. The backfill filled the other two products. P7 remained 22.00/2200, and stock remained 10/0/4. This is a deliberately ordered SQL test; it does not measure simultaneous transaction conflict handling.

Check the resulting relation, including values a null count alone would miss:

SELECT
  count(*) FILTER (
    WHERE price_hundredths IS NULL
  ) AS missing,
  count(*) FILTER (
    WHERE price_hundredths IS DISTINCT FROM price * 100
  ) AS mismatched
FROM products;

Both native acceptance counts were zero after backfill. Deliberately substituting 220 for P7 inside a rolled-back test left zero missing values but one mismatch. That counterexample is why completeness and meaning are separate checks. IS DISTINCT FROM also counts missing values as different from the non-null expected amount.

A zero result describes one snapshot. The continuing bridge rule is what keeps the relation true afterwards. Check representative caller reads as well: 2200 hundredths must produce 22.00, with the same amount and agreed order-line prices as before. Switch readers only after required schema jobs have succeeded, their intended definitions are present, the data checks pass and the caller behaviour is accepted.

Keep both forms current for the agreed return window. Returning to a bridge release can restore old reads; returning to an old-only writer would break the invariant again. The return boundary closes when the old form stops being maintained, even if its column remains present. Before new-only inserts begin, account for the old price NOT NULL requirement and any cross-form checks. Remove old dependencies and then contract in compatible schema steps. Physical cleanup can continue after the old field has ceased to be usable.

What the native run establishes

The disposable fixture uses one v26.2.8 node with replication disabled. Its SQL tests establish column values, duplicate rejection, NULL behaviour and the price checks above. A separate generated workload tests index building with a foreground writer; it does not add products or orders to the shop.

The generated table held 40,000 rows with integer prices and a 128-character payload. A second client committed ten price updates while the index-building client remained outstanding. The completed primary and secondary scans both returned 40,000 rows and a price sum of 19,980,010; a secondary-index read also returned the ten individually changed values. This checks a native online-build outcome with a concurrent writer, without claiming which internal stage each update overlapped.

The successful build reported backfilling, metadata updates and validation while its fraction remained zero in the sampled observations. The failed loyalty job’s phase text said all stages completed while its status was failed. Read status, phase and error together: phase wording alone can describe completed cleanup rather than a successful build.

A separate cancellation attempt remained in reverting at the test’s limit. Closing its waiting client at that bound did not establish a cancelled job, and the later index inspection was not reached. No native pause/resume or completed cancellation is claimed. Separate schema-GC jobs remained at waiting for MVCC GC, including one associated with the successful index build. Such cleanup can concern temporary build data; its presence does not mean the usable new index is being removed. The run establishes neither complete reclamation nor how long it would take.

These bounded checks do not establish production latency, a safe backfill rate, distributed survival or regional timing. Rehearse the change with the actual topology, schema size, request mix and service budget before applying those conclusions to a live cluster.