> ## 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
{children}
;
};
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.
* 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.
* The schema change runs as a [background job][show-jobs] without holding locks on the underlying table data.
* As soon as the statement is accepted, CockroachDB returns a SQL `NOTICE` (as shown below) that includes the ID of the background job. To track the job's progress, use `SELECT * FROM [SHOW JOBS] WHERE job_id = {job_id}` or view the DB Console **Jobs Page**.
```
NOTICE: waiting for job(s) to complete: 1145445286329516033
If the statement is canceled, jobs will continue in the background.
```
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](#schema-changes-within-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.
Some tools and applications may be able to workaround CockroachDB's lack of transactional schema changes by [enabling a setting that automatically commits before running schema changes inside transactions](#enable-automatic-commit-before-running-schema-changes-inside-transactions).
## 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:
1. You initiate a schema change by executing [`ALTER TABLE`][alter-table], [`CREATE INDEX`][create-index], [`TRUNCATE`][truncate], etc.
2. 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][show-jobs], and can be paused, resumed, and canceled.
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][blog].
The following online schema changes pause if the node executing the schema change is running out of disk space:
* Changes that trigger an index backfill (adding data to an index).
* The following statements:
* `ADD COLUMN` when the statement also features `INDEX` or `UNIQUE`.
* `ALTER PRIMARY KEY`
* `CREATE INDEX`
* `CREATE MATERIALIZED VIEW`
* `CREATE TABLE AS`
* `REFRESH`
* `SET LOCALITY` under one of the following conditions:
* The locality changes from `REGIONAL BY ROW` to something that is not `REGIONAL BY ROW`.
* The locality changes from something that is not `REGIONAL BY ROW` to `REGIONAL BY ROW`.
If a schema change job is paused, any jobs waiting on that schema change will stop waiting and return an error.
If a schema change fails, the schema change job will be cleaned up automatically. However, there are limitations with rolling back schema changes within a transaction; for more information, see [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).
For advice about how to avoid running out of space during an online schema change, refer to [Estimate your storage capacity before performing online schema changes](#estimate-your-storage-capacity-before-performing-online-schema-changes).
## Best practices for online schema changes
### Improve changefeed performance with `schema_locked`
Set the `schema_locked` table storage parameter to `true` to indicate that a schema change is not currently ongoing on a table. By default, CockroachDB attempts to automatically unset this parameter before performing many schema changes and reapplies it when done. To require manual unlocks for all DDL on schema-locked tables, set `sql.schema.auto_unlock.enabled` to `false`. Some schema changes, such as `ALTER TABLE ... SET LOCALITY`, still require a manual unlock even when automatic unlocking is enabled. Enabling `schema_locked` can help improve performance of changefeeds running on the table, which can reduce commit-to-emit latency.
Use the `create_table_with_schema_locked` session variable to set this storage parameter to `true` on every table created in the session. In v26.1 and later, it is enabled by default.
### Estimate your storage capacity before performing online schema changes
Some schema change operations, like adding or dropping columns or altering primary keys, will temporarily increase a cluster's storage consumption. Specifically, these operations may temporarily require up to three times more storage space for the range size while the schema change is being applied, and this may cause the cluster to run out of storage space or fail to apply the schema change.
To estimate the size of the indexes in your table, use the `SHOW RANGES` statement.
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW RANGES FROM TABLE {table name} WITH DETAILS, KEYS, INDEXES;
```
The output includes a `range_size_mb` column that shows the size of the range in megabytes for each index.
In many cases this range size is trivial, but when the range size is many gigabytes or terabytes, you will need at least three times that amount of free storage space to successfully apply an online schema change.
#### Example of finding the range size of an index
1. Start a 3 node `cockroach demo` cluster with the MovR dataset.
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
cockroach demo --nodes 3
```
2. Turn off the deprecated behavior of `SHOW RANGES`:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SET CLUSTER SETTING sql.show_ranges_deprecated_behavior.enabled TO 'false';
```
3. Find the range size of the indexes in the `movr.vehicles` table:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
WITH x AS (
SHOW RANGES FROM TABLE movr.vehicles WITH DETAILS, KEYS, INDEXES
)
SELECT index_name,
round(range_size_mb, 4) as range_size_mb
FROM x;
```
```
index_name | range_size_mb
----------------------------------------+----------------
vehicles_pkey | 0.0005
vehicles_pkey | 0.0002
vehicles_pkey | 0.0005
vehicles_pkey | 0.0006
vehicles_pkey | 0.0002
vehicles_pkey | 0.0002
vehicles_pkey | 0.0002
vehicles_pkey | 0.0002
vehicles_pkey | 0.0103
vehicles_auto_index_fk_city_ref_users | 0.0103
(10 rows)
```
### Run schema changes with large backfills during off-peak hours
Online schema changes that result in large backfill operations (for example, `ALTER TABLE ... ALTER COLUMN` statements) are computationally expensive, and can result in degraded performance. The admission control system will help keep high-priority operations running, but it's recommended to run backfill-heavy schema changes during times when the cluster is under relatively low loads.
### Schema changes in multi-region clusters
To reduce latency while making online schema changesonline schema changes, we recommend specifying a `lease_preference` zone configuration on the `system` database to a single region and running all subsequent schema changes from a node within that region. For example, if the majority of online schema changes come from machines that are geographically close to `us-east1`, run the following:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER DATABASE system CONFIGURE ZONE USING constraints = '{"+region=us-east1": 1}', lease_preferences = '[[+region=us-east1]]';
```
Access to tables and built-in functions in the `system` database is controlled by the `allow_unsafe_internals` session variable. The above `ALTER DATABASE system` statement executes regardless of the variable's setting because it does not access tables or invoke built-in functions.
Run all subsequent schema changes from a node in the specified region.
If you do not intend to run more schema changes from that region, you can safely remove the lease preference from the zone configuration for the system database.
## Examples
For more examples of schema change statements, see the [`ALTER TABLE`][alter-table] subcommands.
### Run schema changes inside a transaction with `CREATE TABLE`
As noted in [Known limitations](#known-limitations), you cannot run schema changes inside transactions in general.
However, you can run schema changes inside the same transaction as a [`CREATE TABLE`][create-table] statement. For example:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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, refer to `ALTER TABLE`. For examples, refer to Add and rename columns atomically and Drop and add a primary key constraint.
### 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 theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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 paused, resumed, and canceled.
### Enable automatic commit before running schema changes inside transactions
When the `autocommit_before_ddl` session setting is set to `on`, any schema change statement that is sent during an explicit transaction will cause the transaction to commit before executing the schema change.
This setting can be used to:
* Improve compatibility with some third-party tools that do not work well due to our [limitations on schema changes in explicit transactions](#schema-changes-within-transactions).
* Use schema changes more easily under the `READ COMMITTED` isolation level. The error message returned when running schema changes under `READ COMMITTED` isolation includes a hint to use this setting.
With `autocommit_before_ddl` enabled, `COMMIT`, `ROLLBACK`, and other statements that normally return **errors** when used outside of an explicit transaction will instead return **warnings**. This behavior change is necessary because this setting can cause a transaction to end earlier than a client application may expect.
To enable this setting for the current session:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SET autocommit_before_ddl = on;
```
To enable it for all users:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER ROLE ALL SET autocommit_before_ddl = on
```
You can also enable the setting from your application's connection string.
## Demo videos
### Updating primary key columns
To see a demo of an online schema change against a primary key column, watch the following video:
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
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 `INSERT` statement committed, but the `ALTER TABLE` statement adding a `UNIQUE` constraint 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 primary key change 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.
##### Column-dropping schema changes may not roll back properly
Some schema changes that drop columns cannot be rolled back properly.
In some cases, the rollback will succeed, but the column data might be partially or totally missing, or stale due to the asynchronous nature of the schema change.
In other cases, the rollback will fail in such a way that will never be cleaned up properly, leaving the table descriptor in a state where no other schema changes can be run successfully.
To reduce the chance that a column drop will roll back incorrectly:
* Perform column drops in transactions separate from other schema changes. This ensures that other schema change failures will not cause the column drop to be rolled back.
* Drop all constraints (including unique indexes) on the column in a separate transaction, before dropping the column.
* Drop any default values or computed expressions on a column before attempting to drop the column. This prevents conflicts between constraints and default/computed values during a column drop rollback.
If you think a rollback of a column-dropping schema change has occurred, check the jobs table. Schema changes with an error prefaced by `cannot be reverted, manual cleanup may be required` might require manual intervention.
## See also
* [How online schema changes are possible in CockroachDB][blog]: Blog post with more technical details about how our schema change engine works.
* `ALTER DATABASE`
* `ALTER INDEX`
* `ALTER RANGE`
* `ALTER SEQUENCE`
* [`ALTER TABLE`][alter-table]
* `ALTER VIEW`
* `CREATE DATABASE`
* [`CREATE INDEX`][create-index]
* `CREATE SEQUENCE`
* `CREATE TABLE`
* `CREATE VIEW`
* `DROP DATABASE`
* [`DROP INDEX`][drop-index]
* `DROP SEQUENCE`
* `DROP TABLE`
* `DROP VIEW`
* [`TRUNCATE`][truncate]
[alter-table]: /docs/v26.2/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/v26.2/create-index
[drop-index]: drop-index.html
[create-table]: create-table.html
[select]: selection-queries.html
[show-jobs]: /docs/v26.2/show-jobs
[sql-client]: cockroach-sql.html
[txns]: transactions.html
[truncate]: truncate.html