System Design

SQL vs NoSQL

SQL (Relational) va NoSQL (Non-relational) — ikki xil database paradigmasi.

SQL (Relational Database)

Structured Query Language — ma’lumotlarni jadval (table) va munosabatlar (relations) bilan saqlash.

Struktura

-- Users table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100) UNIQUE
);

-- Posts table (foreign key)
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  title VARCHAR(200),
  content TEXT
);

Asosiy tushunchalar:

Schema: Oldindan aniqlangan struktura
Rows: Ma’lumot qatorlari
Relations: Foreign key’lar orqali bog’lanish
ACID: Transaction support

Mashhur SQL databases

PostgreSQL: Eng kuchli, open-source
MySQL: Ko’p ishlatiladi, WordPress, etc.
SQL Server: Microsoft
Oracle: Enterprise

NoSQL (Non-Relational)

NoSQL — turli xil ma’lumot modellaridan foydalanadi (document, key-value, graph).

1. Document Database (MongoDB, CouchDB)

JSON-like documentlar:

// Users collection
{
  "_id": "507f1f77bcf86cd799439011",
  "name": "Jamshid",
  "email": "jamshid@example.com",
  "posts": [
    {
      "title": "System Design",
      "content": "..."
    },
    {
      "title": "Scaling",
      "content": "..."
    }
  ]
}

Schema yo’q: Har bir document boshqa struktura bo’lishi mumkin.

2. Key-Value Store (Redis, DynamoDB)

Oddiy key → value:

user:123 → { name: "Jamshid", email: "..." }
session:abc → { userId: 123, expires: ... }
cache:homepage → "<html>...</html>"

Juda tez: O(1) lookup, memory-based.

3. Column-Family (Cassandra, HBase)

Wide column store:

Row Key: user123
├─ profile:name = "Jamshid"
├─ profile:email = "jamshid@example.com"
├─ activity:last_login = 2024-01-15
└─ activity:post_count = 42

Qachon: Juda katta datasets, write-heavy.

4. Graph Database (Neo4j, ArangoDB)

Nodes va relationships:

(User:Jamshid)-[:FOLLOWS]->(User:Ali)
(User:Jamshid)-[:POSTED]->(Post:SystemDesign)

Qachon: Social networks, recommendation engines.

SQL vs NoSQL: Asosiy farqlar

	SQL	NoSQL
Schema	Fixed, predefined	Flexible, schema-less
Scaling	Vertical (mostly)	Horizontal
Consistency	ACID	Eventually consistent (BASE)
Joins	Complex joins	No joins (denormalize)
Transactions	Multi-row	Limited
Query	SQL language	API / Query language
Use case	Complex queries, relations	Simple queries, scale

Qachon SQL?

SQL mos keladi:

1. Complex relations:

-- E-commerce: products, categories, orders, users
SELECT u.name, o.total, p.name
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
WHERE o.status = 'completed';

2. ACID kerak:

Banking, financial systems
Inventory management
Booking systems

3. Complex analytics:

SELECT category, AVG(price), COUNT(*)
FROM products
GROUP BY category
HAVING AVG(price) > 100;

4. Data integrity muhim:

Foreign key constraints
Uniqueness
Validation rules

Real misollar:

Banking: PostgreSQL
E-commerce: MySQL, PostgreSQL
Enterprise apps: Oracle, SQL Server

Qachon NoSQL?

NoSQL mos keladi:

1. Massive scale (horizontal):

Instagram: Billions of photos
Twitter: 500M tweets/day
Facebook: 2B+ users

SQL vertical scaling cheklangan, NoSQL horizontal cheksiz.

2. Flexible schema:

// User profile evolve bo'ladi
{ name: "Ali" }
{ name: "Vali", age: 25 }
{ name: "Sardor", age: 30, skills: ["JS", "Go"] }

Schema migration kerak emas.

3. Simple queries, no joins:

// Document database
db.users.findOne({ _id: "123" })
db.posts.find({ userId: "123" })

4. High write throughput:

Logs, metrics, time-series data
→ Cassandra, InfluxDB

Real misollar:

Social media: MongoDB (profiles), Cassandra (feeds)
Caching: Redis
Logs: Elasticsearch
Real-time: Firebase

Scaling taqqoslash

SQL Scaling

Vertical:

Small:  2 CPU, 4GB RAM
Medium: 8 CPU, 32GB RAM
Large:  64 CPU, 512GB RAM ← Qimmat, cheklangan

Horizontal (qiyin):

Read replicas (reads scale)
Sharding (complex, manual)

NoSQL Scaling

Horizontal (built-in):

MongoDB sharded cluster:
- Shard 1: users 0-1M
- Shard 2: users 1M-2M
- Shard 3: users 2M-3M
→ Automatic balancing

Cheksiz scale

Query complexity

SQL: Complex queries easy

-- Top 10 users by post count, with avg likes
SELECT u.name, COUNT(p.id) as posts, AVG(l.count) as avg_likes
FROM users u
JOIN posts p ON p.user_id = u.id
LEFT JOIN (
  SELECT post_id, COUNT(*) as count
  FROM likes
  GROUP BY post_id
) l ON l.post_id = p.id
GROUP BY u.id
ORDER BY posts DESC
LIMIT 10;

Bitta query

NoSQL: Complex queries qiyin

// Same query NoSQL'da:
const users = await db.users.find();
const posts = await db.posts.find();
const likes = await db.likes.find();

// Application code'da join, aggregate 
// Yoki denormalize qilib oldindan saqla

Yechim: Data denormalization (duplicate data).

Denormalization

SQL (Normalized):

users: { id, name, email }
posts: { id, user_id, title }

Join kerak har safar.

NoSQL (Denormalized):

posts: {
  id: 1,
  title: "...",
  author: {
    id: 123,
    name: "Jamshid",  // Duplicate!
    email: "..."      // Duplicate!
  }
}

No join , lekin data duplication

Trade-off: Speed uchun storage qurbon qilish.

Consistency

SQL: Strong consistency

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- Instant consistency

NoSQL: Eventual consistency

await db.users.updateOne({ _id: 123 }, { $set: { name: "Ali" } });
// Replica 1: updated
// Replica 2: updated (10ms later)
// Replica 3: updated (50ms later)
// Eventually consistent

CAP theorem: Consistency vs Availability - ikkalasini birga olib bo’lmaydi (distributed systems’da).

Hybrid Approach (Ko’p qo’llaniladi)

Real projects ikkalasini ishlatadi:

PostgreSQL:
- Users, orders, inventory (ACID kerak)

Redis:
- Session, cache (tez kerak)

MongoDB:
- User profiles, posts (flexible schema)

Elasticsearch:
- Search, logs (full-text search)

Misol: E-commerce

Products catalog → MongoDB (flexible schema)
Orders, payments → PostgreSQL (ACID)
Product search → Elasticsearch
Session → Redis
Analytics → Cassandra (time-series)

Migratsiya qilish

SQL → NoSQL

Nega:

Scale qilish qiyin
Schema migrations og’ir
Performance issues

Qanday:

Read replica orqali data sync
Dual write (SQL + NoSQL)
Verify data consistency
Switch reads to NoSQL
Deprecate SQL

NoSQL → SQL

Nega:

Complex queries kerak
ACID kerak
Data integrity

Qanday:

Denormalize → Normalize
ETL pipeline
Schema design

Xulosa

SQL tanlang:

Complex relations va joins
ACID transactions kerak
Data integrity muhim
Vertical scaling yetarli

NoSQL tanlang:

Massive horizontal scale
Flexible schema
Simple queries
High write throughput
Eventually consistent OK

Real-world:

Ko’pincha ikkalasi birga
Har bir use case uchun to’g’ri tool
Polyglot persistence

Keyingi dars: Database Indexes - querylarni tezlashtirish.