Quick Start: SQL vs NoSQL in 5 Minutes
You’re setting up a new project and someone asks “MySQL or MongoDB?” — that question trips up a lot of engineers early on. Here’s the short version before we get into the details.
SQL databases (PostgreSQL, MySQL, SQLite) store data in tables with fixed schemas. Think of a spreadsheet: columns are defined upfront, every row follows the same structure, and relationships between tables are defined and enforced.
NoSQL databases (MongoDB, Redis, Cassandra, DynamoDB) drop that rigid structure. Documents, key-value pairs, graphs, wide columns — each NoSQL type solves a different specific problem.
Here’s the 30-second cheat sheet:
- SQL: Structured data, complex queries, ACID transactions → e-commerce, banking, CRM
- NoSQL: Flexible schema, massive scale, specific access patterns → social feeds, caches, real-time analytics
The real world is messier than any cheat sheet. Let me walk you through when each one actually wins.
Deep Dive: What Actually Differs
Schema: Rigid vs Flexible
SQL requires you to define your schema before writing a single row. Add a column later? You’re running an ALTER TABLE migration, which can be painful on large tables in production.
-- Schema defined upfront
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
-- Adding a field later requires a migration
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
MongoDB lets you throw in whatever fields you want, per document:
// Each document can have different fields
db.users.insertMany([
{ email: "[email protected]", phone: "+1234567890" },
{ email: "[email protected]", preferences: { theme: "dark" } }
]);
Early in a project, that flexibility is a genuine advantage — requirements shift daily and you don’t want to write migrations every afternoon. The problem surfaces later. Try querying 50 million documents that all have slightly different fields and you’ll quickly understand why schema discipline matters.
Relationships: JOINs vs Embedding
SQL was built for this. Complex relationships between entities — orders, customers, products, invoices — are modeled cleanly with foreign keys and JOINs.
-- Query across related tables
SELECT
u.email,
COUNT(o.id) AS order_count,
SUM(o.total) AS total_spent
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at >= '2024-01-01'
GROUP BY u.email
ORDER BY total_spent DESC;
MongoDB handles this differently. You either embed related data inside documents (fast reads, data duplication) or use references similar to foreign keys — but managed manually. Neither approach is as clean as SQL for complex relational queries.
Scaling: Vertical vs Horizontal
Traditional SQL databases scale vertically — you add more CPU and RAM to the same server. It works well until it doesn’t, and it gets expensive fast.
NoSQL databases were designed from the ground up to scale horizontally: add more servers, shard data across them. That’s how Cassandra handles hundreds of terabytes, or how DynamoDB serves millions of requests per second across AWS infrastructure.
Modern PostgreSQL with read replicas and tools like Citus handles horizontal scaling much better than its reputation suggests. Don’t write off SQL as unscalable — it just scales differently.
ACID vs Eventual Consistency
SQL databases give you ACID guarantees:
- Atomicity: A failed transaction rolls back completely — all or nothing
- Consistency: Data always matches your defined rules
- Isolation: Concurrent transactions don’t interfere with each other
- Durability: Committed data survives crashes
Most NoSQL databases trade some of these guarantees for performance and availability. The technical term is eventual consistency — your data will be consistent, just not immediately. For a shopping cart, that’s fine. For a bank transfer — absolutely not.
Advanced Usage: Real-World Scenarios
When SQL Wins
Financial applications: Bank transfers need atomic transactions. You can’t have money disappear between two accounts because a server crashed mid-write. PostgreSQL with proper transaction isolation is the right call.
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;
-- If anything fails, the whole thing rolls back
Reporting and analytics: Complex aggregations across multiple tables, window functions, CTEs — SQL has a 40-year head start here. BI tools like Metabase and Grafana natively speak SQL, so you’ll save significant integration effort.
Mature products with stable schemas: Once your data model settles down, SQL’s rigidity becomes an asset. Your schema documents your domain model and prevents bad data from slipping in at the database level.
When NoSQL Wins
Content management and catalogs: Product catalogs where each item has wildly different attributes fit MongoDB’s document model naturally. A TV has resolution and panel type; a book has ISBN and page count. Modeling this in SQL means nullable columns everywhere or complex EAV patterns.
// Products with completely different structures — works fine in MongoDB
db.products.insertMany([
{
type: "tv",
brand: "Sony",
resolution: "4K",
panel: "OLED",
size_inches: 55
},
{
type: "book",
title: "Clean Code",
author: "Robert Martin",
isbn: "978-0132350884",
pages: 431
}
]);
Caching and sessions: Redis is the standard here. Sub-millisecond reads, TTL expiry, data structures like lists and sorted sets. Running Redis as a caching layer in front of PostgreSQL is one of the most common production setups I’ve worked with.
# Cache a user session with 24h expiry
redis-cli SET "session:abc123" '{"user_id": 42, "role": "admin"}' EX 86400
# Retrieve it
redis-cli GET "session:abc123"
High-write real-time workloads: IoT sensor data, event logs, user activity streams — Cassandra and DynamoDB handle millions of writes per second without breaking a sweat. Pushing that volume through MySQL would require significant extra engineering.
Polyglot Persistence: Use Both
The best answer in production is often: use both. Called polyglot persistence, this approach assigns each type of data to the database best suited for it — rather than forcing one tool to do everything.
A typical e-commerce stack might look like:
- PostgreSQL: Orders, payments, inventory — relational, ACID-critical
- MongoDB: Product catalog — flexible schema per category
- Redis: Sessions, carts, rate limiting — speed-critical
- Elasticsearch: Product search — full-text, faceting
More pieces to manage, yes — but nothing is being forced into a role it wasn’t designed for.
Practical Tips
Start with PostgreSQL, Migrate If Needed
My default recommendation for any new project: start with PostgreSQL. It’s reliable, has excellent tooling, supports JSON columns natively (which meaningfully closes the SQL/NoSQL gap), and can handle more load than most projects will ever hit. Add Redis for caching later if needed, or migrate specific hot paths to a NoSQL store when you hit concrete bottlenecks.
Premature database architecture is a real trap. I’ve seen teams spend weeks designing a Cassandra cluster for an app that never got more than 100 users.
Map Your Access Patterns Before Choosing
Before committing to a database, write down your top 5 query patterns. If they’re all “get document by ID” or “get all items for user X,” a document database makes sense. If they’re aggregations, reports, or multi-table joins, stay in SQL. Access patterns drive the decision more than anything else.
Watch Your Data Formats During Migration
Format conversion comes up constantly when moving between database systems — CSV exports from PostgreSQL feeding into MongoDB, for example. When I need to quickly convert CSV to JSON for data imports, I use toolcraft.app/en/tools/data/csv-to-json. It runs entirely in the browser, so no data ever leaves your machine — which matters a lot when you’re handling customer records.
Don’t Treat NoSQL as Permanently Schema-Free
Just because MongoDB doesn’t enforce a schema doesn’t mean you shouldn’t have one. Add validation at the application layer, or use MongoDB’s built-in JSON Schema validation. Uncontrolled schema evolution is how you end up with a collection that’s impossible to query reliably six months later.
// Enforce schema validation at the collection level
db.createCollection("users", {
validator: {
$jsonSchema: {
bsonType: "object",
required: ["email", "created_at"],
properties: {
email: { bsonType: "string" },
created_at: { bsonType: "date" }
}
}
}
});
Prefer Managed Services When You Can
Before self-hosting any database, check what your cloud provider offers. RDS for PostgreSQL/MySQL, DynamoDB, DocumentDB, ElastiCache — these handle backups, failover, and patching for you. Self-hosting a Cassandra cluster is effectively a full-time job. Self-hosting PostgreSQL on a VPS for a small project? Totally reasonable. Know the operational cost before you commit.
There’s no universal right answer here. Pick based on your access patterns and consistency needs. Start with something proven — PostgreSQL handles this well for the majority of apps. Add complexity only when a real, measurable bottleneck forces your hand. The fanciest architecture diagram in the room is usually the wrong one to build.
