Cassandra CLUSTERING ORDER with updates [performance]

Question

With Cassandra it is possible to specify the cluster ordering on a table with a particular column.

CREATE TABLE myTable (
    user_id INT,
    message TEXT,
    modified DATE,
    PRIMARY KEY ((user_id), modified)
)
WITH CLUSTERING ORDER BY (modified DESC);

Note: In this example, there is one message per user_id (intended)

Given this table my understanding is that the query's performance will be better in cases where recent data is queried.

However, if one where to make updates to the "modified" column does it add extra overhead on the server to "re-order" and is that overhead vs query performance significant?

In other words given this table would it perform better if the "CLUSTERING ORDER BY (modified DESC)" was dropped?

UPDATE: Updated the invalid CQL by adding modified to primary key, however, the original questions still stand.

I compared it with the table definition here cassandra.apache.org/doc/latest/cql/ddl.html#create-table and seems valid, can you please point out the problem — Exocomp
The issue is that modified is not defined as the clustering key, so you can't define a clustering order on it. To fix this, the primary key should be defined as PRIMARY KEY (user_id, modified). For more information regarding the composite key, and the characteristics of the clustering key stackoverflow.com/questions/24949676/… — Carlos Monroy Nieblas
My mistake it was an oversight, I had the date as the primary key on the server just not in my example here. — Exocomp
also note that if you want to set the clustering order by modified, you won't be able to update that record (as explained in stackoverflow.com/questions/27075596/…); Cassandra is an append-only database engine: this means that any update to the records will add a new record with a different timestamp, a select will consider the records with the latest timestamp. This means that there is no "re-order" operation ever. — Carlos Monroy Nieblas

BernadetteD BernadetteD · Accepted Answer · 2019-07-02T15:05:07

In order to make modified a clustering column, it needs to be defined in the primary key.

CREATE TABLE myTable (
    user_id INT,
    message TEXT,
    modified DATE,
    PRIMARY KEY ((user_id), modified)
)
WITH CLUSTERING ORDER BY (modified DESC);

This way, your data will be sorted primarily by the hashed value of the user_id, and within each user_id by modified. You don't need to drop the "WITH CLUSTERING ORDER BY (modified DESC)"

Cassandra CLUSTERING ORDER with updates [performance]

3 Answers