SQL vs NoSQL: structure, or scale?

SQL databases (Postgres, MySQL…) store data in tables with a defined schema and let you join across them, with ACID transactions guaranteeing correctness even under concurrency. They’re the right default when your data is relational and you value consistency. NoSQL isn’t one thing — it’s document (MongoDB), key-value (DynamoDB, Redis), wide-column (Cassandra), and graph (Neo4j) stores that each drop some relational feature (usually joins and strict schema) to gain horizontal scale and flexibility.

NoSQL’s scale often comes from relaxing consistency — the CAP theorem in action: under a network partition you can’t have both perfect consistency and availability, and many NoSQL stores choose availability with eventual consistency.

The scaling gap isn't arbitrary — it's a direct consequence of what each model promises. A relational database's core value proposition is that any query can join any tables and get a correct answer right now, which means related rows effectively need to be reachable from wherever the query runs. Sharding a relational database across many machines breaks that promise the moment a join needs rows that live on different shards — you either forbid cross-shard joins (giving up a core relational feature) or pay a real latency and complexity cost to fetch and merge across the network.

Most NoSQL stores sidestep this by design: a key-value store or a document store is built around the assumption that a single lookup (by key, or by document ID) is the dominant access pattern, and it never promised you arbitrary joins in the first place. That lets it partition data across many nodes by key with no join to break — each shard is independently correct and complete for the queries the store actually supports. The "cost" isn't scale, it's expressiveness: you paid for horizontal scale by giving up the ability to ask questions the schema didn't anticipate, which is exactly why denormalizing (duplicating related data into the document you'll actually query, instead of normalizing it into a separate joined table) is the standard NoSQL modeling technique — you shape the data around the query, not the query around the data.

Say you're building a social app and need to answer two questions fast: "who liked this post" (for the like-count and avatar row under a post) and "what has this user liked recently" (for their profile). This is a genuinely useful test case because the two questions pull in different directions.

In a relational (SQL) model, this is a likes join table with post_id and user_id columns, indexed on both. "Who liked this post" is SELECT user_id FROM likes WHERE post_id = ?; "what has this user liked" is the same table filtered by user_id instead. One table, two indexes, both questions answered correctly and consistently — and if you later need a third question ("mutual likes between two users"), it's a join away, not a redesign.

In a document (NoSQL) model like MongoDB, you'd typically pick which direction to denormalize for: embed a likedBy array of user IDs directly in the post document (fast "who liked this post," since it's one document read), and separately maintain a likedPosts array on the user document for the reverse query — now the data is duplicated across two documents, and every like requires updating both, with no transaction spanning them by default in many document stores' simplest mode. You've traded a single well-indexed join table for two documents you must keep manually in sync, in exchange for each individual read being a fast, single-document fetch with no join at all.