We Shall Have Order!

Introduction

One of the more misunderstood aspects of Cassandra data modeling is that of how to properly order your data.  In this article we will explore a typical modeling journey from “first attempt” to “end solution.”  We will discuss the limitations of the ORDER BY clause, as well as explain how Cassandra clustering order works – and how to take advantage of that order when building a data model.

A common problem that new Cassandra users will attempt to solve is that of modeling time series data.  One of the more typical early mistakes made in time series data modeling is designing a table that is dependent on a time as its primary key, like this:

CREATE TABLE posts (
  posttime timestamp,
  postcontent text,
  postid uuid,
  userid bigint,
  PRIMARY KEY (posttime));

This is commonly accompanied by a question such as:

“How can I sort my result set by posttime?”

Of course – for this problem – designating posttime as a single primary key is not going to have the desired effect.  Cassandra will place (DataStax 2015a) “data on each node according to the value of the partition key and the range that the node is responsible for.”  It is important to understand that the order in which partitioned rows are returned, depends on the order of the hashed token values and not on the key values themselves.  So if the goal is to order the queried results by posttime then the primary key definition is going to have to be different.

Some new users make the mistake of using the Byte Ordered Partitioner (BOP) to sort data by partition key.  This is discouraged, given its propensity for hot spots and load balancing difficulties.  Some have even gone so far as to identify its use as an anti-pattern.  It is this author’s opinion that no new clusters should be built with the BOP.  Often, a use case which may appear to be a “good fit” for the BOP can be solved with proper data modeling.

The correct way to model this is to first find a different column by which to partition the data.  Proper partitioning and clustering keys will allow the data to be sorted and queried in the desired order.  The column to designate as the partition key largely depends on the requirements of the application and the particular query you are trying to solve.  Be mindful of the cardinality of your potential partition key.  If it is too low you will get “hot spots” (poor data distribution), and if it is too high you will negate the benefits of the “wide row” data model (too little data to make ordering worthwhile).

Another incorrect path which new users can find themselves going down is to designating a “dummy” partition key.  In this solution, the “dummy” key always has the same value (lowest possible cardinality), and its use can fool the user into thinking that the problem is solved.  The two main issues with this are that of hot spots and (Strickland 2014) unbounded row growth.  As the (constant) partition key value will be hashed the same on each insert, all of the data will be written to the same partition, thus creating hot spots of data.  Additionally, Cassandra can only support (McFadin 2014) two billion columns per partition.  With all data being written to that one partition, the chances of reaching that limit will increase over time.  As these two points are significant potential issues, I cannot stress enough that using a “dummy” partition key is a terrible idea.

For our example, we will decide to partition our data by userid with a new table specifically designed to serve the query, like this:

CREATE TABLE postsbyuser (
  userid bigint, posttime timestamp,
  postid uuid,
  postcontent text,
  PRIMARY KEY ((userid), posttime)
) WITH CLUSTERING ORDER BY (posttime DESC);

This particular table uses a compound primary key.  The first column listed in the primary key definition is the partition key (userid) and the second column is a clustering column (posttime).  After the partition key(s), data for each partition (DataStax 2015b) is “clustered by the remaining column or columns of the primary key definition.”  Essentially, the clustering columns determine the on-disk sort order within each partition.  Note that this table definition also uses the WITH clause, which indicates that the data should be clustered in descending order by posttime.

However, this can lead to another common incorrect assumption.  Consider the following query and question:

“I am trying to query the 100 most-recent posts by user.  Why is my data in random order even though CLUSTERING ORDER BY is specified in postsbyuser?”

In the example table above, queries will return data that is sorted according to the clustering key(s) only when a partition key is also specified.  Without a partition key specified in the WHERE clause, the actual order of the result set then becomes dependent on the hashed values of userid.  This is apparent whenpostsbyuser is queried without specifying a WHERE clause and using the token() function on userid:

In any case, to serve the above query we need qualify it with the partition key (userid).  This query will return the 100 most-recent posts where userid is equal to 2:

> SELECT * FROM postsbyuser WHERE userid=2 LIMIT 100;

CREATE TABLE postsbyuseryear (
  userid bigint,
  posttime timestamp,
  postid uuid,
  postcontent text,
  year bigint,
  PRIMARY KEY ((userid, year), posttime)
) WITH CLUSTERING ORDER BY (posttime DESC);

> SELECT userid, year, posttime, postcontent
FROM postsbyuseryear
WHERE userid=1 AND year=2015;

userid | year | posttime | postcontent --------+------+--------------------------+------------------------ 1 | 2015 | 2015-01-25 13:25:00-0600 | Game over, man! 1 | 2015 | 2015-01-25 13:22:00-0600 | B26354 filed & monitored 1 | 2015 | 2015-01-25 13:21:00-0600 | I aim to misbehave.