CODEWITHSUNDEEP
cassandra
dontknow
dontknow

Reputation: 85

Cassandra database design - 1000 columns or dynamically created tables

I wanted to hear your advice about a potential solution for an advertise agency database.

We want to build a system that will be able to track users in a way that we know what they did on the ads, and where.

There are many type of ads, and some of them also FORMS, so user can fill data. Each form is different but we dont want to create table per form.

We thought of creating a very WIDE table with 1k columns, dozens for each type, and store the data.

In short:

  1. Use Cassandra;
  2. Create daily tables so data will be stored on a daily table;
  3. Each table will have 1000 cols (100 for datetime, 100 for int, etc).

Application logic will map the data into relevant cols so we will be able to search and update those later.

What do you think of this ?

Upvotes: 1

Views: 523

Answers (1)

phact
phact

Reputation: 7305

Be careful with generating tables dynamically in Cassandra. You will start to have problems when you have too many tables because there is a per table memory overhead. Per Jonathan Ellis:

Cassandra will reserve a minimum of 1MB for each CF's memtable: http://www.datastax.com/dev/blog/whats-new-in-cassandra-1-0-performance

Even daily tables are not a good idea in Cassandra (tables per form is even worse). I recommend you build a table that can hold all your data and you know will scale well -- verify this with cassandra-stress.

At this point, heed mikea's advice and start thinking about your access patterns (see Patrick's video series), you may have to build additional tables to meet your querying needs.

Note: For anyone wishing for a schemaless option in c*: https://blog.compose.io/schema-less-is-usually-a-lie/ http://rustyrazorblade.com/2014/07/the-myth-of-schema-less/

Upvotes: 2

Related Questions