Database Scaling Strategies

Read Replica Architecture

# Primary Database (Write Operations)
Primary DB (Master)
├── Writes: INSERT, UPDATE, DELETE
├── Reads: Critical queries requiring latest data
└── Replication to replicas

# Read Replicas (Read Operations)
Read Replica 1
├── Reads: Reports, analytics, user queries
├── Slightly behind primary (eventual consistency)
└── Can be promoted to primary if needed

Read Replica 2
├── Reads: Search, filtering, pagination
├── Geographic distribution
└── Load balancing across replicas

// Database connection configuration
const dbConfig = {
  primary: {
    host: process.env.DB_PRIMARY_HOST,
    port: process.env.DB_PRIMARY_PORT,
    database: process.env.DB_NAME,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    ssl: true
  },
  replicas: [
    {
      host: process.env.DB_REPLICA1_HOST,
      port: process.env.DB_REPLICA1_PORT,
      database: process.env.DB_NAME,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      ssl: true
    },
    {
      host: process.env.DB_REPLICA2_HOST,
      port: process.env.DB_REPLICA2_PORT,
      database: process.env.DB_NAME,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      ssl: true
    }
  ]
};

// Connection pool for primary (writes)
const primaryPool = new Pool(dbConfig.primary);

// Connection pools for replicas (reads)
const replicaPools = dbConfig.replicas.map(config => new Pool(config));

// Round-robin load balancer for replicas
let replicaIndex = 0;
const getReplicaPool = () => {
  const pool = replicaPools[replicaIndex];
  replicaIndex = (replicaIndex + 1) % replicaPools.length;
  return pool;
};

// Database service
class DatabaseService {
  // Write operations go to primary
  async write(query, params) {
    const client = await primaryPool.connect();
    try {
      const result = await client.query(query, params);
      return result;
    } finally {
      client.release();
    }
  }

  // Read operations go to replicas
  async read(query, params) {
    const pool = getReplicaPool();
    const client = await pool.connect();
    try {
      const result = await client.query(query, params);
      return result;
    } finally {
      client.release();
    }
  }
}

// Sharding service
class ShardingService {
  constructor(shards) {
    this.shards = shards;
    this.shardCount = shards.length;
  }

  // Determine which shard a record belongs to
  getShard(key) {
    const hash = this.hashFunction(key);
    const shardIndex = hash % this.shardCount;
    return this.shards[shardIndex];
  }

  // Simple hash function (use a better one in production)
  hashFunction(key) {
    let hash = 0;
    for (let i = 0; i < key.length; i++) {
      const char = key.charCodeAt(i);
      hash = ((hash << 5) - hash) + char;
      hash = hash & hash; // Convert to 32-bit integer
    }
    return Math.abs(hash);
  }

  // Insert record into appropriate shard
  async insert(table, data) {
    const shard = this.getShard(data.id);
    return await shard.query(
      `INSERT INTO ${table} (id, name, email) VALUES ($1, $2, $3)`,
      [data.id, data.name, data.email]
    );
  }

  // Find record across all shards
  async findById(table, id) {
    const shard = this.getShard(id);
    const result = await shard.query(
      `SELECT * FROM ${table} WHERE id = $1`,
      [id]
    );
    return result.rows[0];
  }
}

// Redis caching service
const redis = require('redis');
const client = redis.createClient({
  host: process.env.REDIS_HOST,
  port: process.env.REDIS_PORT,
  password: process.env.REDIS_PASSWORD
});

class CacheService {
  constructor() {
    this.client = client;
  }

  // Cache with TTL (Time To Live)
  async set(key, value, ttl = 3600) {
    await this.client.setex(key, ttl, JSON.stringify(value));
  }

  // Get from cache
  async get(key) {
    const value = await this.client.get(key);
    return value ? JSON.parse(value) : null;
  }

  // Cache database query results
  async cacheQuery(query, params, ttl = 300) {
    const cacheKey = `query:${Buffer.from(query + JSON.stringify(params)).toString('base64')}`;
    
    // Try to get from cache first
    let result = await this.get(cacheKey);
    
    if (!result) {
      // Query database if not in cache
      result = await this.executeQuery(query, params);
      
      // Cache the result
      await this.set(cacheKey, result, ttl);
    }
    
    return result;
  }

  // Invalidate cache patterns
  async invalidatePattern(pattern) {
    const keys = await this.client.keys(pattern);
    if (keys.length > 0) {
      await this.client.del(keys);
    }
  }
}

Common Index Types

-- Single column index
CREATE INDEX idx_user_email ON users(email);

-- Composite index (multiple columns)
CREATE INDEX idx_user_status_created ON users(status, created_at);

-- Partial index (with WHERE clause)
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- Covering index (includes all needed columns)
CREATE INDEX idx_user_profile ON users(id, name, email, created_at);

-- Text search index (PostgreSQL)
CREATE INDEX idx_posts_content_search ON posts USING gin(to_tsvector('english', content));