> ## 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.

# Column Families

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>;
};

A column family is a group of columns in a table that are stored as a single key-value pair in the <InternalLink path="architecture/storage-layer">underlying key-value store</InternalLink>. Column families reduce the number of keys stored in the key-value store, resulting in improved performance during <InternalLink path="insert">`INSERT`</InternalLink>, <InternalLink path="update">`UPDATE`</InternalLink>, and <InternalLink path="delete">`DELETE`</InternalLink> 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.

<InternalLink path="indexes">Secondary indexes</InternalLink> 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.

## 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 <InternalLink path="create-table">table creation</InternalLink>, 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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE test (
    id INT PRIMARY KEY,
    last_accessed TIMESTAMP,
    data BYTES,
    FAMILY f1 (id, last_accessed),
    FAMILY f2 (data)
);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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 <InternalLink path="alter-table#add-column">`ALTER TABLE .. ADD COLUMN`</InternalLink> 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:

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

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

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

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

<a id="cost-based-optimizer-kl" />

## Known limitations

* When creating or updating a row, if the combined size of all values in a single <InternalLink path="column-families">column family</InternalLink> exceeds the <InternalLink path="configure-replication-zones">max range size</InternalLink> for the table, the operation may fail, or cluster performance may suffer. As a workaround, you can either <InternalLink path="column-families#manual-override">manually split a table's columns into multiple column families</InternalLink>, or you can <InternalLink path="configure-replication-zones#create-a-replication-zone-for-a-table">create a table-specific zone configuration</InternalLink> with an increased max range size.

## See also

* <InternalLink path="create-table">`CREATE TABLE`</InternalLink>
* <InternalLink path="alter-table#add-column">`ADD COLUMN`</InternalLink>
* <InternalLink path="sql-statements">SQL Statements</InternalLink>