How do I efficiently partition an index-only table in Cassandra?

Montes 0
Viewed 20

I need to create an append only table which should only store a pair of values (foreign_id, some_string). There will be a limited number of foreign_id values (let's say 100 - 10 000) and 10s of millions of some_string values (they may not be evenly distributed between foreign_ids)

I am only interested whether a given (foreign_id, some_string) pair exists in the table.

What would be the most efficient way (when it comes to query response time) of partitioning this table?

I am pretty sure that creating a primary key PRIMARY KEY ((foreign_id), some_string) is a bad idea, because a single partition could easily grow beyond 100 MB which is not recommended AFAIK.

Should I simply partition the table by both foreign_id and some_string like this PRIMARY KEY ((foreign_id, some_string)) or is there some issue with this approach?

cassandra
1 Answers

The primary philosophy of data modelling in Cassandra is -- for each application query, design a table that is optimised for that query. It is the complete opposite of data modelling in traditional relational databases.

Don't get hung up on how you will store the data in the table but focus on what query your application requires because the app query is the crucial aspect that determines how the table will be optimised for reads.

Looking at this statement from you:

I am only interested whether a given (foreign_id, some_string) pair exists in the table.

My understanding is that your app query is something along the lines of:

"Does ID X and string Y exist?"

which means that you should partition the table by both ID and string:

CREATE TABLE tbl_by_id_string (
    foreign_id text,
    some_string text,
    exists boolean,
    PRIMARY KEY ((foreign_id, some_string))
)

The equivalent CQL query to your app query is:

SELECT exists FROM tbl_by_id_string WHERE foreign_id = ? AND some_string = ?

This design is optimised for your app query and completely eliminates your concern around having large partitions because each partition in the table will only ever have ONE row and will never get any bigger than that.

Also, you can have billions and billions of combinations of ID + string and they will be distributed evenly across the nodes in the cluster with this design. Cheers!

Erick Ramirez0
Related