SQL vs NoSQL Database Guide | MySQL, PostgreSQL, MongoDB & Redis Compared

Why Database Choice Matters

Your database decision affects everything downstream: query flexibility, scaling strategy, operational complexity, and cost. The wrong choice is expensive to undo.

Wrong choice:          Right choice:
- Slow queries          - Fast queries at scale
- Rigid schema          - Schema fits your data
- Hard to scale         - Scale when needed
- Migration costs later - Build on solid foundation

---

SQL Databases

SQL (Structured Query Language) databases organize data into tables with fixed schemas and support complex relationships between tables via foreign keys.

Core properties:

• ACID transactions — Atomicity, Consistency, Isolation, Durability
• Relational model — joins, foreign keys, referential integrity
• Fixed schema — all rows conform to the same column structure
• Strong consistency — reads always see committed data

MySQL

The world’s most widely deployed open-source RDBMS. Excellent for read-heavy web applications.

-- Create a users table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert data
INSERT INTO users (name, email) VALUES
    ('Alice', '[email protected]'),
    ('Bob', '[email protected]');

-- Query with join
SELECT users.name, posts.title, posts.created_at
FROM users
INNER JOIN posts ON users.id = posts.user_id
WHERE posts.created_at > '2026-01-01'
ORDER BY posts.created_at DESC
LIMIT 10;

When to use MySQL:

• Web applications with moderate query complexity
• Read-heavy workloads (MySQL’s read performance is excellent)
• When you need a simple, widely-supported SQL database
• WordPress, Drupal, and most PHP applications

PostgreSQL

The most feature-rich open-source SQL database. Handles complex queries, JSON, full-text search, and GIS natively.

-- JSON support — store semi-structured data in a relational table
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    attributes JSONB   -- Binary JSON with indexing support
);

INSERT INTO products (name, attributes) VALUES
    ('Laptop Pro', '{"brand": "Apple", "ram_gb": 16, "storage_gb": 512, "color": "silver"}'),
    ('Laptop Air', '{"brand": "Apple", "ram_gb": 8, "storage_gb": 256, "color": "gold"}');

-- Query inside JSON
SELECT name, attributes->>'brand' AS brand
FROM products
WHERE (attributes->>'ram_gb')::int >= 16;

-- Full-text search
CREATE INDEX idx_products_fts ON products
    USING gin(to_tsvector('english', name));

SELECT name
FROM products
WHERE to_tsvector('english', name) @@ to_tsquery('laptop & pro');

-- Window functions (not supported well in MySQL)
SELECT
    name,
    price,
    AVG(price) OVER (PARTITION BY category) AS category_avg,
    RANK() OVER (ORDER BY price DESC) AS price_rank
FROM products;

When to use PostgreSQL:

• Complex queries, analytics, aggregations
• Need JSON storage alongside relational data
• Full-text search, geospatial queries (PostGIS)
• High-integrity financial or transactional data
• When you want the most SQL-standard-compliant database

---

NoSQL Databases

NoSQL databases trade rigid schemas and complex queries for flexible data models and horizontal scalability.

Types:

Type	Example	Best for
Document	MongoDB	JSON-like objects with nested fields
Key-Value	Redis	Caching, sessions, counters
Column-family	Cassandra	Time-series, write-heavy scale
Graph	Neo4j	Social networks, recommendation engines

MongoDB (Document Store)

Stores data as JSON documents — no schema required. Each document can have different fields.

// Insert a document
db.users.insertOne({
  name: "Alice",
  email: "[email protected]",
  age: 30,
  interests: ["coding", "music"],   // Arrays are first-class
  address: {
    city: "San Francisco",          // Nested objects
    country: "USA"
  }
});

// Query with filters
db.users.find({
  age: { $gte: 25 },
  interests: "coding"               // Match array element
});

// Query nested fields
db.users.find({ "address.city": "San Francisco" });

// Aggregation pipeline
db.orders.aggregate([
  { $match: { status: "completed", year: 2026 } },
  { $group: {
      _id: "$customer_id",
      total: { $sum: "$amount" },
      count: { $sum: 1 }
  }},
  { $sort: { total: -1 } },
  { $limit: 10 }
]);

When to use MongoDB:

• Content management, catalogs, user profiles with varying fields
• Rapid prototyping (no migration needed when you add fields)
• Need to shard horizontally for write scale
• Event logging, time-series data

Limitations:

• Joins are expensive (use $lookup sparingly)
• No ACID across multiple documents in older versions (4.0+ supports multi-document transactions)
• Memory usage higher than SQL for the same data

Redis (Key-Value Store)

An in-memory data store with sub-millisecond latency. Used for caching, sessions, queues, and real-time leaderboards.

# String — simple cache
SET user:42:name "Alice"
GET user:42:name              # "Alice"
SET session:abc123 "user_id=42" EX 3600   # Expire in 1 hour

# Hash — structured object
HSET user:42 name "Alice" email "[email protected]" age 30
HGET user:42 name             # "Alice"
HGETALL user:42               # All fields

# List — queue or recent activity
LPUSH notifications:42 "You have a new message"
LRANGE notifications:42 0 9   # Last 10 notifications

# Sorted set — leaderboard
ZADD leaderboard 2350 "alice"
ZADD leaderboard 1800 "bob"
ZREVRANGE leaderboard 0 9 WITHSCORES  # Top 10 with scores
ZRANK leaderboard "alice"             # Rank (0-indexed)

# Pub/Sub — real-time messaging
PUBLISH chat:room1 "Hello, everyone!"
SUBSCRIBE chat:room1

When to use Redis:

• Session storage (stateless API servers need session storage)
• API response caching (reduce database load)
• Rate limiting (atomic INCR operations)
• Real-time leaderboards, counters
• Message queues (LPUSH/RPOP)
• Pub/Sub for real-time notifications

---

Performance Comparison

Simple lookup by primary key (1M records):

Database	Latency	Notes
Redis	< 0.1ms	In-memory
MongoDB	~1ms	Index on _id
MySQL	~2ms	InnoDB B-tree index
PostgreSQL	~3ms	Slightly more overhead than MySQL

Complex join query (3 tables, 500K rows each):

Database	Latency	Notes
PostgreSQL	~50ms	Best-in-class query planner
MySQL	~80ms	Good but simpler optimizer
MongoDB	~250ms	$lookup is slower than SQL joins

Write throughput (inserts/second):

Database	Inserts/sec	Notes
Redis	500,000+	In-memory, no durability overhead
MongoDB	50,000	Less lock contention than MySQL
MySQL	30,000	ACID overhead
PostgreSQL	25,000	Strictest ACID guarantees

---

Choosing the Right Database

Decision Tree

Do you have structured data with clear relationships?
├─ Yes → SQL
│   ├─ Need JSON/arrays/GIS/full-text search? → PostgreSQL
│   └─ Simple queries, read-heavy web app?    → MySQL
│
└─ No → NoSQL
    ├─ Flexible documents, varying fields?    → MongoDB
    ├─ Caching, sessions, real-time?          → Redis
    ├─ Massive write scale (IoT, logs)?       → Cassandra
    └─ Social graph, recommendations?         → Neo4j

By Use Case

Application	Primary DB	Cache/Secondary
E-commerce	PostgreSQL (orders, inventory)	Redis (cart, sessions)
Social media	MongoDB (posts, comments)	Redis (feed cache, notifications)
Analytics	PostgreSQL (aggregations)	Redis (hot metrics)
Real-time chat	MongoDB (messages)	Redis (online users, Pub/Sub)
CMS	PostgreSQL or MySQL	Redis (page cache)
IoT sensor data	Cassandra or InfluxDB	Redis (last-known values)

---

SQL vs NoSQL: Summary Comparison

	SQL (PostgreSQL/MySQL)	NoSQL (MongoDB)	Cache (Redis)
Schema	Fixed, enforced	Flexible, optional	None
Transactions	Full ACID	Multi-doc ACID (4.0+)	Atomic per key
Joins	Excellent	Expensive ($lookup)	Not applicable
Scaling	Vertical primarily	Horizontal (sharding)	Horizontal
Consistency	Strong	Tunable	Eventual (replication)
Query language	SQL	MongoDB Query Language	Commands
Best for	Relational data, finance	Documents, catalogs	Caching, sessions

---

Hybrid Architecture (Production Pattern)

Most production systems use multiple databases:

Web App
  ├── PostgreSQL  — users, orders, payments (source of truth)
  ├── Redis       — sessions, API cache, rate limiting
  └── Elasticsearch — full-text search across products/content

The rule: use each database for what it does best. Don’t force one database to do everything.

---

Next Steps

• PostgreSQL deep dive: PostgreSQL Performance Tuning Guide
• MySQL optimization: MySQL Query Optimization Guide
• Backend framework: FastAPI Complete Guide