> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Online Schema Changes

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

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][show-jobs] 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][consistent] state throughout the entire schema change process.

<Danger>
  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.
</Danger>

<Note>
  CockroachDB [does not support schema changes](#limitations) within explicit [transactions][txns] 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.
</Note>

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 <InternalLink path="pause-job">paused</InternalLink>, <InternalLink path="resume-job">resumed</InternalLink>, and <InternalLink path="cancel-job">canceled</InternalLink>.

## Undoing a schema change

Prior to <InternalLink path="architecture/storage-layer#garbage-collection">garbage collection</InternalLink>, it's possible to recover data that may have been lost prior to schema changes by using the <InternalLink path="as-of-system-time">`AS OF SYSTEM TIME`</InternalLink> 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 <InternalLink path="take-full-and-incremental-backups">full or incremental backup</InternalLink> of your cluster.

<a id="best-practices" />

## 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][txns] 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 <InternalLink path="transactions">transaction</InternalLink>:

* You can run schema changes inside the same transaction as a <InternalLink path="create-table">`CREATE TABLE`</InternalLink> statement. For more information, see <InternalLink path="online-schema-changes">Run schema changes inside a transaction with `CREATE TABLE`</InternalLink>. However, a `CREATE TABLE` statement containing <InternalLink path="foreign-key">`FOREIGN KEY`</InternalLink> 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](#schema-change-ddl-statements-inside-a-multi-statement-transaction-can-fail-while-other-statements-succeed).
* <InternalLink path="alter-table#drop-column">`DROP COLUMN`</InternalLink> 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](https://wikipedia.org/wiki/Data_definition_language#ALTER_statement) statements that run inside a multi-statement transaction with non-DDL statements can fail at <InternalLink path="commit-transaction">`COMMIT`</InternalLink> 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.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 <InternalLink path="insert">`INSERT`</InternalLink> statement committed, but the <InternalLink path="alter-table">`ALTER TABLE`</InternalLink> statement adding a <InternalLink path="unique">`UNIQUE` constraint</InternalLink> 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.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 <InternalLink path="alter-table#alter-primary-key">primary key change</InternalLink> 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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE TABLE users (id INT PRIMARY KEY);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
PREPARE prep1 AS SELECT * FROM users;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER TABLE users ADD COLUMN name STRING;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
INSERT INTO users VALUES (1, 'Max Roach');
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 <InternalLink path="cancel-job">cancel</InternalLink> <InternalLink path="alter-type">`ALTER TYPE`</InternalLink> 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]: Blog post with more technical details about how our schema change engine works.
* <InternalLink path="alter-database">`ALTER DATABASE`</InternalLink>
* <InternalLink path="alter-index">`ALTER INDEX`</InternalLink>
* <InternalLink path="alter-range">`ALTER RANGE`</InternalLink>
* <InternalLink path="alter-sequence">`ALTER SEQUENCE`</InternalLink>
* [`ALTER TABLE`][alter-table]
* <InternalLink path="alter-view">`ALTER VIEW`</InternalLink>
* <InternalLink path="create-database">`CREATE DATABASE`</InternalLink>
* [`CREATE INDEX`][create-index]
* <InternalLink path="create-sequence">`CREATE SEQUENCE`</InternalLink>
* <InternalLink path="create-table">`CREATE TABLE`</InternalLink>
* <InternalLink path="create-view">`CREATE VIEW`</InternalLink>
* <InternalLink path="drop-database">`DROP DATABASE`</InternalLink>
* [`DROP INDEX`][drop-index]
* <InternalLink path="drop-sequence">`DROP SEQUENCE`</InternalLink>
* <InternalLink path="drop-table">`DROP TABLE`</InternalLink>
* <InternalLink path="drop-view">`DROP VIEW`</InternalLink>
* [`TRUNCATE`][truncate]

[alter-table]: /docs/v23.1/alter-table

[blog]: https://cockroachlabs.com/blog/how-online-schema-changes-are-possible-in-cockroachdb/

[consistent]: frequently-asked-questions.html#how-is-cockroachdb-strongly-consistent

[create-index]: /docs/v23.1/create-index

[drop-index]: drop-index.html

[create-table]: create-table.html

[select]: selection-queries.html

[show-jobs]: /docs/v23.1/show-jobs

[sql-client]: cockroach-sql.html

[txns]: transactions.html

[truncate]: truncate.html