SQL vs NoSQL

Relational databases and ACID

Relational databases (Postgres, MySQL) store data in tables with enforced schemas, and their superpower is ACID transactions: atomicity (all or nothing), consistency (constraints hold), isolation (concurrent transactions behave as if serial), and durability (committed data survives crashes). Transferring money between accounts, decrementing inventory while creating an order, or any multi-row invariant is dramatically simpler with ACID.

SQL also gives you joins, secondary indexes, and ad hoc queries. When product requirements change and someone asks for a new report, a relational schema usually answers it with a query rather than a data migration. A single well-tuned Postgres instance on modern hardware comfortably handles 10,000 to 50,000 transactions per second and terabytes of data, which covers the vast majority of businesses.

The scaling story is the traditional weakness: relational databases scale vertically first, then via read replicas, and only painfully via sharding. Modern answers like Vitess (which shards MySQL and runs YouTube-scale workloads), Citus for Postgres, and NewSQL systems like CockroachDB and Spanner have narrowed this gap considerably.

The NoSQL families

NoSQL is four distinct families, and interviewers expect you to distinguish them. Document stores (MongoDB, CouchDB) hold JSON-like documents with flexible schemas, ideal when entities are self-contained and read together, such as a product with its variants and reviews embedded. Key-value stores (DynamoDB, Redis) offer the simplest model, get and put by key, with predictable single-digit millisecond latency at any scale; DynamoDB famously served over 89 million requests per second during Amazon Prime Day.

Wide-column stores (Cassandra, HBase, Bigtable) organize data into partitions of ordered rows, optimized for massive write throughput and range scans within a partition. Cassandra's masterless design lets it ingest hundreds of thousands of writes per second across commodity nodes, which is why it backs time-series and messaging workloads: Discord stored trillions of messages on Cassandra before migrating to ScyllaDB, a compatible rewrite.

Graph databases (Neo4j, Amazon Neptune) model nodes and edges directly, so multi-hop traversals like friends-of-friends-of-friends run in milliseconds where the equivalent SQL requires exploding self-joins. They shine for fraud rings, recommendations, and social graphs, but are a poor fit for bulk analytics or simple CRUD.

BASE and the consistency spectrum

Many NoSQL systems trade ACID for BASE: Basically Available, Soft state, Eventually consistent. Instead of guaranteeing every read sees the latest write, they guarantee availability and let replicas converge over time. A Cassandra write at consistency level ONE is acknowledged by a single replica and propagates to others asynchronously; a reader hitting a different replica milliseconds later may see the old value.

Eventual consistency is not lawless. Systems offer tunable knobs: Cassandra lets you set read and write quorums per query (writes at QUORUM plus reads at QUORUM gives strongly consistent behavior for that key), and DynamoDB offers strongly consistent reads at double the cost and roughly half the throughput of eventually consistent ones. DynamoDB also added ACID transactions across items in 2018, and MongoDB added multi-document transactions in version 4.0, so the old bright line has blurred.

The interview skill is mapping consistency needs to features: a shopping cart can tolerate eventual consistency (Amazon's original Dynamo paper literally used the cart as its example, resolving conflicts by merging), while a payment ledger cannot.

How to choose in an interview

Start from access patterns, not technology. If you need flexible ad hoc queries, joins across entities, and transactional invariants, and your scale fits a single primary plus replicas, pick Postgres and say why. It is a strong senior signal to default to relational and justify NoSQL only when a concrete pressure demands it.

Reach for NoSQL when you have a specific forcing function: a write rate or dataset size that requires horizontal scale-out across dozens of nodes (Cassandra, DynamoDB), a strict low-latency key lookup SLA at massive scale (DynamoDB, Redis), genuinely schema-less or rapidly evolving documents (MongoDB), or traversal-heavy graph queries (Neo4j). Name the access pattern first, then the store.

Polyglot persistence is the realistic endgame: an e-commerce system might keep orders and payments in Postgres, the product catalog in a document store or search index, sessions in Redis, and clickstream events in Cassandra. Acknowledge the operational cost of running multiple databases; every additional store is another system to secure, back up, and page on.

Tradeoffs