SQL Databases
SQL (relational) databases store data in tables with fixed schemas. Relations between tables are expressed via foreign keys. Queries use SQL — a powerful declarative language that supports joins, aggregations, filtering, and ordering. Examples: MySQL, PostgreSQL, Oracle, SQL Server, SQLite.
ACID guarantees: Atomicity (all steps of a transaction succeed or none do), Consistency (data always satisfies schema constraints and business rules), Isolation (concurrent transactions don't interfere), Durability (committed data survives crashes).
ACID makes SQL databases ideal for financial systems, e-commerce (orders and payments), and any domain where correctness is more important than scale.
NoSQL Databases
NoSQL databases sacrifice some SQL features (joins, strong consistency) for horizontal scalability, flexible schemas, and specialized data models. Four main types:
(1) Key-Value: Redis, DynamoDB, Memcached. Pure key-value lookup. Extremely fast, no query capability. Use for caching, session storage, rate limiting.
(2) Wide-Column (Column Family): Cassandra, HBase, BigTable. Rows have variable columns grouped into column families. Designed for time-series data, event logs, high write throughput. Schema-flexible per row.
(3) Document: MongoDB, CouchDB, Firestore. Each record is a JSON/BSON document. Flexible schema (each document can have different fields). Good for catalogs, user profiles, content management.
(4) Graph: Neo4j, Amazon Neptune. Data is nodes and edges. Ideal for social networks, recommendation engines, fraud detection where relationships are the primary query pattern.
When to Choose SQL
Choose SQL when: (1) Data is structured and relational — clear entity relationships, foreign key constraints needed. (2) ACID compliance is non-negotiable — financial transactions, order processing, booking systems. (3) Complex queries needed — multi-table JOINs, aggregations, window functions, subqueries. (4) Data fits on one server or with modest sharding — SQL scales vertically and with read replicas very well to 100s of millions of rows. (5) Schema is stable — relational schemas are harder to migrate than NoSQL schemas.
When to Choose NoSQL
Choose NoSQL when: (1) Very high write throughput — Cassandra handles millions of writes/sec that would overwhelm MySQL. (2) Massive scale — petabytes of data across thousands of nodes (BigTable, Cassandra). (3) Flexible schema — each record can have different fields (user profiles, product attributes). (4) No complex joins needed — data is accessed by primary key or simple filters. (5) Specific access patterns — key-value lookup (Redis), time-series (HBase), graph traversal (Neo4j).
Real-world picks: Twitter uses both — MySQL for users/social graph, Cassandra for tweet timeline storage. Instagram uses PostgreSQL for posts/follows and Cassandra for feed data.
CAP Theorem Context
Databases can be classified by which CAP properties they prioritize: CA (Consistency + Availability, no network partition tolerance): traditional RDBMS (MySQL, PostgreSQL) — work perfectly when running on a single server; lose partition tolerance when distributed. CP (Consistency + Partition tolerance): MongoDB (in default config), HBase, BigTable, Zookeeper — will refuse reads/writes during a partition to avoid returning stale data. AP (Availability + Partition tolerance): Cassandra, CouchDB, DynamoDB — always serve reads/writes; accept eventual consistency (data may be temporarily inconsistent across nodes).
In practice, modern distributed databases are not a binary CAP choice — they offer tunable consistency (Cassandra allows per-query consistency level from ONE to ALL).