SQL vs NoSQL: Choosing Between PostgreSQL, MySQL, MongoDB and Redis

By 0xmote · Aug 5, 2026 · 12 min read · Updated Sep 25, 2026 Intermediate comparison

Key takeaways

SQL or NoSQL? The answer depends on your data shape, query patterns, and scale requirements. This guide breaks down four major databases — MySQL, PostgreSQL, MongoDB, and Redis — with concrete decision criteria.

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, because the data model leaks into application code. Moving from MongoDB to PostgreSQL is not a driver swap; every query, every embedded document and every assumption about what can be updated atomically has to be revisited.

The framing “SQL vs NoSQL” is also less useful than it sounds. PostgreSQL stores and indexes JSON documents, MongoDB has multi-document transactions and schema validation, and Redis can persist to disk. The real questions are narrower: what does the data look like, which queries must be fast, which invariants must never be violated, and how much operational work the team can take on.

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 on the primary see committed data (reads from asynchronous replicas can lag behind)

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 (assumes price and category columns; MySQL supports these since 8.0)
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

The JSONB example shows why “I need flexible fields” is not by itself a reason to leave SQL. Stable, frequently queried attributes go in columns with types and constraints; attributes that vary per product go in a jsonb column, which can be indexed with GIN (CREATE INDEX ... USING gin (attributes)) for containment queries like attributes @> '{"brand": "Apple"}'. The catch is that the query above, (attributes->>'ram_gb')::int >= 16, cannot use that GIN index; range filters on a JSON field need an expression index on exactly that expression. When a JSON field ends up in most WHERE clauses, it has earned promotion to a real column.

The practical differences between MySQL and PostgreSQL are smaller than they were a decade ago; MySQL 8.0 added window functions, CTEs and a JSON type. The differences that still bite are behavioural: PostgreSQL has transactional DDL (a failed migration rolls back cleanly), stricter type handling, and MVCC that needs VACUUM; MySQL’s InnoDB clusters rows by primary key, which makes random UUID primary keys noticeably worse for insert locality than sequential ones. The PostgreSQL vs MySQL comparison goes through these in detail.

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 on replica sets, 4.2+ on sharded clusters, with extra latency and a default 60-second transaction lifetime)
Field names are stored in every document, so storage per record is often larger than an equivalent table row (compression reduces but does not remove this)
A single document is limited to 16 MB, which matters for designs that keep appending to an embedded array

“No migration needed” is the most misunderstood selling point. The database accepts documents without a schema, but the application still has one: code written after a field was added has to cope with older documents that lack it, or that store it with a different type. Without a migration, that knowledge lives in scattered if (doc.field === undefined) checks. In practice, MongoDB projects that stay healthy add a $jsonSchema validator on each collection and still run backfill scripts when the shape changes. The schema work is moved, not removed.

The design rule that matters most in MongoDB is to model around the queries. Data that is read together should be stored together (embed the address in the user), and data that grows without bound or is shared by many parents should be referenced (don’t embed every comment ever written into a post). The MongoDB schema design post covers the embed-vs-reference decision.

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

Two of these uses come with caveats that are easy to miss. Pub/Sub is fire-and-forget: a message published while a subscriber is disconnected is simply gone, and nothing is stored for later. For notifications that must arrive, use Redis Streams (XADD/XREADGROUP), which keep messages and track acknowledgements per consumer group. A list used as a queue with RPOP loses a job if the worker crashes after popping and before finishing; LMOVE into a per-worker “processing” list, or Streams again, avoid that.

The durability claim also needs precision. By default Redis writes periodic RDB snapshots, so a crash can lose the last few minutes of writes. With AOF and appendfsync everysec the window shrinks to about a second. That is fine for a cache or a session store, where the source of truth lives elsewhere, and a real problem if Redis is the only place an order or a balance is recorded. The other constraint is memory: once the dataset hits maxmemory, Redis either evicts keys (good for a cache, silent data loss for anything else) or rejects writes with OOM command not allowed when used memory > 'maxmemory', depending on the eviction policy.

Performance: What Actually Differs

Published latency and throughput tables for these databases vary by an order of magnitude depending on hardware, configuration, durability settings and data shape, so treat any single set of numbers, including ones you see in blog posts, with suspicion. What holds in general:

Primary-key lookups are fast on all four when the working set fits in memory. Redis avoids disk and query parsing entirely, so it is the fastest for a single key, but the gap to an indexed lookup in PostgreSQL or MySQL on a warm cache is usually not what limits an application; network round trips and N+1 query patterns dominate.
Multi-table joins favour relational databases, which have cost-based planners, join algorithms (hash, merge, nested loop) and statistics built for them. MongoDB’s $lookup works but is not designed as the main way to combine data; if most queries need it, the data is probably relational.
Write throughput depends mostly on durability settings. A database that acknowledges writes before they reach disk (Redis without AOF, MongoDB with w: 1 and no journal wait) looks faster than one that waits for fsync on commit. Compare at equal durability, or you are comparing configurations, not databases.

The only benchmark that answers your question is one with your own schema, queries and data volume. A day spent loading realistic data into two candidates and running the ten most important queries tells you more than any table.

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

When in doubt, the default I would pick is PostgreSQL, and add a specialised store only when a concrete need appears: Redis when a hot read path needs a cache, a search engine when LIKE '%term%' stops being good enough. The reasoning is asymmetric risk. Starting relational and adding a document store later is a contained change; starting with a document store and discovering that the data is relational means reimplementing joins, foreign keys and multi-entity transactions in application code, which is where the subtle consistency bugs come from.

The case for starting with MongoDB is real but narrower: documents that are always read and written as a whole, fields that vary genuinely per record (not just “we haven’t decided yet”), and a write volume that needs sharding from the beginning.

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 + read replicas (sharding via Citus/Vitess)	Horizontal (built-in sharding)	Horizontal (Redis Cluster)
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. The opposite mistake is just as common: every added store is another thing to back up, monitor, upgrade and keep consistent. The hard part of this architecture is not running three databases but keeping them in sync. Writing to PostgreSQL and then to Elasticsearch in the same request fails halfway sooner or later, leaving search results that point at deleted rows. The robust pattern is to treat PostgreSQL as the source of truth and feed the others asynchronously (an outbox table or change data capture), accepting that the cache and the search index are slightly behind.

Next Steps

PostgreSQL deep dive: PostgreSQL Query Optimization Guide
MySQL optimization: MySQL Query Optimization Guide
Backend framework: FastAPI in Production

Frequently Asked Questions (FAQ)

Q. Should I pick MongoDB because its inserts look faster than PostgreSQL?

A. Raw write throughput is only one axis, and insert benchmarks often compare different durability settings. If your data has clear relationships and needs multi-table joins, MongoDB’s $lookup is a poor substitute for a SQL join, and you give up the enforced schema that protects data like orders and payments. The decision tree in this article starts from the shape of your data, not from insert speed, and many production systems keep PostgreSQL as the source of truth while adding Redis for hot paths.