Database Decision Framework

This document summarizes how to choose a database from a solution architecture perspective, covering:

• SQL vs NoSQL
• MySQL vs PostgreSQL
• PostgreSQL JSONB vs MongoDB
• Oracle vs PostgreSQL / SQL Server
• JSON support across major databases
• Licensing & cost considerations
• Why Oracle is expensive and used in banking

---

1. SQL vs NoSQL – First Decision

Choose SQL when:

• Strong ACID transactions are required
• Complex joins and relationships exist
• Data integrity and constraints matter
• Reporting and analytics are important
• Schema evolution is controlled

Examples:

• Banking systems
• Order & payment systems
• Inventory management

Choose NoSQL when:

• Schema is flexible or evolving fast
• Data is document-oriented or key-value
• Horizontal scalability is a priority
• Eventual consistency is acceptable

Examples:

• User profiles
• Logs and events
• Caching layers

Microservices reduce cross-table joins, but do not eliminate the need for SQL. Each service often still needs strong transactional guarantees.

---

2. SQL vs NoSQL Feature Comparison

NewSQL databases (CockroachDB, TiDB, YugabyteDB) combine SQL semantics with horizontal scalability. Consider them for globally distributed applications requiring strong consistency.

---

3. JSON Support Across SQL Databases

Summary Table

---

4. CAP Theorem & Consistency Models

CAP Theorem

In a distributed system, you can only guarantee two of three:

Database Classification

Tip: For financial/gaming transactions, prefer CP databases with strong consistency.

---

5. PostgreSQL JSONB vs MongoDB

PostgreSQL JSONB

Strengths:

• Strong ACID transactions
• SQL + JSON in one engine
• GIN indexes for JSON
• Referential integrity

Weaknesses:

• Not optimized for massive document writes
• JSON schema misuse can degrade performance

MongoDB

Strengths:

• Native document database
• Horizontal scaling
• Flexible schema
• Developer-friendly

Weaknesses:

• Weaker joins
• Cross-document transactions are costly
• Eventual consistency patterns

Rule of Thumb

• PostgreSQL JSONB → transactional + hybrid data
• MongoDB → document-first, schema-flexible systems

---

6. JSONB Anti-Patterns

Avoid using JSONB when:

• JSON contains frequently joined relational data
• You store highly structured data without reason
• You update large JSON blobs frequently
• You use JSONB instead of normalization

Correct usage:

• Optional fields
• Metadata
• Configurations
• Event payloads

---

7. MySQL vs PostgreSQL – Decision Framework

Choose MySQL when:

• Simple CRUD workloads
• Read-heavy applications
• Operational simplicity is key
• Existing MySQL ecosystem (CMS, LAMP)

Choose PostgreSQL when:

• Complex queries and joins
• High write concurrency
• JSON-heavy workloads
• Microservices
• Data correctness matters

Comparison

If unsure, default to PostgreSQL.

---

8. Oracle vs PostgreSQL / SQL Server

Why choose Oracle?

• Real Application Clusters (RAC)
• Active-active writes
• Zero-downtime failover
• Flashback (time travel queries)
• Built-in auditing and security
• Long-term vendor support

Why banks use Oracle

• Regulatory compliance
• Zero data loss tolerance
• Predictable query execution
• Vendor accountability (SLAs)

---

9. Licensing & Cost

PostgreSQL

• Fully open-source
• Free for production
• No licensing risk

MySQL

• Open-source (GPL v2)
• Free for server-based usage
• Paid only if embedded or redistributed

Oracle

• Commercial license
• Paid per core / feature
• Cost justified by risk reduction

Docker usage:

• PostgreSQL: safe
• MySQL Community: safe
• Oracle: license applies

---

10. Architecture Rules of Thumb

• Core financial systems → Oracle
• Microservices → PostgreSQL
• Simple web apps → MySQL
• JSON + transactions → PostgreSQL JSONB
• Document-first systems → MongoDB
• Caching / Session storage → Redis
• Time-series / Metrics → TimescaleDB, InfluxDB
• Full-text search → Elasticsearch
• Global distribution + SQL → CockroachDB, TiDB

Database choice is about risk, scale, and correctness, not popularity.

Polyglot Persistence

Modern applications often use multiple databases:

┌─────────────────────────────────────────────────────────┐
│                      Application                        │
├──────────┬──────────┬──────────┬──────────┬────────────┤
│PostgreSQL│  Redis   │ MongoDB  │Elasticsearch│TimescaleDB│
│ (OLTP)   │ (Cache)  │ (Docs)   │ (Search)    │ (Metrics) │
└──────────┴──────────┴──────────┴─────────────┴───────────┘

Example for GameKami:

• PostgreSQL: Users, games, transactions, rooms
• Redis: Session cache, real-time game state, pub/sub
• Elasticsearch: Game history search, player stats (future)

---

11. Interview-Ready Summary

Quick Answers

Q: SQL vs NoSQL?

"SQL for transactions, relationships, and data integrity. NoSQL for flexible schemas, horizontal scaling, and document-oriented data. Consider NewSQL (CockroachDB) for distributed SQL."

Q: PostgreSQL vs MySQL?

"PostgreSQL for complex queries, JSON support, and write concurrency. MySQL for simple CRUD, read-heavy workloads, and operational simplicity."

Q: Why do banks use Oracle?

"Oracle provides active-active clustering (RAC), zero data loss recovery, flashback queries, and vendor SLAs—critical for regulatory compliance and risk mitigation."

Q: When to use MongoDB?

"Document-first applications with flexible schemas where horizontal scaling matters more than cross-document transactions."

Q: What is CAP theorem?

"In distributed systems, you can only guarantee two of three: Consistency, Availability, Partition Tolerance. PostgreSQL prioritizes CA, MongoDB is CP, Cassandra is AP."

---

Last updated: February 2026