Last week I had an opportunity to experiment with Cassandra database in a semi-real environment.

A few notes:

• Cassandra goes out of its way to be friendly to “traditional” SQL developers.
  • It’s query language and DDL are as close to SQL as humanely possible.
  • It allows composite partition and clustering keys.
  • It allows “bad” queries with filtering by “wrong” columns if you ask nicely.
• There are two ways to ask nicely:
  • Add pretty please ALLOW FILTERING keywords to the query.
  • Create a secondary index.
• Don’t believe people who say to never use ALLOW FILTERING in production. Sometimes “bad” query with ALLOW FILTERING works faster than “good” query with secondary index: see this StackOverflow question.

CREATE TABLE IF NOT EXISTS test.data (
  channel text,
  subdomain text,
  data_center text,
  day date, // we need this to limit the partition size
  message_id timeuuid,
  tags map<text,text>,
  sender_id text,
  sequence_number bigint,
  sent_at timestamp,
  received_at timestamp,
  header blob,
  payload blob,
  // the stuff in the inner parens is the partition key; the rest is the clustering (sorting) key
  PRIMARY KEY ((channel, subdomain, data_center, day), sent_at, message_id)
)
WITH CLUSTERING ORDER BY (sent_at ASC, message_id ASC);

Contrast it with Amazon DynamoDB that insists that both partition key and clustering key be strings with all the parts squished together as in InvoiceNumber#121212#0.

A few more things to note:

• I was able to insert ~90,000 records per second using multiple processes, with the client and the server on the same machine, and each record being slightly over 1KB.
• When creating a new index, Cassandra will return right away, but will not allow to query the table for a while, presumably until the index has been built.
• Some queries that go cross partition are allowed, e.g.
  • SELECT * FROM table [LIMIT N] or
  • SELECT COUNT(*) from table.
• The latter tends to time out when the table reaches a few million records.

Generally, inability to find the size of the table is annoying. There is a tool called nodetool that would return statistics, but the number of records is not part of that statistics unfortunately.

I operated with a single-node setup, and did not have an opportunity to explore replication, quorum and the like, this would be the subject of another excercise I suppose.