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.

Tip:

Support for schema changes within transactions is limited. We recommend doing schema changes outside transactions where possible. When a schema management tool uses transactions on your behalf, we recommend only doing one schema change operation per transaction.

How online schema changes work

At a high level, online schema changes are accomplished by using a bridging strategy involving concurrent uses of multiple versions of the schema. The process is as follows:

  • A user initiates a schema change by executing ALTER TABLE, CREATE INDEX, TRUNCATE, etc.

  • The schema change engine converts the original schema to the new schema in discrete steps while ensuring that the underlying table data is always in a consistent state. These changes are executed as a background job.

This approach allows the schema change engine to roll out a new schema while the previous version is still in use. It then backfills or deletes the underlying table data as needed in the background, while the cluster is still running and servicing reads and writes from your application.

During the backfilling process, the schema change engine updates the underlying table data to make sure all instances of the table are stored according to the requirements of the new schema.

Once backfilling is complete, all nodes will switch over to the new schema, and will allow reads and writes of the table using the new schema.

For more technical details, see How online schema changes are possible in CockroachDB.

Examples

Tip:

For more examples of schema change statements, see the ALTER TABLE subcommands.

Run schema changes inside a transaction with CREATE TABLE

As noted in Limitations, you cannot run schema changes inside transactions in general.

However, as of version v2.1, you can run schema changes inside the same transaction as a CREATE TABLE statement. For example:

  1. > BEGIN;
  2.   SAVEPOINT cockroach_restart;
  3.   CREATE TABLE fruits (
  4.         id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  5.         name STRING,
  6.         color STRING
  7.     );
  8.   INSERT INTO fruits (name, color) VALUES ('apple', 'red');
  9.   ALTER TABLE fruits ADD COLUMN inventory_count INTEGER DEFAULT 5;
  10.   ALTER TABLE fruits ADD CONSTRAINT name CHECK (name IN ('apple', 'banana', 'orange'));
  11.   SELECT name, color, inventory_count FROM fruits;
  12.   RELEASE SAVEPOINT cockroach_restart;
  13.   COMMIT;

The transaction succeeds with the following output:

  1. BEGIN
  2. SAVEPOINT
  3. CREATE TABLE
  4. INSERT 0 1
  5. ALTER TABLE
  6. ALTER TABLE
  7. +-------+-------+-----------------+
  8. | name  | color | inventory_count |
  9. +-------+-------+-----------------+
  10. | apple | red   |               5 |
  11. +-------+-------+-----------------+
  12. (1 row)
  13. COMMIT
  14. COMMIT

Run multiple schema changes in a single ALTER TABLE statement

As of v19.1, some schema changes can be used in combination in a single ALTER TABLE statement. For a list of commands that can be combined, see ALTER TABLE. For a demonstration, see Add and rename columns atomically.

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 statement:

  1. > SELECT * FROM [SHOW JOBS] WHERE job_type = 'SCHEMA CHANGE';
  1. +--------------------+---------------+-----------------------------------------------------------------------------+-----------+-----------+----------------------------+----------------------------+----------------------------+----------------------------+--------------------+-------+----------------+
  2. |             job_id | job_type      | description                                                                 | user_name | status    | created                    | started                    | finished                   | modified                   | fraction_completed | error | coordinator_id |
  3. |--------------------+---------------+-----------------------------------------------------------------------------+-----------+-----------+----------------------------+----------------------------+----------------------------+----------------------------+--------------------+-------+----------------|
  4. | 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           |
  5. | 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           |
  6. | 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           |
  7. +--------------------+---------------+-----------------------------------------------------------------------------+-----------+-----------+----------------------------+----------------------------+----------------------------+----------------------------+--------------------+-------+----------------+
  8. (1 row)

Limitations

Overview

Schema changes keep your data consistent at all times, but they do not run inside transactions in the general case. This is necessary so the cluster can remain online and continue to service application reads and writes.

Specifically, this behavior is necessary because 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.

No schema changes within transactions

Within a single transaction:

  • DDL statements cannot follow DML statements. As a workaround, you can do one of the following:
    • Arrange DML statements so they come after DDL statements.
    • Make any schema change statements the first statements that modify the database in a transaction.
    • Split the statements into separate transactions.
  • A CREATE TABLE statement containing FOREIGN KEY or INTERLEAVE clauses cannot be followed by statements that reference the new table.
  • A table cannot be dropped and then recreated with the same name. This is not possible within a single transaction because DROP TABLE does not immediately drop the name of the table. As a workaround, split the DROP TABLE and CREATE TABLE statements into separate transactions.

Tip:

As of version v2.1, you can run schema changes inside the same transaction as a CREATE TABLE statement. For more information, see this example. Also, as of v19.1, some schema changes can be used in combination in a single ALTER TABLE statement. For a list of commands that can be combined, see ALTER TABLE. For a demonstration, see Add and rename columns atomically.

No schema changes between executions of prepared statements

When the schema of a table targeted by a prepared statement changes before the prepared statement is executed, CockroachDB allows the prepared statement to return results based on the changed table schema, for example:

  1. > CREATE TABLE users (id INT PRIMARY KEY);
  1. > PREPARE prep1 AS SELECT * FROM users;
  1. > ALTER TABLE users ADD COLUMN name STRING;
  1. > INSERT INTO users VALUES (1, 'Max Roach');
  1. > EXECUTE prep1;
  1. +----+-----------+
  2. | id |   name    |
  3. +----+-----------+
  4. |  1 | Max Roach |
  5. +----+-----------+
  6. (1 row)

It's therefore recommended to not use SELECT * in queries that will be repeated, via prepared statements or otherwise.

Also, a prepared INSERT, UPSERT, or DELETE statement acts inconsistently when the schema of the table being written to is changed before the prepared statement is executed:

  • If the number of columns has increased, the prepared statement returns an error but nonetheless writes the data.
  • If the number of columns remains the same but the types have changed, the prepared statement writes the data and does not return an error.

Examples of statements that fail

The following statements fail due to the no schema changes within transactions limitation.

Create an index and then run a select against that index inside a transaction

  1. > CREATE TABLE foo (id INT PRIMARY KEY, name VARCHAR);
  2.   BEGIN;
  3.   SAVEPOINT cockroach_restart;
  4.   CREATE INDEX foo_idx ON foo (id, name);
  5.   SELECT * from foo_idx;
  6.   RELEASE SAVEPOINT cockroach_restart;
  7.   COMMIT;
  1. CREATE TABLE
  2. BEGIN
  3. SAVEPOINT
  4. CREATE INDEX
  5. ERROR:  relation "foo_idx" does not exist
  6. ERROR:  current transaction is aborted, commands ignored until end of transaction block
  7. ROLLBACK

Add a column and then add a constraint against that column inside a transaction

  1. > CREATE TABLE foo ();
  2.   BEGIN;
  3.   SAVEPOINT cockroach_restart;
  4.   ALTER TABLE foo ADD COLUMN bar VARCHAR;
  5.   ALTER TABLE foo ADD CONSTRAINT bar CHECK (foo IN ('a', 'b', 'c', 'd'));
  6.   RELEASE SAVEPOINT cockroach_restart;
  7.   COMMIT;
  1. CREATE TABLE
  2. BEGIN
  3. SAVEPOINT
  4. ALTER TABLE
  5. ERROR:  column "foo" not found for constraint "foo"
  6. ERROR:  current transaction is aborted, commands ignored until end of transaction block
  7. ROLLBACK

Add a column and then select against that column inside a transaction

  1. > CREATE TABLE foo ();
  2.   BEGIN;
  3.   SAVEPOINT cockroach_restart;
  4.   ALTER TABLE foo ADD COLUMN bar VARCHAR;
  5.   SELECT bar FROM foo;
  6.   RELEASE SAVEPOINT cockroach_restart;
  7.   COMMIT;
  1. CREATE TABLE
  2. BEGIN
  3. SAVEPOINT
  4. ALTER TABLE
  5. ERROR:  column name "bar" not found
  6. ERROR:  current transaction is aborted, commands ignored until end of transaction block
  7. ROLLBACK

See also

Was this page helpful?
YesNo