Explain Collections (SET, LIST, MAP) & User-Defined Types in Databases

35 / 66

Apache Cassandra

Collections (SET, LIST, MAP) & User-Defined Types

Learn how to model structured, multi-valued, and nested data using CQL collections and user-defined types.

A collection stores several values in one column, so a handful of tags or phone numbers does not need its own table.

Collections and UDTs are like allowing a filing cabinet drawer (a row) to contain a small labeled envelope with a few related sub-items (like a set of phone numbers or a small address card), instead of requiring every tiny detail to have its own separate drawer.

Key Concepts

    SET<text>          unique, unordered      {'java', 'sql'}
    LIST<text>         ordered, duplicates ok ['step1', 'step2']
    MAP<text, text>    key-value pairs        {'home': '...', 'work': '...'}

A user-defined type groups related fields. Rather than addr_street, addr_city, addr_pin as separate columns:

addr_streetaddr_cityaddr_pin

    CREATE TYPE address (street text, city text, pin text);
    ALTER TABLE users ADD home_address address;

UDTs nest inside collections and inside other UDTs, so MAP<text, frozen<address>> is a valid way to hold several named addresses.

MAP<text, frozen<address>>

The word "small" is doing real work here. A collection is stored and usually read as a whole. It is not a child table with a different name.

Keep them to tens of items, low hundreds at most. A collection of a few thousand entries reads the whole thing on every access and shows up as unexplained latency on a query that looks trivial.

Frozen means all-or-nothing. A frozen<...> collection or UDT is serialised into one blob:

frozen<...>

    frozen     -> one value; change one field, rewrite the whole thing
    non-frozen -> individual elements can be added or removed in place

Freezing is required when nesting a collection inside another collection. It is also what you want for a value that always changes together, such as an address.

LIST is the one to avoid. Appending is fine, but inserting or deleting at a position requires a read-before-write inside the database — the one thing Cassandra's write path otherwise never does. It also makes writes non-idempotent: retry an append after a timeout and the value can appear twice.

Use a SET if order does not matter, or a MAP keyed by position if it does.

SETMAP

Deleting elements creates tombstones. Removing items from a collection, or overwriting a non-frozen collection entirely, writes range tombstones. A column updated this way repeatedly accumulates them, with all the read cost that implies.

When to use a table instead. If the answer to "how many could there ever be?" is unbounded — all orders for a customer, all readings for a device — it is a table with clustering columns, not a collection. The collection is for the bounded case where you would otherwise create a table with three rows in it.

What the interviewer is probing.1. "What is the size guidance for a collection?" Probing: the limit. Stalls: "Unlimited." Moves up: small — they are read entirely, so a large collection hurts; past a few dozen entries use clustering columns instead.

2. "Why are lists discouraged?" Probing: the mechanism. Stalls: "They are fine." Moves up: some operations require a read before write and can produce surprising behaviour on concurrent updates — prefer sets or maps.

3. "What happens when you update a collection?" Probing: the tombstone issue. Stalls: "It updates in place." Moves up: replacing one generates a tombstone for the old contents, so frequent full replacement creates tombstone pressure.

4. "What is a UDT for?" Probing: the purpose. Stalls: "Any structure." Moves up: grouping related fields into one column, which keeps the schema readable — but it is read and written whole.