Auditor de Optimización - Prompt del Sistema

# Optimization Audit: E-Commerce Checkout Service ## Context - **Technology Stack**: Node.js 18 (Express), PostgreSQL 14, Redis 7, React 18 (frontend), AWS ECS (deployment) - **Runtime Environment**: Containerized microservice, 2 vCPU / 4 GB RAM per instance, 3 instances behind ALB - **Scope**: Full checkout service architecture - API endpoints, database queries, caching layer, and frontend bundle - **Known Pain Points**: Checkout latency spikes at peak traffic (p99 > 3s), high database CPU (70% sustained), memory usage at 85% of container limit - **Traffic Patterns**: 500 req/min average, 2000 req/min peak during sales events, 60% cart operations, 30% checkout, 10% order history ## Optimization Summary - **Overall Assessment**: Service is functionally correct but has critical performance bottlenecks in database query patterns and memory management. The architecture is sound but requires targeted optimizations to handle projected 3x traffic growth. - **Top 3 Improvements**: 1. Fix N+1 query pattern in cart retrieval (reduces DB load by ~40%) 2. Implement Redis caching for product data (reduces API latency by ~50%) 3. Optimize React bundle with code splitting (reduces initial load time by ~60%) - **Biggest Risk**: Database CPU saturation will cause cascading timeouts and failed checkouts during next sales event if not addressed ## Quick Wins - [ ] **OA-QUICK-1.1 [Cart Retrieval N+1 Query Fix]**: - **Category**: DB - **Severity**: Critical - **Evidence**: `getCartWithItems` in `cart-service.js` (line 45) executes 1 query for cart + N queries for items in loop - **Fix**: Use single JOIN query with `GROUP BY` or batch fetch items with `WHERE cart_id IN (...)` - **Impact**: 40% reduction in DB queries per cart operation, ~200ms latency improvement - [ ] **OA-QUICK-1.2 [Product Data Redis Caching]**: - **Category**: Caching - **Severity**: High - **Evidence**: `getProductDetails` in `product-service.js` (line 78) queries DB on every call; product data changes < 5% daily - **Fix**: Cache product data in Redis with 15-min TTL, invalidate on product update events - **Impact**: 50% reduction in product-related DB queries, ~150ms latency improvement - [ ] **OA-QUICK-1.3 [React Bundle Code Splitting]**: - **Category**: Memory - **Severity**: Medium - **Evidence**: Main bundle is 2.4MB (uncompressed), contains checkout, cart, and admin components loaded upfront - **Fix**: Implement React.lazy() for non-critical routes (admin panel, order history), extract vendor libraries to separate chunk - **Impact**: 60% reduction in initial bundle size, ~1.5s faster first paint - [ ] **OA-QUICK-1.4 [Missing Database Index on Orders Table]**: - **Category**: DB - **Severity**: High - **Evidence**: `orders.user_id` column lacks index; order history queries perform full table scans (confirmed via EXPLAIN) - **Fix**: Add composite index on `(user_id, created_at DESC)` for order history queries - **Impact**: 90% reduction in order history query time, ~300ms latency improvement - [ ] **OA-QUICK-1.5 [Connection Pool Size Misconfiguration]**: - **Category**: Concurrency - **Severity**: Medium - **Evidence**: PostgreSQL pool configured with max 50 connections per instance; 3 instances = 150 total, DB max_connections = 100 - **Fix**: Reduce pool size to 25 per instance (75 total), add connection timeout and retry logic - **Impact**: Eliminates connection exhaustion errors, ~20% reduction in error rate ## Deeper Optimizations - [ ] **OA-DEEP-1.1 [Checkout Transaction Refactoring]**: - **Category**: Architectural - **Evidence**: Current checkout process holds DB transaction open during external payment API call (avg 800ms), causing lock contention - **Fix**: Implement two-phase checkout - reserve inventory in transaction, process payment outside transaction, confirm order in separate transaction - **Tradeoffs**: Requires idempotency keys and reconciliation job for failed payments; moderate complexity increase - **Impact**: 60% reduction in transaction hold time, eliminates lock contention bottleneck - [ ] **OA-DEEP-1.2 [Event-Driven Inventory Updates]**: - **Category**: Algorithmic - **Evidence**: Inventory counts are read from DB on every cart view (500 req/min), causing unnecessary load - **Fix**: Maintain inventory counts in Redis with pub/sub updates from inventory service; DB becomes source of truth only for writes - **Tradeoffs**: Requires eventual consistency handling, potential stale reads up to 1s; needs fallback to DB on Redis failure - **Impact**: 70% reduction in inventory-related DB reads, ~100ms latency improvement - [ ] **OA-DEEP-1.3 [GraphQL Federation for Checkout Data]**: - **Category**: Architectural - **Evidence**: Checkout page makes 5 separate REST calls to different services (cart, product, shipping, tax, payment) - **Fix**: Implement GraphQL federation gateway to aggregate checkout data in single request with parallel resolution - **Tradeoffs**: Significant infrastructure investment, requires schema design and service refactoring; high effort - **Impact**: 80% reduction in checkout page network requests, ~400ms latency improvement - [ ] **OA-DEEP-1.4 [Database Read Replicas for Order History]**: - **Category**: Infrastructure - **Evidence**: Order history queries (30% of traffic) compete with write-heavy checkout operations on same DB instance - **Fix**: Deploy 2 read replicas, route order history and reporting queries to replicas, keep writes on primary - **Tradeoffs**: Data replication lag (up to 1s), requires query routing logic; moderate infrastructure cost increase - **Impact**: 50% reduction in primary DB load, eliminates read-write contention - [ ] **OA-DEEP-1.5 [Streaming Response for Cart Operations]**: - **Category**: Algorithmic - **Evidence**: Cart operations return full cart state after every modification, including unchanged product details - **Fix**: Implement delta-based responses - return only changed items with version number, client merges with cached state - **Tradeoffs**: Requires client-side state management changes, more complex API contract; moderate effort - **Impact**: 70% reduction in cart response payload size, ~50ms latency improvement ## Validation Plan - **Benchmarks**: - Load test with k6: 1000 concurrent users, 30-min duration, measure p50/p95/p99 latency - Database query profiling with pg_stat_statements before/after index changes - Memory profiling with Node.js --inspect and heap snapshots - Frontend performance with Lighthouse CI (FCP, LCP, TTI metrics) - **Profiling Strategy**: - Use AWS X-Ray for distributed tracing across services - Enable pg_stat_statements for query-level DB performance - Use Redis MONITOR command (sampled) for cache hit-rate analysis - Chrome DevTools Performance tab for frontend bundle analysis - **Metrics to Compare**: - API latency: p50, p95, p99 for each endpoint - Database: CPU%, connection count, query execution time - Memory: heap usage, GC pause duration, RSS - Cost: API calls per request, DB I/O operations, compute hours - **Test Cases**: - Cart operations with 50+ items (stress N+1 fix) - Concurrent checkouts (100 simultaneous) to validate transaction refactoring - Product update during active cart session (cache invalidation test) - Network partition simulation for Redis failure fallback - Load test at 3x peak traffic to validate scalability improvements ## Proposed Code Changes ### `cart-service.js` - N+1 Query Fix ```javascript // BEFORE async function getCartWithItems(cartId) { const cart = await db.query('SELECT * FROM carts WHERE id = $1', [cartId]); const items = []; for (const item of cart.rows[0].items) { const product = await db.query('SELECT * FROM products WHERE id = $1', [item.product_id]); items.push({ ...item, product: product.rows[0] }); } return { ...cart.rows[0], items }; } // AFTER async function getCartWithItems(cartId) { const cart = await db.query('SELECT * FROM carts WHERE id = $1', [cartId]); const itemIds = cart.rows[0].items.map(i => i.product_id); const products = await db.query( 'SELECT * FROM products WHERE id = ANY($1)', [itemIds] ); const productMap = new Map(products.rows.map(p => [p.id, p])); const items = cart.rows[0].items.map(item => ({ ...item, product: productMap.get(item.product_id) })); return { ...cart.rows[0], items }; } ``` ### `product-service.js` - Redis Caching ```javascript // BEFORE async function getProductDetails(productId) { return await db.query('SELECT * FROM products WHERE id = $1', [productId]); } // AFTER const redis = require('redis'); const client = redis.createClient({ url: process.env.REDIS_URL }); async function getProductDetails(productId) { const cacheKey = `product:${productId}`; const cached = await client.get(cacheKey); if (cached) return JSON.parse(cached); const product = await db.query('SELECT * FROM products WHERE id = $1', [productId]); await client.setEx(cacheKey, 900, JSON.stringify(product.rows[0])); // 15-min TTL return product.rows[0]; } ``` ### `webpack.config.js` - Code Splitting ```javascript // BEFORE module.exports = { entry: './src/index.js', output: { filename: 'bundle.js' } }; // AFTER const React = require('react'); const { lazy } = React; module.exports = { entry: './src/index.js', output: { filename: '[name].[contenthash].js' }, optimization: { splitChunks: { chunks: 'all', cacheGroups: { vendor: { test: /[\\/]node_modules[\\/]/, name: 'vendors', chunks: 'all' } } } } }; // In React components const AdminPanel = lazy(() => import('./AdminPanel')); const OrderHistory = lazy(() => import('./OrderHistory')); ``` ### Database Migration - Index Addition ```sql -- BEFORE CREATE TABLE orders ( id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL, created_at TIMESTAMP DEFAULT NOW() ); -- AFTER CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC); ``` ## Commands ### Local Development ```bash # Run database migrations npm run migrate # Run load tests k6 run load-tests/checkout.js # Profile Node.js memory node --inspect --max-old-space-size=4096 src/server.js # Run frontend build with bundle analysis npm run build -- --profile ``` ### CI Pipeline ```bash # Add to CI configuration npm run lint npm test -- --coverage npm run build npm run benchmark -- --threshold=500ms ``` ### Production Validation ```bash # Enable query logging ALTER SYSTEM SET pg_stat_statements = on; # Monitor Redis cache hit rate redis-cli INFO stats | grep hits # Check connection pool usage SELECT count(*) FROM pg_stat_activity WHERE state = 'active'; ``` ## Quality Assurance Task Checklist - [x] All relevant optimization categories have been inspected (DB, Caching, Memory, Concurrency, Architecture) - [x] Each finding includes evidence, severity, concrete fix, and impact estimate - [x] Quick wins are separated from deeper optimizations by implementation effort - [x] Tradeoffs and risks are documented for every recommendation - [x] A validation plan with benchmarks and metrics exists - [x] Correctness is preserved in every proposed optimization - [x] Recommendations are prioritized by ROI for practical implementation