Database Scaling

// src/lib/database.js
import { PrismaClient } from '@prisma/client';

// Primary for writes
export const prisma = new PrismaClient({
  datasources: {
    db: { url: process.env.DATABASE_URL },
  },
});

// Replica for reads
export const prismaRead = new PrismaClient({
  datasources: {
    db: { url: process.env.DATABASE_REPLICA_URL },
  },
});

// Usage
// Writes go to primary
await prisma.user.create({ data: userData });

// Reads go to replica
const users = await prismaRead.user.findMany();

// src/lib/database.js
class DatabaseRouter {
  constructor(primary, replica) {
    this.primary = primary;
    this.replica = replica;
  }

  // Proxy that routes based on method
  get user() {
    return this.createProxy('user');
  }

  get post() {
    return this.createProxy('post');
  }

  createProxy(model) {
    return new Proxy({}, {
      get: (target, prop) => {
        // Write operations go to primary
        const writeOps = ['create', 'update', 'delete', 'upsert', 'createMany', 'updateMany', 'deleteMany'];

        if (writeOps.includes(prop)) {
          return this.primary[model][prop].bind(this.primary[model]);
        }

        // Read operations go to replica
        return this.replica[model][prop].bind(this.replica[model]);
      },
    });
  }
}

export const db = new DatabaseRouter(prisma, prismaRead);

// Usage - automatically routed
await db.user.create({ data }); // → Primary
await db.user.findMany();       // → Replica

# pgbouncer.ini
[databases]
myapp = host=localhost port=5432 dbname=myapp

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

// Prisma automatically pools connections
const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL + '?connection_limit=20&pool_timeout=10',
    },
  },
});

// For serverless (limit connections)
// DATABASE_URL="postgresql://...?connection_limit=1"

import pg from 'pg';

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,              // Max connections in pool
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

// Use pool for queries
const result = await pool.query('SELECT * FROM users WHERE id = $1', [id]);

// src/lib/cache.js
import { Redis } from 'ioredis';

const redis = new Redis(process.env.REDIS_URL);

export async function cached(key, ttl, fetchFn) {
  // Try cache first
  const cached = await redis.get(key);
  if (cached) {
    return JSON.parse(cached);
  }

  // Fetch from database
  const data = await fetchFn();

  // Cache result
  await redis.setex(key, ttl, JSON.stringify(data));

  return data;
}

// Usage
const user = await cached(
  `user:${id}`,
  300, // 5 minutes
  () => prisma.user.findUnique({ where: { id } })
);

// Invalidate on update
async function updateUser(id, data) {
  const user = await prisma.user.update({
    where: { id },
    data,
  });

  // Clear cache
  await redis.del(`user:${id}`);

  return user;
}

// Pattern-based invalidation
async function clearUserCache(userId) {
  const keys = await redis.keys(`user:${userId}:*`);
  if (keys.length > 0) {
    await redis.del(...keys);
  }
}

-- Create partitioned table
CREATE TABLE orders (
  id SERIAL,
  user_id INT,
  created_at TIMESTAMPTZ,
  total DECIMAL(10, 2)
) PARTITION BY RANGE (created_at);

-- Create partitions
CREATE TABLE orders_2024_q1 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE orders_2024_q2 PARTITION OF orders
  FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

CREATE TABLE orders_2024_q3 PARTITION OF orders
  FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');

CREATE TABLE orders_2024_q4 PARTITION OF orders
  FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');

-- Queries automatically route to correct partition
SELECT * FROM orders WHERE created_at > '2024-06-01';

CREATE TABLE users (
  id SERIAL,
  email TEXT,
  region TEXT
) PARTITION BY LIST (region);

CREATE TABLE users_na PARTITION OF users
  FOR VALUES IN ('us', 'ca', 'mx');

CREATE TABLE users_eu PARTITION OF users
  FOR VALUES IN ('uk', 'de', 'fr');

CREATE TABLE users_asia PARTITION OF users
  FOR VALUES IN ('jp', 'cn', 'in');

// src/lib/sharding.js
import { PrismaClient } from '@prisma/client';

const shards = [
  new PrismaClient({ datasources: { db: { url: process.env.SHARD_1_URL } } }),
  new PrismaClient({ datasources: { db: { url: process.env.SHARD_2_URL } } }),
  new PrismaClient({ datasources: { db: { url: process.env.SHARD_3_URL } } }),
];

// Hash-based sharding
function getShardIndex(key) {
  let hash = 0;
  for (let i = 0; i < key.length; i++) {
    hash = ((hash << 5) - hash) + key.charCodeAt(i);
    hash = hash & hash;
  }
  return Math.abs(hash) % shards.length;
}

export function getShard(shardKey) {
  const index = getShardIndex(shardKey);
  return shards[index];
}

// Usage
const userShard = getShard(userId);
const user = await userShard.user.findUnique({ where: { id: userId } });

// Query all shards
async function findUserByEmail(email) {
  const results = await Promise.all(
    shards.map(shard => shard.user.findFirst({ where: { email } }))
  );
  return results.find(user => user !== null);
}

// Aggregate across shards
async function countAllUsers() {
  const counts = await Promise.all(
    shards.map(shard => shard.user.count())
  );
  return counts.reduce((sum, count) => sum + count, 0);
}

// mongoose connection with replica set
import mongoose from 'mongoose';

mongoose.connect(process.env.MONGODB_URI, {
  replicaSet: 'rs0',
  readPreference: 'secondaryPreferred', // Read from replicas
});

// Read from primary for critical reads
const user = await User.findById(id).read('primary');

// Read from secondary for non-critical
const posts = await Post.find().read('secondary');

// Enable sharding on collection
// mongosh
sh.enableSharding("mydb");
sh.shardCollection("mydb.users", { "region": 1 }); // Shard by region
sh.shardCollection("mydb.orders", { "userId": "hashed" }); // Hash shard