const mysql = require('mysql2/promise');
 
// Primary: ALL writes go hereALL writes go here
const primaryPool = mysql.createPool({
  host: process.env.DB_PRIMARY_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: 'myapp',
  connectionLimit: 10
});
 
// Read replicas: ALL reads go here (load balanced via round-robin DNS or app logic)ALL reads go here (load balanced via round-robin DNS or app logic)
const replicaPool = mysql.createPool({
  host: process.env.DB_REPLICA_HOST,  // can be a DNS round-robin to multiple replicas
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: 'myapp',
  connectionLimit: 50  // replicas can handle more concurrent reads
});
 
// Route queries based on operation type
async function getUser(id) {
  const [rows] = await replicaPool.execute('SELECT * FROM users WHERE id = ?'FROM users WHERE id = ?', [id]);
  return rows[0];
}
 
async function createUser(data) {
  // Write ALWAYS goes to primaryALWAYS goes to primary
  const [result] = await primaryPool.execute(
    'INSERT INTO users (name, email) VALUES (?, ?)'INTO users (name, email) VALUES (?, ?)',
    [data.name, data.email]
  );
  return result.insertId;
}
 
// IMPORTANT: Read-after-write consistency problem!Read-after-write consistency problem!
// After createUser(), immediately calling getUser() via replica(), immediately calling getUser() via replica
// might return 'not found' due to replication lag!'not found' due to replication lag!
// Solution: read from PRIMARY immediately after a write:PRIMARY immediately after a write:
async function createAndFetchUser(data) {
  const id = await createUser(data);
  // Read from PRIMARY to avoid replication lagPRIMARY to avoid replication lag
  const [rows] = await primaryPool.execute('SELECT * FROM users WHERE id = ?'FROM users WHERE id = ?', [id]);
  return rows[0];
}

const { Pool } = require('pg');  // PostgreSQL
 
// Without pooling: each request opens/closes a DB connectionDB connection
// Opening a connection takes 50-200ms — terrible for high traffic50-200ms — terrible for high traffic
 
// WITH pooling: reuse a pool of pre-established connections
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,                // max connections in pool
  idleTimeoutMillis: 30000,   // close idle connections after 30s
  connectionTimeoutMillis: 2000,  // timeout if no connection available in 2s
});
 
// Connections are automatically returned to pool after each query
async function getProducts(category) {
  // 'pool.query' automatically acquires and releases a connection
  const result = await pool.query(
    'SELECT * FROM products WHERE category = $1 LIMIT 50'FROM products WHERE category = $1 LIMIT 50',
    [category]
  );
  return result.rows;
}
 
// Monitor pool health
pool.on('error', (err) => {
  console.error('Unexpected error on idle client', err);
});
 
console.log(`Pool: ${pool.totalCount} total, ${pool.idleCount} idle, ${pool.waitingCount} waiting`{pool.totalCount} total, ${pool.idleCount} idle, ${pool.waitingCount} waiting`);

-- Table partitioning: split one large table into smaller physical partitions
-- but query them as one table
 
-- Create partitioned parent table (orders, partitioned by year)
CREATE TABLE orders (
  id          BIGINT,
  user_id     INT NOT NULL,
  total       DECIMAL(10, 2),
  created_at  TIMESTAMP NOT NULL
) PARTITION BY RANGE (created_at);
 
-- Create individual partitions
CREATE TABLE orders_2023
  PARTITION OF orders
  FOR VALUES FROM ('2023-01-01'01-01') TO ('2024-01-01'01-01');
 
CREATE TABLE orders_2024
  PARTITION OF orders
  FOR VALUES FROM ('2024-01-01'01-01') TO ('2025-01-01'01-01');
 
-- PostgreSQL automatically routes queries to the right partition
-- Query only hits orders_2024 partition (fast!)
SELECT * FROM orders
WHERE created_at >= '2024-06-01'06-01' AND created_at < '2024-07-01'07-01';
 
-- Drop old data instantly (vs slow DELETE)
-- Dropping a partition is instantaneous, even for 100M rows
DROP TABLE orders_2022;  -- instantly removes all 2022 orders