Apache Cassandra Skills Assessment

Cassandra's architecture is purpose-built for exactly this profile: high write throughput, predictable read latency by primary key, linear horizontal scale, and built-in multi-datacenter replication. Using product ID as the partition key distributes data evenly and enables O(1) reads. Redis is an in-memory store unsuitable for 500 million persistent records at this scale. PostgreSQL's vertical scaling and single-master write model becomes a bottleneck under global write load. MongoDB's sharding requires more operational complexity for equivalent multi-region availability guarantees.

In Cassandra, all rows with the same partition key are stored on the same set of nodes. When a small number of partition keys receive the majority of traffic, those nodes become hot spots - they handle far more reads and writes than their peers, degrading cluster-wide throughput. The fix is either to add a bucket (such as a date or random suffix) to the partition key to spread load, or to redesign the data model around the actual query pattern. Hot partitions are one of the most common and damaging Cassandra anti-patterns.

QUORUM consistency requires a majority of replicas to acknowledge a write, calculated as (replication_factor / 2) + 1. With RF=3, QUORUM = 2. When one replica is unavailable due to a partition, 2 replicas remain reachable and QUORUM is still satisfied - writes continue successfully. This is a core strength of Cassandra: tunable consistency allows operators to balance between availability and durability based on the use case. Cassandra does not auto-downgrade consistency levels; the application or operator controls that.

Financial transaction systems require atomic multi-row updates - for example, debiting one account and crediting another must either both succeed or both fail. Cassandra's lightweight transactions (LWT) provide limited compare-and-set atomicity within a single partition but do not support true multi-partition ACID transactions. PostgreSQL's full ACID guarantees, foreign key constraints, and JOIN capabilities make it the correct tool for relational financial data. Cassandra excels at append-heavy, partition-local workloads like event streams, time series, and catalog data.

TimeWindowCompactionStrategy is designed for time-series data where reads typically target recent windows. It groups SSTables by time window and compacts within each window, keeping recent data in a small number of large, optimized files. This dramatically reduces read amplification for recent-data queries compared to STCS, which creates increasingly large SSTables across the entire dataset. LCS is better for read-heavy, non-time-series workloads where row updates are common. STCS is optimized for write-heavy workloads where data is rarely updated or read.

Cassandra hashes the partition key using a partitioner (typically Murmur3) to produce a token value. That token maps to a specific range of the token ring, and the node responsible for that range is the coordinator that stores the primary replica. Additional replicas are placed on subsequent nodes in the ring based on the replication strategy. This is why partition key design is the most critical modeling decision in Cassandra - it directly controls data distribution and access patterns.

Cassandra does not support JOIN operations. Because data is distributed across nodes by partition key, joining tables would require cross-node coordination that would break Cassandra's performance guarantees. Instead, Cassandra uses denormalization as a first-class design principle: data that is queried together is stored together. This means teams often maintain multiple tables that duplicate data but are each optimized for a specific query pattern. This is a fundamental architectural difference from relational databases and must be understood before designing a Cassandra schema.

Cassandra's core design principle is 'one table per query pattern.' The messages_by_conversation table optimizes the ordered conversation retrieval query by using conversation_id as the partition key (all messages in a conversation on the same node) and timestamp as the clustering column (automatically sorted). A separate messages_by_id table handles direct lookups efficiently. Option B requires a full table scan, which is catastrophically slow in Cassandra. Secondary indexes in option D are suitable for low-cardinality columns and are not recommended as the primary access path for high-traffic lookups.

Cassandra's strengths are high write throughput, global distribution without a single point of failure, and efficient time-series patterns where data is partitioned by an entity ID and sorted by time. IoT ingest (option A) and time-series metrics (option C) are canonical Cassandra use cases. Payroll systems (option B) require complex relational modeling and ACID transactions across multiple entities - PostgreSQL's domain. Referential integrity (option D) is a relational concept that Cassandra explicitly does not enforce; foreign keys do not exist in Cassandra's data model.

The consistency level is a per-operation (or per-session) setting that controls how many replicas must acknowledge a read or write before the coordinator returns success to the client. Common levels include ONE (fastest, least consistent), QUORUM (majority, balanced), and ALL (strongest consistency, lowest availability). The choice of consistency level directly reflects the CAP theorem trade-off: higher consistency levels sacrifice availability during node failures, while lower levels maintain availability at the cost of potential stale reads or unacknowledged writes.

The primary key in Cassandra has two components: the partition key (which determines the node responsible for the data) and one or more optional clustering columns (which determine the sort order of rows within a partition). Together they uniquely identify a row. Unlike relational primary keys that exist purely for uniqueness, Cassandra's primary key encodes both data distribution (via the partition key) and retrieval efficiency (via clustering columns), making its design the single most important modeling decision.

Each consistency level maps to a specific acknowledgment requirement. ONE prioritizes availability and speed, accepting a single replica acknowledgment. QUORUM balances consistency and availability by requiring a majority (RF/2 + 1). ALL maximizes consistency but fails if any replica is unavailable. LOCAL_QUORUM is critical for multi-datacenter deployments: it satisfies quorum within the local datacenter only, avoiding cross-datacenter latency on the write path while still maintaining local consistency guarantees.

NetworkTopologyStrategy allows per-datacenter replication factors. In this configuration, each row has 2 replicas in DC1 and 1 replica in DC2, totalling 3 replicas across the cluster. The replication_factor specified at keyspace creation is the sum when using NetworkTopologyStrategy. This design provides local read/write performance in DC1 (where most traffic likely originates) while DC2 provides a disaster recovery copy. NetworkTopologyStrategy is the required strategy for any production multi-datacenter deployment.

The WITH CLUSTERING ORDER BY clause in a CREATE TABLE statement defines the default sort direction for clustering columns within a partition. For example, WITH CLUSTERING ORDER BY (event_time DESC) stores the most recent rows first within each partition, making 'get latest N events' queries highly efficient because Cassandra reads from the beginning of the partition without scanning. This is a critical optimization for time-series patterns. The sort direction cannot be changed after table creation without migrating data.