Critical to know how to scale the DB : relational and non-relational

Vertical Scaling :

• add more CPU, RAM, Disk to the database
• requires downtime during reboot
• gives the ability to handle “scale”, more load
• vertical scaling has physical hardware limitations

Horizontal Scaling :

When data is huge, need to scale horizontally.

Will discuss two concepts : Read Replicas and Sharding / Partitioning.

Read Replicas :

Read Replicas is the most common way to scale the reads.

<aside>

• Master DB → handles writes
• Replica DB(s) → handle reads
• API / Server decides which DB to hit </aside>

Points to note :

• when read : write = 90 : 10
• move read to other DB so that ‘master’ is free to do writes
• API servers should know which DB to connect to get things done

Code Implementation :

// Establishing Connection
const mysql = require("mysql2/promise");

const masterDB = mysql.createPool({
  host: "master-db",
  user: "user",
  password: "pass",
  database: "appdb"
});

const replicaDB = mysql.createPool({
  host: "replica-db",
  user: "user",
  password: "pass",
  database: "appdb"
});

// Write → Master
app.post("/users", async (req, res) => {
  const { user_id, name } = req.body;
  await masterDB.query(
    "INSERT INTO users (user_id, name) VALUES (?, ?)",
    [user_id, name]
  );
  res.send("User created");
});

// Read → Replica
app.get("/users/:id", async (req, res) => {
  const [rows] = await replicaDB.query(
    "SELECT * FROM users WHERE user_id = ?",
    [req.params.id]
  );
  res.json(rows[0]);
});