Choosing a Database — SQL vs NoSQL

#Introduction

"SQL or NoSQL?" isn't a matter of fashion — it depends on the data's shape, the access patterns, the consistency needs and the scale. Most real systems use several kinds of storage, each for what it does best.

#Relational (SQL) Databases

MySQL, PostgreSQL, Oracle, SQL Server — tables with a fixed schema, rows and columns, relationships through foreign keys, and SQL for queries. (See the SQL & MySQL topic.)

Use for: orders, payments, accounts, inventory, bookings — anything relational and transactional. Modern relational databases scale further than many people assume (read replicas, partitioning, very large single instances).

#NoSQL Families

Key-Value Stores

Redis, Amazon DynamoDB (also document-like), Memcached, etcd.

GET  session:9f1c2a7e  →  {"userId": 42, "cart": [...]}
SET  rate:user:42  17  EX 60

Use for: caching, sessions, rate limiting, leaderboards, feature flags, shopping carts.

Document Databases

MongoDB, Couchbase, Firestore, Amazon DocumentDB — JSON-like documents, flexible schema.

{
  "_id": "ORD-1001",
  "customer": { "id": "C-42", "name": "Ravi" },
  "items": [ { "sku": "SKU-1", "qty": 2, "price": 499 } ],
  "status": "PAID"
}

Use for: product catalogues, content management, user profiles, event data with varying fields.

Wide-Column Stores

Apache Cassandra, ScyllaDB, HBase, Google Bigtable — rows identified by a partition key, with many columns, spread across many nodes.

Use for: time-series and event logs, messaging history, IoT data, activity feeds — huge write volumes queried by key.

Graph Databases

Neo4j, Amazon Neptune — nodes and relationships as first-class data.

(Ravi)-[:FOLLOWS]->(Asha)-[:FOLLOWS]->(Kiran)
MATCH (me)-[:FOLLOWS]->()-[:FOLLOWS]->(suggestion) RETURN suggestion

Use for: social graphs, recommendations, fraud rings, network and dependency analysis — queries that traverse many relationships, which are slow as SQL joins.

#Other Specialised Stores

#SQL vs NoSQL

#How to Choose

Questions to ask:

1. What are the main queries? (Design storage around them.)
2. What's the read/write ratio and the expected data size?
3. How consistent must the data be? Can a read be slightly stale?
4. Do you need transactions across entities?
5. How will the schema evolve?
6. What does the team know how to operate?

#Polyglot Persistence

Large systems use the right store per job — often one per microservice:

The cost: more systems to run, secure, back up and keep in sync (usually through events or CDC).

#Common Misconceptions

• "NoSQL is always faster." — Only for the access patterns it's designed for; ad-hoc queries can be much slower.
• "SQL doesn't scale." — Relational databases handle very large workloads with replicas, partitioning and sharding (Vitess, Citus).
• "NoSQL means no schema." — The schema still exists; it's just enforced by your code instead of the database.
• "Pick one database for everything." — Different data has different needs.

#Key Takeaways

• Relational databases: ACID, joins, constraints — the default for transactional data.
• Key-value for speed, document for flexible objects, wide-column for huge write volumes, graph for relationships.
• Search engines, time-series stores, object storage and Kafka fill specialised roles.
• Choose by access patterns, consistency, scale and team expertise.
• Polyglot persistence is normal — and adds operational cost.