NoSQL vs SQL: An Honest Decision Framework

The Answer Is Almost Always "Start With PostgreSQL"

The NoSQL vs SQL debate has produced more bad architectural decisions than any other topic in software engineering. Teams choose MongoDB because "it's flexible," then spend months building application-level joins and data validation that PostgreSQL gives you for free. Other teams stick with PostgreSQL for everything, then struggle when they need sub-millisecond key-value lookups or need to model deeply connected graph data.

After building systems with PostgreSQL, MongoDB, Redis, DynamoDB, Cassandra, and Neo4j, I've developed a simple framework: start with PostgreSQL, and add specialized databases only when you hit a specific limitation that PostgreSQL can't solve. This guide covers the real trade-offs -- not the marketing pitches -- so you can make that call with confidence.

What Is the Difference Between SQL and NoSQL?

Definition: SQL databases (relational) store data in structured tables with predefined schemas, enforce relationships via foreign keys, and provide ACID transactions with a powerful query language (SQL). NoSQL databases use flexible data models -- documents, key-value pairs, wide columns, or graphs -- and typically trade schema enforcement or strong consistency for scalability, flexibility, or specialized query patterns.

NoSQL Database Types at a Glance

Type	Examples	Data Model	Best For	Worst For
Document	MongoDB, CouchDB	JSON-like documents	Content management, catalogs, user profiles	Complex relationships, reporting
Key-Value	Redis, DynamoDB, Memcached	Key mapped to value	Caching, sessions, real-time data	Complex queries, relationships
Wide-Column	Cassandra, ScyllaDB, HBase	Rows with dynamic columns	Write-heavy, time-series at massive scale	Ad-hoc queries, transactions
Graph	Neo4j, Amazon Neptune	Nodes and edges	Social networks, fraud detection, recommendations	Bulk analytics, simple CRUD

SQL (Relational) Databases: What You Actually Get

Relational databases aren't just "tables with rows." They provide a set of guarantees that are extremely expensive to replicate in application code:

• Schema enforcement -- the database rejects invalid data. No null emails, no negative prices, no orphaned records.
• ACID transactions -- multi-step operations are atomic. Transfer money between accounts without worrying about partial failures.
• Joins -- combine data from multiple tables in a single query. No need to fetch users, then fetch their orders, then merge in your application.
• Constraints -- unique, foreign key, check constraints enforced at the database level. Two users can't have the same email, period.
• SQL -- a mature, standardized query language that supports filtering, aggregation, window functions, CTEs, and subqueries.

-- SQL handles complex queries that would take dozens of lines in application code
SELECT
    u.name,
    COUNT(o.id) AS order_count,
    SUM(o.total) AS total_spent,
    RANK() OVER (ORDER BY SUM(o.total) DESC) AS spending_rank
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at >= '2025-01-01'
GROUP BY u.id, u.name
HAVING SUM(o.total) > 100
ORDER BY total_spent DESC
LIMIT 20;

Document Databases: MongoDB

MongoDB stores data as JSON-like documents (BSON). Each document can have a different structure -- no schema migrations needed. This is genuinely useful for content management systems, product catalogs with varying attributes, and rapid prototyping.

// MongoDB: flexible document structure
db.products.insertOne({
  name: "Wireless Headphones",
  price: 79.99,
  category: "electronics",
  specs: {
    bluetooth: "5.3",
    battery_hours: 40,
    noise_cancelling: true,
    drivers: "40mm"
  },
  reviews: [
    { user: "alice", rating: 5, text: "Great sound quality" },
    { user: "bob", rating: 4, text: "Good battery life" }
  ]
});

// Query nested documents
db.products.find({
  "specs.noise_cancelling": true,
  price: { $lt: 100 }
});

// Aggregation pipeline (MongoDB's answer to SQL GROUP BY)
db.orders.aggregate([
  { $match: { created_at: { $gte: ISODate("2025-01-01") } } },
  { $group: { _id: "$user_id", total: { $sum: "$amount" }, count: { $sum: 1 } } },
  { $sort: { total: -1 } },
  { $limit: 20 }
]);

Watch out: MongoDB's "schemaless" advantage often becomes a liability. Without schema enforcement, you'll find documents with typos in field names, inconsistent types (string "42" vs integer 42), and missing required fields. MongoDB supports JSON Schema validation since 3.6, but most teams don't use it. If you end up adding validation rules, you're effectively recreating a schema -- but with less tooling and no migration framework.

Where MongoDB genuinely wins: when your data has highly variable structure (different product types with different attributes), when you need to store and query deeply nested documents, or when your team needs to iterate on the data model faster than schema migrations allow.

Key-Value Databases: Redis and DynamoDB

Key-value databases do one thing: map a key to a value. The value can be a string, a JSON blob, or a binary object. Lookups by key are O(1) and extremely fast.

Redis

In-memory, sub-millisecond latency. Perfect for caching, sessions, rate limiting, and real-time leaderboards. Not durable by default -- use it alongside a primary database, not instead of one.

DynamoDB

AWS's managed key-value/document store with single-digit millisecond latency at any scale. Pricing is per read/write capacity unit. It's excellent for high-scale workloads with predictable access patterns, but its query model is restrictive -- you must design your tables around your access patterns upfront.

# DynamoDB: design for access patterns, not normalization
import boto3

dynamodb = boto3.resource('dynamodb')
table = dynamodb.Table('Orders')

# Single-table design: partition key + sort key
table.put_item(Item={
    'PK': 'USER#alice',
    'SK': 'ORDER#2025-04-07#001',
    'total': 99.99,
    'status': 'shipped',
    'items': [{'sku': 'WH-100', 'qty': 1}]
})

# Query all orders for a user (efficient -- same partition)
response = table.query(
    KeyConditionExpression='PK = :pk AND begins_with(SK, :sk)',
    ExpressionAttributeValues={':pk': 'USER#alice', ':sk': 'ORDER#'}
)

# Query across users? You need a Global Secondary Index
# or a full table scan -- this is DynamoDB's biggest limitation

Wide-Column Databases: Cassandra

Cassandra is designed for write-heavy workloads at massive scale -- think billions of rows across hundreds of nodes with no single point of failure. It uses eventual consistency by default and distributes data automatically.

Cassandra excels at: IoT data ingestion, time-series at massive scale, any workload where write throughput is the primary constraint and you can tolerate eventual consistency. It's a poor choice for: ad-hoc queries (no JOINs, limited WHERE clauses), transactions, or any workload where you need to query data in ways you didn't design for upfront.

Graph Databases: Neo4j

Graph databases model data as nodes (entities) and edges (relationships). Traversing relationships is a first-class operation, making queries like "friends of friends who bought product X" orders of magnitude faster than the equivalent SQL with multiple JOINs.

// Neo4j Cypher: find friends of friends who bought a product
MATCH (me:User {name: "Alice"})-[:FRIEND]->(friend)-[:FRIEND]->(fof)-[:PURCHASED]->(p:Product)
WHERE p.category = "electronics"
AND NOT (me)-[:PURCHASED]->(p)
RETURN p.name, COUNT(fof) AS recommendations
ORDER BY recommendations DESC
LIMIT 10;

This query would require 3-4 self-JOINs in SQL and degrade rapidly with depth. In a graph database, traversal depth has minimal impact on performance. Use Neo4j for social networks, recommendation engines, fraud detection (tracing transaction chains), and knowledge graphs. Don't use it for bulk analytics, simple CRUD, or any workload that doesn't heavily exploit relationships.

The Real Trade-offs Nobody Talks About

Schema Flexibility vs Data Integrity

MongoDB lets you store anything. PostgreSQL rejects invalid data. "Flexibility" sounds great until you're debugging why 0.3% of your user documents have email spelled as emial and your login flow silently fails for those users. Schema enforcement costs more upfront but saves enormously in production debugging.

Eventual vs Strong Consistency

DynamoDB and Cassandra default to eventual consistency -- a read immediately after a write might return stale data. This is fine for social media feeds. It's catastrophic for inventory management ("sorry, we sold the same item twice"). PostgreSQL gives you strong consistency by default. You can add eventual consistency (read replicas) when you need it, but you start safe.

Horizontal Scaling vs Operational Simplicity

Cassandra scales horizontally to thousands of nodes. PostgreSQL scales vertically to machines with 128 cores and 1 TB of RAM, plus read replicas. For 95% of applications, the vertical scaling ceiling is never reached. The 5% that need horizontal write scaling should consider distributed SQL (CockroachDB, YugabyteDB) before jumping to NoSQL.

Database Hosting: Cost Comparison

Database	Managed Service	Starting Price/mo	Free Tier
PostgreSQL	Supabase / Neon / RDS	$0 / $19 / $190	Yes (Supabase, Neon)
MongoDB	MongoDB Atlas	$0 (shared) / $57 (dedicated)	Yes (512 MB)
Redis	Redis Cloud / Upstash	$0 / $5	Yes
DynamoDB	AWS DynamoDB	Pay-per-use	Yes (25 WCU/RCU)
Cassandra	DataStax Astra	$0 / $25	Yes (limited)
Neo4j	Neo4j Aura	$0 / $65	Yes (limited)

Decision Framework: SQL or NoSQL?

1. Default to PostgreSQL. It handles JSONB (document-like queries), full-text search, key-value patterns (with hstore/JSONB), and even graph queries (recursive CTEs) adequately for most workloads.
2. Add Redis when you need sub-millisecond caching, sessions, rate limiting, or real-time leaderboards. Redis complements PostgreSQL; it doesn't replace it.
3. Consider MongoDB when your data has genuinely variable structure that changes frequently -- content management with wildly different content types, for example. Not just because "we don't want to write migrations."
4. Consider DynamoDB when you're on AWS, have predictable access patterns, and need single-digit millisecond latency at any scale. Accept the query limitations upfront.
5. Consider Cassandra when write throughput exceeds what a single server handles (hundreds of thousands of writes per second) and eventual consistency is acceptable.
6. Consider Neo4j when relationship traversal is your core query pattern -- recommendations, fraud detection, social graphs. Not for general-purpose storage.
7. Consider distributed SQL (CockroachDB, YugabyteDB) when you need horizontal scaling with SQL semantics. This is often a better choice than NoSQL for teams that want scale without giving up consistency.

Frequently Asked Questions

Should I use MongoDB or PostgreSQL for a new project?

Start with PostgreSQL. It handles structured data, JSONB for semi-structured data, full-text search, and scales vertically to handle most workloads. MongoDB makes sense when your data has genuinely variable structure that changes frequently, or when your team has deep MongoDB expertise. For most web applications, APIs, and SaaS products, PostgreSQL is the safer and more capable choice.

Is NoSQL faster than SQL?

Not inherently. Redis is faster than PostgreSQL for key-value lookups because it's in-memory. But PostgreSQL with proper indexes handles point queries in under a millisecond. NoSQL databases optimize for specific access patterns; if your queries match those patterns, they're faster. If they don't, they can be much slower -- Cassandra with an unindexed query on a non-partition-key column, for example.

Can PostgreSQL replace MongoDB?

For most use cases, yes. PostgreSQL's JSONB type supports document-style storage with indexing (GIN), nested queries, and schema validation via CHECK constraints. You lose MongoDB's flexible schema (though JSONB is pretty flexible) and its built-in horizontal sharding. You gain ACID transactions, JOINs, and the entire PostgreSQL ecosystem.

When is DynamoDB the right choice over PostgreSQL?

DynamoDB excels when you need single-digit millisecond latency at any scale, have well-defined access patterns that won't change, and are heavily invested in AWS. It handles millions of requests per second without capacity planning. It's the wrong choice when you need ad-hoc queries, complex filtering, or relationships between entities.

What does "eventual consistency" mean in practice?

Eventual consistency means that after a write, not all readers immediately see the new value. A user updates their profile, but another user viewing that profile might see the old version for a few hundred milliseconds. For social feeds and content sites, this is imperceptible. For bank balances and inventory counts, it's a data integrity risk. PostgreSQL provides strong consistency by default.

Is Redis a database or a cache?

Both. Redis stores data in memory with optional persistence (RDB snapshots and AOF logs). It can serve as a primary datastore for ephemeral data (sessions, rate limits, real-time leaderboards) and as a cache for data sourced from another database. Don't use it as your only database for important data -- memory is volatile, and its persistence guarantees are weaker than PostgreSQL's.

What is the CAP theorem and how does it affect my database choice?

The CAP theorem states that a distributed system can provide at most two of three guarantees: Consistency (all nodes see the same data), Availability (every request gets a response), and Partition tolerance (the system works despite network failures). PostgreSQL prioritizes consistency. Cassandra and DynamoDB prioritize availability. In practice, all modern databases handle network partitions, so the real trade-off is between consistency and availability during failures.