System Design

Database Replication

Replication — ma’lumotlarni bir nechta serverlarda nusxalash.

Nega Replication kerak?

1. High Availability

Master crash → Replica becomes Master 
Zero downtime

2. Read Scaling

1 Master (writes)
3 Replicas (reads)
→ 4x read capacity

3. Geographic Distribution

Master: US
Replica: Europe
Replica: Asia
→ Low latency reads

4. Backup

Replica → Snapshot
Master crash → Restore from replica

Master-Slave Replication

En кўп qў’llaniladigan pattern:

        ┌────────┐
        │ Master │ ← All writes
        └────┬───┘
             │ Replication
      ┌──────┼──────┐
      ▼      ▼      ▼
   ┌────┐ ┌────┐ ┌────┐
   │Rep1│ │Rep2│ │Rep3│ ← Read-only
   └────┘ └────┘ └────┘

Write operation

-- Application
INSERT INTO users VALUES (...);

1. Write → Master
2. Master logs change (binlog/WAL)
3. Master → Replicates to slaves
4. Slaves apply change

Latency: 10-100ms replication lag.

Read operation

// Application code
// Write
await masterDB.query('INSERT INTO users ...');

// Reads
await replicaDB.query('SELECT * FROM users WHERE id = 123');

Load distribution:

• Master: 10K writes/sec
• Each replica: 50K reads/sec

Asynchronous vs Synchronous

Asynchronous (default):

Master writes → Return success → Replicate (background)

Fast: No wait
Risk: Master crash bo’lsa, last N seconds data yo’qolishi mumkin

Synchronous:

Master writes → Wait for replica ACK → Return success

Safe: No data loss
Slow: 2x latency

Semi-synchronous (best):

Master writes → Wait for 1 replica → Return success

Trade-off

Master-Master Replication

Ikki yoki ko’proq master:

   ┌────────┐  ←→  ┌────────┐
   │Master1 │      │Master2 │
   └────────┘      └────────┘
   Both can write

Use case: Multi-region

US Region          EU Region
┌────────┐   ↔   ┌────────┐
│Master1 │       │Master2 │
└────────┘       └────────┘
US users write → Master1
EU users write → Master2

Low latency: Local writes
Conflicts: Ikkala master’da bir xil row o’zgarishi mumkin

Conflict resolution

Example conflict:

Master1: UPDATE users SET balance = 100 WHERE id = 1
Master2: UPDATE users SET balance = 200 WHERE id = 1 (same time)

Yechimlar:

1. Last Write Wins:

Timestamp-based, oxirgi yozuv qoladi

2. Application-level:

Version vectors, CRDTs

3. Manual resolution:

Flag conflict, admin resolves

Replication Lag

Masala: Replica orqada qolishi.

Time: 10:00:00
Master: INSERT user_id=123

Time: 10:00:00.050 (50ms later)
Replica: Still no user_id=123

User: "Men ro'yxatdan o'tdim, lekin profilim yo'q!" 

Read-after-write consistency

Yechim 1: Read from master after write

await master.insert({ id: 123, name: 'Ali' });
const user = await master.findOne({ id: 123 }); // Master'dan

Yechim 2: Session stickiness

// Bitta user har doim bitta replica'dan
const replicaId = userId % replicaCount;

Yechim 3: Wait for replication

await master.insert(...);
await sleep(100); // 100ms kutish
await replica.findOne(...); // Endi bor

PostgreSQL Replication

Setup

Master config (postgresql.conf):

wal_level = replica
max_wal_senders = 3

Create replication user:

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secret';

Replica setup:

pg_basebackup -h master-ip -D /var/lib/postgresql/data -U replicator -P

standby_mode = 'on'
primary_conninfo = 'host=master-ip port=5432 user=replicator password=secret'

Failover

Master crash:

pg_ctl promote -D /var/lib/postgresql/data

Manual failover qiyin → Use tools:

• Patroni: Auto failover
• repmgr: Replication manager
• pgpool: Connection pooling + failover

MySQL Replication

Setup

Master config (my.cnf):

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW

Create replication user:

CREATE USER 'replicator'@'%' IDENTIFIED BY 'secret';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';

Replica setup:

CHANGE MASTER TO
  MASTER_HOST='master-ip',
  MASTER_USER='replicator',
  MASTER_PASSWORD='secret',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=107;

START SLAVE;

Check status:

SHOW SLAVE STATUS\G

MongoDB Replica Set

// Init replica set
rs.initiate({
  _id: "rs0",
  members: [
    { _id: 0, host: "mongo1:27017" },
    { _id: 1, host: "mongo2:27017" },
    { _id: 2, host: "mongo3:27017" }
  ]
});

Automatic failover:

Primary crash → Replica election (seconds) → New primary

Read preference:

// Primary only (default)
db.collection.find().readPref('primary');

// Any replica
db.collection.find().readPref('secondary');

// Nearest (low latency)
db.collection.find().readPref('nearest');

Real-world Architectures

Instagram (PostgreSQL)

1 Master (writes)
12 Replicas (reads)
- 3 in US-East
- 3 in US-West
- 3 in Europe
- 3 in Asia

Read routing: Geographic
Failover: Automatic (Patroni)

GitHub (MySQL)

Orchestrator: Auto failover
Read replicas: Per datacenter
Writes: All to master
Read/Write ratio: 95% reads

Netflix (Cassandra)

Multi-master (no single master)
Replication factor: 3
Consistency: Quorum (2/3 must agree)

Monitoring

-- PostgreSQL replication lag
SELECT 
  client_addr,
  state,
  pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS lag_bytes
FROM pg_stat_replication;

-- MySQL replication lag
SHOW SLAVE STATUS\G
-- Check: Seconds_Behind_Master

Alerts:

• Lag > 5 seconds
• Replica stopped
• Master disk full

Best Practices

1. Monitor lag

Alert if lag > 5s

2. Regular failover tests

Test every month

3. Automated failover

Patroni, Orchestrator, etc.

4. Read/write split in application

const writeDB = masterConnection;
const readDB = replicaConnection;

5. Connection pooling

PgBouncer, ProxySQL

6. Backup from replica

Master: Production traffic
Replica: Backup source (no impact)

Xulosa

Replication:

• High availability
• Read scaling
• Geographic distribution

Master-Slave:

• Oddiy, ishonchli
• Read scaling
• Auto failover (tools bilan)

Master-Master:

• Multi-region writes
• Conflict resolution kerak

Best practice:

• Monitoring va alerting
• Automated failover
• Application-level read/write routing

Keyingi dars: Database Sharding - horizontal partitioning.