Online Schema Changes

CockroachDB’s online schema changes provide a simple way to update a table schema without imposing any negative consequences on an application — including downtime. The schema change engine is a built-in feature requiring no additional tools, resources, or ad hoc sequencing of operations.

Benefits of online schema changes include:

• Changes to your table schema happen while the database is running.
• The schema change runs as a background job without holding locks on the underlying table data.
• Your application’s queries can run normally, with no effect on read/write latency. The schema is cached for performance.
• Your data is kept in a safe, consistent state throughout the entire schema change process.

Schema changes consume additional resources, and if they are run when the cluster is near peak capacity, latency spikes can occur. This is especially true for any schema change that adds columns, drops columns, or adds an index. We do not recommend doing more than one schema change at a time while in production.

CockroachDB does not support schema changes within explicit transactions with full atomicity guarantees. CockroachDB only supports DDL changes within implicit transactions (individual statements). If a schema management tool uses transactions on your behalf, it should only execute one schema change operation per transaction.

To see a demo of an online schema change, watch the following video:

BEGIN; SAVEPOINT cockroach_restart; CREATE TABLE fruits ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name STRING, color STRING ); INSERT INTO fruits (name, color) VALUES (‘apple’, ‘red’); ALTER TABLE fruits ADD COLUMN inventory_count INTEGER DEFAULT 5; ALTER TABLE fruits ADD CONSTRAINT name CHECK (name IN (‘apple’, ‘banana’, ‘orange’)); SELECT name, color, inventory_count FROM fruits; RELEASE SAVEPOINT cockroach_restart; COMMIT;

The transaction succeeds with the following output:

BEGIN SAVEPOINT CREATE TABLE INSERT 0 1 ALTER TABLE ALTER TABLE +-------+-------+-----------------+ | name | color | inventory_count | +-------+-------+-----------------+ | apple | red | 5 | +-------+-------+-----------------+ (1 row) COMMIT COMMIT

### Run multiple schema changes in a single `ALTER TABLE` statement

Some schema changes can be used in combination in a single `ALTER TABLE` statement. For a list of commands that can be combined, see <InternalLink path="alter-table">`ALTER TABLE`</InternalLink>. For a demonstration, see <InternalLink path="alter-table#add-and-rename-columns-atomically">Add and rename columns atomically</InternalLink>.

### Show all schema change jobs

You can check on the status of the schema change jobs on your system at any time using the [`SHOW JOBS`][show-jobs] statement:

``` sql
> WITH x AS (SHOW JOBS) SELECT * FROM x WHERE job_type = 'SCHEMA CHANGE';

+--------------------+---------------+-----------------------------------------------------------------------------+-----------+-----------+----------------------------+----------------------------+----------------------------+----------------------------+--------------------+-------+----------------+
|             job_id | job_type      | description                                                                 | user_name | status    | created                    | started                    | finished                   | modified                   | fraction_completed | error | coordinator_id |
|--------------------|---------------|-----------------------------------------------------------------------------|-----------|-----------|----------------------------|----------------------------|----------------------------|----------------------------|--------------------|-------|----------------|
| 368863345707909121 | SCHEMA CHANGE | ALTER TABLE test.public.fruits ADD COLUMN inventory_count INTEGER DEFAULT 5 | root      | succeeded | 2018-07-26 20:55:59.698793 | 2018-07-26 20:55:59.739032 | 2018-07-26 20:55:59.816007 | 2018-07-26 20:55:59.816008 |                  1 |       | NULL           |
| 370556465994989569 | SCHEMA CHANGE | ALTER TABLE test.public.foo ADD COLUMN bar VARCHAR                          | root      | pending   | 2018-08-01 20:27:38.708813 | NULL                       | NULL                       | 2018-08-01 20:27:38.708813 |                  0 |       | NULL           |
| 370556522386751489 | SCHEMA CHANGE | ALTER TABLE test.public.foo ADD COLUMN bar VARCHAR                          | root      | pending   | 2018-08-01 20:27:55.830832 | NULL                       | NULL                       | 2018-08-01 20:27:55.830832 |                  0 |       | NULL           |
+--------------------+---------------+-----------------------------------------------------------------------------+-----------+-----------+----------------------------+----------------------------+----------------------------+----------------------------+--------------------+-------+----------------+
(1 row)

All schema change jobs can be , , and .

Undoing a schema change

Prior to , it’s possible to recover data that may have been lost prior to schema changes by using the parameter. However, this solution is limited in terms of time, and doesn’t work beyond the designated garbage collection window.

For more long-term recovery solutions, consider taking either a of your cluster.

Limitations

Schema changes within transactions

Most schema changes should not be performed within an explicit transaction with multiple statements, as they do not have the same atomicity guarantees as other SQL statements. Execute schema changes either as single statements (as an implicit transaction), or in an explicit transaction consisting of the single schema change statement. There are some exceptions to this, detailed below.

Schema changes keep your data consistent at all times, but they do not run inside transactions in the general case. Making schema changes transactional would mean requiring a given schema change to propagate across all the nodes of a cluster. This would block all user-initiated transactions being run by your application, since the schema change would have to commit before any other transactions could make progress. This would prevent the cluster from servicing reads and writes during the schema change, requiring application downtime.

Some schema change operations can be run within explicit, multiple statement transactions. CREATE TABLE and CREATE INDEX statements can be run within the same transaction with the same atomicity guarantees as other SQL statements. There are no performance or rollback issues when using these statements within a multiple statement transaction.

Within a single :

• You can run schema changes inside the same transaction as a statement. For more information, see . However, a CREATE TABLE statement containing clauses cannot be followed by statements that reference the new table.
• Schema change DDL statements inside a multi-statement transaction can fail while other statements succeed.
• can result in data loss if one of the other schema changes in the transaction fails or is canceled. To work around this, move the DROP COLUMN statement to its own explicit transaction or run it in a single statement outside the existing transaction.

If a schema change within a transaction fails, manual intervention may be needed to determine which statement has failed. After determining which schema change(s) failed, you can then retry the schema change.

Schema change DDL statements inside a multi-statement transaction can fail while other statements succeed

Most schema change DDL statements that run inside a multi-statement transaction with non-DDL statements can fail at time, even if other statements in the transaction succeed. This leaves such transactions in a “partially committed, partially aborted” state that may require manual intervention to determine whether the DDL statements succeeded.

Some DDL statements do not have this limitation. CREATE TABLE and CREATE INDEX statements have the same atomicity guarantees as other statements within a transaction.

If such a failure occurs, CockroachDB will emit a CockroachDB-specific error code, XXA00, and the following error message:

transaction committed but schema change aborted with error: <description of error>
HINT: Some of the non-DDL statements may have committed successfully, but some of the DDL statement(s) failed.
Manual inspection may be required to determine the actual state of the database.

If you must execute schema change DDL statements inside a multi-statement transaction, we strongly recommend checking for this error code and handling it appropriately every time you execute such transactions.

This error will occur in various scenarios, including but not limited to:

• Creating a unique index fails because values aren’t unique.
• The evaluation of a computed value fails.
• Adding a constraint (or a column with a constraint) fails because the constraint is violated for the default/computed values in the column.

To see an example of this error, start by creating the following table.

CREATE TABLE T(x INT);
INSERT INTO T(x) VALUES (1), (2), (3);

Then, enter the following multi-statement transaction, which will trigger the error.

BEGIN;
ALTER TABLE t ADD CONSTRAINT unique_x UNIQUE(x);
INSERT INTO T(x) VALUES (3);
COMMIT;

pq: transaction committed but schema change aborted with error: (23505): duplicate key value (x)=(3) violates unique constraint "unique_x"
HINT: Some of the non-DDL statements may have committed successfully, but some of the DDL statement(s) failed.
Manual inspection may be required to determine the actual state of the database.

In this example, the statement committed, but the statement adding a failed. We can verify this by looking at the data in table t and seeing that the additional non-unique value 3 was successfully inserted.

SELECT * FROM t;

  x
+---+
  1
  2
  3
  3
(4 rows)

No online schema changes if primary key change in progress

You cannot start an online schema change on a table if a is currently in progress on the same table.

No online schema changes between executions of prepared statements

When the schema of a table targeted by a prepared statement changes after the prepared statement is created, future executions of the prepared statement could result in an error. For example, adding a column to a table referenced in a prepared statement with a SELECT * clause will result in an error:

CREATE TABLE users (id INT PRIMARY KEY);

PREPARE prep1 AS SELECT * FROM users;

ALTER TABLE users ADD COLUMN name STRING;

INSERT INTO users VALUES (1, 'Max Roach');

EXECUTE prep1;

ERROR: cached plan must not change result type
SQLSTATE: 0A000

It’s therefore recommended to explicitly list result columns instead of using SELECT * in prepared statements, when possible.

ALTER TYPE schema changes cannot be cancelled

You can only schema change jobs that drop values. All other ALTER TYPE schema change jobs are non-cancellable.

See also

• How online schema changes are possible in CockroachDB: Blog post with more technical details about how our schema change engine works.
• ALTER TABLE
• CREATE INDEX
• DROP INDEX
• TRUNCATE