SQL vs NoSQL: When to Use Which

• SQL databases : relational stores with fixed schemas, enforced relationships, and ACID transactions, optimized for correctness and complex queries.
• NoSQL databases : a family of non-relational stores (document, key-value, wide-column, graph) with flexible schemas, optimized for horizontal scale and specific access patterns.
• ACID : atomicity, consistency, isolation, durability : guarantees that a group of changes either all commit or all roll back.
• Polyglot persistence : using SQL for the transactional core and NoSQL for scale-out workloads within the same system.
• NewSQL : databases that offer ACID guarantees at horizontal scale (CockroachDB, TiDB).

When should you use SQL?

SQL databases store data in tables with a fixed schema and enforce relationships through foreign keys. They provide ACID transactions, so a group of changes either all commit or all roll back. They support powerful queries with joins, aggregations, and ad-hoc filtering.

package com.bank.repository;

import java.math.BigDecimal;
import org.springframework.transaction.annotation.Transactional;

public class SqlTransferService {
    @Transactional
    public void transferWithAudit(String from, String to, BigDecimal amount) {
        jdbc.update("UPDATE accounts SET balance = balance - ? WHERE id = ?", amount, from);
        jdbc.update("UPDATE accounts SET balance = balance + ? WHERE id = ?", amount, to);
        jdbc.update("INSERT INTO audit(from_id,to_id,amount) VALUES (?,?,?)", from, to, amount);
    }
}

When should you use NoSQL?

NoSQL covers several shapes. Document stores like MongoDB hold flexible JSON documents, good when records vary. Key-value stores like DynamoDB give fast access by key. Wide-column stores like Cassandra scale writes massively. Graph stores like Neo4j model richly connected data like fraud networks.

package com.bank.repository;

import java.util.Map;
import java.util.List;

public class NoSqlCustomerProfile {
    public void save(String customerId) {
        customerDoc.put("_id", customerId);
        customerDoc.put("profile", Map.of("name", "Rajesh", "tier", "gold"));
        customerDoc.put("recentLogins", List.of("2026-11-01", "2026-10-31"));
        // No joins needed: everything in one document
    }
}

How do you compare them?

| Aspect | SQL | NoSQL |
|---|---|---|
| Schema | Fixed, enforced | Flexible, schema-on-read |
| Transactions | ACID | Limited or eventual |
| Scaling | Vertical (shard for more) | Horizontal natively |
| Queries | Joins, aggregations, ad-hoc | Key-based lookups |
| Best for | Ledger, transfers, reports | Activity logs, sessions, profiles |

What is the common principle for NoSQL data modeling?

The common principle is designing the data model around the queries you will run, denormalizing so a read is a single lookup rather than a join. This is the opposite of SQL normalization and requires knowing your access patterns up front.

What are the decision criteria?

Choose SQL when you need strict ACID transactions, when data is highly relational with many joins, when the schema is well-defined and stable, and when you need flexible ad-hoc queries. Choose NoSQL when you need horizontal scale, when the schema varies or evolves fast, and when access is predominantly by a known key.

package com.bank.design;

public class DatabaseDecision {
    public String choose(String workload) {
        return switch (workload) {
            case "account_ledger" -> "SQL (ACID, joins, audit)";
            case "activity_events" -> "NoSQL/DynamoDB (scale, key-based)";
            case "fraud_network" -> "Graph/Neo4j (connected data)";
            case "user_sessions" -> "Redis/key-value (speed, TTL)";
            default -> "Evaluate per workload";
        };
    }
}

What is polyglot persistence?

Modern systems rarely use one store for everything. Polyglot persistence means using SQL for the transactional core and NoSQL for scale-out workloads. At National Bank of India, the account ledger is in PostgreSQL, activity events in DynamoDB, and balances cached in Redis.

What are common mistakes?

Using NoSQL for everything because it is trendy, then struggling with lack of transactions and joins. Using SQL for everything including massive append-only logs that would work better in a wide-column store. Not evaluating the actual access patterns before choosing.

What should a senior engineer know?

A senior engineer knows about NewSQL databases like CockroachDB and TiDB that offer ACID at horizontal scale, blurring the line. They justify every datastore choice by the specific access pattern, consistency requirement, and scale of the workload, not by dogma.