🏗️ BD10 — Architecture & Performance

1. Problème N+1

Le N+1 survient quand on effectue 1 requête pour charger N entités, puis N requêtes supplémentaires pour les relations de chacune.

// ❌ N+1 — 1 + N requêtes
const orders = await Order.find();
for (const order of orders) {
  order.user = await User.findById(order.userId); // N requêtes !
}

// ✅ Solution 1 — populate (Mongoose)
const orders = await Order.find().populate('userId', 'name email');

// ✅ Solution 2 — $lookup dans le pipeline
const orders = await db.collection('orders').aggregate([
  { $lookup: { from: 'users', localField: 'userId', foreignField: '_id', as: 'user' } },
  { $unwind: '$user' }
]).toArray();

// ✅ Solution 3 — JOIN SQL (une seule requête)
const { rows } = await pool.query(`
  SELECT o.*, u.name, u.email
  FROM orders o
  JOIN users u ON u.id = o.user_id
`);

// ✅ Solution 4 — DataLoader (batching)
const DataLoader = require('dataloader');
const userLoader = new DataLoader(async (ids) => {
  const { rows } = await pool.query('SELECT * FROM users WHERE id = ANY($1)', [ids]);
  return ids.map(id => rows.find(u => u.id === id));
});

2. CQRS — Command Query Responsibility Segregation

Séparer les opérations de lecture (Query) et d'écriture (Command) pour optimiser chacune indépendamment.

// Commandes (écriture) → PostgreSQL (source de vérité)
class CreateOrderCommand {
  async execute({ userId, items }) {
    return await prisma.$transaction(async (tx) => {
      const order = await tx.order.create({
        data: { userId, status: 'pending', items: { createMany: { data: items } } }
      });
      await tx.inventoryEvent.create({ data: { orderId: order.id, type: 'reserved' } });
      return order;
    });
  }
}

// Requêtes (lecture) → Vue matérialisée ou replica
class GetOrderStatsQuery {
  async execute({ userId }) {
    // Lecture depuis replica ou cache
    const cached = await redis.get(`stats:user:${userId}`);
    if (cached) return JSON.parse(cached);

    const stats = await readPool.query(`
      SELECT COUNT(*) as total, SUM(total) as revenue
      FROM orders WHERE user_id = $1
    `, [userId]);
    await redis.setex(`stats:user:${userId}`, 300, JSON.stringify(stats.rows[0]));
    return stats.rows[0];
  }
}

3. Cache Multi-couche (L1 + L2)

// L1 = mémoire locale (Map) — ultra-rapide, volatile par instance
// L2 = Redis — partagé, persistant entre instances

const L1 = new Map();
const L1_MAX = 1000;
const L1_TTL = 30 * 1000; // 30s

async function getWithMultiLayerCache(key, fetchFn, ttlSeconds = 300) {
  // L1
  const l1 = L1.get(key);
  if (l1 && Date.now() < l1.expires) return l1.data;

  // L2 Redis
  const l2 = await redis.get(key);
  if (l2) {
    const data = JSON.parse(l2);
    if (L1.size >= L1_MAX) L1.delete(L1.keys().next().value); // LRU simple
    L1.set(key, { data, expires: Date.now() + L1_TTL });
    return data;
  }

  // DB
  const data = await fetchFn();
  await redis.setex(key, ttlSeconds, JSON.stringify(data));
  L1.set(key, { data, expires: Date.now() + L1_TTL });
  return data;
}

4. Réplication PostgreSQL

// Pool read/write séparés
const writePool = new Pool({ connectionString: process.env.PG_PRIMARY_URL });
const readPool  = new Pool({ connectionString: process.env.PG_REPLICA_URL });

// Utilisation
async function getProducts(categoryId) {
  const { rows } = await readPool.query( // lecture → replica
    'SELECT * FROM products WHERE category_id = $1', [categoryId]
  );
  return rows;
}
async function createProduct(data) {
  const { rows } = await writePool.query( // écriture → primary
    'INSERT INTO products (name, price) VALUES ($1, $2) RETURNING *',
    [data.name, data.price]
  );
  return rows[0];
}

// PgBouncer (connection pooling)
// PG_POOL_URL=postgresql://user:pass@pgbouncer:6432/mydb?pgbouncer=true

5. Sharding MongoDB

// Activer le sharding sur une base
use admin
db.adminCommand({ enableSharding: 'myapp' });

// Choisir une shard key
db.adminCommand({
  shardCollection: 'myapp.orders',
  key: { userId: 'hashed' }  // distribution uniforme
});

// Clé de shard par plage (range)
db.adminCommand({
  shardCollection: 'myapp.events',
  key: { createdAt: 1, _id: 1 }
});

// Zones géographiques
sh.addShardTag('shard01', 'EU');
sh.addShardTag('shard02', 'US');
sh.addTagRange('myapp.users', { region: 'EU' }, { region: 'EV' }, 'EU');
sh.addTagRange('myapp.users', { region: 'US' }, { region: 'UT' }, 'US');

6. Monitoring

PostgreSQL

-- Requêtes lentes
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC LIMIT 10;

-- Connexions actives
SELECT pid, usename, state, query_start, query
FROM pg_stat_activity WHERE state = 'active';

-- Taille des tables et index
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total
FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC;

-- Index non utilisés
SELECT indexrelname, idx_scan FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname = 'public';

Redis

redis-cli info stats
redis-cli info memory
redis-cli monitor  # attention en prod, verbeux
redis-cli --latency
redis-cli --bigkeys

7. Checklist Performance