SQL vs NoSQL - Choosing the Right Database for the Job

Helpful context:

• Databases & Indexes - The Structures That Make Queries Fast
• Consistency and CAP Theorem - Why Distributed Systems Are Hard

The worst way to choose a database is to pick one because it is newer, or because a company you admire uses it. MongoDB is not automatically better than PostgreSQL. Cassandra is not automatically more scalable than MySQL. The question is never “which is better?” - it is “which fits this access pattern?”

Every NoSQL database type was invented to solve a specific problem that relational databases handled poorly. If you understand why each type exists, the choice usually becomes obvious. If you do not understand why, you will pick based on hype and regret it later.

Relational Databases: The Default

SQL databases - PostgreSQL, MySQL, SQLite, SQL Server, Aurora - organize data as tables: rows and columns with a fixed schema. Two properties make them special.

The first is the join. A join combines data from multiple tables at query time, on the fly, based on matching values:

SELECT users.name, orders.total, products.name
FROM orders
JOIN users ON orders.user_id = users.id
JOIN products ON orders.product_id = products.id
WHERE orders.created_at > '2024-01-01';

No data was physically duplicated to make this query work. The tables store data independently; the database computes the relationship at query time using indexes. This flexibility is enormous: the same underlying data supports any query you can express in SQL, without needing to redesign the storage.

The second property is ACID transactions:

• Atomicity: all operations in a transaction succeed or none do. “Transfer $100 from A to B” - debit A and credit B happen together, or neither happens. There is no state where A is debited but B is not.
• Consistency: the database enforces rules on every write - foreign keys must point to existing rows, unique constraints cannot be violated, check constraints hold.
• Isolation: concurrent transactions do not see each other’s in-progress state. Two users booking the last available seat cannot both succeed.
• Durability: once a transaction commits, it survives crashes and power failures.

The common objection - “SQL cannot scale” - is mostly wrong. PostgreSQL, properly indexed and tuned, handles tens of thousands of queries per second and hundreds of millions of rows without breaking a sweat. The companies that moved away from SQL at scale were usually hitting specific access pattern problems, not raw capacity limits. Most teams that reach for a NoSQL database because they think their SQL database “will not scale” are premature.

Start with PostgreSQL. Move to something else when you have a concrete reason, not a vague performance anxiety.

Document Stores: The Whole Entity in One Place

Document stores - MongoDB, Firestore, DynamoDB in document mode, CouchDB - store each entity as a self-contained document, typically JSON. There is no fixed schema: documents in the same collection can have different fields.

{
  "_id": "user-42",
  "name": "Megha",
  "email": "meg@example.com",
  "address": {
    "city": "Bangalore",
    "country": "India"
  },
  "preferences": ["dark_mode", "weekly_digest"],
  "subscription": {
    "plan": "pro",
    "expires": "2025-12-31",
    "features": ["unlimited_exports", "api_access"]
  }
}

The entire user record - nested address, array of preferences, embedded subscription with its own nested features - is one document. Reading a user is one lookup. In a relational database, this might be four tables (users, addresses, user_preferences, subscriptions) requiring three joins to reconstruct.

The core insight: document stores win when your dominant access pattern is “give me everything about entity X.” If you almost always read a full entity and rarely need to query across entities using relationships, document stores are faster to query and simpler to model.

The price is losing joins. “All users who placed orders over $1000 last month” is awkward in a document store - you either denormalize (duplicate order data inside user documents, then keep it synchronized manually on every order), or join in application code (fetch all orders, then fetch each user - the N+1 problem, but now you chose it deliberately).

Use document stores for: user profiles with variable structure, product catalogs where different product categories have different attributes, content management systems, configuration storage, any entity where the “fetch everything about this one thing” pattern dominates.

Key-Value Stores: The Lookup Table

A key-value store - Redis, DynamoDB as KV, Memcached, etcd - is a dictionary. Every value is accessed by its exact key in O(1). There is no schema, no query language, no joins.

# Store a session
redis.setex("session:user-42-abc123", 3600, json.dumps(session_data))

# Retrieve it
session = redis.get("session:user-42-abc123")

# Increment a counter atomically
redis.incr("likes:post-99")

# Distributed lock
redis.set("lock:checkout-order-7", "1", nx=True, ex=30)

The extreme simplicity is the strength. Microsecond latency. Horizontal scaling by hashing keys across shards. No indexes to manage, no query planner to tune.

The limitation is also the simplicity: you can only access data by its exact key. No range queries. No filtering on field values. No aggregations.

Redis in particular has grown beyond a pure key-value store: sorted sets (for leaderboards), hashes (for partial object updates), streams (for event logs), and pub/sub (for real-time notifications) are all built on the same key-value core. This makes Redis the most versatile tool in the caching and coordination category.

Use key-value stores for: session storage, application caches, rate limiting counters, distributed locks, leaderboards, feature flags, real-time counters. Almost every production application uses Redis alongside its primary database, not instead of it.

Wide-Column Stores: Optimized for Time-Series

Wide-column stores - Cassandra, HBase, Google Bigtable - look like SQL tables from a distance but behave fundamentally differently. Data is organized by a partition key (which node holds the data) and a clustering key (the sort order within a partition). Within a partition, different rows can have different columns.

The design is purpose-built for one access pattern: “give me all records for partition X, sorted by clustering key, in a time range.” Cassandra is extraordinarily good at this and poor at almost everything else.

A concrete example: a messaging system stores each conversation’s messages with (sender_id, recipient_id) as the partition key and timestamp as the clustering key. Fetching the 50 most recent messages in a conversation is: locate the partition (one hop, O(1)) and scan the last 50 rows (sequential disk read). This is fast regardless of whether the table contains a billion rows, because it only touches one partition.

Cassandra achieves high write throughput because writes append to an in-memory buffer (MemTable) and a sequential write-ahead log. No random writes, no immediate index updates. Writes are always fast. Reads can be more expensive - they may need to merge data from the MemTable and several on-disk SSTables.

The constraint that trips people up: the schema must be designed around the queries you intend to run. You cannot query arbitrary columns. If you realize you need a query that does not match your partition key, you need a separate table with a different partition key, maintained via denormalization. The upfront design discipline is significant.

Use wide-column stores for: time-series data (IoT sensor readings, application metrics), activity logs, message history, any high-write workload where the query patterns are known in advance.

Graph Databases: When Relationships Are the Data

In a relational database, a relationship between two entities is an implicit thing - a foreign key you join on. In a graph database (Neo4j, Amazon Neptune, ArangoDB), relationships are first-class objects stored explicitly with their own properties.

Imagine a social network:

(Megha)-[:FOLLOWS {since: "2022-01-01"}]->(Priya)
(Priya)-[:FOLLOWS]->(Rahul)
(Rahul)-[:POSTED]->(Post{content: "hello", timestamp: "2024-01-15"})

The question “show me posts from people Megha follows, who are also followed by more than 100 people, posted in the last 7 days” is a multi-hop traversal. In SQL, this is a recursive CTE or multiple self-joins - possible, but slow at depth and painful to write. In a graph database it is a natural traversal that follows edges.

Graph databases are specialized tools. They do not replace a relational database for your users, orders, and products. They solve specific problems that SQL makes painful: social graphs (mutual friends, six degrees), fraud detection (finding rings of connected fraudulent accounts), recommendation engines (people who liked X also liked Y), and knowledge graph queries.

The operational overhead is real - graph databases are less mature, less commonly deployed, and require more specialized knowledge than Postgres or DynamoDB. Use them when the graph traversal access pattern is genuinely your core use case, not as a novelty.

ACID vs BASE: Two Different Bets

The reliability model of NoSQL databases differs from SQL, and this difference matters more than query language syntax.

ACID (most SQL databases): every write is immediately consistent. Concurrent reads see either the old value or the new value, never a partial state. Durability is guaranteed. The database coordinates locking and commit across nodes. The cost is coordination overhead - slower writes, harder to partition across geography.

BASE (many NoSQL systems): Basically Available, Soft state, Eventually consistent. Writes are accepted immediately on any available replica and propagated asynchronously. For a brief window after a write, different replicas may return different values. Eventually, all replicas converge.

Cassandra, DynamoDB (with eventual consistency reads), and Couchbase operate BASE by default. This is not a flaw - it is a deliberate trade-off for write availability and horizontal scale across geography. Whether it is acceptable depends entirely on the use case.

“Did this page view counter increment?” - losing a few events in a partial failure is acceptable. Eventual consistency is fine.

“Was this $50,000 wire transfer safely deducted from the sender?” - reading stale data that says the balance is higher than it is would allow an overdraft. Not fine.

The rule: use BASE systems for data where brief inconsistency is tolerable. Use ACID systems for data where correctness is non-negotiable.

The Decision Framework

The right answer almost always starts with: what are the access patterns?

A common trap is picking a NoSQL database because you assume SQL “will not scale,” then discovering that what you actually needed was a better index and a read replica. Another common trap is picking a NoSQL database for its flexibility, then spending six months manually implementing the joins and transactions that SQL would have given you for free.

The technology you choose shapes the problems you have. Start conservative. Move to specialized tools when the specific problem they solve is the problem you actually have.

Read Next:

• Transactions - Atomicity Across Services, Two Ways
• Sharding & Replication - Splitting Data So No Single Machine Drowns