Collections in CQL

The collection data types map, list, and set are used to group and store values together in one column. The elements within a collection each have a defined type that you must specify in the table definition.

Cassandra avoids joins between two tables by storing the grouping of items in a collection column in the user table. In a relational database, this grouping would be stored in separate tables and joined between tables with one or more foreign keys.

Collections are indexable for more versatile querying.

Best practices for collections

Follow these best practices to avoid performance impacts that can occur when collection types are used improperly.

When to use collections

Collection types are best for data that is reasonably limited, consistently structured, and always read together, such as the tags applied to blog posts or each employee’s home, mobile, and office phone numbers.

Use partitions for unbounded data

Don’t use collections for data that has unbounded growth potential, like message history or sensor events registered every second. Instead, use a table with a compound primary key where unbounded data can be stored in partitions based on clustering columns.

Use separate columns or tables to read distinct elements

Collections are meant to be read from the database as a complete group. If your query patterns frequently require reading distinct elements, consider using separate columns or tables instead of collections. Although you can SELECT distinct elements from non-frozen collections, this is usually less efficient than separate tables or columns.

Reconsider your data model if data types are inconsistent

Elements in a collection always have a defined data type, such as text or int. Collections aren’t appropriate when stored values are unpredictable and you cannot enforce specific user input or normalize data before writing to the database.

When to use a map, list, or set

The ideal collection type (map, list, or set) depends on the characteristics of the data being stored:

Sets store unordered, unique elements

Use set instead of list when order doesn’t matter and you don’t have duplicate elements. Sets are more performant than lists because they don’t require read-before-write.

Lists store ordered, non-unique elements

Use a list when the order of elements matters or when you need to store the same value multiple times.

Maps store key-value pairs

Use a map when you need to associate unique keys with specific values.

Frozen and non-frozen collections

Collections can be either frozen or non-frozen.

As a general rule, if collection values are fixed or rarely changed, use frozen collections for improved performance. If you need to update collection elements regularly, use non-frozen collections.
Frozen

Use the FROZEN keyword on a set, map, or list (frozen<<collection_definition>>) to serialize the elements into a single stored value.

Frozen collections are more efficient than non-frozen collections, they can be part of the primary key, and they can be nested inside other collections. However, they must be read and written as a whole:

  • You cannot update distinct elements; INSERT and UPDATE overwrite the entire value and must include all desired elements.

  • You cannot read distinct elements; a SELECT statement queries and reads the entire frozen collection, even if only one element is needed.

Non-frozen

If the FROZEN keyword isn’t specified, then the collection is non-frozen.

Non-frozen collections are stored as distinct elements that can be written and read independently. Updates don’t require rewriting the entire collection, and SELECT statements using a WHERE clause with CONTAINS or CONTAINS KEY only read the requested elements, not the entire collection. However, non-frozen collections have limitations and performance impacts:

Collection size guardrails

The database retrieves and reads collections in their entirety, so unacceptable read amplification is possible when collections are too large. In general, keep collections below the following guardrails to maintain acceptable read latency:

  • Frozen collections must not exceed 2 GB. Ideally, they should be smaller than 1 MB. Only exceed 1 MB in edge cases where this is unavoidable and no other data type can be used.

  • A non-frozen collection must have no more than 2 billion elements.

  • The maximum size of an element in a non-frozen set is 65,535 bytes.

  • The maximum size of an element in a non-frozen list or map is 2 GB.

  • The maximum number of keys in a non-frozen map is 65,535.

  • In any collection, limit individual collection cells to fewer than 100 elements.

When a query returns many rows, it is inefficient to return them as a single response message. Instead, Cassandra drivers break the results into pages that are returned as needed. Applications can control how many rows are included in a single page, but this must not exceed the maximum page size defined by the Apache Cassandra Native Protocol. The maximum page size is 256 MB, and collection cells with hundreds of elements easily exceed this limit.

Read-before-write and non-idempotent list operations

The following operations on non-frozen list collections require the database to perform a read-before-write, which inherently has a performance impact:

  • Setting an element at a specific index position.

  • Removing an element at a specific index position.

  • Removing all occurrences of a specific value from anywhere in the list.

In contrast, set and map never incur an internal read-before-write on insertion.

Additionally, prepend and append operations on non-frozen list collections are non-idempotent. Implicit retries can result in duplicated operations or incorrect last-write-wins situations. Additional application logic is required to determine the completion state and manually retry the operation to avoid data loss.

To avoid both of these issues, use a set or frozen list (FROZEN<list<type>>) instead of a non-frozen list.

Create a collection

To create a collection, you must define the collection type, column name, and column data type when you create or alter a table.

  • Collections are not paged internally. Reading a range of values is not supported for collections.

  • Make sure you understand the use and importance of the primary key in table schema.

Create set collections

A set consists of a unordered group of elements with unique values. Duplicate values will not be stored distinctly. The values of a set are stored unordered, but will return the elements in sorted order when queried. Use the set data type to store data that has a many-to-one relationship with another column.

Non-frozen set

To create a non-frozen set collection in a table, specify the collection column name and column data type enclosed in angle brackets:

CREATE TABLE IF NOT EXISTS cycling.cyclist_career_teams (
  id UUID PRIMARY KEY,
  lastname text,
  teams set<text>
);

The set collection is created with the column name teams, and the data type text:

Result
CREATE TABLE cycling.cyclist_career_teams (
    id uuid PRIMARY KEY,
    lastname text,
    teams set<text>
) WITH additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND nodesync = {'enabled': 'true', 'incremental': 'true'}
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';
Frozen set

To create a frozen set collection in a table specify the collection column name and a column data type enclosed with angle brackets, with the FROZEN keyword:

CREATE TABLE IF NOT EXISTS cycling.race_results (
  race_name text PRIMARY KEY,
  race_history frozen<set<int>>,
  top_three frozen<map<int, text>>
);

The set collection is created with the column name race_history, and the data type int.

Result
CREATE TABLE cycling.race_results (
    race_name text PRIMARY KEY,
    race_history frozen<set<int>>,
    top_three frozen<map<int, text>>
) WITH additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND nodesync = {'enabled': 'true', 'incremental': 'true'}
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';

Create list collections

A list is similar to a set; it groups and stores values. Unlike a set, the values stored in a list do not need to be unique and can be duplicated. In addition, a list stores the elements in a particular order and may be inserted or retrieved according to an index value.

Use the list data type to store data that has a possible many-to-many relationship with another column.

Non-frozen list

To create a non-frozen list collection in a table, specify a collection column name, and a column data type enclosed in angle brackets:

CREATE TABLE IF NOT EXISTS cycling.upcoming_calendar (
  year int,
  month int,
  events list<text>,
  PRIMARY KEY (year, month)
);

The list collection is created with the column name events, and the data type text.

Result
CREATE TABLE cycling.upcoming_calendar (
    year int,
    month int,
    events list<text>,
    PRIMARY KEY (year, month)
) WITH CLUSTERING ORDER BY (month ASC)
    AND additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND nodesync = {'enabled': 'true', 'incremental': 'true'}
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';
Frozen list

To create a frozen list collection in a table, specify a collection column name and a column data type enclosed in angle brackets, with the FROZEN keyword:

CREATE TABLE IF NOT EXISTS cycling.race_starts (
  cyclist_name text PRIMARY KEY,
  rnumbers FROZEN<LIST<int>>
);

The list collection is created with the column name rnumbers, and the data type int.

Result
CREATE TABLE cycling.race_starts (
    cyclist_name text PRIMARY KEY,
    rnumbers frozen<list<int>>
) WITH additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND nodesync = {'enabled': 'true', 'incremental': 'true'}
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';

Create map collections

A map stores related values as key-value pairs. For each key, only one value may exist, and duplicates cannot be stored. Both the key and the value have a defined data type.

Each element of a non-frozen map is internally stored as a single column that you can modify, replace, delete, and query. Each element can have an individual time-to-live and expire when the TTL ends.

Non-frozen map

To create a non-frozen map collection in a table, specify a collection column name, and a pair of column data types enclosed in angle brackets:

CREATE TABLE IF NOT EXISTS cycling.cyclist_teams (
  id uuid PRIMARY KEY,
  firstname text,
  lastname text,
  teams map<int, text>
);

The map collection is created with the column name teams, and the data types will have a key year of integer type, and a team name value of text type.

Result
CREATE TABLE cycling.cyclist_teams (
    id uuid PRIMARY KEY,
    firstname text,
    lastname text,
    teams map<int, text>
) WITH additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND nodesync = {'enabled': 'true', 'incremental': 'true'}
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';
Frozen map

To create a frozen map collection in a table, specify a collection column name, and a pair of column data types enclosed in angle brackets, with the FROZEN keyword:

CREATE TABLE IF NOT EXISTS cycling.race_results (
  race_name text PRIMARY KEY,
  race_history frozen<set<int>>,
  top_three frozen<map<int, text>>
);

The map collection is created with the column name top_three, and the data types will have a key rank of integer type, and a cyclist name value of text type.

Result
CREATE TABLE cycling.race_results (
    race_name text PRIMARY KEY,
    race_history frozen<set<int>>,
    top_three frozen<map<int, text>>
) WITH additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND nodesync = {'enabled': 'true', 'incremental': 'true'}
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';

Manage collection columns

Insert or update collection data

To insert or edit the data in a collection column in a table, use an INSERT or UPDATE statement.

To write to a collection, you must have a table with a collection column.

Insert or update a set

Set values must be unique, because no order is defined in a set internally.

A frozen set column only allows you to insert or update the entire set. Elements cannot be prepended or appended to the set unless it is non-frozen.

  • Insert data into the set, enclosing values in curly braces:

    INSERT INTO cycling.cyclist_career_teams (
  id, lastname, teams
 ) VALUES (
  5b6962dd-3f90-4c93-8f61-eabfa4a803e2,
  'VOS',
  {
    'Rabobank-Liv Woman Cycling Team',
    'Rabobank-Liv Giant',
    'Rabobank Women Team',
    'Nederland bloeit'
  }
);

  • Add an element to a set using an UPDATE statement and the addition (+) operator:

    UPDATE cycling.cyclist_career_teams
  SET teams = teams + {'Team DSB - Ballast Nedam'}
  WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;

  • Remove an element from a set using the subtraction (-) operator:

    UPDATE cycling.cyclist_career_teams
  SET teams = teams - {'DSB Bank Nederland bloeit'}
  WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;

  • Remove all elements from a set by using an UPDATE or DELETE statement

    If you remove all elements from a set, the empty set is stored as a null set.

    UPDATE cycling.cyclist_career_teams SET teams = {}
WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;

DELETE teams FROM cycling.cyclist_career_teams
  WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;

    A query for the teams returns null:

    SELECT id, lastname, teams FROM cycling.cyclist_career_teams
  WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;
    Result
     id                                   | lastname | teams
--------------------------------------+----------+-------
 5b6962dd-3f90-4c93-8f61-eabfa4a803e2 |      VOS |  null

(1 rows)

Insert or update a list

A frozen list column only allows you to insert or update the entire list. Elements cannot be prepended or appended to the list unless it is non-frozen.

Insert data into the list, enclosing values in square brackets:

INSERT INTO cycling.upcoming_calendar ( year, month, events )
VALUES ( 2015, 06, [ 'Criterium du Dauphine', 'Tour de Suisse' ] );

INSERT INTO cycling.upcoming_calendar ( year, month, events )
VALUES ( 2015, 07, [ 'Tour de France' ] );

Use an UPDATE statement to insert values into the list:

  • Prepend an element to the list by enclosing it in square brackets and using the addition (+) operator:

    UPDATE cycling.upcoming_calendar SET events = [ 'Tour de France' ] + events
  WHERE year = 2015 AND month = 06;

    This update operation is implemented internally without any read-before-write. Prepending a new element to the list writes only the new element.

  • Append an element to the list by switching the order of the new element data and the list name in the UPDATE statement:

    UPDATE cycling.upcoming_calendar SET events = events + [ 'Tour de France' ]
  WHERE year = 2017 AND month = 05;

    This update operation is implemented internally without any read-before-write. Appending a new element to the list writes only the new element.

  • Add an element at a particular position using the list index position in square brackets:

    UPDATE cycling.upcoming_calendar SET events[2] = 'Vuelta Ciclista a Venezuela'
  WHERE year = 2015 AND month = 06;

    To add an element at a particular position, the database reads the entire list, and then rewrites the part of the list that needs to be shifted to the new index positions. Consequently, adding an element at a particular position results in greater latency than appending or prepending an element to a list.

  • Remove an element from a list, use a DELETE statement and the list index position in square brackets. For example, to remove the first event in the list:

    DELETE events[0] FROM cycling.upcoming_calendar
  WHERE year = 2015 AND month = 07;

    The method of removing elements using an indexed position from a list requires an internal read. In addition, the client-side application could only discover the indexed position by reading the whole list and finding the values to remove, adding additional latency to the operation. If another thread or client prepends elements to the list before the operation is done, incorrect data will be removed.

  • Remove all elements having a particular value using an UPDATE statement, the subtraction operator (-), and the list value in square brackets.

    UPDATE cycling.upcoming_calendar SET events = events - ['Tour de France Stage 10']
  WHERE year = 2015 AND month = 07;

    This is a safer and faster way to delete values from a list than by index position.

Insert or update a map

A frozen map column only allows you to insert or update the entire map. Elements cannot be prepended or appended to the map unless it is non-frozen.

Set or replace map data, using an INSERT or UPDATE statement. Enclose the integer and text values in a map collection with curly braces, separated by a colon.

  • Use INSERT to replace an entire map:

    INSERT INTO cycling.cyclist_teams (
  id, firstname, lastname, teams
) VALUES (
  5b6962dd-3f90-4c93-8f61-eabfa4a803e2,
  'Marianne',
  'VOS',
  {
    2015 : 'Rabobank-Liv Woman Cycling Team',
    2014 : 'Rabobank-Liv Woman Cycling Team'
  }
);

  • Use an UPDATE statement to insert values into the map:

    • Append an element to the map by enclosing the key-value pair in curly braces and using the addition (+) operator:

      UPDATE cycling.cyclist_teams
  SET teams = teams + { 2009 : 'DSB Bank - Nederland bloeit' }
  WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;

    • Set a specific element using an UPDATE statement, enclosing the specific key of the element (an integer) in square brackets, and using the equals operator (=) to map the value assigned to the key:

      UPDATE cycling.cyclist_teams
  SET teams[2006] = 'Team DSB - Ballast Nedam'
  WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;

  • Delete an element from the map using a DELETE statement and enclosing the specific key of the element in square brackets:

    DELETE teams[2009] FROM cycling.cyclist_teams
  WHERE id=e7cd5752-bc0d-4157-a80f-7523add8dbcd;

  • Remove all elements having a particular value using an UPDATE statement, the subtraction operator (-), and the map key values in curly braces:

    UPDATE cycling.cyclist_teams
  SET teams = teams - { 2013, 2014 }
  WHERE id = e7cd5752-bc0d-4157-a80f-7523add8dbcd;

Minimize tombstones in sets and maps

Tombstones for collection updates aren’t propagated by read repair.

Tombstones are created for inserts-as-upserts and non-incremental updates to non-frozen collections. These types of changes overwrite the entire collection value, even if some elements are the same or net new. In contrast, an incremental update adds an element to an existing collection value without changing the existing elements.

These tombstones are meant to prevent overlap with previous data, but they are unnecessary for new and unchanged elements. If you need to add new elements or update specific elements within non-frozen collections, use incremental updates whenever possible to avoid performance degradation from too many tombstones. If you always overwrite the entire collection, use frozen collections.

When you know that an update doesn’t modify any existing elements in the collection, use an append operation to avoid unnecessary tombstones when inserting data into a set or map. This can also be used when performing a full update of a set or map by appending updates for all of the elements.

For example, given the following schema:

CREATE TABLE test.m1 (
  id int PRIMARY KEY,
  m map<int, text>
);

Don’t use full overwrites, as shown in the following examples, which generate tombstones for elements that aren’t changed:

INSERT INTO test.m1(id, m) VALUES (1, {1:'t1', 2:'t2'});

UPDATE test.m1 SET m = {1:'t1', 2:'t2'} WHERE id = 1;

Instead, use the append operator (+) to adds new elements to the collection without overwriting the existing ones, thereby avoiding the creation of tombstones for unchanged elements:

UPDATE test.m1 SET m = m + {1:'t1', 2:'t2'} WHERE id = 1;

Alternatively, if there is only one collection column in the table, you can model it as an additional clustering column in the primary key. For example, the same table from the previous example can be created without a map column:

CREATE TABLE test.m1 (
  id int,
  m_key int,
  m_value text,
  PRIMARY KEY(id, m_key)
);

In this case, the map entries are stored in separate columns, and then the m_key is used as a clustering column. You can select all values for a specific partition by omitting the condition on m_key, or you can select a specific element by providing a full primary key. This can be more useful than a collection column, which returns the entire map, list, or set entry.

Was this helpful?

Give Feedback

How can we improve the documentation?

© Copyright IBM Corporation 2026 | Privacy policy | Terms of use |  Manage Privacy Choices

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: Contact IBM