05-sql-vs-nosql.md

SQL vs NoSQL & The Distributed DB Landscape

Interview Definition:

"SQL databases are like strict spreadsheets with fixed columns, great for handling complex relationships. NoSQL databases are more like flexible folders where you can throw in different types of documents without a rigid structure, making them easier to scale horizontally."

1. Relational Databases (SQL)

e.g., PostgreSQL, MySQL, Oracle

Key Characteristics:

  • Structure: Tabular (Rows and Columns).
  • Schema: Rigid. You must define columns and data types before inserting data.
  • Scaling: Vertical Scaling (Scale-Up). To handle more load, you buy a bigger server (more RAM/CPU). Sharding a relational DB across multiple servers is notoriously difficult due to JOINs and ACID guarantees.
  • Guarantees: Strict ACID properties.

When to use SQL:

  • Financial systems, payment gateways (Juspay), inventory management.
  • When data relationships are complex and require complex querying (JOIN, GROUP BY).
  • When the schema is well-defined and stable.

2. Non-Relational Databases (NoSQL)

NoSQL databases were built to solve the horizontal scaling problem of SQL databases.

Key Characteristics:

  • Structure: Varied (Document, Key-Value, Graph, Column-Family).
  • Schema: Flexible/Dynamic. You can insert JSON documents with entirely different fields into the same collection.
  • Scaling: Horizontal Scaling (Scale-Out). To handle more load, you add more cheap commodity servers to a cluster. Data is automatically partitioned/sharded across them.
  • Guarantees: Often follow BASE properties (Basically Available, Soft state, Eventual consistency).

Types of NoSQL Databases:

A. Document Stores (MongoDB, Couchbase)

  • Data is stored as JSON/BSON documents.
  • Use Case: Content Management Systems (CMS), E-commerce product catalogs where products have vastly different attributes (a laptop has RAM, a shirt has size).

B. Key-Value Stores (Redis, Memcached, DynamoDB)

  • The simplest database. Just a dictionary/hash map.
  • Extremely fast (often entirely in-memory).
  • Use Case: Caching, Session management, Shopping carts, Rate limiting counters.

C. Column-Family Stores (Cassandra, HBase)

  • Data is stored in columns rather than rows. Optimized for fast writes and time-series data.
  • Use Case: IoT sensor data, logging systems, Netflix viewing history.

D. Graph Databases (Neo4j)

  • Data is stored as Nodes (entities) and Edges (relationships).
  • Use Case: Social networks (finding friends of friends), recommendation engines, fraud detection rings.

3. CAP Theorem

The CAP theorem states that in a distributed data store (a database running across multiple servers), it is impossible to simultaneously provide more than two out of three guarantees:

Consistency (C) / \ / \ / \ Availability (A) ----------- Partition Tolerance (P)
  1. Consistency (C): Every read receives the most recent write. All nodes in the cluster return the exact same data at the same time.
  2. Availability (A): Every request receives a (non-error) response, even if a node is down.
  3. Partition Tolerance (P): The system continues operating even when some servers cannot communicate with other servers because of a network failure. The system continues to operate despite network failures (dropped packets) that divide the cluster into isolated partitions.

The Reality of Network Partitions:

In a distributed system, network partitions will happen (cables break, switches fail). Therefore, Partition Tolerance (P) is a given. You must choose between Consistency and Availability.

CP vs AP Systems:

  • CP (Consistent & Partition Tolerant): If a network partition happens, the database prioritizes consistency. It might refuse to answer a query (losing Availability) rather than returning stale data. Examples: MongoDB, HBase, Redis (in cluster mode).
  • AP (Available & Partition Tolerant): If a network partition happens, the database will return data from whatever node you connect to, even if that node hasn't received the latest updates (losing Consistency). Examples: Cassandra, DynamoDB, CouchDB.

Eventual Consistency

AP systems rely on "Eventual Consistency". This means if no new updates are made to a given piece of data, eventually all nodes will synchronize and return the last updated value.

  • Example: When you update your Facebook profile picture, it might take a few minutes for your friends on different continents (served by different data centers) to see the new picture. This temporary inconsistency is acceptable for high availability.