Master advanced database performance optimization techniques. Learn indexing strategies, query optimization, connection pooling, caching, and scaling patterns.
Master advanced database performance optimization techniques. Learn indexing strategies, query optimization, connection pooling, caching, and scaling patterns.
Master database indexing strategies and query optimization
Implement efficient connection pooling and resource management
Design comprehensive caching layers with Redis
Apply database monitoring and profiling techniques
Understand horizontal and vertical scaling patterns
Optimize database performance for high-traffic applications
Proper indexing is crucial for database performance. Learn how to design and implement effective indexing strategies.
Learn about MongoDB Indexing Optimization.
1const mongoose = require('mongoose');
2
3class MongoIndexOptimizer {
4 constructor() {
5 this.db = mongoose.connection;
6 }
7
8 // Comprehensive indexing strategy for e-commerce
9 async createOptimizedIndexes() {
10 try {
11 console.log('🔧 Creating optimized MongoDB indexes...');
12
13 // Users collection indexes
14 await this.db.collection('users').createIndexes([
15 // Single field indexes
16 { key: { email: 1 }, unique: true, background: true },
17 { key: { role: 1 }, background: true },
18 { key: { isActive: 1 }, background: true },
19 { key: { createdAt: -1 }, background: true },
20 { key: { lastLogin: -1 }, background: true },
21
22 // Compound indexes for common queries
23 { key: { role: 1, isActive: 1 }, background: true },
24 { key: { isActive: 1, createdAt: -1 }, background: true },
25
26 // Text search index
27 {
28 key: {
29 firstName: 'text',
30 lastName: 'text',
31 email: 'text'
32 },
33 name: 'user_search_index',
34 background: true,
35 weights: {
36 firstName: 10,
37 lastName: 10,
38 email: 5
39 }
40 },
41
42 // Geospatial index for location-based queries
43 { key: { 'address.location': '2dsphere' }, background: true },
44
45 // Sparse index for optional fields
46 {
47 key: { phoneNumber: 1 },
48 sparse: true,
49 background: true
50 }
51 ]);
52
53 // Products collection indexes
54 await this.db.collection('products').createIndexes([
55 // Core product indexes
56 { key: { sku: 1 }, unique: true, background: true },
57 { key: { slug: 1 }, unique: true, background: true },
58 { key: { categoryId: 1 }, background: true },
59 { key: { isActive: 1 }, background: true },
60 { key: { isFeatured: 1 }, background: true },
61 { key: { publishedAt: -1 }, background: true },
62
63 // Price and inventory indexes
64 { key: { price: 1 }, background: true },
65 { key: { inventoryQuantity: 1 }, background: true },
66
67 // Compound indexes for filtering
68 { key: { isActive: 1, isFeatured: 1 }, background: true },
69 { key: { categoryId: 1, isActive: 1 }, background: true },
70 { key: { categoryId: 1, price: 1 }, background: true },
71 { key: { isActive: 1, publishedAt: -1 }, background: true },
72
73 // Complex compound index for product listing
74 {
75 key: {
76 isActive: 1,
77 categoryId: 1,
78 isFeatured: -1,
79 publishedAt: -1
80 },
81 background: true
82 },
83
84 // Text search index for products
85 {
86 key: {
87 name: 'text',
88 description: 'text',
89 shortDescription: 'text'
90 },
91 name: 'product_search_index',
92 background: true,
93 weights: {
94 name: 20,
95 shortDescription: 10,
96 description: 5
97 }
98 },
99
100 // TTL index for draft products (auto-delete after 30 days)
101 {
102 key: { createdAt: 1 },
103 expireAfterSeconds: 2592000, // 30 days
104 partialFilterExpression: { status: 'draft' },
105 background: true
106 }
107 ]);
108
109 // Orders collection indexes
110 await this.db.collection('orders').createIndexes([
111 // Order identification
112 { key: { orderNumber: 1 }, unique: true, background: true },
113 { key: { userId: 1 }, background: true },
114
115 // Status and payment indexes
116 { key: { status: 1 }, background: true },
117 { key: { paymentStatus: 1 }, background: true },
118
119 // Date indexes for reporting
120 { key: { createdAt: -1 }, background: true },
121 { key: { shippedAt: -1 }, sparse: true, background: true },
122 { key: { deliveredAt: -1 }, sparse: true, background: true },
123
124 // Compound indexes for user orders
125 { key: { userId: 1, status: 1 }, background: true },
126 { key: { userId: 1, createdAt: -1 }, background: true },
127
128 // Admin dashboard indexes
129 { key: { status: 1, createdAt: -1 }, background: true },
130 { key: { paymentStatus: 1, createdAt: -1 }, background: true },
131
132 // Analytics indexes
133 { key: { createdAt: 1, totalAmount: 1 }, background: true }
134 ]);
135
136 // Reviews collection indexes
137 await this.db.collection('reviews').createIndexes([
138 // Basic review indexes
139 { key: { productId: 1 }, background: true },
140 { key: { userId: 1 }, background: true },
141 { key: { rating: 1 }, background: true },
142 { key: { isApproved: 1 }, background: true },
143 { key: { createdAt: -1 }, background: true },
144
145 // Compound indexes for product reviews
146 { key: { productId: 1, isApproved: 1, rating: -1 }, background: true },
147 { key: { productId: 1, isApproved: 1, createdAt: -1 }, background: true },
148
149 // User review history
150 { key: { userId: 1, createdAt: -1 }, background: true },
151
152 // Unique constraint for user-product reviews
153 {
154 key: { userId: 1, productId: 1 },
155 unique: true,
156 background: true
157 },
158
159 // Text search for review content
160 {
161 key: {
162 title: 'text',
163 content: 'text'
164 },
165 name: 'review_search_index',
166 background: true
167 }
168 ]);
169
170 console.log('✅ MongoDB indexes created successfully');
171
172 // Analyze index usage
173 await this.analyzeIndexUsage();
174
175 } catch (error) {
176 console.error('❌ Error creating indexes:', error);
177 throw error;
178 }
179 }
180
181 // Analyze index performance and usage
182 async analyzeIndexUsage() {
183 try {
184 const collections = ['users', 'products', 'orders', 'reviews'];
185
186 for (const collectionName of collections) {
187 console.log(\`\\n📊 Index analysis for \${collectionName}:\`);
188
189 // Get index stats
190 const stats = await this.db.collection(collectionName).indexStats().toArray();
191
192 stats.forEach(stat => {
193 console.log(\` Index: \${stat.name}\`);
194 console.log(\` Usage count: \${stat.accesses.ops}\`);
195 console.log(\` Since: \${stat.accesses.since}\`);
196 });
197
198 // Check for unused indexes
199 const unusedIndexes = stats.filter(stat => stat.accesses.ops === 0);
200 if (unusedIndexes.length > 0) {
201 console.log(\` ⚠️ Unused indexes: \${unusedIndexes.map(i => i.name).join(', ')}\`);
202 }
203 }
204
205 } catch (error) {
206 console.error('Error analyzing index usage:', error);
207 }
208 }
209
210 // Optimize queries based on explain plans
211 async optimizeQuery(collection, query, options = {}) {
212 try {
213 // Get explain plan
214 const explainResult = await this.db
215 .collection(collection)
216 .find(query, options)
217 .explain('executionStats');
218
219 const stats = explainResult.executionStats;
220
221 console.log(\`\\n🔍 Query analysis for \${collection}:\`);
222 console.log(\` Documents examined: \${stats.totalDocsExamined}\`);
223 console.log(\` Documents returned: \${stats.totalDocsReturned}\`);
224 console.log(\` Execution time: \${stats.executionTimeMillis}ms\`);
225 console.log(\` Index used: \${stats.winningPlan.inputStage?.indexName || 'NONE'}\`);
226
227 // Calculate efficiency ratio
228 const efficiency = stats.totalDocsReturned / (stats.totalDocsExamined || 1);
229 console.log(\` Efficiency ratio: \${(efficiency * 100).toFixed(2)}%\`);
230
231 // Optimization suggestions
232 if (efficiency < 0.1) {
233 console.log(' ⚠️ Low efficiency - consider adding indexes');
234 }
235
236 if (stats.totalDocsExamined > stats.totalDocsReturned * 10) {
237 console.log(' ⚠️ High document examination ratio - optimize indexes');
238 }
239
240 if (stats.executionTimeMillis > 100) {
241 console.log(' ⚠️ Slow query - consider optimization');
242 }
243
244 return {
245 efficiency,
246 executionTime: stats.executionTimeMillis,
247 indexUsed: stats.winningPlan.inputStage?.indexName,
248 docsExamined: stats.totalDocsExamined,
249 docsReturned: stats.totalDocsReturned
250 };
251
252 } catch (error) {
253 console.error('Error optimizing query:', error);
254 throw error;
255 }
256 }
257
258 // Monitor slow queries
259 async enableSlowQueryLogging() {
260 try {
261 // Enable MongoDB profiler for slow queries (>100ms)
262 await this.db.db.setProfilingLevel(1, { slowms: 100 });
263
264 console.log('✅ Slow query profiling enabled (>100ms)');
265
266 // Set up monitoring
267 this.monitorSlowQueries();
268
269 } catch (error) {
270 console.error('Error enabling profiling:', error);
271 }
272 }
273
274 async monitorSlowQueries() {
275 try {
276 // Check profiler collection every minute
277 setInterval(async () => {
278 const slowQueries = await this.db.db
279 .collection('system.profile')
280 .find({
281 ts: { $gte: new Date(Date.now() - 60000) }, // Last minute
282 millis: { $gte: 100 } // Slow queries
283 })
284 .sort({ ts: -1 })
285 .limit(10)
286 .toArray();
287
288 if (slowQueries.length > 0) {
289 console.log(\`\\n🐌 Found \${slowQueries.length} slow queries in the last minute:\`);
290
291 slowQueries.forEach((query, index) => {
292 console.log(\` \${index + 1}. \${query.command.find || query.command.aggregate || 'Unknown'}\`);
293 console.log(\` Duration: \${query.millis}ms\`);
294 console.log(\` Docs examined: \${query.docsExamined || 'N/A'}\`);
295 });
296 }
297 }, 60000); // Check every minute
298
299 } catch (error) {
300 console.error('Error monitoring slow queries:', error);
301 }
302 }
303}
304
305module.exports = MongoIndexOptimizer;Learn about PostgreSQL Query Optimization.
1const { Pool } = require('pg');
2
3class PostgreSQLOptimizer {
4 constructor() {
5 this.pool = new Pool({
6 host: process.env.DB_HOST,
7 port: process.env.DB_PORT,
8 database: process.env.DB_NAME,
9 user: process.env.DB_USER,
10 password: process.env.DB_PASSWORD,
11
12 // Optimized pool settings
13 max: 20,
14 min: 5,
15 idleTimeoutMillis: 30000,
16 connectionTimeoutMillis: 2000,
17
18 // Performance settings
19 statement_timeout: 30000,
20 query_timeout: 30000,
21 application_name: 'nodejs_optimizer'
22 });
23 }
24
25 // Create comprehensive indexes for PostgreSQL
26 async createOptimizedIndexes() {
27 const client = await this.pool.connect();
28
29 try {
30 console.log('🔧 Creating optimized PostgreSQL indexes...');
31
32 // Analyze table statistics first
33 await client.query('ANALYZE;');
34
35 // Users table indexes
36 await client.query(\`
37 -- Basic indexes
38 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users(email);
39 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_role ON users(role);
40 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_active ON users(is_active);
41 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_created ON users(created_at DESC);
42 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_last_login ON users(last_login DESC);
43
44 -- Compound indexes for common query patterns
45 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_role_active
46 ON users(role, is_active) WHERE is_active = true;
47
48 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_active_created
49 ON users(is_active, created_at DESC) WHERE is_active = true;
50
51 -- Partial indexes for specific conditions
52 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_unverified_email
53 ON users(email, created_at) WHERE is_email_verified = false;
54
55 -- GIN index for JSONB preferences
56 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_preferences_gin
57 ON users USING GIN(preferences);
58
59 -- Full-text search index
60 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_search
61 ON users USING GIN(to_tsvector('english', first_name || ' ' || last_name || ' ' || email));
62 \`);
63
64 // Products table indexes
65 await client.query(\`
66 -- Core product indexes
67 CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS idx_products_sku ON products(sku);
68 CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS idx_products_slug ON products(slug);
69 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_products_category ON products(category_id);
70 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_products_active ON products(is_active);
71 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_products_featured ON products(is_featured);
72 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_products_published ON products(published_at DESC);
73
74 -- Price range indexes
75 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_products_price ON products(price);
76 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_products_price_range
77 ON products(price) WHERE is_active = true AND published_at IS NOT NULL;
78
79 -- Inventory management
80 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_products_inventory
81 ON products(inventory_quantity) WHERE track_inventory = true;
82
83 -- Compound indexes for product listings
84 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_products_category_active
85 ON products(category_id, is_active, published_at DESC)
86 WHERE is_active = true AND published_at IS NOT NULL;
87
88 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_products_featured_published
89 ON products(is_featured DESC, published_at DESC)
90 WHERE is_active = true AND published_at IS NOT NULL;
91
92 -- Full-text search with ranking
93 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_products_search
94 ON products USING GIN(
95 setweight(to_tsvector('english', name), 'A') ||
96 setweight(to_tsvector('english', COALESCE(description, '')), 'B')
97 );
98
99 -- JSONB attributes index
100 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_products_attributes_gin
101 ON products USING GIN(attributes);
102 \`);
103
104 // Orders table indexes
105 await client.query(\`
106 -- Order identification and user relation
107 CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_number ON orders(order_number);
108 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_user ON orders(user_id);
109 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_created ON orders(created_at DESC);
110
111 -- Status tracking
112 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_status ON orders(status);
113 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_payment_status ON orders(payment_status);
114
115 -- Date-based queries
116 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_shipped ON orders(shipped_at DESC)
117 WHERE shipped_at IS NOT NULL;
118 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_delivered ON orders(delivered_at DESC)
119 WHERE delivered_at IS NOT NULL;
120
121 -- User order history
122 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_user_created
123 ON orders(user_id, created_at DESC);
124
125 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_user_status
126 ON orders(user_id, status);
127
128 -- Analytics and reporting
129 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_status_created
130 ON orders(status, created_at DESC);
131
132 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_amount_created
133 ON orders(total_amount, created_at DESC);
134
135 -- Monthly/weekly reports
136 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_monthly_reports
137 ON orders(date_trunc('month', created_at), status);
138 \`);
139
140 // Order items for analytics
141 await client.query(\`
142 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_order_items_order ON order_items(order_id);
143 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_order_items_product ON order_items(product_id);
144 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_order_items_variant ON order_items(product_variant_id)
145 WHERE product_variant_id IS NOT NULL;
146
147 -- Product sales analytics
148 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_order_items_product_analytics
149 ON order_items(product_id, quantity, total);
150 \`);
151
152 // Reviews table indexes
153 await client.query(\`
154 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_reviews_product ON reviews(product_id);
155 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_reviews_user ON reviews(user_id);
156 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_reviews_rating ON reviews(rating);
157 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_reviews_approved ON reviews(is_approved);
158 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_reviews_created ON reviews(created_at DESC);
159
160 -- Product review aggregation
161 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_reviews_product_approved
162 ON reviews(product_id, rating, is_approved) WHERE is_approved = true;
163
164 -- User review history
165 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_reviews_user_created
166 ON reviews(user_id, created_at DESC);
167
168 -- Unique constraint for user-product reviews
169 CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS idx_reviews_user_product_unique
170 ON reviews(user_id, product_id);
171
172 -- Full-text search on review content
173 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_reviews_search
174 ON reviews USING GIN(to_tsvector('english', title || ' ' || COALESCE(content, '')));
175 \`);
176
177 console.log('✅ PostgreSQL indexes created successfully');
178
179 // Update table statistics
180 await client.query('ANALYZE;');
181
182 } catch (error) {
183 console.error('❌ Error creating PostgreSQL indexes:', error);
184 throw error;
185 } finally {
186 client.release();
187 }
188 }
189
190 // Analyze query performance
191 async analyzeQuery(query, params = []) {
192 const client = await this.pool.connect();
193
194 try {
195 // Get query execution plan
196 const explainQuery = \`EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) \${query}\`;
197 const result = await client.query(explainQuery, params);
198
199 const plan = result.rows[0]['QUERY PLAN'][0];
200
201 console.log(\`\\n🔍 PostgreSQL Query Analysis:\`);
202 console.log(\` Execution Time: \${plan['Execution Time']}ms\`);
203 console.log(\` Planning Time: \${plan['Planning Time']}ms\`);
204
205 this.analyzePlanRecursive(plan.Plan, 0);
206
207 // Performance recommendations
208 this.generateRecommendations(plan);
209
210 return {
211 executionTime: plan['Execution Time'],
212 planningTime: plan['Planning Time'],
213 plan: plan.Plan
214 };
215
216 } catch (error) {
217 console.error('Error analyzing query:', error);
218 throw error;
219 } finally {
220 client.release();
221 }
222 }
223
224 analyzePlanRecursive(node, depth) {
225 const indent = ' '.repeat(depth + 1);
226
227 console.log(\`\${indent}Node Type: \${node['Node Type']}\`);
228 console.log(\`\${indent}Actual Time: \${node['Actual Total Time']}ms\`);
229 console.log(\`\${indent}Rows: \${node['Actual Rows']}\`);
230
231 if (node['Index Name']) {
232 console.log(\`\${indent}Index Used: \${node['Index Name']}\`);
233 }
234
235 if (node['Shared Hit Blocks']) {
236 console.log(\`\${indent}Buffer Hits: \${node['Shared Hit Blocks']}\`);
237 }
238
239 if (node['Shared Read Blocks']) {
240 console.log(\`\${indent}Buffer Reads: \${node['Shared Read Blocks']}\`);
241 }
242
243 // Analyze child plans
244 if (node.Plans) {
245 node.Plans.forEach(childPlan => {
246 this.analyzePlanRecursive(childPlan, depth + 1);
247 });
248 }
249 }
250
251 generateRecommendations(plan) {
252 const executionTime = plan['Execution Time'];
253 const planningTime = plan['Planning Time'];
254
255 console.log(\`\\n💡 Performance Recommendations:\`);
256
257 if (executionTime > 1000) {
258 console.log(' ⚠️ Slow query (>1s) - consider optimization');
259 }
260
261 if (planningTime > 100) {
262 console.log(' ⚠️ High planning time - consider using prepared statements');
263 }
264
265 if (this.hasSeqScan(plan.Plan)) {
266 console.log(' ⚠️ Sequential scan detected - consider adding indexes');
267 }
268
269 if (this.hasSort(plan.Plan)) {
270 console.log(' ⚠️ Sort operation detected - consider index ordering');
271 }
272 }
273
274 hasSeqScan(node) {
275 if (node['Node Type'] === 'Seq Scan') {
276 return true;
277 }
278
279 if (node.Plans) {
280 return node.Plans.some(child => this.hasSeqScan(child));
281 }
282
283 return false;
284 }
285
286 hasSort(node) {
287 if (node['Node Type'] === 'Sort') {
288 return true;
289 }
290
291 if (node.Plans) {
292 return node.Plans.some(child => this.hasSort(child));
293 }
294
295 return false;
296 }
297
298 // Monitor database performance
299 async enablePerformanceMonitoring() {
300 try {
301 // Enable pg_stat_statements for query statistics
302 await this.pool.query(\`
303 SELECT pg_stat_statements_reset();
304 \`);
305
306 console.log('✅ Performance monitoring enabled');
307
308 // Start monitoring loop
309 this.startPerformanceMonitoring();
310
311 } catch (error) {
312 console.error('Error enabling performance monitoring:', error);
313 }
314 }
315
316 async startPerformanceMonitoring() {
317 setInterval(async () => {
318 try {
319 // Get slow queries from the last 5 minutes
320 const slowQueries = await this.pool.query(\`
321 SELECT
322 query,
323 calls,
324 total_time,
325 mean_time,
326 rows,
327 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
328 FROM pg_stat_statements
329 WHERE mean_time > 100 -- Queries taking more than 100ms on average
330 ORDER BY mean_time DESC
331 LIMIT 10;
332 \`);
333
334 if (slowQueries.rows.length > 0) {
335 console.log(\`\\n🐌 Top slow queries (last 5 minutes):\`);
336
337 slowQueries.rows.forEach((query, index) => {
338 console.log(\` \${index + 1}. Query: \${query.query.substring(0, 100)}...\`);
339 console.log(\` Calls: \${query.calls}, Mean time: \${parseFloat(query.mean_time).toFixed(2)}ms\`);
340 console.log(\` Hit ratio: \${parseFloat(query.hit_percent || 0).toFixed(2)}%\`);
341 });
342 }
343
344 } catch (error) {
345 console.error('Error in performance monitoring:', error);
346 }
347 }, 300000); // Check every 5 minutes
348 }
349
350 // Vacuum and maintenance
351 async performMaintenance() {
352 const client = await this.pool.connect();
353
354 try {
355 console.log('🧹 Performing database maintenance...');
356
357 // Analyze all tables
358 await client.query('ANALYZE;');
359
360 // Get table sizes and bloat information
361 const tableStats = await client.query(\`
362 SELECT
363 schemaname,
364 tablename,
365 pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size,
366 pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size,
367 pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) as index_size
368 FROM pg_tables
369 WHERE schemaname = 'public'
370 ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
371 \`);
372
373 console.log('\\n📊 Table sizes:');
374 tableStats.rows.forEach(row => {
375 console.log(\` \${row.tablename}: \${row.size} (table: \${row.table_size}, indexes: \${row.index_size})\`);
376 });
377
378 // Check for unused indexes
379 const unusedIndexes = await client.query(\`
380 SELECT
381 schemaname,
382 tablename,
383 indexname,
384 pg_size_pretty(pg_relation_size(indexrelid)) as size
385 FROM pg_stat_user_indexes
386 WHERE idx_scan = 0
387 AND schemaname = 'public'
388 ORDER BY pg_relation_size(indexrelid) DESC;
389 \`);
390
391 if (unusedIndexes.rows.length > 0) {
392 console.log('\\n⚠️ Unused indexes found:');
393 unusedIndexes.rows.forEach(row => {
394 console.log(\` \${row.indexname} on \${row.tablename} (\${row.size})\`);
395 });
396 }
397
398 console.log('✅ Database maintenance completed');
399
400 } catch (error) {
401 console.error('Error performing maintenance:', error);
402 throw error;
403 } finally {
404 client.release();
405 }
406 }
407}
408
409module.exports = PostgreSQLOptimizer;Implement efficient connection pooling strategies for optimal database performance.
1class DatabaseConnectionManager {
2 constructor() {
3 this.pools = new Map();
4 this.connectionStats = {
5 totalConnections: 0,
6 activeConnections: 0,
7 idleConnections: 0,
8 waitingClients: 0
9 };
10
11 this.monitoring = {
12 enabled: false,
13 interval: null,
14 metrics: []
15 };
16 }
17
18 // Advanced MongoDB connection pool
19 async createMongoPool(connectionString, options = {}) {
20 const mongoose = require('mongoose');
21
22 const defaultOptions = {
23 // Connection pool settings
24 maxPoolSize: 10, // Maximum number of connections
25 minPoolSize: 2, // Minimum number of connections
26 maxIdleTimeMS: 30000, // Close connections after 30 seconds of inactivity
27 waitQueueTimeoutMS: 5000, // How long to wait for a connection
28
29 // Connection timeout settings
30 serverSelectionTimeoutMS: 5000, // How long to wait for server selection
31 socketTimeoutMS: 45000, // How long to wait for socket operations
32 connectTimeoutMS: 10000, // How long to wait for initial connection
33
34 // Heartbeat and monitoring
35 heartbeatFrequencyMS: 10000, // How often to check server health
36
37 // Write concern
38 w: 'majority',
39 retryWrites: true,
40
41 // Read preference
42 readPreference: 'primaryPreferred',
43
44 // Application identification
45 appName: 'nodejs-app',
46
47 // Compression
48 compressors: ['snappy', 'zlib'],
49
50 // Buffer management
51 bufferMaxEntries: 0, // Disable mongoose buffering
52
53 ...options
54 };
55
56 try {
57 const connection = await mongoose.createConnection(connectionString, defaultOptions);
58
59 // Set up connection event handlers
60 this.setupMongoEventHandlers(connection);
61
62 this.pools.set('mongodb', {
63 type: 'mongodb',
64 connection,
65 options: defaultOptions,
66 stats: {
67 created: Date.now(),
68 totalQueries: 0,
69 slowQueries: 0,
70 errors: 0
71 }
72 });
73
74 console.log('✅ MongoDB connection pool created');
75 return connection;
76
77 } catch (error) {
78 console.error('❌ Failed to create MongoDB pool:', error);
79 throw error;
80 }
81 }
82
83 setupMongoEventHandlers(connection) {
84 connection.on('connected', () => {
85 console.log('🔗 MongoDB connected');
86 this.updateConnectionStats();
87 });
88
89 connection.on('disconnected', () => {
90 console.log('🔌 MongoDB disconnected');
91 this.updateConnectionStats();
92 });
93
94 connection.on('error', (error) => {
95 console.error('❌ MongoDB connection error:', error);
96 this.pools.get('mongodb').stats.errors++;
97 });
98
99 connection.on('reconnected', () => {
100 console.log('🔄 MongoDB reconnected');
101 });
102
103 // Monitor slow operations
104 connection.on('slow', (event) => {
105 console.warn(\`🐌 Slow MongoDB operation: \${event.duration}ms\`);
106 this.pools.get('mongodb').stats.slowQueries++;
107 });
108 }
109
110 // Advanced PostgreSQL connection pool
111 async createPostgresPool(config, options = {}) {
112 const { Pool } = require('pg');
113
114 const defaultConfig = {
115 // Basic connection
116 host: config.host || 'localhost',
117 port: config.port || 5432,
118 database: config.database,
119 user: config.user,
120 password: config.password,
121
122 // Pool configuration
123 max: 20, // Maximum number of connections
124 min: 5, // Minimum number of connections
125 idleTimeoutMillis: 30000, // How long a client can sit idle
126 connectionTimeoutMillis: 2000, // How long to wait for connection
127 acquireTimeoutMillis: 60000, // How long to wait to acquire connection
128
129 // Connection settings
130 statement_timeout: 30000, // Cancel queries after 30 seconds
131 query_timeout: 30000,
132 idle_in_transaction_session_timeout: 60000,
133
134 // SSL configuration
135 ssl: process.env.NODE_ENV === 'production' ? {
136 rejectUnauthorized: false
137 } : false,
138
139 // Application name for monitoring
140 application_name: 'nodejs-app',
141
142 // Keep alive settings
143 keepAlive: true,
144 keepAliveInitialDelayMillis: 10000,
145
146 ...options
147 };
148
149 try {
150 const pool = new Pool(defaultConfig);
151
152 // Set up pool event handlers
153 this.setupPostgresEventHandlers(pool);
154
155 // Test connection
156 const client = await pool.connect();
157 const result = await client.query('SELECT NOW() as server_time, version() as version');
158 client.release();
159
160 console.log(\`✅ PostgreSQL pool created - Server time: \${result.rows[0].server_time}\`);
161
162 this.pools.set('postgresql', {
163 type: 'postgresql',
164 pool,
165 config: defaultConfig,
166 stats: {
167 created: Date.now(),
168 totalQueries: 0,
169 slowQueries: 0,
170 errors: 0,
171 connections: {
172 total: 0,
173 idle: 0,
174 waiting: 0
175 }
176 }
177 });
178
179 return pool;
180
181 } catch (error) {
182 console.error('❌ Failed to create PostgreSQL pool:', error);
183 throw error;
184 }
185 }
186
187 setupPostgresEventHandlers(pool) {
188 pool.on('connect', (client) => {
189 console.log(\`🔗 PostgreSQL client connected (PID: \${client.processID})\`);
190 this.updateConnectionStats();
191 });
192
193 pool.on('remove', (client) => {
194 console.log(\`🔌 PostgreSQL client removed (PID: \${client.processID})\`);
195 this.updateConnectionStats();
196 });
197
198 pool.on('error', (err, client) => {
199 console.error('❌ PostgreSQL pool error:', err);
200 this.pools.get('postgresql').stats.errors++;
201 });
202
203 pool.on('acquire', (client) => {
204 // Client acquired from pool
205 this.updateConnectionStats();
206 });
207
208 pool.on('release', (client) => {
209 // Client released back to pool
210 this.updateConnectionStats();
211 });
212 }
213
214 // Advanced Redis connection pool
215 async createRedisPool(config, options = {}) {
216 const Redis = require('ioredis');
217
218 const defaultConfig = {
219 host: config.host || 'localhost',
220 port: config.port || 6379,
221 password: config.password,
222 db: config.db || 0,
223
224 // Connection pool settings
225 maxRetriesPerRequest: 3,
226 retryDelayOnFailover: 100,
227 enableReadyCheck: false,
228 lazyConnect: true,
229
230 // Connection timeout
231 connectTimeout: 10000,
232 commandTimeout: 5000,
233
234 // Keep alive
235 keepAlive: 30000,
236
237 // Reconnection
238 maxRetriesPerRequest: 3,
239 retryDelayOnFailover: 100,
240
241 ...options
242 };
243
244 try {
245 // Create Redis cluster or standalone instance
246 let redis;
247
248 if (config.cluster) {
249 redis = new Redis.Cluster(config.cluster, {
250 ...defaultConfig,
251 scaleReads: 'slave',
252 maxRedirections: 16,
253 retryDelayOnFailover: 100,
254 slotsRefreshTimeout: 10000
255 });
256 } else {
257 redis = new Redis(defaultConfig);
258 }
259
260 // Set up Redis event handlers
261 this.setupRedisEventHandlers(redis);
262
263 // Test connection
264 await redis.ping();
265
266 this.pools.set('redis', {
267 type: 'redis',
268 client: redis,
269 config: defaultConfig,
270 stats: {
271 created: Date.now(),
272 totalCommands: 0,
273 slowCommands: 0,
274 errors: 0
275 }
276 });
277
278 console.log('✅ Redis connection pool created');
279 return redis;
280
281 } catch (error) {
282 console.error('❌ Failed to create Redis pool:', error);
283 throw error;
284 }
285 }
286
287 setupRedisEventHandlers(redis) {
288 redis.on('connect', () => {
289 console.log('🔗 Redis connected');
290 });
291
292 redis.on('ready', () => {
293 console.log('✅ Redis ready');
294 });
295
296 redis.on('error', (error) => {
297 console.error('❌ Redis error:', error);
298 this.pools.get('redis').stats.errors++;
299 });
300
301 redis.on('close', () => {
302 console.log('🔌 Redis connection closed');
303 });
304
305 redis.on('reconnecting', () => {
306 console.log('🔄 Redis reconnecting');
307 });
308
309 // Monitor slow commands
310 redis.on('slow', (command) => {
311 console.warn(\`🐌 Slow Redis command: \${command.command} took \${command.duration}ms\`);
312 this.pools.get('redis').stats.slowCommands++;
313 });
314 }
315
316 // Update connection statistics
317 updateConnectionStats() {
318 this.connectionStats = {
319 totalConnections: 0,
320 activeConnections: 0,
321 idleConnections: 0,
322 waitingClients: 0
323 };
324
325 // Calculate stats for each pool
326 this.pools.forEach((pool, name) => {
327 if (pool.type === 'postgresql') {
328 this.connectionStats.totalConnections += pool.pool.totalCount || 0;
329 this.connectionStats.idleConnections += pool.pool.idleCount || 0;
330 this.connectionStats.waitingClients += pool.pool.waitingCount || 0;
331 }
332 // Add similar calculations for other pool types
333 });
334
335 this.connectionStats.activeConnections =
336 this.connectionStats.totalConnections - this.connectionStats.idleConnections;
337 }
338
339 // Start monitoring
340 startMonitoring(intervalMs = 30000) {
341 if (this.monitoring.enabled) {
342 return;
343 }
344
345 this.monitoring.enabled = true;
346 this.monitoring.interval = setInterval(() => {
347 this.collectMetrics();
348 }, intervalMs);
349
350 console.log(\`📊 Connection monitoring started (interval: \${intervalMs}ms)\`);
351 }
352
353 // Collect performance metrics
354 collectMetrics() {
355 const timestamp = Date.now();
356 const metrics = {
357 timestamp,
358 connections: { ...this.connectionStats },
359 pools: {}
360 };
361
362 this.pools.forEach((pool, name) => {
363 metrics.pools[name] = {
364 type: pool.type,
365 stats: { ...pool.stats }
366 };
367
368 if (pool.type === 'postgresql') {
369 metrics.pools[name].poolStats = {
370 total: pool.pool.totalCount,
371 idle: pool.pool.idleCount,
372 waiting: pool.pool.waitingCount
373 };
374 }
375 });
376
377 this.monitoring.metrics.push(metrics);
378
379 // Keep only last 100 metrics (to prevent memory bloat)
380 if (this.monitoring.metrics.length > 100) {
381 this.monitoring.metrics = this.monitoring.metrics.slice(-100);
382 }
383
384 // Log warnings for concerning metrics
385 this.checkHealthThresholds(metrics);
386 }
387
388 checkHealthThresholds(metrics) {
389 // Check high connection usage
390 if (metrics.connections.activeConnections > 15) {
391 console.warn(\`⚠️ High connection usage: \${metrics.connections.activeConnections} active connections\`);
392 }
393
394 // Check waiting clients
395 if (metrics.connections.waitingClients > 5) {
396 console.warn(\`⚠️ Clients waiting for connections: \${metrics.connections.waitingClients}\`);
397 }
398
399 // Check error rates
400 this.pools.forEach((pool, name) => {
401 if (pool.stats.errors > 10) {
402 console.warn(\`⚠️ High error rate in \${name} pool: \${pool.stats.errors} errors\`);
403 }
404
405 if (pool.stats.slowQueries > 5) {
406 console.warn(\`⚠️ High slow query rate in \${name} pool: \${pool.stats.slowQueries} slow queries\`);
407 }
408 });
409 }
410
411 // Get pool statistics
412 getPoolStats() {
413 const stats = {};
414
415 this.pools.forEach((pool, name) => {
416 stats[name] = {
417 type: pool.type,
418 uptime: Date.now() - pool.stats.created,
419 ...pool.stats
420 };
421
422 if (pool.type === 'postgresql') {
423 stats[name].connections = {
424 total: pool.pool.totalCount,
425 idle: pool.pool.idleCount,
426 waiting: pool.pool.waitingCount
427 };
428 }
429 });
430
431 return {
432 overview: this.connectionStats,
433 pools: stats,
434 monitoring: {
435 enabled: this.monitoring.enabled,
436 metricsCount: this.monitoring.metrics.length
437 }
438 };
439 }
440
441 // Graceful shutdown
442 async shutdown() {
443 console.log('🔄 Shutting down connection pools...');
444
445 if (this.monitoring.enabled) {
446 clearInterval(this.monitoring.interval);
447 this.monitoring.enabled = false;
448 }
449
450 const shutdownPromises = [];
451
452 this.pools.forEach((pool, name) => {
453 if (pool.type === 'postgresql') {
454 shutdownPromises.push(pool.pool.end());
455 } else if (pool.type === 'mongodb') {
456 shutdownPromises.push(pool.connection.close());
457 } else if (pool.type === 'redis') {
458 shutdownPromises.push(pool.client.quit());
459 }
460 });
461
462 try {
463 await Promise.all(shutdownPromises);
464 console.log('✅ All connection pools closed gracefully');
465 } catch (error) {
466 console.error('❌ Error closing connection pools:', error);
467 }
468
469 this.pools.clear();
470 }
471}
472
473module.exports = DatabaseConnectionManager;Implement comprehensive caching strategies using Redis for optimal performance.
1const Redis = require('ioredis');
2
3class RedisCacheManager {
4 constructor(config = {}) {
5 this.config = {
6 host: config.host || process.env.REDIS_HOST || 'localhost',
7 port: config.port || process.env.REDIS_PORT || 6379,
8 password: config.password || process.env.REDIS_PASSWORD,
9 db: config.db || 0,
10 keyPrefix: config.keyPrefix || 'app:',
11 defaultTTL: config.defaultTTL || 3600, // 1 hour
12 ...config
13 };
14
15 this.redis = null;
16 this.stats = {
17 hits: 0,
18 misses: 0,
19 sets: 0,
20 deletes: 0,
21 errors: 0
22 };
23
24 this.patterns = {
25 USER: 'user:',
26 PRODUCT: 'product:',
27 ORDER: 'order:',
28 SESSION: 'session:',
29 ANALYTICS: 'analytics:',
30 SEARCH: 'search:',
31 API_RESPONSE: 'api:',
32 RATE_LIMIT: 'rate_limit:'
33 };
34 }
35
36 async connect() {
37 try {
38 this.redis = new Redis({
39 ...this.config,
40 retryDelayOnFailover: 100,
41 enableReadyCheck: false,
42 lazyConnect: true,
43 maxRetriesPerRequest: 3
44 });
45
46 // Set up event handlers
47 this.setupEventHandlers();
48
49 // Test connection
50 await this.redis.ping();
51 console.log('✅ Redis cache manager connected');
52
53 return this.redis;
54
55 } catch (error) {
56 console.error('❌ Failed to connect to Redis:', error);
57 throw error;
58 }
59 }
60
61 setupEventHandlers() {
62 this.redis.on('error', (error) => {
63 console.error('❌ Redis cache error:', error);
64 this.stats.errors++;
65 });
66
67 this.redis.on('connect', () => {
68 console.log('🔗 Redis cache connected');
69 });
70
71 this.redis.on('ready', () => {
72 console.log('✅ Redis cache ready');
73 });
74
75 this.redis.on('close', () => {
76 console.log('🔌 Redis cache connection closed');
77 });
78 }
79
80 // Advanced caching methods
81 async get(key, options = {}) {
82 try {
83 const fullKey = this.buildKey(key);
84 const cached = await this.redis.get(fullKey);
85
86 if (cached !== null) {
87 this.stats.hits++;
88
89 // Handle compressed data
90 if (options.compressed) {
91 return this.decompress(cached);
92 }
93
94 // Parse JSON if needed
95 if (options.json !== false) {
96 try {
97 return JSON.parse(cached);
98 } catch {
99 return cached;
100 }
101 }
102
103 return cached;
104 }
105
106 this.stats.misses++;
107 return null;
108
109 } catch (error) {
110 console.error(\`Cache get error for key \${key}:\`, error);
111 this.stats.errors++;
112 return null;
113 }
114 }
115
116 async set(key, value, options = {}) {
117 try {
118 const fullKey = this.buildKey(key);
119 const ttl = options.ttl || this.config.defaultTTL;
120
121 let serializedValue;
122
123 // Handle different value types
124 if (typeof value === 'object') {
125 serializedValue = JSON.stringify(value);
126 } else {
127 serializedValue = String(value);
128 }
129
130 // Compress large values
131 if (options.compress && serializedValue.length > 1024) {
132 serializedValue = await this.compress(serializedValue);
133 }
134
135 // Set with TTL
136 if (ttl > 0) {
137 await this.redis.setex(fullKey, ttl, serializedValue);
138 } else {
139 await this.redis.set(fullKey, serializedValue);
140 }
141
142 this.stats.sets++;
143 return true;
144
145 } catch (error) {
146 console.error(\`Cache set error for key \${key}:\`, error);
147 this.stats.errors++;
148 return false;
149 }
150 }
151
152 async mget(keys) {
153 try {
154 const fullKeys = keys.map(key => this.buildKey(key));
155 const values = await this.redis.mget(...fullKeys);
156
157 const result = {};
158 keys.forEach((key, index) => {
159 const value = values[index];
160 if (value !== null) {
161 try {
162 result[key] = JSON.parse(value);
163 this.stats.hits++;
164 } catch {
165 result[key] = value;
166 this.stats.hits++;
167 }
168 } else {
169 this.stats.misses++;
170 }
171 });
172
173 return result;
174
175 } catch (error) {
176 console.error('Cache mget error:', error);
177 this.stats.errors++;
178 return {};
179 }
180 }
181
182 async mset(keyValues, ttl = null) {
183 try {
184 const pipeline = this.redis.pipeline();
185
186 Object.entries(keyValues).forEach(([key, value]) => {
187 const fullKey = this.buildKey(key);
188 const serializedValue = typeof value === 'object'
189 ? JSON.stringify(value)
190 : String(value);
191
192 if (ttl) {
193 pipeline.setex(fullKey, ttl, serializedValue);
194 } else {
195 pipeline.set(fullKey, serializedValue);
196 }
197 });
198
199 await pipeline.exec();
200 this.stats.sets += Object.keys(keyValues).length;
201 return true;
202
203 } catch (error) {
204 console.error('Cache mset error:', error);
205 this.stats.errors++;
206 return false;
207 }
208 }
209
210 async del(key) {
211 try {
212 const fullKey = this.buildKey(key);
213 const result = await this.redis.del(fullKey);
214
215 if (result > 0) {
216 this.stats.deletes++;
217 }
218
219 return result > 0;
220
221 } catch (error) {
222 console.error(\`Cache delete error for key \${key}:\`, error);
223 this.stats.errors++;
224 return false;
225 }
226 }
227
228 async exists(key) {
229 try {
230 const fullKey = this.buildKey(key);
231 return await this.redis.exists(fullKey) === 1;
232 } catch (error) {
233 console.error(\`Cache exists error for key \${key}:\`, error);
234 return false;
235 }
236 }
237
238 async ttl(key) {
239 try {
240 const fullKey = this.buildKey(key);
241 return await this.redis.ttl(fullKey);
242 } catch (error) {
243 console.error(\`Cache TTL error for key \${key}:\`, error);
244 return -1;
245 }
246 }
247
248 async expire(key, seconds) {
249 try {
250 const fullKey = this.buildKey(key);
251 return await this.redis.expire(fullKey, seconds) === 1;
252 } catch (error) {
253 console.error(\`Cache expire error for key \${key}:\`, error);
254 return false;
255 }
256 }
257
258 // Advanced patterns
259 async getOrSet(key, fetchFunction, options = {}) {
260 const cached = await this.get(key, options);
261
262 if (cached !== null) {
263 return cached;
264 }
265
266 try {
267 const value = await fetchFunction();
268 await this.set(key, value, options);
269 return value;
270 } catch (error) {
271 console.error('getOrSet fetch error:', error);
272 throw error;
273 }
274 }
275
276 async invalidatePattern(pattern) {
277 try {
278 const fullPattern = this.buildKey(pattern);
279 const keys = await this.redis.keys(fullPattern);
280
281 if (keys.length > 0) {
282 await this.redis.del(...keys);
283 this.stats.deletes += keys.length;
284 console.log(\`🗑️ Invalidated \${keys.length} keys matching pattern: \${pattern}\`);
285 }
286
287 return keys.length;
288
289 } catch (error) {
290 console.error(\`Cache invalidate pattern error for \${pattern}:\`, error);
291 this.stats.errors++;
292 return 0;
293 }
294 }
295
296 // Cache warming
297 async warmCache(warmingStrategies) {
298 console.log('🔥 Starting cache warming...');
299
300 const results = {
301 success: 0,
302 failed: 0,
303 total: warmingStrategies.length
304 };
305
306 for (const strategy of warmingStrategies) {
307 try {
308 await this.executeWarmingStrategy(strategy);
309 results.success++;
310 } catch (error) {
311 console.error(\`Cache warming failed for \${strategy.name}:\`, error);
312 results.failed++;
313 }
314 }
315
316 console.log(\`✅ Cache warming completed: \${results.success}/\${results.total} successful\`);
317 return results;
318 }
319
320 async executeWarmingStrategy(strategy) {
321 const { name, keys, fetchFunction, ttl } = strategy;
322
323 console.log(\` Warming \${name}...\`);
324
325 for (const key of keys) {
326 const exists = await this.exists(key);
327
328 if (!exists) {
329 try {
330 const value = await fetchFunction(key);
331 await this.set(key, value, { ttl });
332 } catch (error) {
333 console.error(\` Failed to warm key \${key}:\`, error);
334 }
335 }
336 }
337 }
338
339 // Rate limiting
340 async rateLimit(identifier, options = {}) {
341 const {
342 limit = 100,
343 window = 3600, // 1 hour
344 keyPrefix = 'rate_limit'
345 } = options;
346
347 try {
348 const key = \`\${keyPrefix}:\${identifier}\`;
349 const current = await this.redis.incr(this.buildKey(key));
350
351 if (current === 1) {
352 await this.redis.expire(this.buildKey(key), window);
353 }
354
355 const remaining = Math.max(0, limit - current);
356 const resetTime = await this.redis.ttl(this.buildKey(key));
357
358 return {
359 allowed: current <= limit,
360 limit,
361 remaining,
362 resetTime: resetTime > 0 ? Date.now() + (resetTime * 1000) : null,
363 current
364 };
365
366 } catch (error) {
367 console.error('Rate limit error:', error);
368 // Fail open - allow request if cache is down
369 return {
370 allowed: true,
371 limit,
372 remaining: limit,
373 resetTime: null,
374 current: 0
375 };
376 }
377 }
378
379 // Session management
380 async setSession(sessionId, data, ttl = 86400) { // 24 hours
381 const key = \`\${this.patterns.SESSION}\${sessionId}\`;
382 return await this.set(key, data, { ttl });
383 }
384
385 async getSession(sessionId) {
386 const key = \`\${this.patterns.SESSION}\${sessionId}\`;
387 return await this.get(key);
388 }
389
390 async deleteSession(sessionId) {
391 const key = \`\${this.patterns.SESSION}\${sessionId}\`;
392 return await this.del(key);
393 }
394
395 // Analytics caching
396 async cacheAnalytics(analyticsKey, data, ttl = 1800) { // 30 minutes
397 const key = \`\${this.patterns.ANALYTICS}\${analyticsKey}\`;
398 return await this.set(key, data, { ttl });
399 }
400
401 async getAnalytics(analyticsKey) {
402 const key = \`\${this.patterns.ANALYTICS}\${analyticsKey}\`;
403 return await this.get(key);
404 }
405
406 // Search result caching
407 async cacheSearchResults(query, results, ttl = 1800) { // 30 minutes
408 const key = \`\${this.patterns.SEARCH}\${this.hashQuery(query)}\`;
409 return await this.set(key, results, { ttl });
410 }
411
412 async getSearchResults(query) {
413 const key = \`\${this.patterns.SEARCH}\${this.hashQuery(query)}\`;
414 return await this.get(key);
415 }
416
417 // API response caching
418 async cacheAPIResponse(endpoint, params, response, ttl = 300) { // 5 minutes
419 const key = \`\${this.patterns.API_RESPONSE}\${this.hashAPIKey(endpoint, params)}\`;
420 return await this.set(key, response, { ttl });
421 }
422
423 async getAPIResponse(endpoint, params) {
424 const key = \`\${this.patterns.API_RESPONSE}\${this.hashAPIKey(endpoint, params)}\`;
425 return await this.get(key);
426 }
427
428 // Helper methods
429 buildKey(key) {
430 return \`\${this.config.keyPrefix}\${key}\`;
431 }
432
433 hashQuery(query) {
434 const crypto = require('crypto');
435 return crypto.createHash('md5').update(JSON.stringify(query)).digest('hex');
436 }
437
438 hashAPIKey(endpoint, params) {
439 const crypto = require('crypto');
440 const combined = \`\${endpoint}:\${JSON.stringify(params || {})}\`;
441 return crypto.createHash('md5').update(combined).digest('hex');
442 }
443
444 async compress(data) {
445 const zlib = require('zlib');
446 return zlib.gzipSync(data).toString('base64');
447 }
448
449 async decompress(data) {
450 const zlib = require('zlib');
451 const buffer = Buffer.from(data, 'base64');
452 return zlib.gunzipSync(buffer).toString();
453 }
454
455 // Statistics and monitoring
456 getStats() {
457 const hitRate = this.stats.hits / (this.stats.hits + this.stats.misses) * 100;
458
459 return {
460 ...this.stats,
461 hitRate: hitRate.toFixed(2) + '%',
462 total: this.stats.hits + this.stats.misses
463 };
464 }
465
466 resetStats() {
467 this.stats = {
468 hits: 0,
469 misses: 0,
470 sets: 0,
471 deletes: 0,
472 errors: 0
473 };
474 }
475
476 async getRedisInfo() {
477 try {
478 const info = await this.redis.info();
479 return this.parseRedisInfo(info);
480 } catch (error) {
481 console.error('Error getting Redis info:', error);
482 return null;
483 }
484 }
485
486 parseRedisInfo(info) {
487 const sections = {};
488 let currentSection = null;
489
490 info.split('\\r\\n').forEach(line => {
491 if (line.startsWith('#')) {
492 currentSection = line.slice(2).toLowerCase();
493 sections[currentSection] = {};
494 } else if (line.includes(':') && currentSection) {
495 const [key, value] = line.split(':');
496 sections[currentSection][key] = value;
497 }
498 });
499
500 return sections;
501 }
502
503 async close() {
504 if (this.redis) {
505 await this.redis.quit();
506 console.log('✅ Redis cache manager disconnected');
507 }
508 }
509}
510
511module.exports = RedisCacheManager;Implement comprehensive monitoring and profiling tools for database performance analysis.
1class DatabaseMonitor {
2 constructor() {
3 this.metrics = {
4 queries: [],
5 connections: [],
6 performance: [],
7 errors: []
8 };
9
10 this.thresholds = {
11 slowQueryMs: 1000,
12 highConnectionCount: 15,
13 errorRate: 0.05, // 5%
14 memoryUsagePercent: 80
15 };
16
17 this.alerts = [];
18 this.monitoring = false;
19 this.interval = null;
20 }
21
22 async startMonitoring(config = {}) {
23 if (this.monitoring) {
24 console.log('Database monitoring already running');
25 return;
26 }
27
28 this.config = {
29 interval: 30000, // 30 seconds
30 retentionPeriod: 3600000, // 1 hour
31 enableSlowQueryLogging: true,
32 enablePerformanceMetrics: true,
33 enableErrorTracking: true,
34 ...config
35 };
36
37 this.monitoring = true;
38
39 console.log('📊 Starting database monitoring...');
40
41 // Start monitoring intervals
42 this.startQueryMonitoring();
43 this.startConnectionMonitoring();
44 this.startPerformanceMonitoring();
45 this.startErrorMonitoring();
46
47 // Start cleanup interval
48 this.startCleanupInterval();
49
50 console.log(\`✅ Database monitoring started (interval: \${this.config.interval}ms)\`);
51 }
52
53 stopMonitoring() {
54 if (!this.monitoring) {
55 return;
56 }
57
58 this.monitoring = false;
59
60 if (this.interval) {
61 clearInterval(this.interval);
62 }
63
64 console.log('🛑 Database monitoring stopped');
65 }
66
67 // Query monitoring
68 startQueryMonitoring() {
69 // Hook into database query execution
70 this.instrumentQueryExecution();
71 }
72
73 instrumentQueryExecution() {
74 // MongoDB query instrumentation
75 const mongoose = require('mongoose');
76
77 mongoose.connection.on('query', (query) => {
78 const startTime = Date.now();
79
80 query.on('end', () => {
81 const duration = Date.now() - startTime;
82
83 this.recordQuery({
84 type: 'mongodb',
85 operation: query.op,
86 collection: query.collection,
87 duration,
88 timestamp: Date.now()
89 });
90 });
91 });
92
93 // PostgreSQL query instrumentation would go here
94 // This would require hooking into the pg library
95 }
96
97 recordQuery(queryInfo) {
98 const metric = {
99 id: this.generateId(),
100 ...queryInfo,
101 slow: queryInfo.duration > this.thresholds.slowQueryMs
102 };
103
104 this.metrics.queries.push(metric);
105
106 // Check for slow query alert
107 if (metric.slow) {
108 this.generateAlert({
109 type: 'slow_query',
110 severity: 'warning',
111 message: \`Slow query detected: \${queryInfo.operation} took \${queryInfo.duration}ms\`,
112 metadata: metric
113 });
114 }
115
116 // Cleanup old metrics
117 this.cleanupMetrics('queries');
118 }
119
120 // Connection monitoring
121 startConnectionMonitoring() {
122 setInterval(() => {
123 this.collectConnectionMetrics();
124 }, this.config.interval);
125 }
126
127 async collectConnectionMetrics() {
128 try {
129 const metrics = {
130 timestamp: Date.now(),
131 mongodb: await this.getMongoConnectionMetrics(),
132 postgresql: await this.getPostgresConnectionMetrics(),
133 redis: await this.getRedisConnectionMetrics()
134 };
135
136 this.metrics.connections.push(metrics);
137
138 // Check connection thresholds
139 this.checkConnectionThresholds(metrics);
140
141 this.cleanupMetrics('connections');
142
143 } catch (error) {
144 console.error('Error collecting connection metrics:', error);
145 }
146 }
147
148 async getMongoConnectionMetrics() {
149 try {
150 const mongoose = require('mongoose');
151 const db = mongoose.connection.db;
152
153 if (!db) return null;
154
155 const stats = await db.admin().serverStatus();
156
157 return {
158 connections: {
159 current: stats.connections.current,
160 available: stats.connections.available,
161 totalCreated: stats.connections.totalCreated
162 },
163 memory: {
164 resident: stats.mem.resident,
165 virtual: stats.mem.virtual,
166 mapped: stats.mem.mapped
167 },
168 operations: {
169 insert: stats.opcounters.insert,
170 query: stats.opcounters.query,
171 update: stats.opcounters.update,
172 delete: stats.opcounters.delete
173 }
174 };
175
176 } catch (error) {
177 console.error('Error getting MongoDB metrics:', error);
178 return null;
179 }
180 }
181
182 async getPostgresConnectionMetrics() {
183 try {
184 // This would require access to the PostgreSQL pool
185 // Implementation depends on your connection manager
186 return {
187 connections: {
188 total: 0,
189 idle: 0,
190 active: 0,
191 waiting: 0
192 }
193 };
194
195 } catch (error) {
196 console.error('Error getting PostgreSQL metrics:', error);
197 return null;
198 }
199 }
200
201 async getRedisConnectionMetrics() {
202 try {
203 // This would require access to the Redis client
204 // Implementation depends on your Redis setup
205 return {
206 connections: {
207 connected: true,
208 commands: 0
209 }
210 };
211
212 } catch (error) {
213 console.error('Error getting Redis metrics:', error);
214 return null;
215 }
216 }
217
218 checkConnectionThresholds(metrics) {
219 // Check MongoDB connections
220 if (metrics.mongodb?.connections?.current > this.thresholds.highConnectionCount) {
221 this.generateAlert({
222 type: 'high_connections',
223 severity: 'warning',
224 message: \`High MongoDB connection count: \${metrics.mongodb.connections.current}\`,
225 metadata: { connections: metrics.mongodb.connections }
226 });
227 }
228
229 // Add similar checks for other databases
230 }
231
232 // Performance monitoring
233 startPerformanceMonitoring() {
234 setInterval(() => {
235 this.collectPerformanceMetrics();
236 }, this.config.interval);
237 }
238
239 async collectPerformanceMetrics() {
240 try {
241 const metrics = {
242 timestamp: Date.now(),
243 system: await this.getSystemMetrics(),
244 database: await this.getDatabasePerformanceMetrics()
245 };
246
247 this.metrics.performance.push(metrics);
248 this.cleanupMetrics('performance');
249
250 } catch (error) {
251 console.error('Error collecting performance metrics:', error);
252 }
253 }
254
255 async getSystemMetrics() {
256 const os = require('os');
257 const process = require('process');
258
259 return {
260 cpu: {
261 usage: process.cpuUsage(),
262 loadAvg: os.loadavg()
263 },
264 memory: {
265 total: os.totalmem(),
266 free: os.freemem(),
267 used: os.totalmem() - os.freemem(),
268 process: process.memoryUsage()
269 },
270 uptime: {
271 system: os.uptime(),
272 process: process.uptime()
273 }
274 };
275 }
276
277 async getDatabasePerformanceMetrics() {
278 return {
279 queryCount: this.metrics.queries.length,
280 slowQueryCount: this.metrics.queries.filter(q => q.slow).length,
281 averageQueryTime: this.calculateAverageQueryTime(),
282 errorRate: this.calculateErrorRate()
283 };
284 }
285
286 // Error monitoring
287 startErrorMonitoring() {
288 // Hook into error events
289 process.on('uncaughtException', (error) => {
290 this.recordError({
291 type: 'uncaught_exception',
292 message: error.message,
293 stack: error.stack,
294 timestamp: Date.now()
295 });
296 });
297
298 process.on('unhandledRejection', (reason, promise) => {
299 this.recordError({
300 type: 'unhandled_rejection',
301 message: String(reason),
302 promise: promise.toString(),
303 timestamp: Date.now()
304 });
305 });
306 }
307
308 recordError(errorInfo) {
309 const error = {
310 id: this.generateId(),
311 ...errorInfo
312 };
313
314 this.metrics.errors.push(error);
315
316 // Generate alert for critical errors
317 this.generateAlert({
318 type: 'database_error',
319 severity: 'error',
320 message: \`Database error: \${errorInfo.message}\`,
321 metadata: error
322 });
323
324 this.cleanupMetrics('errors');
325 }
326
327 // Alert system
328 generateAlert(alert) {
329 const alertWithId = {
330 id: this.generateId(),
331 timestamp: Date.now(),
332 ...alert
333 };
334
335 this.alerts.push(alertWithId);
336
337 console.log(\`🚨 ALERT [\${alert.severity.toUpperCase()}]: \${alert.message}\`);
338
339 // Keep only last 100 alerts
340 if (this.alerts.length > 100) {
341 this.alerts = this.alerts.slice(-100);
342 }
343
344 // Trigger alert handlers
345 this.handleAlert(alertWithId);
346 }
347
348 handleAlert(alert) {
349 // Implement alert handling (email, Slack, etc.)
350 switch (alert.severity) {
351 case 'critical':
352 this.sendCriticalAlert(alert);
353 break;
354 case 'error':
355 this.sendErrorAlert(alert);
356 break;
357 case 'warning':
358 this.sendWarningAlert(alert);
359 break;
360 }
361 }
362
363 sendCriticalAlert(alert) {
364 // Implementation for critical alerts
365 console.error('🔥 CRITICAL ALERT:', alert);
366 }
367
368 sendErrorAlert(alert) {
369 // Implementation for error alerts
370 console.error('❌ ERROR ALERT:', alert);
371 }
372
373 sendWarningAlert(alert) {
374 // Implementation for warning alerts
375 console.warn('⚠️ WARNING ALERT:', alert);
376 }
377
378 // Analytics and reporting
379 generateReport(timeRange = 3600000) { // 1 hour default
380 const cutoff = Date.now() - timeRange;
381
382 const filteredQueries = this.metrics.queries.filter(q => q.timestamp > cutoff);
383 const filteredConnections = this.metrics.connections.filter(c => c.timestamp > cutoff);
384 const filteredPerformance = this.metrics.performance.filter(p => p.timestamp > cutoff);
385 const filteredErrors = this.metrics.errors.filter(e => e.timestamp > cutoff);
386 const filteredAlerts = this.alerts.filter(a => a.timestamp > cutoff);
387
388 return {
389 timeRange: {
390 start: new Date(cutoff),
391 end: new Date(),
392 duration: timeRange
393 },
394 queries: {
395 total: filteredQueries.length,
396 slow: filteredQueries.filter(q => q.slow).length,
397 averageTime: this.calculateAverage(filteredQueries.map(q => q.duration)),
398 operations: this.groupBy(filteredQueries, 'operation')
399 },
400 connections: {
401 samples: filteredConnections.length,
402 averageConnections: this.calculateAverageConnections(filteredConnections)
403 },
404 performance: {
405 samples: filteredPerformance.length,
406 averageMemoryUsage: this.calculateAverageMemoryUsage(filteredPerformance)
407 },
408 errors: {
409 total: filteredErrors.length,
410 types: this.groupBy(filteredErrors, 'type')
411 },
412 alerts: {
413 total: filteredAlerts.length,
414 bySeverity: this.groupBy(filteredAlerts, 'severity'),
415 byType: this.groupBy(filteredAlerts, 'type')
416 }
417 };
418 }
419
420 // Utility methods
421 calculateAverageQueryTime() {
422 if (this.metrics.queries.length === 0) return 0;
423 const total = this.metrics.queries.reduce((sum, q) => sum + q.duration, 0);
424 return total / this.metrics.queries.length;
425 }
426
427 calculateErrorRate() {
428 const totalOperations = this.metrics.queries.length;
429 const errorCount = this.metrics.errors.length;
430 return totalOperations > 0 ? errorCount / totalOperations : 0;
431 }
432
433 calculateAverage(values) {
434 if (values.length === 0) return 0;
435 return values.reduce((sum, val) => sum + val, 0) / values.length;
436 }
437
438 calculateAverageConnections(connections) {
439 // Implementation would calculate average across all database types
440 return 0;
441 }
442
443 calculateAverageMemoryUsage(performance) {
444 // Implementation would calculate average memory usage
445 return 0;
446 }
447
448 groupBy(array, key) {
449 return array.reduce((groups, item) => {
450 const group = item[key];
451 groups[group] = (groups[group] || 0) + 1;
452 return groups;
453 }, {});
454 }
455
456 generateId() {
457 return Math.random().toString(36).substr(2, 9);
458 }
459
460 cleanupMetrics(type) {
461 const cutoff = Date.now() - this.config.retentionPeriod;
462 this.metrics[type] = this.metrics[type].filter(m => m.timestamp > cutoff);
463 }
464
465 startCleanupInterval() {
466 setInterval(() => {
467 Object.keys(this.metrics).forEach(type => {
468 this.cleanupMetrics(type);
469 });
470 }, 300000); // Cleanup every 5 minutes
471 }
472
473 // API endpoints for monitoring dashboard
474 getMetrics() {
475 return {
476 queries: this.metrics.queries.slice(-100), // Last 100 queries
477 connections: this.metrics.connections.slice(-20), // Last 20 connection samples
478 performance: this.metrics.performance.slice(-20), // Last 20 performance samples
479 errors: this.metrics.errors.slice(-50), // Last 50 errors
480 alerts: this.alerts.slice(-20) // Last 20 alerts
481 };
482 }
483
484 getHealth() {
485 const recent = Date.now() - 300000; // Last 5 minutes
486 const recentQueries = this.metrics.queries.filter(q => q.timestamp > recent);
487 const recentErrors = this.metrics.errors.filter(e => e.timestamp > recent);
488
489 return {
490 status: recentErrors.length === 0 ? 'healthy' : 'degraded',
491 queryCount: recentQueries.length,
492 slowQueryCount: recentQueries.filter(q => q.slow).length,
493 errorCount: recentErrors.length,
494 alertCount: this.alerts.filter(a => a.timestamp > recent).length
495 };
496 }
497}
498
499module.exports = DatabaseMonitor;Learn about Practice Exercises.
Exercise 1: Performance Benchmarking Suite
Build a comprehensive performance benchmarking system that tests database operations under various loads and generates optimization recommendations.
Exercise 2: Caching Layer Implementation
Implement a multi-tier caching strategy with Redis, including cache warming, invalidation patterns, and performance monitoring.
Exercise 3: Database Scaling Simulation
Create a simulation environment to test database scaling strategies including read replicas, sharding, and load balancing.
Indexing Strategy:
Design comprehensive indexes for optimal query performance
Connection Management:
Implement efficient pooling and resource management
Caching Layers:
Use Redis for multi-tier caching strategies
Performance Monitoring:
Implement comprehensive monitoring and alerting
Query Optimization:
Analyze and optimize slow queries systematically
Scaling Patterns:
Apply horizontal and vertical scaling techniques
You've mastered database optimization and performance techniques! You can now build high-performance, scalable database systems with comprehensive monitoring and optimization strategies.
Your progress is saved in this browser, on this device only.