When Two Great Ideas Had a Child
Facebook's inbox search team had a problem in 2007. Users needed to search through thousands of messages with sub-second latency. Relational databases choked on the write volume (millions of messages per hour) and struggled with the search patterns. The team needed a database that could absorb extreme write throughput, scale horizontally without a master node, and support fast lookups by user ID.
Avinash Lakshman, who had worked on Dynamo at Amazon, combined two designs: Dynamo's consistent hashing and replication model with BigTable's column-family data structure. The result was Cassandra. No master node means no single point of failure. Any node can accept any read or write. Adding nodes increases capacity linearly. Facebook open-sourced Cassandra in 2008, and it became the go-to database for write-heavy, geo-distributed workloads. Apple runs 160,000 Cassandra nodes. Netflix stores trillions of rows in it.
The NoSQL Explosion of 2009
In 2009, the database landscape fragmented almost overnight. MongoDB launched with a 'schema-less JSON documents' pitch that terrified experienced DBAs but thrilled developers who hated SQL migrations. Redis appeared as a blazing-fast in-memory key-value store that could do data structures (lists, sets, sorted sets) natively. CouchDB offered eventual consistency with HTTP APIs. Neo4j brought graph databases to the mainstream. HBase (the Hadoop BigTable clone) matured for analytical workloads.
The term 'NoSQL' caught on, though nobody agreed what it meant. The useful interpretation: these databases reject the assumption that one data model fits all workloads. A social graph is not naturally a set of relational tables. A session cache does not need ACID. A time-series of sensor readings does not need joins. The movement was not about rejecting SQL. It was about rejecting the idea that every problem should be hammered into the relational model.
Polyglot persistence: use different databases for different problems. A relational database for user accounts. Redis for session caching. Cassandra for activity feeds. Elasticsearch for search. The best database is always the one that matches your access pattern.
CAP: Twelve Years Later (2012)
Brewer revisited his own theorem and issued corrections. The biggest one: people were misusing CAP as a justification for building permanently inconsistent systems. 'We are AP, so consistency is someone else's problem.' That is not what CAP says.
Partitions are rare events. During normal operation, you can have all three: consistency, availability, and partition tolerance. The question is what you do during the brief window when a partition actually occurs. And even then, the trade-off is not all-or-nothing. You can be consistent for financial transactions while being available for recommendation feeds. You can sacrifice consistency for 5 seconds during a partition and then reconcile.
The revised mental model Brewer proposed: design your system to detect partitions, enter a well-defined degraded mode with explicit trade-offs, and recover to full consistency when the partition heals. Do not design for permanent inconsistency. Design for graceful degradation and fast recovery.
Choosing Your Database Today
Every system design interview eventually asks you to justify your database choice. Here is a practical framework:
- High write throughput with eventual consistency acceptable: Cassandra, DynamoDB. Activity feeds, IoT sensor data, user event tracking.
- Strong consistency required: PostgreSQL, MySQL, or any ACID-compliant RDBMS. Financial transactions, inventory, user accounts.
- Low-latency reads with simple key-value access: Redis, Memcached. Sessions, rate limiting counters, leaderboards.
- Full-text search: Elasticsearch. Product catalogs, log search, any free-text search.
- Graph relationships: Neo4j, Amazon Neptune. Social networks, fraud detection, recommendation engines.
- Flexible schema with document structure: MongoDB, Firestore. Content management, catalogs, user-generated data.
Further Reading
- Lakshman & Malik (2010): 'Cassandra: A Decentralized Structured Storage System'
- Brewer (2012): 'CAP Twelve Years Later: How the Rules Have Changed'
- Stonebraker (2010): 'SQL Databases v. NoSQL Databases', a balanced view from a relational database pioneer
- Cassandra's data modeling guide for understanding wide-column design patterns
Cassandra Data Modeling: Query-First, Not Schema-First
The biggest mistake engineers make with Cassandra is modeling data the way they would model a relational database. In PostgreSQL, normalize first, denormalize when needed for performance. In Cassandra, start from the queries you need to serve and model data to serve them directly. Cassandra's partition key determines which node holds the data and how efficiently it can be read. All data for a query must live in a single partition or the query becomes a scatter-gather across the cluster, destroying performance.
The golden rule: one query, one table. If you need to look up messages by user AND by conversation, you maintain two tables with the same data organized differently. Cassandra's storage is cheap. The cost of denormalization is disk space and write amplification. The benefit is guaranteed single-partition reads for every query pattern. Attempting to do multi-partition queries or secondary index queries in Cassandra the way you would in PostgreSQL is the path to a slow, unstable cluster.
- Partition key: determines which node stores the data. Must distribute evenly across nodes (avoid hotspots).
- Clustering columns: determine the sort order within a partition. Range queries on clustering columns are efficient.
- Denormalization: duplicate data across multiple tables, one per access pattern. Normal in Cassandra, a smell in relational.
- Tombstones: deletes in Cassandra write a 'tombstone' marker, not an actual removal. Too many tombstones slow reads.
- TTL (time-to-live): built-in expiration per column or row. Automatically cleans up tombstones. Essential for time-series and session data.
When NOT to Use a NoSQL Database
The NoSQL movement oversold its case in 2009-2012, and many teams adopted NoSQL databases for workloads where a relational database would have been simpler and more correct. The enthusiasm faded when teams discovered they had replaced SQL's JOIN complexity with application-level join complexity, and ACID's transaction guarantees with hand-rolled compensation logic. Use NoSQL when the workload genuinely requires what NoSQL provides: extreme write throughput, flexible schema, horizontal scaling of both reads and writes, or a non-relational data model (graphs, documents, wide rows).
- Stick with SQL when: your data is relational with complex many-to-many relationships that need ad-hoc JOIN queries
- Stick with SQL when: you need multi-entity ACID transactions (e-commerce checkout modifying inventory + orders + payments atomically)
- Stick with SQL when: your team is small and operational complexity matters more than raw scale
- Stick with SQL when: your write volume is under 10,000 writes/second, which PostgreSQL handles fine
- Choose NoSQL when: write throughput exceeds what a primary-replica relational cluster can handle, schema evolution is constant, or data model is genuinely non-relational
What Interviewers Test About NoSQL Selection
Database choice questions test whether you understand trade-offs rather than reciting a NoSQL vs SQL binary. Interviewers probe whether you know the operational costs of NoSQL (no transactions, denormalized data, schema discipline required at the application level) and whether your choice is justified by the workload requirements.
- Know: when Cassandra's ring architecture makes it the right choice vs when DynamoDB's managed service simplifies operations
- Know: the write path in Cassandra (MemTable write + commit log append, then SSTable flush)
- Know: how tombstones affect read performance and why delete-heavy workloads need careful TTL management
- Know: why Cassandra secondary indexes are dangerous and how to model around them with additional tables
- Know: CAP position of each major NoSQL store and what workloads that makes each suitable for
Cassandra Data Modeling: Query-First, Not Schema-First
The biggest mistake engineers make with Cassandra is modeling data the way they would model a relational database. In PostgreSQL, normalize first, denormalize when needed for performance. In Cassandra, start from the queries you need to serve and model data to serve them directly. Cassandra's partition key determines which node holds the data and how efficiently it can be read. All data for a query must live in a single partition or the query becomes a scatter-gather across the cluster, destroying performance.
The golden rule: one query, one table. If you need to look up messages by user AND by conversation, you maintain two tables with the same data organized differently. Cassandra's storage is cheap. The cost of denormalization is disk space and write amplification. The benefit is guaranteed single-partition reads for every query pattern. Attempting to do multi-partition queries or secondary index queries in Cassandra the way you would in PostgreSQL is the path to a slow, unstable cluster.
- Partition key: determines which node stores the data. Must distribute evenly across nodes (avoid hotspots).
- Clustering columns: determine the sort order within a partition. Range queries on clustering columns are efficient.
- Denormalization: duplicate data across multiple tables, one per access pattern. Normal in Cassandra, a smell in relational.
- Tombstones: deletes in Cassandra write a 'tombstone' marker, not an actual removal. Too many tombstones slow reads.
- TTL (time-to-live): built-in expiration per column or row. Automatically cleans up tombstones. Essential for time-series and session data.
When NOT to Use a NoSQL Database
The NoSQL movement oversold its case in 2009-2012, and many teams adopted NoSQL databases for workloads where a relational database would have been simpler and more correct. The enthusiasm faded when teams discovered they had replaced SQL's JOIN complexity with application-level join complexity, and ACID's transaction guarantees with hand-rolled compensation logic. Use NoSQL when the workload genuinely requires what NoSQL provides: extreme write throughput, flexible schema, horizontal scaling of both reads and writes, or a non-relational data model (graphs, documents, wide rows).
- Stick with SQL when: your data is relational with complex many-to-many relationships that need ad-hoc JOIN queries
- Stick with SQL when: you need multi-entity ACID transactions (e-commerce checkout modifying inventory + orders + payments atomically)
- Stick with SQL when: your team is small and operational complexity matters more than raw scale
- Stick with SQL when: your write volume is under 10,000 writes/second, which PostgreSQL handles fine
- Choose NoSQL when: write throughput exceeds what a primary-replica relational cluster can handle, schema evolution is constant, or data model is genuinely non-relational
What Interviewers Test About NoSQL Selection
Database choice questions test whether you understand trade-offs rather than reciting a NoSQL vs SQL binary. Interviewers probe whether you know the operational costs of NoSQL (no transactions, denormalized data, schema discipline required at the application level) and whether your choice is justified by the workload requirements.
- Know: when Cassandra's ring architecture makes it the right choice vs when DynamoDB's managed service simplifies operations
- Know: the write path in Cassandra (MemTable write + commit log append, then SSTable flush)
- Know: how tombstones affect read performance and why delete-heavy workloads need careful TTL management
- Know: why Cassandra secondary indexes are dangerous and how to model around them with additional tables
- Know: CAP position of each major NoSQL store and what workloads that makes each suitable for
Cassandra Data Modeling: Query-First, Not Schema-First
The biggest mistake engineers make with Cassandra is modeling data the way they would model a relational database. In PostgreSQL, normalize first, denormalize when needed for performance. In Cassandra, start from the queries you need to serve and model data to serve them directly. Cassandra's partition key determines which node holds the data and how efficiently it can be read. All data for a query must live in a single partition or the query becomes a scatter-gather across the cluster, destroying performance.
The golden rule: one query, one table. If you need to look up messages by user AND by conversation, you maintain two tables with the same data organized differently. Cassandra's storage is cheap. The cost of denormalization is disk space and write amplification. The benefit is guaranteed single-partition reads for every query pattern. Attempting to do multi-partition queries or secondary index queries in Cassandra the way you would in PostgreSQL is the path to a slow, unstable cluster.
- Partition key: determines which node stores the data. Must distribute evenly across nodes (avoid hotspots).
- Clustering columns: determine the sort order within a partition. Range queries on clustering columns are efficient.
- Denormalization: duplicate data across multiple tables, one per access pattern. Normal in Cassandra, a smell in relational.
- Tombstones: deletes in Cassandra write a 'tombstone' marker, not an actual removal. Too many tombstones slow reads.
- TTL (time-to-live): built-in expiration per column or row. Automatically cleans up tombstones. Essential for time-series and session data.
When NOT to Use a NoSQL Database
The NoSQL movement oversold its case in 2009-2012, and many teams adopted NoSQL databases for workloads where a relational database would have been simpler and more correct. The enthusiasm faded when teams discovered they had replaced SQL's JOIN complexity with application-level join complexity, and ACID's transaction guarantees with hand-rolled compensation logic. Use NoSQL when the workload genuinely requires what NoSQL provides: extreme write throughput, flexible schema, horizontal scaling of both reads and writes, or a non-relational data model (graphs, documents, wide rows).
- Stick with SQL when: your data is relational with complex many-to-many relationships that need ad-hoc JOIN queries
- Stick with SQL when: you need multi-entity ACID transactions (e-commerce checkout modifying inventory + orders + payments atomically)
- Stick with SQL when: your team is small and operational complexity matters more than raw scale
- Stick with SQL when: your write volume is under 10,000 writes/second, which PostgreSQL handles fine
- Choose NoSQL when: write throughput exceeds what a primary-replica relational cluster can handle, schema evolution is constant, or data model is genuinely non-relational
What Interviewers Test About NoSQL Selection
Database choice questions test whether you understand trade-offs rather than reciting a NoSQL vs SQL binary. Interviewers probe whether you know the operational costs of NoSQL (no transactions, denormalized data, schema discipline required at the application level) and whether your choice is justified by the workload requirements.
- Know: when Cassandra's ring architecture makes it the right choice vs when DynamoDB's managed service simplifies operations
- Know: the write path in Cassandra (MemTable write + commit log append, then SSTable flush)
- Know: how tombstones affect read performance and why delete-heavy workloads need careful TTL management
- Know: why Cassandra secondary indexes are dangerous and how to model around them with additional tables
- Know: CAP position of each major NoSQL store and what workloads that makes each suitable for
Cassandra Data Modeling: Query-First, Not Schema-First
The biggest mistake engineers make with Cassandra is modeling data the way they would model a relational database. In PostgreSQL, normalize first, denormalize when needed for performance. In Cassandra, start from the queries you need to serve and model data to serve them directly. Cassandra's partition key determines which node holds the data and how efficiently it can be read. All data for a query must live in a single partition or the query becomes a scatter-gather across the cluster, destroying performance.
The golden rule: one query, one table. If you need to look up messages by user AND by conversation, you maintain two tables with the same data organized differently. Cassandra's storage is cheap. The cost of denormalization is disk space and write amplification. The benefit is guaranteed single-partition reads for every query pattern. Attempting to do multi-partition queries or secondary index queries in Cassandra the way you would in PostgreSQL is the path to a slow, unstable cluster.
- Partition key: determines which node stores the data. Must distribute evenly across nodes (avoid hotspots).
- Clustering columns: determine the sort order within a partition. Range queries on clustering columns are efficient.
- Denormalization: duplicate data across multiple tables, one per access pattern. Normal in Cassandra, a smell in relational.
- Tombstones: deletes in Cassandra write a 'tombstone' marker, not an actual removal. Too many tombstones slow reads.
- TTL (time-to-live): built-in expiration per column or row. Automatically cleans up tombstones. Essential for time-series and session data.
When NOT to Use a NoSQL Database
The NoSQL movement oversold its case in 2009-2012, and many teams adopted NoSQL databases for workloads where a relational database would have been simpler and more correct. The enthusiasm faded when teams discovered they had replaced SQL's JOIN complexity with application-level join complexity, and ACID's transaction guarantees with hand-rolled compensation logic. Use NoSQL when the workload genuinely requires what NoSQL provides: extreme write throughput, flexible schema, horizontal scaling of both reads and writes, or a non-relational data model (graphs, documents, wide rows).
- Stick with SQL when: your data is relational with complex many-to-many relationships that need ad-hoc JOIN queries
- Stick with SQL when: you need multi-entity ACID transactions (e-commerce checkout modifying inventory + orders + payments atomically)
- Stick with SQL when: your team is small and operational complexity matters more than raw scale
- Stick with SQL when: your write volume is under 10,000 writes/second, which PostgreSQL handles fine
- Choose NoSQL when: write throughput exceeds what a primary-replica relational cluster can handle, schema evolution is constant, or data model is genuinely non-relational
What Interviewers Test About NoSQL Selection
Database choice questions test whether you understand trade-offs rather than reciting a NoSQL vs SQL binary. Interviewers probe whether you know the operational costs of NoSQL (no transactions, denormalized data, schema discipline required at the application level) and whether your choice is justified by the workload requirements.
- Know: when Cassandra's ring architecture makes it the right choice vs when DynamoDB's managed service simplifies operations
- Know: the write path in Cassandra (MemTable write + commit log append, then SSTable flush)
- Know: how tombstones affect read performance and why delete-heavy workloads need careful TTL management
- Know: why Cassandra secondary indexes are dangerous and how to model around them with additional tables
- Know: CAP position of each major NoSQL store and what workloads that makes each suitable for