Database Tutorial #1: SQL vs NoSQL — When to Use What

You are building a new app. You need to store data. Now comes the question: should I use SQL or NoSQL?

This is one of the most common decisions in backend development. The wrong choice can hurt performance, scalability, and developer experience. The right choice makes everything simpler.

This article explains both options clearly so you can make the right call.

What Is SQL?

SQL databases are relational databases. Data is stored in tables. Tables have rows and columns. Every row has the same structure, defined by the table schema.

Example — a users table:

You query this data with SQL (Structured Query Language):

SELECT name, email FROM users WHERE id = 1;

Popular SQL databases:

• PostgreSQL — open source, feature-rich, most popular
• MySQL / MariaDB — widely used, good for read-heavy workloads
• SQLite — embedded, no server needed
• SQL Server — Microsoft’s enterprise database

What Is NoSQL?

NoSQL databases do not use tables and rows. They store data in different formats. There are four main types:

1. Document databases — store JSON-like documents. Each document can have a different structure.

Example (MongoDB):

{
  "_id": "64abc123",
  "name": "Alex",
  "email": "alex@example.com",
  "preferences": {
    "theme": "dark",
    "language": "en"
  }
}

2. Key-value stores — store a value under a key. Very simple, very fast.

Example (Redis):

SET user:1:name "Alex"
GET user:1:name  → "Alex"

3. Column-family databases — store data in column groups. Good for time-series data. Example: Apache Cassandra, Google Bigtable.

4. Graph databases — store nodes and relationships. Good for social networks. Example: Neo4j, Amazon Neptune.

ACID vs BASE

SQL databases are ACID:

• Atomicity — a transaction either fully completes or fully rolls back
• Consistency — data always follows schema rules
• Isolation — concurrent transactions do not interfere with each other
• Durability — committed data survives crashes

NoSQL databases often use BASE:

• Basically Available — the system is usually available
• Soft state — data may be in flux
• Eventual consistency — data will be consistent eventually

ACID is stricter. BASE gives up some guarantees for speed and scalability.

For financial transactions, you need ACID. For a user activity feed, eventual consistency is fine.

The CAP Theorem

The CAP theorem says that a distributed database can only guarantee two of these three properties:

• Consistency — all nodes return the same data at the same time
• Availability — every request gets a response
• Partition tolerance — the system works even if network splits happen

In a real distributed system, network partitions always happen. So you must choose between CP (consistency + partition tolerance) or AP (availability + partition tolerance).

PostgreSQL is CP. MongoDB can be configured as CP or AP. Redis (standalone) is a single-node store — CAP applies when running Redis Cluster, where it leans toward AP (availability over consistency).

This matters when you design systems that span multiple servers or data centers.

When to Use SQL (PostgreSQL)

Use a relational database when:

You have structured data with relationships. Your data fits into tables. You need joins between tables.

-- Get all orders with customer names
SELECT orders.id, users.name, orders.total
FROM orders
JOIN users ON orders.user_id = users.id
WHERE orders.status = 'pending';

You need ACID transactions. Money transfers, inventory updates, any operation where partial writes would be dangerous.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Your schema is stable. You know your data structure in advance and it does not change often.

Real-world examples:

• E-commerce (orders, products, inventory)
• Banking and financial apps
• HR systems (employees, salaries, departments)
• SaaS apps with complex reporting

When to Use NoSQL (MongoDB)

Use a document database when:

Your data has a variable structure. Different documents can have different fields.

// User with basic profile
{ "_id": "1", "name": "Alex", "plan": "free" }

// User with detailed profile
{ "_id": "2", "name": "Sam", "plan": "pro", "company": "Acme", "phone": "+1-555-0100" }

You are storing hierarchical or nested data. A blog post with embedded comments. A product with embedded reviews.

You need to scale horizontally. MongoDB shards data across many servers naturally.

Your schema changes frequently. Early-stage startup, rapid prototyping, catalog data.

Real-world examples:

• Product catalogs (each product type has different fields)
• Content management systems
• User-generated content
• Mobile app backends with flexible schemas

When to Use Redis

Use a key-value store when:

Speed is critical. Redis stores data in memory. Reads and writes happen in microseconds.

You need temporary data. Sessions, tokens, rate limiting counters, OTP codes.

You need caching. Store expensive database query results in Redis to avoid hitting PostgreSQL every time.

# Check cache first
cached = redis.get(f"user:{user_id}")
if cached:
    return json.loads(cached)

# Cache miss — query database
user = db.query("SELECT * FROM users WHERE id = ?", user_id)
redis.setex(f"user:{user_id}", 3600, json.dumps(user))  # Cache for 1 hour
return user

Real-world examples:

• Session storage
• Caching database queries
• Rate limiting
• Leaderboards (sorted sets)
• Real-time notifications

Decision Table

Real-World Example: E-commerce App

Here is how a typical e-commerce app uses multiple databases:

┌─────────────────────────────────────────────────┐
│                  E-Commerce App                  │
│                                                  │
│  PostgreSQL (primary)                            │
│  ├── users, orders, payments (ACID needed)       │
│  ├── inventory (need transactions)               │
│                                                  │
│  MongoDB (product catalog)                       │
│  ├── products (varied schemas per category)      │
│  ├── reviews (nested, high volume)               │
│                                                  │
│  Redis (speed layer)                             │
│  ├── sessions (user login state)                 │
│  ├── cart (temporary, fast read/write)           │
│  ├── rate limiting (per-user API limits)         │
└─────────────────────────────────────────────────┘

This is called polyglot persistence — using the right database for each job.

Common Mistakes

1. Using NoSQL because it is “more scalable.” PostgreSQL scales well for most applications. Many companies run PostgreSQL at billions of rows. Do not switch to MongoDB just for scale.

2. Using SQL for everything, even when it does not fit. If you are building a product catalog with 50 different product types, each with different fields, MongoDB may be a better fit.

3. Not using Redis at all. Many slow applications just need a caching layer. Adding Redis in front of your PostgreSQL queries can cut response times by 10-100x.

4. Picking a database and never changing it. In early stages, pick what you know. Optimize later when you understand your data access patterns.

Summary

• PostgreSQL — structured data, relationships, transactions, complex queries
• MongoDB — flexible schemas, nested documents, horizontal write scaling
• Redis — caching, sessions, real-time features, speed
• SQLite — embedded, testing, edge computing

Most production applications use at least two of these.

Related Articles

• PostgreSQL Setup and Basics
• MongoDB Setup and CRUD
• Redis Setup and Data Types

What’s Next?

In the next tutorial, we install PostgreSQL 17 with Docker, connect with psql, and run our first queries.