Chapter 7: Schema Creation and Management

In Chapter 6, How Indexes Work in CockroachDB, we learned what indexes are, how they are useful in improving query performance, the various types of indexes that are supported in CockroachDB, and the best practices while using indexes.

In this chapter, we will go through the syntax for various Structured Query Language (SQL) operations. Though we have learned some of the syntaxes throughout other chapters, it's useful to have all of them in a single place. Throughout this chapter, only commonly used query options are included, and some of the experimental and enterprise-only features have been left out.

The following topics will be covered in this chapter:

  • DDL
  • DML
  • DQL
  • Supported data types
  • Column-level constraints
  • Table joins
  • Using sequences
  • Managing schema changes

Technical requirements

We will need at least a single-node CockroachDB cluster to try some of the queries discussed in this chapter. So, please refer to the Technical requirements section of Chapter 2, How Does CockroachDB Work Internally?

If you want to try this on a larger CockroachDB cluster, then please refer to the Working example of fault tolerance at play section of Chapter 5, Fault Tolerance and Auto-Rebalancing, where we create a six-node cluster.

Also, if you are not sure about some SQL operations and how to use them, you can just use the CockroachDB Help option to get more information.

For example, if you want to know about all the options available with CREATE TABLE, you can just try h CREATE TABLE in the SQL client console, as shown in the following code snippet:

root@localhost:26258/test> h CREATE TABLE

You can try h with any of the commands to get more detailed information.

In the first section, we will learn about DDL statements.

DDL

DDL statements are mainly responsible for creating, altering, and dropping tables, indexes, and users. DDL statements typically comprise CREATE, ALTER, and DROP operations. In this section, we will go over various DDL operations and their syntax, starting with CREATE.

CREATE

CREATE is the keyword used for creating something new such as a database, schema, table, view, or user.

A CREATE DATABASE statement accepts the following parameters:

  • IF NOT EXISTS: Creates a new database, only if the database with the same name does not exist previously
  • database_name: Name of the database to be created

The following databases are created by default and are used internally by CockroachDB:

  • postgres: Empty database that is provided for compatibility with Postgres clients
  • system: A database that contains CockroachDB metadata and that is read-only

The following default database is used for default connections:

  • defaultdb: Used when a client doesn't specify a database in connection parameters

The following default databases are used for demonstration purposes:

  • movr: Sample database with users, vehicles, and rides for vehicle-sharing apps
  • startrek: A database that contains quotes from Star Trek episodes

Some of these databases are used internally by CockroachDB, and some are there with the sample schema maintained by Cockroach Labs, so it is advisable not to use any of these preloaded databases and to instead create separate ones for your application.

Let's look at a specific example of creating a database. You can view the code here:

CREATE DATABASE users PRIMARY REGION "us-central1" REGIONS "us-east1", "us-central1", "us-west1" SURVIVE REGION FAILURE;

The preceding statement will create a database with the primary region being "us-central1" and with "us-east1", "us-central1", and "us-west1" database regions.

Next, we will look at the syntax of the CREATE TABLE statement.

CREATE TABLE syntax

The CREATE TABLE statement accepts the following parameters:

  • IF NOT EXISTS: Creates a table if a table with the same name doesn't exist already.
  • table_name: Name of the table.
  • column_def: Column definition that includes a column name and a data type. Please refer to the Column-level constraints section to explore all the constraints you can provide for a column.
  • index_def: Comma-separated list of index definitions.
  • family_def: List of column family definitions. A comma is used as a separator. A Column family is stored as a single key-value pair.
  • table_constraint: List of table-level constraints. A comma is used as a separator.

For complete options, please check out the CREATE TABLE documentation by Cockroach Labs at the following link: https://www.cockroachlabs.com/docs/stable/create-table.html.

Here is an example of the CREATE TABLE statement being deployed:

CREATE TABLE users (

id INT NOT NULL,

name STRING NOT NULL,

age INT NOT NULL,

PRIMARY KEY (id)

);

Optionally, you can also create a schema. In the naming hierarchy, a cluster can have multiple databases, a database can have multiple schemas, and a schema can contain multiple tables, views, and sequences. A schema can be created using the CREATE SCHEMA <schema_name> statement.

CREATE VIEW

CREATE VIEW is used for creating views. A view is a virtual table that stores the result of a query. Whenever there is a change in the data in the original table, it is automatically updated. Views don't take up additional physical storage space.

A materialized view is similar to a view but it's physically stored separately. When the data is changed in the original table, materialized views don't get updated automatically. You can use REFRESH MATERIALIZED VIEW <view_name> to refresh the contents of a materialized view.

Views are useful for the following reasons:

  • They're helpful if you don't want to expose all the columns in the original table due to security concerns.
  • Views can contain query results of complex queries, which you don't have to execute explicitly every time.
  • A single view can have data from multiple tables and databases.
  • They provide meaningful aliases for column names.
  • Materialized views can be used for better performance.

CREATE VIEW accepts the following parameters:

  • MATERIALIZED: Creates a materialized view.
  • IF NOT EXISTS: Creates a view if a view with the same name doesn't already exist.
  • OR REPLACE: Creates a view if it doesn't already exist and replaces the view if it already exists. When replacing an existing view, columns in the previous view must appear in the same order as a prefix. However, additional columns are allowed.
  • view_name: Name of the view to be created.
  • column_name_list: Comma-separated list of column names.
  • AS select_statement: A SELECT query, whose results are stored in the view.

Let's look at example of how views are helpful. Let's first insert some records into the users table that was created previously, as follows:

INSERT INTO users(id, name, age) values ( 1, 'foo', 13);

INSERT INTO users(id, name, age) values ( 2, 'bar', 24);

INSERT INTO users(id, name, age) values ( 3, 'alice', 14);

INSERT INTO users(id, name, age) values ( 4, 'bob', 29);

> select * from users;

  id | name  | age

-----+-------+------

   1 | foo   |  13

   2 | bar   |  24

   3 | alice |  14

   4 | bob   |  29

(4 rows)

Now, let's say you want to track all users who are in the age group of 12-17.

You can run this query on the users table itself and get the result, as follows:

> SELECT * FROM users where age > 12 AND age < 17;

  id | name  | age

-----+-------+------

   1 | foo   |  13

   3 | alice |  14

Now, let's say you don't want to run any query directly on the users table. Then, you can create a view with that condition and query the view, like this:

> CREATE VIEW vaccine_big_kids_group

  AS SELECT id, name, age

  FROM users

  WHERE age > 12 AND age <17;

Let's query the view and check the results, as follows:

> select * from vaccine_big_kids_group;

  id | name  | age

-----+-------+------

   1 | foo   |  13

   3 | alice |  14

(2 rows)

Let's insert one more record that falls under the same age group of 12-17, as follows:

INSERT INTO users(id, name, age) values ( 5, 'john', 15);

Since the view directly refers to the original table, it's automatically updated, as shown here:

> select * from vaccine_big_kids_group;

  id | name  | age

-----+-------+------

   1 | foo   |  13

   3 | alice |  14

   5 | john  |  15

(3 rows)

Instead of a view, if you had created a materialized view, you would have had to explicitly refresh in order to get the latest data. Here is an example of creating a materialized view and refreshing it:

> CREATE MATERIALIZED VIEW vaccine_big_kids_group_materialized

  AS SELECT *

  FROM users

  WHERE age > 12 AND age <17;

REFRESH MATERIALIZED VIEW vaccine_big_kids_group_materialized;

CockroachDB also supports session-scoped temporary views, which are automatically dropped at the end of a session. You can use CREATE VIEW TEMP <view_definition> to create a temporary view.

ALTER

The ALTER statement is used to modify an existing schema object. You can alter the definition of several schema objects such as DATABASE, SCHEMA, TABLE, COLUMN, TYPE, USER, INDEX, VIEW, and so on. We will go over ALTER TABLE and ALTER INDEX in this subsection.

ALTER TABLE takes the following parameters:

  • ADD COLUMN: Adds one or more columns
  • ADD CONSTRAINT: Adds a constraint to a column
  • ALTER COLUMN: Modifies an existing column
  • ALTER PRIMARY KEY: Changes the primary key (PK)
  • DROP COLUMN: Removes a column or multiple columns
  • DROP CONSTRAINT: Removes column-level constraints  

ALTER INDEX takes the following parameters:

  • CONFIGURATION ZONE: Configures replication zones for the index
  • RENAME: Renames the index

Here is an example of a column being added:

ALTER TABLE users ADD COLUMN new_column INT;

> SHOW COLUMNS from users;

  column_name | data_type | is_nullable | column_default | generation_expression |  indices  | is_hidden

--------------+-----------+-------------+----------------+-----------------------+-----------+------------

  id          | INT8      |    false    | NULL           |                       | {primary} |   false

  name        | STRING    |    false    | NULL           |                       | {}        |   false

  age         | INT8      |    false    | NULL           |                       | {}        |   false

  new_column  | INT8      |    true     | NULL           |                       | {}        |   false

(4 rows)

Here is an example of a constraint being added:

> ALTER TABLE users ADD CONSTRAINT age_check CHECK (age > 0);

INSERT INTO users(id, name, age) values ( 0, 'cindy', -1);

ERROR: failed to satisfy CHECK constraint (age > 0:::INT8)

SQLSTATE: 23514

CONSTRAINT: age_check

DROP

The DROP statement is used to delete a schema object and all the data within it.

The general syntax for DROP is DROP <SCHEMA TYPE> name.

You can see an example here:

DROP DATABASE <database_name>;

DROP ROLE <role_name>;

DROP TABLE <table_name>;

DROP TABLE takes the following parameters:

  • IF EXISTS: Drops the table if it exists; if not, it doesn't return any error
  • table_name: Name of the table to drop
  • CASCADE: Drops all schema objects that depend on the table, such as views and constraints
  • RESTRICT: Restricts the table from getting dropped, if any objects depend on it

Now, let's look at DML statements in the next section.

DML

DML statements are used for managing data within schema objects. They consist of INSERT, UPDATE, UPSERT, and DELETE statements and are generally referred to as statements that insert, update, or delete the data in a database. We will go over the syntax of INSERT, UPDATE, UPSERT, and DELETE statements used for a table in this section.

The INSERT statement takes the following parameters:

  • common_table_expression: Common table expressions (CTEs) provide a shorthand for a subquery, to improve the readability.
  • table_name: Name of the table into which the data is inserted.
  • AS table_alias: Alias for the table name.
  • column_name: Name of the column that is being populated.
  • select_statement: A selection query, whose result is used to insert the data. The column order and column data types of the SELECT query result should match that of the table into which the data is getting inserted.

Here is an example of INSERT INTO:

INSERT INTO users(id, name, age) values ( 0, 'cindy', 15);

The UPDATE statement updates the existing rows of a table and takes the following parameters:

  • common_table_expression: CTEs provide a shorthand for a subquery, to improve the readability.
  • table_name: Name of the table in which the rows are updated.
  • AS table_alias: Alias for the table name.
  • column_name: Name of the column that is being updated.
  • a_expression: A new value you want to update, an aggregate function, or a scalar expression used to derive the value.
  • FROM table_reference: Specifies a table to reference, but not update.
  • select_statement: A selection query, whose result is used to update the data. The column order and column data types of the SELECT query result should match that of the table where the data is getting updated.
  • WHERE a_expression: An expression that should evaluate to a Boolean value. A row is updated if this expression returns TRUE.
  • sort_clause: An ORDER BY clause.
  • limit_clause: A LIMIT clause.

Here is an example of the UPDATE statement being deployed, and also the table data before and after the update:

> select * from users;

  id | name  | age | new_column

-----+-------+-----+-------------

   0 | cindy |  15 |       NULL

   1 | foo   |  13 |       NULL

   2 | bar   |  24 |       NULL

   3 | alice |  14 |       NULL

   4 | bob   |  29 |       NULL

   5 | john  |  15 |       NULL

(6 rows)

> UPDATE users SET age = 39 WHERE id = 4;

> select * from users;

  id | name  | age | new_column

-----+-------+-----+-------------

   0 | cindy |  15 |       NULL

   1 | foo   |  13 |       NULL

   2 | bar   |  24 |       NULL

   3 | alice |  14 |       NULL

   4 | bob   |  39 |       NULL

   5 | john  |  15 |       NULL

(6 rows)

The UPSERT statement inserts rows if they don't violate uniqueness constraints, and it updates rows if the values violate uniqueness constraints. UPSERT only looks at PKs for uniqueness.

The UPSERT statement takes the following parameters:

  • common_table_expression: CTEs provide a shorthand for a subquery, to improve the readability.
  • table_name: Name of the table into which the data is upserted.
  • AS table_alias: Alias for the table name.
  • column_name: Name of the column that is being populated during the upsert.
  • select_statement: A selection query, whose result is used to upsert the data. The column order and column data types of the SELECT query result should match that of the table into which the data is getting upserted.
  • DEFAULT VALUES: Used to fill a column with its default value, instead of a SELECT query. Here's an example of this:

    > select * from users;

      id | name  | age | new_column

    -----+-------+-----+-------------

       0 | cindy |  15 |       NULL

       1 | foo   |  13 |       NULL

       2 | bar   |  24 |       NULL

       3 | alice |  14 |       NULL

       4 | bob   |  39 |       NULL

       5 | john  |  15 |       NULL

    (6 rows)

    > UPSERT INTO users(id, name, age) VALUES (2, 'bar', 34);

    > select * from users;

      id | name  | age | new_column

    -----+-------+-----+-------------

       0 | cindy |  15 |       NULL

       1 | foo   |  13 |       NULL

       2 | bar   |  34 |       NULL

       3 | alice |  14 |       NULL

       4 | bob   |  39 |       NULL

       5 | john  |  15 |       NULL

    (6 rows)

The DELETE statement deletes rows from a table. It takes the following parameters:

  • common_table_expression: CTEs provide a shorthand for a subquery, to improve the readability.
  • table_name: Name of the table in which the data is deleted.
  • AS table_alias: Alias for the table name.
  • WHERE a_expression: An expression that should evaluate to a Boolean value. A row is updated if this expression returns TRUE.
  • sort_clause: An ORDER BY clause.
  • limit_clause: A LIMIT clause.

Have a look at the following example:

> select * from users;

  id | name  | age | new_column

-----+-------+-----+-------------

   0 | cindy |  15 |       NULL

   1 | foo   |  13 |       NULL

   2 | bar   |  34 |       NULL

   3 | alice |  14 |       NULL

   4 | bob   |  39 |       NULL

   5 | john  |  15 |       NULL

(6 rows)

> DELETE from users where id = 4;

> select * from users;

  id | name  | age | new_column

-----+-------+-----+-------------

   0 | cindy |  15 |       NULL

   1 | foo   |  13 |       NULL

   2 | bar   |  34 |       NULL

   3 | alice |  14 |       NULL

   5 | john  |  15 |       NULL

(5 rows)

In the next section, we will go through some examples of DQL.

DQL

DQL is used for reading or querying data or table metadata. SQL statements involving SELECT and SHOW fall under this category.

SELECT is a very commonly used SQL syntax to read table data. It takes the following parameters:

  • ALL: Doesn't eliminate duplicate rows.
  • DISTINCT: Eliminates duplicate rows.
  • DISTINCT ON ( a_expression): Eliminates duplicate rows based on a scalar expression.
  • target_element: A scalar expression to determine a column in each result row, or to retrieve all columns in case of an asterisk (*).
  • table_expression: A table expression from which the data has to be retrieved.
  • AS OF SYSTEM TIME  timestamp: Retrieves data as it existed at the time of this timestamp, where timestamp refers to a specific time in the past. This can return historical data, which can be stale.
  • WHERE a_expression: Only retrieves rows that return TRUE for a_expression.
  • GROUP BY a_expression: Groups results on one or more columns.
  • HAVING a_expression: Only retrieves aggregate function groups that return TRUE for a_expression.
  • WINDOW window_definition_list: List of window definitions. The window function computes values by operating on one or more rows returned by a SELECT query.

SHOW SQL syntax is commonly used to retrieve the table metadata.

You can use the following queries to list down databases, tables in a database, and columns for a given table respectively:

SHOW DATABASES;

SHOW TABLES;

SHOW COLUMNS FROM <table_name>;

SHOW STATISTICS FOR TABLE <table_name>; is useful for looking at table statistics. This data is used by the cost-based optimizer to improve query performance. This also comes in handy when debugging slow queries.

SHOW can be combined with keywords such as COLUMNS, DATABASES, TABLES, CREATE, RANGES, STATISTICS, TYPES, USERS, ROLES, REGIONS, and so on to retrieve the metadata associated with a given schema object.

Next, we will go over the list of data types supported by CockroachDB.

Supported data types

In this section, we will go through a list of data types that CockroachDB supports. Here is a list of data types and their descriptions:

  • ARRAY: Single-dimensional, homogenous array of any non-array data type—for example, {"cockroachdb", "spanner", "yugabytedb"}.
  • BIT: String of binary digits.
  • BOOL: Boolean value.
  • BYTES: String of binary characters.
  • DATE: Date.
  • ENUM: User-defined data type that consists of a list of static values—for example, ENUM ('earth', 'mars', 'venus').
  • DECIMAL: Exact, fixed-point number.
  • FLOAT: A 64-bit, inexact, floating-point number.
  • INET: Internet Protocol version 4 (IPv4) and version 6 (IPv6) address.
  • INT: Signed integer.
  • INTERVAL: Span of time—for example, INTERVAL '5h39m23s'.
  • JSONB: JavaScript Object Notation (JSON) data. Here is an example of JSON data:

    '{

        "id": 12345,

        "name": "elan",

        "isAlien": "true"

      }'

  • SERIAL: Pseudo data type that combines an integer with a DEFAULT expression. A DEFAULT expression generates different values every time it is evaluated. It ensures a given column gets a value if the INSERT statement doesn't specify a value for it, instead of populating it as NULL.
  • STRING: String of Unicode characters.
  • TIME: Time in Coordinated Universal Time (UTC).
  • TIMETZ: Time value with a specified time zone offset from UTC.
  • TIMESTAMP: Stores time and date in UTC.
  • TIMESTAMPTZ: Time and date, with a specified time zone offset from UTC.
  • UUID: 128-bit hexadecimal value.

In the next section, we will learn about database constraints and various types of constraints in CockroachDB.

Column-level constraints

Constraints are rules that are enforced on data columns in a table. Whenever there is any change in data within a table, all the constraints are verified to make sure none is violated. If violated, the changes are rejected with the appropriate error message. Here is a list of column-level constraints:

  • CHECK <condition>: A given condition is checked whenever a value is being inserted into the table. The condition is a Boolean expression that should evaluate to TRUE or NULL. If it returns FALSE for any value, the entire statement is rejected. It is possible to have multiple checks for the same column.

Here's an example to illustrate this:

CREATE TABLE user (

id INT NOT NULL,

name STRING NOT NULL,

age INT NOT NULL CHECK (age > 18) CHECK (age < 65),

PRIMARY KEY (id)

);

  • DEFAULT: The DEFAULT value constraint is exercised whenever the INSERT statement doesn't explicitly insert a specific value or NULL for the column that has a DEFAULT constraint. A data type of a DEFAULT value should be the same as that of the original column.

Here's an example to illustrate this:

CREATE TABLE employees (

id INT NOT NULL,

name STRING NOT NULL,

salary FLOAT NOT NULL,

bonus FLOAT DEFAULT 0.0,

PRIMARY KEY (id)

);

Now, if you insert a row without providing any value for the bonus column (for example, INSERT INTO employee (id, name, salary) VALUES (1, 'foo', 10000.00);), CockroachDB inserts the default value 0.0 for the bonus column.

  • FOREIGN KEY: This refers to columns in some other table.

Let's look at an example whereby we create an employees table, as shown in the following code snippet, followed by an employee_info table in which the emp_id column refers to the id column of the employees table:

CREATE TABLE employees (

id INT NOT NULL,

name STRING NOT NULL,

salary FLOAT NOT NULL,

bonus FLOAT DEFAULT 0.0,

PRIMARY KEY (id)

);

Here is an example where the emp_id column references the id column in the previous employees table:

CREATE TABLE employees_info (

emp_id INT NOT NULL,

address STRING NOT NULL,

CONSTRAINT fk_emp_id FOREIGN KEY (emp_id)  REFERENCES employees (id)

);

Alternatively, an employees_info table can also be created, as follows:

CREATE TABLE employees_info (

emp_id INT NOT NULL REFERENCES employees(id),

address STRING NOT NULL

);

  • NOT NULL: Use this if you don't want a column to have NULL values. Any INSERT or UPDATE statement that tries to insert a NULL value will be rejected.
  • PRIMARY KEY: The PRIMARY KEY constraint specifies that a column or set of columns that are part of a PK must uniquely identify each row in a table. Tables can have only one PK.

Here's an example to illustrate this:

CREATE TABLE employees (

id INT NOT NULL,

name STRING NOT NULL,

salary FLOAT NOT NULL,

bonus FLOAT DEFAULT 0.0,

PRIMARY KEY (id)

);

  • UNIQUE: The UNIQUE constraint, shown in the following code snippet, ensures that any non-NULL column has a unique value:

    CREATE TABLE user (

    id INT NOT NULL,

    email STRING UNIQUE,

    name STRING NOT NULL,

    age INT NOT NULL CHECK (age > 18) CHECK (age < 65),

    PRIMARY KEY (id)

    );

In the next section, we will go over various types of joins.

Table joins

Table joins are used to combine data from more than one table based on certain conditions on a certain column or columns.

For example, let's assume the following two tables exist:

CREATE TABLE customers (

    id UUID PRIMARY KEY,

    name STRING NOT NULL

);

CREATE TABLE purchase_orders (

     id UUID PRIMARY KEY,

     customer_id UUID,

     n_of_items INT,

     total_price DECIMAL(10,2)

);

Now, let's look at each of the JOIN types with an example, as follows:

  • INNER JOIN: Returns rows from the left and right operands that match the condition.

Let's look at the following example involving an inner join between the customers and purchase_orders tables:

SELECT a.id as customer_id, a.name AS customer_name, b.id AS purchase_order_id  FROM customers AS a

INNER JOIN purchase_orders AS b ON a.id = b.customer_id;

This returns all customers and their purchase orders that have matching customers.id values with that of purchase_orders.customer_id.

  • LEFT OUTER JOIN: All values from the left table, and for every left row where there is no match on the right, NULL values are returned for the columns on the right.

Let's look at the following example involving a left outer join between the customers and purchase_orders tables:

SELECT a.id as customer_id, a.name AS customer_name, b.id AS purchase_order_id  FROM customers AS a

LEFT OUTER JOIN purchase_orders AS b ON a.id = b.customer_id;

The preceding query will return all customers, whether they have any purchase orders or not.

  • RIGHT OUTER JOIN: All values from the right table, and for every right row where there is no match on the left, NULL values are returned for the columns on the left.

Let's look at the following example involving a right outer join between the customers and purchase_orders tables:

SELECT a.id as customer_id, a.name AS customer_name, b.id AS purchase_order_id  FROM customers AS a

RIGHT OUTER JOIN purchase_orders AS b ON a.id = b.customer_id;

The preceding query will return all purchase_orders values, whether they have a corresponding customer in the customers table or not.

  • FULL JOIN: For every row on one side of the join where there is no match on the other side, NULL values are returned for the columns on the non-matching side.

Have a look at the following example to illustrate this:

SELECT a.id as customer_id, a.name AS customer_name, b.id AS purchase_order_id  FROM customers AS a

FULL OUTER JOIN purchase_orders AS b ON a.id = b.customer_id;

The preceding query will return all the rows from both tables.

Next, we will learn about sequences and the syntax for creating a sequence.

Using sequences

Sequences are helpful when you need an auto-increment integer sequence in a table.

Let's look at the following example in which we create a sequence with the default setting:

root@localhost:26258/test> CREATE SEQUENCE customer_id_seq;

CREATE SEQUENCE

Time: 158ms total (execution 158ms / network 0ms)

root@localhost:26258/test> SHOW CREATE customer_id_seq;

    table_name    |                                          create_statement

------------------+-----------------------------------------------------------------------------------------------------

  customer_id_seq | CREATE SEQUENCE public.customer_id_seq MINVALUE 1 MAXVALUE 9223372036854775807 INCREMENT 1 START 1

(1 row)

Time: 136ms total (execution 135ms / network 1ms)

Here is an example of using the previously created sequence in a table column:

CREATE TABLE customers (

    id INT,

    row_id INT DEFAULT nextval('customer_id_seq'),

    name STRING

);

Here, whenever a row is inserted, DEFAULT will call nextval, which will generate increments in the customer_id_seq sequence and use that as the value for the row_id column.

Next, we will go through the benefits of online schema changes.

Managing schema changes

CockroachDB supports online schema changes, which don't require any downtime. An existing schema can be changed using statements that include ALTER and CREATE INDEX operations.

CockroachDB internally maintains a consistent distributed schema cache, along with consistent table data that works with multiple versions of the schema concurrently. This enables the rolling out of a new schema while the older schema is still being used. CockroachDB backfills the table data into the newer schema without holding locks. So, online schema changes don't affect the current read/write operations on the cluster on a particular table whose schema is being modified.

Here are some of the benefits of online schema changes:

  • Zero downtime.
  • Schema changes happen without holding any table-level locks, so application workloads on the cluster can continue to operate without performance degradation.
  • Data is kept consistent throughout the schema upgrade.

Here are some of the known limitations of schema changes:

  • If you are using a multi-statement transaction in which you combine DDL and non-DDL statements, DDL statements can fail at COMMIT, while non-DDL statements might have been committed. This can result in an inconsistent state, whereby some of the statements in a transaction are committed and some of them are aborted. It's important to look for appropriate error codes and handle them correctly.
  • You have to pay extra attention if you are using prepared statements on a table whose schema is changed before the prepared statement is executed. This can result in inconsistencies.

Please refer to a complete list of known limitations here: https://www.cockroachlabs.com/docs/stable/known-limitations.html.

Some of these limitations might get fixed in future releases, and there might also be new ones.

With that, we have reached the end of this chapter.

Summary

In this chapter, we learned about the SQL syntax for DDLs, DMLs, and DQLs. We also went over other popular features such as indexes, joins, and sequences. We have left out some of the experimental and enterprise-only features. Also, whenever you are not sure about the syntax of a given SQL operation, please use /h to get detailed information about the SQL operation.

In the next chapter, we will go over the admin user interface (UI) and learn how to use it to manage a CockroachDB cluster.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset