Jonathan Ellis

<h2>The evolution of schema in Cassandra</h2>

When Cassandra was first released several years ago, it followed closely the data model outlined in Google's&nbsp;<a href="http://research.google.com/archive/bigtable.html">Bigtable</a>&nbsp;paper (with the notable addition of SuperColumns -- more on these later): ColumnFamilies grouping related columns needed to be defined up-front, but column names were just byte arrays interpreted by the application. It would be fair to characterize this early Cassandra data model as "schemaless."

However, as systems deployed on Cassandra grew and matured, lack of schema became a pain point. When multiple teams are using the same data, it's very useful to be able to ask "what data is in this table (or columnfamily)," without diving into the source of the code that uses it. And as more codebases share a database, it also becomes more useful to have the database validate that the birth_date column in one row is always an integer.

So, starting with the 0.7 release roughly a year ago, Cassandra has first allowed, then encouraged telling Cassandra about your data types. I've taken to describing Cassandra as "Schema-optional:" it's not required, and you can ignore it at first then go back and add it later if you'd rather, but it's a good habit to get into. Today, doing this in CQL looks familiar:

 
<code>CREATE TABLE users ( 
&nbsp;&nbsp;&nbsp;&nbsp;id uuid PRIMARY KEY, 
&nbsp;&nbsp;&nbsp;&nbsp;name varchar, 
&nbsp;&nbsp;&nbsp;&nbsp;state varchar 
);</code>

&nbsp;

<code>ALTER TABLE users ADD birth_date INT;</code>

(Using UUIDs as a&nbsp;<a href="http://en.wikipedia.org/wiki/Surrogate_key">surrogate key</a>&nbsp;is common in Cassandra, so that you don't need to worry about sequence or autoincrement synchronization across multiple machines.)

<h2>The best of both worlds</h2>

Superficially it may sound like Cassandra is headed back where relational databases started: every column predefined and typed. The big difference is in the practical limitations of Cassandra's&nbsp;<a href="http://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.44.2782">log-structured merge-tree</a>&nbsp;storage engine, compared to RDBMS&nbsp;<a href="http://en.wikipedia.org/wiki/B-tree">b-trees</a>.

Without going into too much detail, traditional storage engines allocate room for each column in each row, up front. (Rows that have different sets of columns are grudgingly accomodated via nulls.)
<img alt="log structured merge tree" data-align="center" data-entity-type="file" data-entity-uuid="9bf0dab9-8d3d-4c03-b92f-dca1e933dad9" src="https://www.datastax.com/sites/default/files/inline-images/Screen-shot-2012-02-16-at-4.06.24-PM-3.png" />
In a static-column storage engine, each row must reserve space for every column

In Cassandra's storage engine, each row is sparse: for a given row, we store only the columns present in that row. Technically this implies that we store the column names redundantly in each row, trading disk space to gain flexibility. Thus, adding columns to a Cassandra table always only takes a few milliseconds, rather than growing from minutes to hours or even&nbsp;<a href="http://twitoaster.com/country-us/abraham/15-months-ago-twitter-had-800-million-tweets-and-alter-table-took-2-weeks-twitter-is-now-over-12-billion-chirp/">weeks</a>&nbsp;as data is added to the table with a storage engine that needs to re-allocate space row by row to accommodate the new data.

&nbsp;
<img alt="cassandra table" data-align="center" data-entity-type="file" data-entity-uuid="7962d6b9-09c2-40c4-a222-2eb993819dc5" src="https://www.datastax.com/sites/default/files/inline-images/Screen-shot-2012-02-16-at-4.11.01-PM1-2.png" />
In a sparse-column engine, space is only used by columns present in each row

This also means that Cassandra can easily support thousands of columns per table, without wasting space if each row only needs a few of them.

Thus, Cassandra gives you the flexibility normally associated with schemaless systems, while also delivering the benefits of having a defined schema.

<h2>Clustering, compound keys, and more</h2>

Starting in the upcoming Cassandra 1.1 release, CQL (the&nbsp;<a href="https://www.datastax.com/dev/blog/what%E2%80%99s-new-in-cassandra-0-8-part-1-cql-the-cassandra-query-language">Cassandra Query Language</a>) supports defining columnfamilies with compound primary keys. The first column in a compound key definition continues to be used as the&nbsp;<a href="https://www.datastax.com/docs/1.0/cluster_architecture/partitioning">partition key</a>, and remaining columns are automatically&nbsp;<a href="http://en.wikipedia.org/wiki/Database_index#Clustered">clustered</a>: that is, all the rows sharing a given partition key will be sorted by the remaining components of the primary key.

For example, consider the&nbsp;<tt>sblocks</tt>&nbsp;table in the&nbsp;<a href="https://www.datastax.com/dev/blog/cassandra-file-system-design">CassandraFS</a>&nbsp;data model:

 
<code>CREATE TABLE sblocks ( 
&nbsp;&nbsp;&nbsp;&nbsp;block_id uuid, 
&nbsp;&nbsp;&nbsp;&nbsp;subblock_id uuid, 
&nbsp;&nbsp;&nbsp;&nbsp;data blob, 
&nbsp;&nbsp;&nbsp;&nbsp;PRIMARY KEY (block_id, subblock_id) 
) 
WITH COMPACT STORAGE;</code>

The first element of the primary key,&nbsp;<tt>block_id</tt>, is the partition key, which means that all subblocks of a given block will be routed to the same replicas. For each block, subblocks are also ordered by the subblock id.&nbsp;<a href="https://www.datastax.com/products/enterprise">DataStax Enterprise</a>&nbsp;uses this property to make sure that&nbsp;<tt>SELECT data FROM sblocks WHERE block_id = ?</tt>&nbsp;is sequential i/o in subblock_id order.

Compound keys can also be useful when denormalizing data for faster queries. Consider a Twitter data model like&nbsp;<a href="https://github.com/twissandra/twissandra">Twissandra's</a>. We have tweet data:

 
<code>CREATE TABLE tweets ( 
&nbsp;&nbsp;&nbsp;&nbsp;tweet_id uuid PRIMARY KEY, 
&nbsp;&nbsp;&nbsp;&nbsp;author varchar, 
&nbsp;&nbsp;&nbsp;&nbsp;body varchar 
);</code>

But the most frequent query ("show me the 20 most recent tweets from people I follow") would be expensive against a normalized model. So we denormalize into another table:

 
<code>CREATE TABLE timeline ( 
&nbsp;&nbsp;&nbsp;&nbsp;user_id varchar, 
&nbsp;&nbsp;&nbsp;&nbsp;tweet_id uuid, 
&nbsp;&nbsp;&nbsp;&nbsp;author varchar, 
&nbsp;&nbsp;&nbsp;&nbsp;body varchar, 
&nbsp;&nbsp;&nbsp;&nbsp;PRIMARY KEY (user_id, tweet_id) 
);</code>

That is, any time a given author makes a tweet, we look up who follows him, and insert a copy of the tweet into the followers' timeline. Cassandra orders&nbsp;<a href="http://en.wikipedia.org/wiki/Universally_unique_identifier#Version_1_.28MAC_address.29">version 1 UUIDs</a>&nbsp;by their time component, so&nbsp;<tt>SELECT * FROM timeline WHERE user_id = ? ORDER BY tweet_id DESC LIMIT 20</tt>&nbsp;requires no sort at query time.

(At the time of this writing, ORDER BY syntax is&nbsp;<a href="https://issues.apache.org/jira/browse/CASSANDRA-3925">being finalized</a>; this is my best guess as to what it will look like.)

<h2>Under the hood and historical notes</h2>

Cassandra's storage engine uses&nbsp;<a href="https://www.datastax.com/dev/blog/introduction-to-composite-columns-part-1">composite columns</a>&nbsp;under the hood to store clustered rows. This means that all the logical rows with the same partition key get stored as a single physical "wide row." This is why Cassandra supports up to&nbsp;<a href="https://blogs.apache.org/foundation/entry/the_apache_software_foundation_announces9">2 billion columns</a>&nbsp;per (physical) row, and why Cassandra's old Thrift api has methods to take "slices" of such rows.

To illustrate this, let's consider three tweets for our timeline data model above:

&nbsp;
<img alt="tweet timeline" data-align="center" data-entity-type="file" data-entity-uuid="6e36b9ce-b8bc-44e0-8e9c-9fc05371c73c" src="https://www.datastax.com/sites/default/files/inline-images/Screen-shot-2012-02-16-at-4.13.49-PM-1.png" />
Raw tweet data

We'll have timeline entries for jadams, who follows gwashington and jmadison, and ahamilton, who follows gwashington and gmason. I've colored these rows by their partition key, the user_id:

&nbsp;
<img alt="timeline entries" data-align="center" data-entity-type="file" data-entity-uuid="ddad28b8-6330-44ee-ab0f-5389e54b936b" src="https://www.datastax.com/sites/default/files/inline-images/Screen-shot-2012-02-16-at-4.17.44-PM_0.png" />
Logical representation of the denormalized timeline rows

The physical layout of this data looks like this to Cassandra's storage engine:
<img alt="physical layout of this data" data-align="center" data-entity-type="file" data-entity-uuid="48cb6a3b-98a6-42d3-9eab-0a1c05412ad2" src="https://www.datastax.com/sites/default/files/inline-images/Screen-shot-2012-02-16-at-4.20.23-PM_0.png" />
Physical representation of the denormalized timeline rows

&nbsp;

The &nbsp;<tt>WITH COMPACT STORAGE</tt>&nbsp;directive is provided for backwards compatibility with older Cassandra applications, as in the CassandraFS example above; new applications should avoid it. Using&nbsp;<tt>COMPACT STORAGE</tt>&nbsp;will prevent you from adding new columns that are not part of the&nbsp;<tt>PRIMARY KEY</tt>. With&nbsp;<tt>COMPACT STORAGE</tt>, each logical row corresponds to exactly one physical column:

<img alt="compact engine" data-entity-type="file" data-entity-uuid="5c86745b-0830-4d13-b595-0946e43f27f9" src="https://www.datastax.com/sites/default/files/inline-images/Screen-shot-2012-02-16-at-4.21.40-PM.png" />

Physical representation of the denormalized timeline rows, WITH COMPACT STORAGE

<a href="https://www.datastax.com/docs/1.0/ddl/column_family">SuperColumns</a>&nbsp;were an early attempt at providing the same kinds of denormalization tools discussed above. They have important limitations (e.g., reading any subcolumn from a SuperColumn pulls the entire SuperColumn into memory) and will eventually be&nbsp;<a href="https://issues.apache.org/jira/browse/CASSANDRA-3237">replaced by a composite column implementation</a>&nbsp;with the same API. So if you have an application using SuperColumns, you don't need to rewrite anything, but if you are starting fresh, you should use the more flexible approach described above.

Schema in Cassandra 1.1

Jonathan EllisTechnology

Share

More Technology

Getting Started with DataStax Astra DB and Amazon Bedrock

Why You Can't Miss Cassandra Summit

Mitigate the OpenAI Debacle with Open Source and Cloud

Supercharge AI Assistants with Superagent and DataStax

NoSQL and Vector DB
for Generative AI,
Instantly, At Scale