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. Column families reduce the number of keys stored in the key-value store, resulting in improved performance during
This page explains how CockroachDB organizes columns into families as well as cases in which you might want to manually override the default behavior.
New in v20.1: Secondary indexes respect the column family definitions applied to tables. When you define a secondary index, CockroachDB breaks the secondary index key-value pairs into column families, according to the family and stored column configurations. In versions prior to v20.1, all secondary indexes store values in a single column family.
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.
Assign column families on table creation
To manually assign a column family on table creation, use the
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 test;
table_name | create_statement -------------+------------------------------------------------- test | CREATE TABLE test ( | id INT8 NOT NULL, | last_accessed TIMESTAMP NULL, | data BYTES NULL, | CONSTRAINT "primary" PRIMARY KEY (id ASC), | FAMILY f1 (id, last_accessed), | FAMILY f2 (data) | ) (1 row)
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 FAMILYkeyword to assign a new column to a new family. For example, the following would add a
data2 BYTEScolumn to the
testtable above and assign it to a new column family:
> ALTER TABLE test ADD COLUMN data2 BYTES CREATE FAMILY f3;
- Use the
FAMILYkeyword to assign a new column to an existing family. For example, the following would add a
name STRINGcolumn to the
testtable above and assign it to family
> ALTER TABLE test ADD COLUMN name STRING FAMILY f1;
- Use the
CREATE IF NOT EXISTS FAMILYkeyword 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
f1family; 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
f1family, since that is the first column family:
> ALTER TABLE test ADD COLUMN last_name STRING;