CODEWITHSUNDEEP
cassandra
tagsense
tagsense

Reputation: 81

Efficient Cassandra keyspace design

I have a question about optimal Cassandra database design: is it efficient to have a single table with a large number of skinny rows or is it efficient to have a keyspace with many many tables?

The context: I am trying to store data from multiple sensors. One approach would be to have a single table that stores data from all sensors. The other approach would be to have one table per sensor. Which one is better?

Please advise.

Upvotes: 3

Views: 329

Answers (2)

Aeham
Aeham

Reputation: 438

I'd go with fewer tables for a number of reasons:

  1. As Andy Tolbert mentioned in his reply, each table introduces some overhead which builds up to a large amount when you have 10s or 100s of thousands of tables. Think of it as increasing your overhead/value ratio
  2. If you are dealing with a large number of tables, chances are you'll be creating some of them dynamically during the application's normal operating time. If that is the case, you may see errors in Cassandra as it can fail to propagate the schemas of some new tables across the cluster when it's under pressure. I've seen this in C* 2.0 but I'm not sure if it's still an issue in the latest versions.
  3. Most of the benefits of a multi-table schema can be gained from putting extra thought into single-table data modelling. Having said that, there are cases when segregating data into discrete tables really is the most appropriate solution. One example of this is in certain multi-tenancy systems where data for different tenants needs to be kept physically separate and backed up in isolation, for regulatory reasons.

Upvotes: 3

Andy Tolbert
Andy Tolbert

Reputation: 11638

It is much better and idiomatic to have 1 table for all sensors. There is some overhead introduced with each table (mxbeans for metrics, files, etc.) so you don't have want to have too many.

When you say 'a large number of skinny rows' I don't anticipate that being a problem, you can have many unique keys/partitions (some crazy large number).

Upvotes: 2

Related Questions