Databases (SQL vs NoSQL)

Databases are the core persistence layer of every application. Choosing the right database paradigm directly determines your system's scalability, consistency guarantees, and query performance.

1. SQL (Relational Databases)

SQL databases store structured data in tables with rows and columns, with schema enforcement and foreign-key relational integrity.

Popular SQL Engines:

Database	Primary Strengths	Ideal Use Cases
PostgreSQL	Advanced JSONB, ACID compliance, custom extensions, concurrency	Financial ledgers, complex enterprise apps, mixed relational/document
MySQL / MariaDB	High read performance, massive ecosystem, mature replication	Web applications, content platforms, read-heavy workloads
SQLite	Zero-configuration, file-based embedded engine	Mobile apps, CLI tools, edge devices, testing

ACID Properties:

Property	Definition	Real-World Example
Atomicity	All-or-nothing execution. If any step fails, the entire transaction rolls back.	Money transfer: deducting account A and crediting account B must both complete or none.
Consistency	Ensures state transitions always satisfy defined validation rules and constraints.	Balance constraint: an account balance must never fall below zero.
Isolation	Concurrent transactions execute without interfering with one another.	Two shoppers buying the last ticket concurrently: only one completes.
Durability	Once committed, data changes are permanently recorded and survive power outages.	After receiving "Order Confirmed", the record remains even if DB crashes immediately.

2. Database Indexes

Indexes speed up query read operations by trading off extra disk space and slower write throughput.

-- Without index: Full Table Scan O(N)
SELECT * FROM users WHERE email = 'surya@example.com';

-- With B-Tree index: Binary Tree Lookup O(log N)
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = 'surya@example.com'; -- 100x to 1000x faster

Performance Comparison on 10,000,000 Rows:

Query Type	Without Index	With B-Tree Index
`WHERE email = '...'`	~450 ms	~1.5 ms
`ORDER BY created_at DESC LIMIT 20`	~1,800 ms	~4 ms
`JOIN orders ON orders.user_id = users.id`	~4,200 ms	~25 ms

3. NoSQL Database Types

NoSQL databases are designed for schema flexibility, horizontal scale, and specialized query access patterns.

Category	Representative DBs	Data Model	Best Used For
Document Store	MongoDB, Couchbase	JSON/BSON documents	CMS, catalogues, polymorphic schemas, mobile backends
Key-Value Store	Redis, DynamoDB, Memcached	Key → Value pairs	Caching, session stores, leaderboards, rate limit counters
Wide-Column (Columnar)	Apache Cassandra, ScyllaDB, HBase	Rows with dynamic column families	Time-series metrics, IoT telemetry, write-heavy event logs
Graph Database	Neo4j, AWS Neptune	Nodes, Edges & Properties	Social graphs, fraud rings, recommendation engines

4. SQL vs NoSQL Decision Matrix

Dimension	SQL (Relational)	NoSQL
Schema	Rigid, predefined upfront	Dynamic, schema-on-read
Relationships	First-class JOINs, foreign keys	Denormalized, embedded data or application-level joins
Scaling	Vertical scale-up; complex sharding	Horizontal scale-out by default
Transactions	Strict multi-record ACID transactions	Eventual consistency / single-document atomicity (BASE)
Query Flexibility	Rich, standardized SQL queries	Access pattern specific APIs / key lookups

Quick Selection Framework:

flowchart TD Q1{"Need multi-table\\nACID transactions?"} -->|"Yes"| SQL["🐘 SQL (PostgreSQL / MySQL)"] Q1 -->|"No"| Q2{"Need ultra-low latency\\nkey lookups?"} Q2 -->|"Yes"| KV["⚡ Key-Value (Redis / DynamoDB)"] Q2 -->|"No"| Q3{"Need flexible\\nnested JSON docs?"} Q3 -->|"Yes"| DOC["📄 Document (MongoDB)"] Q3 -->|"No"| Q4{"Massive write throughput\\n& time-series?"} Q4 -->|"Yes"| WC["📊 Wide-Column (Cassandra)"] Q4 -->|"No"| Q5{"Complex highly-connected\\ngraph relations?"} Q5 -->|"Yes"| GR["🕸️ Graph (Neo4j)"]