Cassandra and CodeWords: query-first, and tombstones

Cassandra is chosen for write throughput and for surviving the loss of a node without anybody noticing, and it charges for that in query flexibility. You design a table per query, and a question nobody anticipated is a new table rather than a new index.

Automation always arrives with a question nobody anticipated. How you answer it without damaging a cluster serving production traffic is the substance of automating against Cassandra, and the answer is rarely a clever query.

What we'll cover

• Query-first modelling, and what it costs you
• Tombstones, the failure mode nobody expects
• What Cassandra reaches
• Connecting it to CodeWords
• Seven automations worth building
• Consistency levels, chosen per statement
• Analytics belongs somewhere else
• Time series, which is what most clusters hold
• Building it so it survives
• Limits worth knowing about
• What to build first
• Frequently asked questions

Query-first modelling, and what it costs you

The partition key decides everything. A query knowing its partition is fast at any scale; one that does not is a cluster-wide scan.

Clustering columns order rows within a partition, which is what makes range queries possible.

Denormalisation is the design, so the same data lives in several tables, each shaped for one query. Keeping them consistent is your application's job.

Secondary indexes exist and are usually the wrong answer, since they can turn a query into a scatter across every node.

ALLOW FILTERING is a warning, not a feature. It makes a query work and makes it unpredictable, and it appears in automation far more often than in application code because automation asks the questions nobody designed for.

For automation, the sequence is: state the question, find the table designed for it, and if there isn't one, use an export rather than forcing the cluster to answer something it was not shaped for.

Tombstones, the failure mode nobody expects

Deletes in Cassandra do not remove data. They write a marker, and the marker is read alongside the data until compaction removes it.

A partition with many tombstones is slow to read, and the slowness grows rather than appearing suddenly.

Reads can fail entirely past a configured tombstone threshold, which is a read failing because of deletes that happened weeks ago.

A queue pattern is the classic trap. Writing rows and deleting them as they are processed produces exactly the tombstone accumulation that makes the table unusable, and it is the most intuitive design for the use case.

Time to live creates tombstones too, at expiry, so expiring data has the same effect on a schedule.

Bulk deletion by an automation is a good way to cause an incident, which is why cleanup work needs to be spread out and scheduled rather than run as one pass.

If your automation deletes, know how many tombstones it is creating and when compaction will clear them.

What Cassandra reaches

CQL statements — select, insert, update, delete — against tables, executed per statement with a chosen consistency level.

Prepared statements are reused efficiently and are what any repeated query should use.

Batches exist and are for atomicity within a partition rather than for throughput; a multi-partition batch is slower than separate statements.

Lightweight transactions provide compare-and-set semantics at considerable cost, suitable for rare operations rather than routine ones.

Time to live expires rows automatically, with the tombstone caveat above.

Change data capture is available where configured, and is more involved to consume than the equivalent in other databases.

System tables and nodetool expose cluster health, which is where operational reporting comes from.

Connecting it to CodeWords

CodeWords connects to more than 3,000 integrations, and the connection is made once and reused.

1. Open CodeWords and start a new automation.
2. Describe what should happen in plain language to Cody, the automation builder: which table, which partition, and what to do with the rows.
3. Authorize the connection with credentials for a role scoped to the specific keyspace, over a route your cluster permits.
4. Describe the exceptions: a query that would need filtering, a partition larger than expected, a write timeout.
5. Run it against a test cluster or a restored snapshot before anything touches production.

You describe the outcome; Cody builds it, connects it, and deploys it. The free plan covers light use, with Pro at $39 per month and Business at $100 per month as usage grows; details are on the pricing page.

Seven automations worth building

Cluster health reporting. Node status, disk usage, pending compactions, and dropped messages, gathered and reported so degradation is noticed before it becomes an outage.

Tombstone and partition size reporting. Which tables have large partitions and heavy tombstone counts, which is the earliest warning of the failure described above.

Scheduled export for analysis. Feed a warehouse so analytical questions are answered somewhere that can answer them.

Data quality checks against the export, not the cluster, since the denormalised copies drifting apart is the characteristic problem here.

Reconciliation between denormalised tables. Where the same entity lives in several tables, compare and report differences.

Controlled cleanup. Expiring data removed in small, paced batches at quiet hours rather than in one pass, with the tombstone impact accounted for.

Schema change reporting. What changed in the keyspace and when, since schema changes in a distributed database deserve a record.

Consistency levels, chosen per statement

Cassandra's consistency is a choice you make per query, and the choice has real consequences.

Quorum reads and writes together give you read-your-writes across a single datacentre, which is what most correctness-sensitive work needs.

One is fast and may return stale data, which is fine for reporting and not fine for a check that something was written.

All is rarely right, since it fails if any replica is unavailable, which removes the availability you chose Cassandra for.

Local quorum matters in multi-datacentre deployments, where a plain quorum can mean waiting for another continent.

Automation should usually read at one for reporting and at quorum for anything that will act on the result. Choosing deliberately per automation, rather than taking a driver default, is a five-minute decision with a large effect.

Analytics belongs somewhere else

Stated plainly because it is the most common mistake.

Cassandra cannot aggregate across partitions cheaply. There is no efficient equivalent of a full-table grouped query.

A scan competes with production traffic and can degrade latency for everybody.

Export to a warehouse or a lake and answer analytical questions there, on a schedule.

Spark connectors exist for genuinely large processing, and they are a deliberate piece of infrastructure rather than something an automation reaches for casually.

A counted row estimate from system tables is often enough when somebody just wants to know roughly how big something is.

Time series, which is what most clusters hold

A large share of Cassandra deployments store events over time, and the pattern has its own rules.

Bucket the partition key. A partition per device per day, or per hour, rather than a partition per device for all time — otherwise the partition grows without bound and eventually becomes unreadable.

Choose the bucket by expected volume. Too coarse and partitions grow too large; too fine and a query spanning a week touches hundreds of partitions.

Clustering by timestamp descending makes the most recent data cheapest to read, which is usually what is wanted.

Time to live suits expiring data, and it produces tombstones at expiry, so set the window with compaction behaviour in mind.

Use the compaction strategy intended for time series where the data is written once and expires, since the default strategy is a poor fit and the difference in disk and read cost is substantial.

Query within a bucket. An automation asking for a range spanning many buckets should fetch per bucket deliberately rather than letting one query span them all.

Building it so it survives

Use prepared statements for anything repeated.

Set timeouts explicitly on both the client and the statement, since a slow query holding a connection affects more than itself.

Page through large result sets rather than fetching everything, and be aware the driver may do this for you in ways that still hold resources.

Avoid multi-partition batches, which are slower than individual statements and put load on the coordinator node.

Report the outcome. Rows read and written, timeouts, and the consistency level used, since the last one explains a result that looks wrong.

Limits worth knowing about

Large partitions degrade everything touching them, and a partition key with unbounded growth is the commonest modelling error.

Tombstone thresholds cause read failures, configured per cluster and not obvious until you hit them.

Lightweight transactions are expensive and do not scale like ordinary writes.

No joins, no aggregation across partitions, by design.

Write timeouts do not mean the write failed. It may have succeeded on some replicas, which is why idempotent writes matter more here than almost anywhere else.

What to build first

Tombstone and partition size reporting: which tables have the largest partitions and the heaviest tombstone counts, tracked weekly. It reads system metadata rather than data, it costs the cluster almost nothing, and it gives warning of the two failures that take a Cassandra cluster down with very little notice.

Two habits make the difference. Report the trend rather than the absolute, since a partition growing steadily is the thing to act on before it is large. And show it to whoever owns the data model, because both problems are modelling decisions rather than operational ones.

Frequently asked questions

Why are reads getting slower on a table we only delete from?

Tombstones. Deletes write markers that are read alongside the data until compaction clears them, so a table used as a queue accumulates exactly the thing that makes it slow. Past a threshold, reads fail outright.

Can I run a report across the whole table?

Not efficiently, and not without affecting production. Export to a warehouse and report there. Cassandra is built for known queries against known partitions, and everything else is expensive.

What does ALLOW FILTERING actually do?

It permits a query the data model does not support, by reading more than it needs and discarding the rest. It makes the query work and its cost unpredictable, which is why it appears in automation far more than in application code.

Which consistency level should automation use?

One for reporting, quorum for anything that will act on the result or that needs to see a recent write. Take the decision deliberately per automation rather than accepting the driver default.

A write timed out — did it fail?

Not necessarily. It may have succeeded on some replicas, which is why writes should be idempotent. Retrying a non-idempotent write after a timeout is how duplicates appear.

How should an automation delete a lot of data?

In small paced batches at quiet hours, with the tombstone impact understood, or by using time to live so expiry is spread out. A single bulk deletion pass is a reliable way to cause an incident some hours later.

Should automation write to the same tables as the application?

Preferably not, and if it must, respecting the same denormalisation. Writing to one of several tables holding the same entity leaves the others wrong, and nothing in the database will tell you.

How should time series data be partitioned?

Bucketed — per entity per day or per hour, depending on volume — rather than per entity for all time. An unbucketed partition grows without bound, and a very large partition degrades every read that touches it.

Does the compaction strategy matter for automation?

It matters for whether your reads stay fast. Time series data written once and expiring wants the strategy designed for it; the default behaves poorly with that pattern and the difference shows up as disk usage and read latency rather than as an error.

Related reading

• MongoDB and CodeWords
• DynamoDB and CodeWords
• CodeWords integrations and templates