-- Our most problematic query (2.5s+ execution time)SELECT     o.id, o.status, o.created_at,    c.name, c.email,    p.title, p.price,    i.quantity,    a.street, a.city, a.country,    (SELECT COUNT(*) FROM order_items WHERE order_id = o.id) as items_countFROM orders oJOIN customers c ON o.customer_id = c.idJOIN order_items i ON o.id = i.order_idJOIN products p ON i.product_id = p.idJOIN addresses a ON o.shipping_address_id = a.idWHERE o.status = 'processing'    AND o.created_at > NOW() - INTERVAL '24 HOURS'ORDER BY o.created_at DESC;

Nested Loop  (cost=1.13..2947.32 rows=89 width=325)  ->  Index Scan using orders_created_at on orders  (cost=0.42..1234.56 rows=1000)  ->  Materialize  (cost=0.71..1701.23 rows=89 width=285)        ->  Nested Loop  (cost=0.71..1698.12 rows=89 width=285)              ->  Index Scan using customers_pkey on customers              ->  Index Scan using order_items_pkey on order_items

- Added composite indexesCREATE INDEX idx_orders_status_created ON orders(status, created_at);CREATE INDEX idx_order_items_order_product ON order_items(order_id, product_id);
-- Materialized views for common queriesCREATE MATERIALIZED VIEW order_summaries ASSELECT     o.id,    COUNT(i.id) as items_count,    SUM(p.price * i.quantity) as total_amountFROM orders oJOIN order_items i ON o.id = i.order_idJOIN products p ON i.product_id = p.idGROUP BY o.id;
-- Query rewriteWITH order_data AS (    SELECT         o.id, o.status, o.created_at,        c.name, c.email    FROM orders o    JOIN customers c ON o.customer_id = c.id    WHERE o.status = 'processing'        AND o.created_at > NOW() - INTERVAL '24 HOURS')SELECT     od.*,    os.items_count,    os.total_amountFROM order_data odJOIN order_summaries os ON od.id = os.id;

// Redis caching layerconst getOrderDetails = async (orderId) => {  const cacheKey = `order:${orderId}:details`;
  // Try cache first  let orderDetails = await redis.get(cacheKey);  if (orderDetails) {    return JSON.parse(orderDetails);  }
  // Cache miss - query database  orderDetails = await db.query(ORDER_DETAILS_QUERY, [orderId]);  // Cache for 5 minutes  await redis.setex(cacheKey, 300, JSON.stringify(orderDetails));
  return orderDetails;};
// Cache invalidation on updatesconst updateOrder = async (orderId, data) => {  await db.query(UPDATE_ORDER_QUERY, [data, orderId]);  await redis.del(`order:${orderId}:details`);};

// Warm cache for active ordersconst warmOrderCache = async () => {  const activeOrders = await db.query(`    SELECT id FROM orders     WHERE status IN ('processing', 'shipped')     AND created_at > NOW() - INTERVAL '24 HOURS'  `);
  await Promise.all(    activeOrders.map(order => getOrderDetails(order.id))  );};
// Run every 5 minutescron.schedule('*/5 * * * *', warmOrderCache);

// Database connection pool with read-write splitconst pool = {  write: new Pool({    host: 'master.database.aws',    max: 20,    min: 5  }),  read: new Pool({    hosts: [      'replica1.database.aws',      'replica2.database.aws',      'replica3.database.aws',      'replica4.database.aws',      'replica5.database.aws'    ],    max: 50,    min: 10  })};
// Load balancer for read replicasconst getReadConnection = () => {  const replicaIndex = Math.floor(Math.random() * 5);  return pool.read.connect(replicaIndex);};
// Query routerconst executeQuery = async (query, params, queryType = 'read') => {  const connection = queryType === 'write'     ? await pool.write.connect()    : await getReadConnection();      try {    return await connection.query(query, params);  } finally {    connection.release();  }};

// MongoDB order document model{  _id: ObjectId("507f1f77bcf86cd799439011"),  status: "processing",  created_at: ISODate("2024-02-07T10:00:00Z"),  customer: {    _id: ObjectId("507f1f77bcf86cd799439012"),    name: "John Doe",    email: "john@example.com",    shipping_address: {      street: "123 Main St",      city: "San Francisco",      country: "USA"    }  },  items: [{    product_id: ObjectId("507f1f77bcf86cd799439013"),    title: "Gaming Laptop",    price: 1299.99,    quantity: 1,    variants: {      color: "black",      size: "15-inch"    }  }],  payment: {    method: "credit_card",    status: "completed",    amount: 1299.99  },  shipping: {    method: "express",    tracking_number: "1Z999AA1234567890",    estimated_delivery: ISODate("2024-02-10T10:00:00Z")  },  metadata: {    user_agent: "Mozilla/5.0...",    ip_address: "192.168.1.1"  }}

// MongoDB schema validationdb.createCollection("orders", {  validator: {    $jsonSchema: {      bsonType: "object",      required: ["customer", "items", "status", "created_at"],      properties: {        customer: {          bsonType: "object",          required: ["name", "email"],          properties: {            name: { bsonType: "string" },            email: { bsonType: "string" }          }        },        items: {          bsonType: "array",          items: {            bsonType: "object",            required: ["product_id", "price", "quantity"],            properties: {              product_id: { bsonType: "objectId" },              price: { bsonType: "double" },              quantity: { bsonType: "int" }            }          }        }      }    }  }});

MongoDB indexesdb.orders.createIndex({ "created_at": 1, "status": 1 });db.orders.createIndex({ "customer.email": 1 });db.orders.createIndex({ "items.product_id": 1 });

// Dual-write implementationclass OrderService {  async createOrder(orderData) {    try {      // Start MongoDB transaction      const session = await mongoose.startSession();      session.startTransaction();            // Write to MongoDB      const mongoOrder = await this.createMongoOrder(orderData, session);            // Write to PostgreSQL      const pgOrder = await this.createPostgresOrder(orderData);            // Verify consistency      if (!this.verifyOrderConsistency(mongoOrder, pgOrder)) {        throw new Error('Data inconsistency detected');      }            await session.commitTransaction();      return mongoOrder;          } catch (error) {      await session.abortTransaction();      throw error;    }  }    private async verifyOrderConsistency(mongoOrder, pgOrder) {    const checksums = await Promise.all([      this.calculateChecksum(mongoOrder),      this.calculateChecksum(pgOrder)    ]);    return checksums[0] === checksums[1];  }}

// MongoDB change streams for real-time monitoringconst monitorOrderChanges = async () => {  const changeStream = db.collection('orders').watch();    changeStream.on('change', async (change) => {    // Send metrics to DataDog    const metrics = {      operation_type: change.operationType,      execution_time: change.clusterTime.getTime() - change.operationTime.getTime(),      collection: 'orders'    };        await datadog.gauge('mongodb.operation', metrics);        // Alert on specific conditions    if (change.operationType === 'update' &&         change.updateDescription.updatedFields.status === 'failed') {      await slack.sendAlert({        channel: '#db-alerts',        text: `Order ${change.documentKey._id} failed processing`,        level: 'critical'      });    }  });};// Performance monitoringconst monitorPerformance = async () => {  while (true) {    const stats = await db.collection('orders').stats();        await Promise.all([      datadog.gauge('mongodb.size', stats.size),      datadog.gauge('mongodb.count', stats.count),      datadog.gauge('mongodb.avgObjSize', stats.avgObjSize)    ]);        await sleep(60000); // Check every minute  }};