Database Choice: SQL vs NoSQL vs Cache

• SQL databases (PostgreSQL, MySQL) : relational stores with fixed schemas, enforced relationships, ACID transactions, and rich queries with joins and aggregations.
• NoSQL databases (MongoDB, DynamoDB, Cassandra) : non-relational stores with flexible schemas, horizontal scale, and predictable key-based access, typically offering tunable or eventual consistency.
• In-memory cache (Redis) : a speed layer holding hot data for microsecond reads, not the durable source of truth.
• Polyglot persistence : deliberately using multiple store types, each for its strengths, within the same system.

What are SQL databases and when should you use them?

SQL databases such as PostgreSQL and MySQL store data in tables with enforced schemas and relationships, and they guarantee ACID properties: atomicity, consistency, isolation, and durability. This makes them the natural home for anything where correctness is non-negotiable and data is relational, like account balances, ledgers, and transfers.

package com.bank.repository;

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

public class SqlTransferRepository {
    @Transactional
    public void transfer(String from, String to, BigDecimal amount) {
        jdbc.update("UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?",
            amount, from, amount);
        jdbc.update("UPDATE accounts SET balance = balance + ? WHERE id = ?",
            amount, to);
    }
}

What are NoSQL databases and when should you use them?

NoSQL covers several shapes. Document stores like MongoDB hold flexible JSON-like documents. Wide-column stores like Cassandra and key-value stores like DynamoDB scale horizontally to enormous write and read volumes with predictable latency, as long as you access data by key. The trade-off is eventual consistency and limited transactional scope.

package com.bank.repository;

import java.util.Map;

public class NoSqlActivityRepository {
    public void logActivity(String accountId, String event) {
        activityTable.put(Map.of(
            "pk", accountId,
            "sk", Instant.now().toString(),
            "event", event,
            "device", "mobile"));
    }
}

What is a cache and when should you use it?

A cache like Redis holds hot data in memory for microsecond reads, used in front of the durable store for frequently accessed values like balances or session data. It is not the source of truth and must tolerate being cleared.

package com.bank.cache;

import java.math.BigDecimal;

public class BalanceCache {
    public BigDecimal getBalance(String accountId) {
        BigDecimal cached = redis.get("balance:" + accountId);
        if (cached != null) return cached;
        BigDecimal fresh = database.loadBalance(accountId);
        redis.setex("balance:" + accountId, 60, fresh.toString());
        return fresh;
    }
}

How do you compare SQL, NoSQL, and Cache?

| Aspect | SQL | NoSQL | Cache |
|---|---|---|---|
| Consistency | Strong (ACID) | Tunable/Eventual | Not durable |
| Schema | Fixed, enforced | Flexible | Key-value |
| Scale writes | Vertical (shard for more) | Horizontal | Horizontal |
| Query power | Joins, aggregations | Key-based lookups | Key-based lookups |
| Best for | Ledger, transfers | Activity logs, sessions | Hot reads, balances |

What is polyglot persistence?

Polyglot persistence means deliberately using multiple stores for their strengths. For example: PostgreSQL for the ledger, Cassandra for the activity feed, and Redis for balances. Each added store is more operational complexity, so each one must earn its place.

package com.bank.design;

public class PolyglotPersistence {
    // PostgreSQL: ledger, accounts (ACID, joins, reports)
    // DynamoDB: activity events (scale, key-based, flexible)
    // Redis: balance cache (speed, TTL, cache-aside)
    // S3: archived statements (cheap, durable, cold)
}

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 to very high volume, when the schema varies or evolves fast, and when access is predominantly by a known key. Add a cache when data is hot and read often.

What are common mistakes?

The most common mistake is choosing NoSQL for everything because it is trendy, then struggling with the lack of transactions and joins when they are needed. Another mistake is not adding a cache when the read-to-write ratio is very high, forcing the database to serve every read. A third mistake is adding too many stores without justifying the operational complexity.

What should a senior engineer know?

A senior engineer knows that NewSQL databases now blur the line by offering ACID guarantees at horizontal scale (CockroachDB, TiDB). They justify each datastore choice by the specific workload rather than by dogma, and they are honest about the operational cost of every additional store in the system.