Column Families

A column family is a group of columns in a table that are stored as a single key-value pair in the underlying key-value store. The reduced number of keys results in a smaller storage overhead and, even more significantly, in improved performance during INSERT, UPDATE, and DELETE operations.

This page explains how CockroachDB organizes columns into families as well as cases in which you might want to manually override the default behavior.

Default behavior

When a table is created, all columns are stored as a single column family.

This default approach ensures efficient key-value storage and performance in most cases. However, when frequently updated columns are grouped with seldom updated columns, the seldom updated columns are nonetheless rewritten on every update. Especially when the seldom updated columns are large, it's more performant to split them into a distinct family.

Manual override

Assign column families on table creation

To manually assign a column family on table creation, use the FAMILY keyword.

For example, let's say we want to create a table to store an immutable blob of data (data BYTES) with a last accessed timestamp (last_accessed TIMESTAMP). Because we know that the blob of data will never get updated, we use the FAMILY keyword to break it into a separate column family:

> CREATE TABLE test (
    id INT PRIMARY KEY,
    last_accessed TIMESTAMP,
    data BYTES,
    FAMILY f1 (id, last_accessed),
    FAMILY f2 (data)
);

> SHOW CREATE users;

+-------+---------------------------------------------+
| Table |                 CreateTable                 |
+-------+---------------------------------------------+
| test  | CREATE TABLE test (
                       |
|       |     id INT NOT NULL,
                       |
|       |     last_accessed TIMESTAMP NULL,
          |
|       |     data BYTES NULL,
                       |
|       |     CONSTRAINT "primary" PRIMARY KEY (id),
 |
|       |     FAMILY f1 (id, last_accessed),
         |
|       |     FAMILY f2 (data)
                       |
|       | )                                           |
+-------+---------------------------------------------+
(1 row)

Note:
Columns that are part of the primary index are always assigned to the first column family. If you manually assign primary index columns to a family, it must therefore be the first family listed in the CREATE TABLE statement.

Assign column families when adding columns

When using the ALTER TABLE .. ADD COLUMN statement to add a column to a table, you can assign the column to a new or existing column family.

• Use the CREATE FAMILY keyword to assign a new column to a new family. For example, the following would add a data2 BYTES column to the test table above and assign it to a new column family:

  > ALTER TABLE test ADD COLUMN data2 BYTES CREATE FAMILY f3;

• Use the FAMILY keyword to assign a new column to an existing family. For example, the following would add a name STRING column to the test table above and assign it to family f1:

  > ALTER TABLE test ADD COLUMN name STRING FAMILY f1;

• Use the CREATE IF NOT EXISTS FAMILY keyword to assign a new column to an existing family or, if the family doesn't exist, to a new family. For example, the following would assign the new column to the existing f1 family; if that family didn't exist, it would create a new family and assign the column to it:

  > ALTER TABLE test ADD COLUMN name STRING CREATE IF NOT EXISTS FAMILY f1;

• If a column is added to a table and the family is not specified, it will be added to the first column family. For example, the following would add the new column to the f1 family, since that is the first column family:

  > ALTER TABLE test ADD COLUMN last_name STRING;

Compatibility with past releases

Using the beta-20160714 release makes your data incompatible with versions earlier than the beta-20160629 release.

See also

• CREATE TABLE
• ADD COLUMN
• Other SQL Statements

Was this page helpful?
YesNo