Database performance adalah critical factor yang menentukan success dari web applications. Slow database queries dapat menyebabkan poor user experience, high server costs, dan scalability issues. Di tahun 2025, dengan data volumes yang terus meningkat dan user expectations yang semakin tinggi, database optimization menjadi lebih penting dari sebelumnya. Artikel ini akan membahas comprehensive database optimization strategies untuk modern web applications.<\/p>\n
Understanding Database Performance Fundamentals<\/p>\n
1. Core Performance Metrics
\n\u2022 Query Response Time: Time taken untuk execute individual queries
\n\u2022 Throughput: Number of queries processed per second
\n\u2022 Latency: Time delay antara request dan response
\n\u2022 Concurrency: Number of simultaneous connections
\n\u2022 Resource Utilization: CPU, memory, I\/O usage patterns
\n\u2022 Cache Hit Rate: Percentage of queries served dari cache<\/p>\n
2. Common Performance Bottlenecks
\n\u2022 Poorly Optimized Queries: Inefficient SQL queries dengan missing indexes
\n\u2022 Lock Contention: Multiple processes competing untuk same resources
\n\u2022 Inadequate Indexing: Missing atau suboptimal index usage
\n\u2022 Memory Pressure: Insufficient RAM allocated untuk database operations
\n\u2022 I\/O Bottlenecks: Slow disk operations limiting performance
\n\u2022 Network Latency: Delays dalam database communication<\/p>\n
3. Database Types dan Use Cases
\n“`javascript
\n\/\/ Database Type Selection Guide
\nconst databaseGuide = {
\n \/\/ Relational Databases
\n relational: {
\n mysql: {
\n bestFor: [‘Traditional applications’, ‘E-commerce’, ‘Financial systems’],
\n strengths: [‘ACID compliance’, ‘Mature ecosystem’, ‘Good tooling’],
\n limitations: [‘Vertical scaling’, ‘Complex joins overhead’],
\n useCases: [‘User management’, ‘Order processing’, ‘Inventory management’]
\n },
\n postgresql: {
\n bestFor: [‘Complex applications’, ‘Data analytics’, ‘Geographic data’],
\n strengths: [‘Advanced features’, ‘JSON support’, ‘Extensions’],
\n limitations: [‘Steeper learning curve’, ‘Resource intensive’],
\n useCases: [‘Content management’, ‘Analytics platforms’, ‘GIS applications’]
\n },
\n mssql: {
\n bestFor: [‘Enterprise applications’, ‘Windows environments’],
\n strengths: [‘Integration with Microsoft stack’, ‘Business intelligence’],
\n limitations: [‘Platform dependency’, ‘Licensing costs’],
\n useCases: [‘Corporate applications’, ‘ERP systems’, ‘Data warehousing’]
\n }
\n },<\/p>\n
\/\/ NoSQL Databases
\n nosql: {
\n mongodb: {
\n bestFor: [‘Content management’, ‘Real-time analytics’, ‘IoT data’],
\n strengths: [‘Flexible schema’, ‘Horizontal scaling’, ‘Document-oriented’],
\n limitations: [‘No ACID transactions’, ‘Memory intensive’],
\n useCases: [‘CMS’, ‘User profiles’, ‘Product catalogs’]
\n },
\n cassandra: {
\n bestFor: [‘High-velocity data’, ‘Time-series data’, ‘Distributed systems’],
\n strengths: [‘Linear scalability’, ‘High availability’, ‘Multi-datacenter’],
\n limitations: [‘Complex querying’, ‘Limited consistency’],
\n useCases: [‘IoT platforms’, ‘Recommendation engines’, ‘Messaging systems’]
\n },
\n redis: {
\n bestFor: [‘Caching’, ‘Real-time data’, ‘Session management’],
\n strengths: [‘In-memory performance’, ‘Data structures’, ‘Pub\/sub’],
\n limitations: [‘Memory constraints’, ‘Persistence overhead’],
\n useCases: [‘Session storage’, ‘Real-time leaderboards’, ‘Rate limiting’]
\n }
\n },<\/p>\n
\/\/ Specialized Databases
\n specialized: {
\n elasticsearch: {
\n bestFor: [‘Full-text search’, ‘Log analytics’, ‘Monitoring’],
\n strengths: [‘Search capabilities’, ‘Analytics’, ‘Scaling’],
\n limitations: [‘Resource intensive’, ‘Complex setup’],
\n useCases: [‘Search engines’, ‘Log analysis’, ‘Monitoring systems’]
\n },
\n influxdb: {
\n bestFor: [‘Time-series data’, ‘Monitoring’, ‘IoT metrics’],
\n strengths: [‘Time-series optimized’, ‘Compression’, ‘Retention policies’],
\n limitations: [‘Limited query flexibility’, ‘Single-node performance’],
\n useCases: [‘Application monitoring’, ‘IoT sensor data’, ‘Financial metrics’]
\n },
\n neo4j: {
\n bestFor: [‘Graph data’, ‘Relationship mapping’, ‘Social networks’],
\n strengths: [‘Graph queries’, ‘Relationship modeling’, ‘Performance’],
\n limitations: [‘Learning curve’, ‘Scaling challenges’],
\n useCases: [‘Social networks’, ‘Recommendation engines’, ‘Fraud detection’]
\n }
\n }
\n};
\n“`<\/p>\n
Query Optimization Strategies<\/p>\n
1. Index Optimization Techniques
\n“`sql
\n— Index Analysis dan Optimization
\n— 1. Identify Missing Indexes
\nSELECT
\n schemaname,
\n tablename,
\n attname,
\n n_distinct,
\n correlation
\nFROM pg_stats
\nWHERE schemaname = ‘public’
\nORDER BY n_distinct DESC;<\/p>\n
— 2. Analyze Query Performance
\nEXPLAIN (ANALYZE, BUFFERS)
\nSELECT u.id, u.name, p.title, p.created_at
\nFROM users u
\nJOIN posts p ON u.id = p.user_id
\nWHERE u.status = ‘active’
\n AND p.created_at >= ‘2025-01-01’
\nORDER BY p.created_at DESC
\nLIMIT 50;<\/p>\n
— 3. Create Optimal Indexes
\n— Composite index untuk frequently queried columns
\nCREATE INDEX idx_users_status_created_at
\nON users(status, created_at);<\/p>\n
— Partial index untuk specific conditions
\nCREATE INDEX idx_active_users
\nON users(id, name)
\nWHERE status = ‘active’;<\/p>\n
— Covering index untuk commonly accessed columns
\nCREATE INDEX idx_posts_user_title_created
\nON posts(user_id, title, created_at, status);<\/p>\n
— 4. Index Usage Analysis
\nSELECT
\n schemaname,
\n tablename,
\n indexname,
\n idx_scan,
\n idx_tup_read,
\n idx_tup_fetch
\nFROM pg_stat_user_indexes
\nORDER BY idx_scan DESC;<\/p>\n
— 5. Remove Unused Indexes
\nDROP INDEX IF EXISTS idx_unused_index;<\/p>\n
— 6. Optimize untuk JOIN operations
\n— Bad query dengan missing indexes
\nSELECT o.*, c.name, p.product_name
\nFROM orders o
\nJOIN customers c ON o.customer_id = c.id
\nJOIN order_items oi ON o.id = oi.order_id
\nJOIN products p ON oi.product_id = p.id
\nWHERE o.status = ‘pending’
\n AND o.created_at >= ‘2025-01-01’;<\/p>\n
— Optimized dengan proper indexes
\nCREATE INDEX idx_orders_status_created ON orders(status, created_at);
\nCREATE INDEX idx_order_items_order_id ON order_items(order_id);
\nCREATE INDEX idx_order_items_product_id ON order_items(product_id);<\/p>\n
— Query dengan JOIN optimization
\nSELECT o.id, o.total, c.name, COUNT(oi.id) as item_count
\nFROM orders o
\nJOIN customers c ON o.customer_id = c.id
\nLEFT JOIN order_items oi ON o.id = oi.order_id
\nWHERE o.status = ‘pending’
\n AND o.created_at >= ‘2025-01-01’
\nGROUP BY o.id, o.total, c.name
\nORDER BY o.created_at DESC;
\n“`<\/p>\n
2. Query Writing Best Practices
\n“`sql
\n— Query Optimization Patterns<\/p>\n
— 1. Avoid SELECT * (Anti-pattern)
\n— Bad:
\nSELECT * FROM users WHERE email = ‘user@example.com’;<\/p>\n
— Good:
\nSELECT id, name, email, last_login
\nFROM users
\nWHERE email = ‘user@example.com’;<\/p>\n
— 2. Use EXISTS instead of IN untuk subqueries
\n— Bad:
\nSELECT name
\nFROM departments
\nWHERE id IN (SELECT department_id FROM employees WHERE salary > 50000);<\/p>\n
— Good:
\nSELECT d.name
\nFROM departments d
\nWHERE EXISTS (
\n SELECT 1
\n FROM employees e
\n WHERE e.department_id = d.id
\n AND e.salary > 50000
\n);<\/p>\n
— 3. Optimize JOIN operations
\n— Bad: Multiple JOINs tanpa proper indexing
\nSELECT u.name, p.title, c.name as category
\nFROM users u
\nJOIN posts p ON u.id = p.user_id
\nJOIN categories c ON p.category_id = c.id
\nWHERE p.status = ‘published’;<\/p>\n
— Good: Dengan proper indexes dan query structure
\nCREATE INDEX idx_posts_user_status ON posts(user_id, status);
\nCREATE INDEX idx_posts_category ON posts(category_id);<\/p>\n
SELECT u.name, p.title, c.name as category
\nFROM users u
\nJOIN posts p ON u.id = p.user_id
\n AND p.status = ‘published’
\nJOIN categories c ON p.category_id = c.id;<\/p>\n
— 4. Use LIMIT untuk pagination
\n— Bad: Fetching all records
\nSELECT * FROM products ORDER BY created_at DESC;<\/p>\n
— Good: Implementasi pagination
\nSELECT * FROM products
\nORDER BY created_at DESC
\nLIMIT 20 OFFSET 0; — Page 1<\/p>\n
SELECT * FROM products
\nORDER BY created_at DESC
\nLIMIT 20 OFFSET 20; — Page 2<\/p>\n
— Better: Cursor-based pagination untuk large datasets
\nSELECT * FROM products
\nWHERE created_at = NOW() – INTERVAL ’30 days’
\n),
\nuser_orders AS (
\n SELECT
\n user_id,
\n COUNT(*) as order_count,
\n SUM(total) as total_spent
\n FROM orders
\n WHERE created_at >= NOW() – INTERVAL ’30 days’
\n GROUP BY user_id
\n)
\nSELECT
\n au.name,
\n au.email,
\n COALESCE(uo.order_count, 0) as recent_orders,
\n COALESCE(uo.total_spent, 0) as recent_spent
\nFROM active_users au
\nLEFT JOIN user_orders uo ON au.id = uo.user_id
\nORDER BY recent_spent DESC
\nLIMIT 100;<\/p>\n
— Materialized Views untuk Complex Queries
\nCREATE MATERIALIZED VIEW user_analytics AS
\nSELECT
\n u.id,
\n u.name,
\n u.email,
\n COUNT(DISTINCT o.id) as total_orders,
\n COALESCE(SUM(o.total), 0) as total_spent,
\n MAX(o.created_at) as last_order_date,
\n COUNT(DISTINCT DATE(o.created_at)) as active_days
\nFROM users u
\nLEFT JOIN orders o ON u.id = o.user_id
\nGROUP BY u.id, u.name, u.email;<\/p>\n
— Refresh materialized view periodically
\nREFRESH MATERIALIZED VIEW user_analytics;<\/p>\n
— Query materialized view (much faster)
\nSELECT * FROM user_analytics
\nWHERE total_orders > 10
\nORDER BY total_spent DESC;<\/p>\n
— Partitioning untuk Large Tables
\nCREATE TABLE sales (
\n id BIGSERIAL,
\n product_id INTEGER NOT NULL,
\n customer_id INTEGER NOT NULL,
\n sale_date DATE NOT NULL,
\n amount DECIMAL(10,2) NOT NULL,
\n created_at TIMESTAMP DEFAULT NOW()
\n) PARTITION BY RANGE (sale_date);<\/p>\n
— Create partitions
\nCREATE TABLE sales_2025_q1 PARTITION OF sales
\n FOR VALUES FROM (‘2025-01-01’) TO (‘2025-04-01’);<\/p>\n
CREATE TABLE sales_2025_q2 PARTITION OF sales
\n FOR VALUES FROM (‘2025-04-01’) TO (‘2025-07-01’);<\/p>\n
CREATE TABLE sales_2025_q3 PARTITION OF sales
\n FOR VALUES FROM (‘2025-07-01’) TO (‘2025-10-01’);<\/p>\n
CREATE TABLE sales_2025_q4 PARTITION OF sales
\n FOR VALUES FROM (‘2025-10-01’) TO (‘2025-01-01’);<\/p>\n
— Query automatically uses relevant partitions
\nSELECT product_id, SUM(amount) as total_sales
\nFROM sales
\nWHERE sale_date BETWEEN ‘2025-01-01’ AND ‘2025-03-31’
\nGROUP BY product_id;
\n“`<\/p>\n
Database Caching Strategies<\/p>\n
1. Application-Level Caching
\n“`javascript
\n\/\/ Redis Caching Implementation
\nconst Redis = require(‘redis’);
\nconst redis = Redis.createClient({
\n host: process.env.REDIS_HOST,
\n port: process.env.REDIS_PORT,
\n password: process.env.REDIS_PASSWORD,
\n retry_strategy: (options) => {
\n if (options.error && options.error.code === ‘ECONNREFUSED’) {
\n return new Error(‘Redis server connection refused’);
\n }
\n if (options.total_retry_time > 1000 * 60 * 60) {
\n return new Error(‘Retry time exhausted’);
\n }
\n if (options.attempt > 10) {
\n return undefined;
\n }
\n return Math.min(options.attempt * 100, 3000);
\n }
\n});<\/p>\n
class DatabaseCache {
\n constructor(ttl = 3600) { \/\/ Default TTL 1 hour
\n this.ttl = ttl;
\n }<\/p>\n
\/\/ Cache key generator
\n generateKey(operation, params) {
\n const paramString = JSON.stringify(params);
\n return `db:${operation}:${Buffer.from(paramString).toString(‘base64’)}`;
\n }<\/p>\n
\/\/ Get cached data
\n async get(key) {
\n try {
\n const cached = await redis.get(key);
\n return cached ? JSON.parse(cached) : null;
\n } catch (error) {
\n console.error(‘Cache get error:’, error);
\n return null;
\n }
\n }<\/p>\n
\/\/ Set cache data
\n async set(key, data, customTtl = null) {
\n try {
\n const ttl = customTtl || this.ttl;
\n await redis.setex(key, ttl, JSON.stringify(data));
\n return true;
\n } catch (error) {
\n console.error(‘Cache set error:’, error);
\n return false;
\n }
\n }<\/p>\n
\/\/ Delete cache
\n async delete(key) {
\n try {
\n await redis.del(key);
\n return true;
\n } catch (error) {
\n console.error(‘Cache delete error:’, error);
\n return false;
\n }
\n }<\/p>\n
\/\/ Clear cache by pattern
\n async clearPattern(pattern) {
\n try {
\n const keys = await redis.keys(pattern);
\n if (keys.length > 0) {
\n await redis.del(keys);
\n }
\n return keys.length;
\n } catch (error) {
\n console.error(‘Cache clear pattern error:’, error);
\n return 0;
\n }
\n }<\/p>\n
\/\/ Cache wrapper untuk database operations
\n async cacheQuery(operation, params, queryFunction, ttl = null) {
\n const cacheKey = this.generateKey(operation, params);<\/p>\n
\/\/ Try cache first
\n let result = await this.get(cacheKey);
\n if (result) {
\n console.log(`Cache hit for ${operation}`);
\n return result;
\n }<\/p>\n
\/\/ Cache miss, execute query
\n console.log(`Cache miss for ${operation}, executing query`);
\n result = await queryFunction();<\/p>\n
\/\/ Cache the result
\n await this.set(cacheKey, result, ttl);<\/p>\n
return result;
\n }<\/p>\n
\/\/ Batch cache operations
\n async mget(keys) {
\n try {
\n const values = await redis.mget(keys);
\n return values.map(value => value ? JSON.parse(value) : null);
\n } catch (error) {
\n console.error(‘Cache mget error:’, error);
\n return new Array(keys.length).fill(null);
\n }
\n }<\/p>\n
\/\/ Set multiple cache values
\n async mset(keyValuePairs, ttl = null) {
\n try {
\n const pipeline = redis.pipeline();<\/p>\n
for (const [key, value] of keyValuePairs) {
\n pipeline.setex(key, ttl || this.ttl, JSON.stringify(value));
\n }<\/p>\n
await pipeline.exec();
\n return true;
\n } catch (error) {
\n console.error(‘Cache mset error:’, error);
\n return false;
\n }
\n }
\n}<\/p>\n
\/\/ Usage Example
\nconst dbCache = new DatabaseCache(1800); \/\/ 30 minutes cache<\/p>\n
\/\/ Database query functions
\nclass UserService {
\n \/\/ Get user by ID dengan caching
\n async getUserById(userId) {
\n return await dbCache.cacheQuery(
\n ‘user_by_id’,
\n { userId },
\n async () => {
\n const query = ‘SELECT id, name, email, created_at FROM users WHERE id = $1’;
\n const result = await pool.query(query, [userId]);
\n return result.rows[0] || null;
\n }
\n );
\n }<\/p>\n
\/\/ Get user posts dengan caching
\n async getUserPosts(userId, page = 1, limit = 20) {
\n return await dbCache.cacheQuery(
\n ‘user_posts’,
\n { userId, page, limit },
\n async () => {
\n const offset = (page – 1) * limit;
\n const query = `
\n SELECT id, title, content, created_at
\n FROM posts
\n WHERE user_id = $1
\n ORDER BY created_at DESC
\n LIMIT $2 OFFSET $3
\n `;
\n const result = await pool.query(query, [userId, limit, offset]);
\n return result.rows;
\n },
\n 900 \/\/ 15 minutes cache
\n );
\n }<\/p>\n
\/\/ Invalidate cache when data changes
\n async updateUser(userId, updateData) {
\n const query = ‘UPDATE users SET name = $1, email = $2 WHERE id = $3’;
\n await pool.query(query, [updateData.name, updateData.email, userId]);<\/p>\n
\/\/ Clear cache
\n await dbCache.delete(dbCache.generateKey(‘user_by_id’, { userId }));
\n await dbCache.clearPattern(`db:user_posts*${userId}*`);<\/p>\n
return true;
\n }<\/p>\n
\/\/ Get popular posts dengan longer cache
\n async getPopularPosts() {
\n return await dbCache.cacheQuery(
\n ‘popular_posts’,
\n {},
\n async () => {
\n const query = `
\n SELECT p.*, u.name as author_name,
\n COUNT(l.id) as like_count,
\n COUNT(c.id) as comment_count
\n FROM posts p
\n JOIN users u ON p.user_id = u.id
\n LEFT JOIN likes l ON p.id = l.post_id
\n LEFT JOIN comments c ON p.id = c.post_id
\n WHERE p.created_at >= NOW() – INTERVAL ‘7 days’
\n GROUP BY p.id, u.name
\n ORDER BY like_count DESC, comment_count DESC
\n LIMIT 20
\n `;
\n const result = await pool.query(query);
\n return result.rows;
\n },
\n 3600 \/\/ 1 hour cache
\n );
\n }
\n}
\n“`<\/p>\n
2. Query Result Caching
\n“`javascript
\n\/\/ Advanced Caching Strategies
\nclass AdvancedCacheManager {
\n constructor(redisClient) {
\n this.redis = redisClient;
\n this.localCache = new Map(); \/\/ L1 cache
\n this.cacheStats = {
\n hits: 0,
\n misses: 0,
\n sets: 0,
\n deletes: 0
\n };
\n }<\/p>\n
\/\/ Multi-level caching (L1: Memory, L2: Redis)
\n async multiLevelGet(key) {
\n \/\/ L1 Cache (Memory)
\n if (this.localCache.has(key)) {
\n this.cacheStats.hits++;
\n return this.localCache.get(key);
\n }<\/p>\n
\/\/ L2 Cache (Redis)
\n try {
\n const cached = await this.redis.get(key);
\n if (cached) {
\n const data = JSON.parse(cached);
\n this.localCache.set(key, data);
\n this.cacheStats.hits++;
\n return data;
\n }
\n } catch (error) {
\n console.error(‘Redis get error:’, error);
\n }<\/p>\n
this.cacheStats.misses++;
\n return null;
\n }<\/p>\n
\/\/ Set di both cache levels
\n async multiLevelSet(key, data, ttl = 3600) {
\n \/\/ L1 Cache (Memory) – dengan limited size
\n if (this.localCache.size {
\n try {
\n await this.writeToDatabase(key, data);
\n } catch (error) {
\n console.error(‘Write-behind error:’, error);
\n \/\/ Retry logic or error handling
\n }
\n }, 100);
\n }<\/p>\n
\/\/ Cache warming strategy
\n async warmCache(keys) {
\n const promises = keys.map(async (key) => {
\n try {
\n const data = await this.fetchFromDatabase(key);
\n if (data) {
\n await this.multiLevelSet(key, data, 3600);
\n }
\n } catch (error) {
\n console.error(`Cache warming error for key ${key}:`, error);
\n }
\n });<\/p>\n
await Promise.all(promises);
\n }<\/p>\n
\/\/ Cache invalidation strategies
\n async invalidateByPattern(pattern) {
\n \/\/ Clear local cache
\n for (const [key] of this.localCache) {
\n if (key.includes(pattern)) {
\n this.localCache.delete(key);
\n }
\n }<\/p>\n
\/\/ Clear Redis cache
\n try {
\n const redisKeys = await this.redis.keys(`*${pattern}*`);
\n if (redisKeys.length > 0) {
\n await this.redis.del(redisKeys);
\n }
\n this.cacheStats.deletes += redisKeys.length;
\n } catch (error) {
\n console.error(‘Cache invalidation error:’, error);
\n }
\n }<\/p>\n
\/\/ Get cache statistics
\n getStats() {
\n const total = this.cacheStats.hits + this.cacheStats.misses;
\n return {
\n …this.cacheStats,
\n hitRate: total > 0 ? (this.cacheStats.hits \/ total * 100).toFixed(2) + ‘%’ : ‘0%’,
\n localCacheSize: this.localCache.size
\n };
\n }
\n}<\/p>\n
\/\/ Cache-Aside Pattern Implementation
\nclass CacheAsideService {
\n constructor(cacheManager, database) {
\n this.cache = cacheManager;
\n this.db = database;
\n }<\/p>\n
async getUser(id) {
\n const cacheKey = `user:${id}`;<\/p>\n
\/\/ Try cache first
\n let user = await this.cache.multiLevelGet(cacheKey);
\n if (user) {
\n return user;
\n }<\/p>\n
\/\/ Cache miss, fetch from database
\n user = await this.db.getUserById(id);
\n if (user) {
\n \/\/ Write to cache
\n await this.cache.multiLevelSet(cacheKey, user, 3600);
\n }<\/p>\n
return user;
\n }<\/p>\n
async updateUser(id, userData) {
\n \/\/ Update database
\n const updatedUser = await this.db.updateUser(id, userData);<\/p>\n
\/\/ Update cache
\n const cacheKey = `user:${id}`;
\n await this.cache.multiLevelSet(cacheKey, updatedUser, 3600);<\/p>\n
return updatedUser;
\n }<\/p>\n
async deleteUser(id) {
\n \/\/ Delete from database
\n await this.db.deleteUser(id);<\/p>\n
\/\/ Delete from cache
\n const cacheKey = `user:${id}`;
\n await this.cache.localCache.delete(cacheKey);
\n await this.cache.redis.del(cacheKey);
\n }
\n}
\n“`<\/p>\n
Connection Pooling dan Resource Management<\/p>\n
1. Database Connection Pooling
\n“`javascript
\n\/\/ PostgreSQL Connection Pool Configuration
\nconst { Pool } = require(‘pg’);<\/p>\n
class DatabaseManager {
\n constructor(config) {
\n this.pool = new Pool({
\n host: config.host,
\n port: config.port,
\n database: config.database,
\n user: config.user,
\n password: config.password,<\/p>\n
\/\/ Pool configuration
\n min: config.minConnections || 2,
\n max: config.maxConnections || 20,
\n idleTimeoutMillis: config.idleTimeout || 30000,
\n connectionTimeoutMillis: config.connectionTimeout || 2000,<\/p>\n
\/\/ Advanced settings
\n allowExitOnIdle: false,
\n maxUses: config.maxUses || 7500,<\/p>\n
\/\/ SSL configuration
\n ssl: config.ssl || false,<\/p>\n
\/\/ Application name untuk monitoring
\n application_name: config.applicationName || ‘web_app’,<\/p>\n
\/\/ Statement timeout
\n statement_timeout: config.statementTimeout || 30000
\n });<\/p>\n
this.pool.on(‘connect’, (client) => {
\n console.log(‘New client connected to database’);
\n });<\/p>\n
this.pool.on(‘error’, (err, client) => {
\n console.error(‘Database connection error:’, err);
\n });<\/p>\n
this.setupGracefulShutdown();
\n }<\/p>\n
\/\/ Execute query dengan connection pool
\n async query(text, params, options = {}) {
\n const start = Date.now();<\/p>\n
try {
\n const client = await this.pool.connect();<\/p>\n
try {
\n const result = await client.query(text, params);<\/p>\n
\/\/ Log slow queries
\n const duration = Date.now() – start;
\n if (duration > 1000) {
\n console.warn(`Slow query detected (${duration}ms):`, text);
\n }<\/p>\n
return result;
\n } finally {
\n client.release();
\n }
\n } catch (error) {
\n console.error(‘Database query error:’, error);
\n throw error;
\n }
\n }<\/p>\n
\/\/ Transaction helper
\n async transaction(callback) {
\n const client = await this.pool.connect();<\/p>\n
try {
\n await client.query(‘BEGIN’);
\n const result = await callback(client);
\n await client.query(‘COMMIT’);
\n return result;
\n } catch (error) {
\n await client.query(‘ROLLBACK’);
\n throw error;
\n } finally {
\n client.release();
\n }
\n }<\/p>\n
\/\/ Batch operations
\n async batchInsert(table, columns, values, batchSize = 1000) {
\n if (values.length === 0) return;<\/p>\n
const columnsStr = columns.join(‘, ‘);
\n const placeholders = columns.map((_, index) => `$${index + 1}`).join(‘, ‘);<\/p>\n
for (let i = 0; i {
\n const offset = index * columns.length;
\n return `(${columns.map((_, colIndex) => `$${offset + colIndex + 1}`).join(‘, ‘)})`;
\n }).join(‘, ‘);<\/p>\n
const flattenedValues = batch.flat();<\/p>\n
const query = `
\n INSERT INTO ${table} (${columnsStr})
\n VALUES ${valuePlaceholders}
\n `;<\/p>\n
await this.query(query, flattenedValues);
\n }
\n }<\/p>\n
\/\/ Health check
\n async healthCheck() {
\n try {
\n const result = await this.query(‘SELECT 1 as health_check’);
\n const poolInfo = {
\n totalCount: this.pool.totalCount,
\n idleCount: this.pool.idleCount,
\n waitingCount: this.pool.waitingCount
\n };<\/p>\n
return {
\n status: ‘healthy’,
\n timestamp: new Date(),
\n pool: poolInfo
\n };
\n } catch (error) {
\n return {
\n status: ‘unhealthy’,
\n timestamp: new Date(),
\n error: error.message
\n };
\n }
\n }<\/p>\n
\/\/ Get pool statistics
\n getPoolStats() {
\n return {
\n total: this.pool.totalCount,
\n idle: this.pool.idleCount,
\n waiting: this.pool.waitingCount
\n };
\n }<\/p>\n
\/\/ Graceful shutdown
\n setupGracefulShutdown() {
\n const shutdown = async (signal) => {
\n console.log(`Received ${signal}, shutting down database connections…`);<\/p>\n
try {
\n await this.pool.end();
\n console.log(‘Database connections closed gracefully’);
\n process.exit(0);
\n } catch (error) {
\n console.error(‘Error during database shutdown:’, error);
\n process.exit(1);
\n }
\n };<\/p>\n
process.on(‘SIGTERM’, () => shutdown(‘SIGTERM’));
\n process.on(‘SIGINT’, () => shutdown(‘SIGINT’));
\n }
\n}<\/p>\n
\/\/ Usage example
\nconst dbConfig = {
\n host: process.env.DB_HOST,
\n port: process.env.DB_PORT,
\n database: process.env.DB_NAME,
\n user: process.env.DB_USER,
\n password: process.env.DB_PASSWORD,
\n minConnections: 5,
\n maxConnections: 50,
\n idleTimeout: 30000,
\n connectionTimeout: 5000,
\n statementTimeout: 10000,
\n applicationName: ‘my_web_app’
\n};<\/p>\n
const dbManager = new DatabaseManager(dbConfig);<\/p>\n
\/\/ Database operations
\nclass UserRepository {
\n constructor(dbManager) {
\n this.db = dbManager;
\n }<\/p>\n
async createUser(userData) {
\n const query = `
\n INSERT INTO users (name, email, password_hash, created_at)
\n VALUES ($1, $2, $3, NOW())
\n RETURNING id, name, email, created_at
\n `;<\/p>\n
const result = await this.db.query(query, [
\n userData.name,
\n userData.email,
\n userData.passwordHash
\n ]);<\/p>\n
return result.rows[0];
\n }<\/p>\n
async findUserById(id) {
\n const query = `
\n SELECT id, name, email, created_at, updated_at
\n FROM users
\n WHERE id = $1 AND deleted_at IS NULL
\n `;<\/p>\n
const result = await this.db.query(query, [id]);
\n return result.rows[0] || null;
\n }<\/p>\n
async updateUserLastLogin(id) {
\n const query = `
\n UPDATE users
\n SET last_login = NOW()
\n WHERE id = $1
\n `;<\/p>\n
await this.db.query(query, [id]);
\n }<\/p>\n
async getUserWithPosts(userId) {
\n return await this.db.transaction(async (client) => {
\n \/\/ Get user
\n const userQuery = ‘SELECT id, name, email FROM users WHERE id = $1’;
\n const userResult = await client.query(userQuery, [userId]);
\n const user = userResult.rows[0];<\/p>\n
if (!user) return null;<\/p>\n
\/\/ Get user posts
\n const postsQuery = `
\n SELECT id, title, content, created_at
\n FROM posts
\n WHERE user_id = $1
\n ORDER BY created_at DESC
\n LIMIT 10
\n `;
\n const postsResult = await client.query(postsQuery, [userId]);<\/p>\n
return {
\n …user,
\n posts: postsResult.rows
\n };
\n });
\n }
\n}<\/p>\n
module.exports = { DatabaseManager, UserRepository };
\n“`<\/p>\n
2. Connection Pool Monitoring
\n“`javascript
\n\/\/ Database Connection Pool Monitoring
\nclass PoolMonitor {
\n constructor(pool) {
\n this.pool = pool;
\n this.metrics = {
\n totalQueries: 0,
\n slowQueries: 0,
\n errors: 0,
\n avgResponseTime: 0,
\n maxResponseTime: 0,
\n minResponseTime: Infinity
\n };<\/p>\n
this.startMonitoring();
\n }<\/p>\n
startMonitoring() {
\n \/\/ Monitor pool setiap 30 detik
\n setInterval(() => {
\n this.collectMetrics();
\n }, 30000);<\/p>\n
\/\/ Reset metrics harian
\n setInterval(() => {
\n this.resetMetrics();
\n }, 24 * 60 * 60 * 1000);
\n }<\/p>\n
collectMetrics() {
\n const stats = this.pool.getStats();<\/p>\n
console.log(‘Database Pool Statistics:’, {
\n timestamp: new Date(),
\n pool: {
\n total: stats.total,
\n idle: stats.idle,
\n waiting: stats.waiting,
\n utilization: ((stats.total – stats.idle) \/ stats.total * 100).toFixed(2) + ‘%’
\n },
\n metrics: this.metrics
\n });<\/p>\n
\/\/ Check untuk potential issues
\n if (stats.waiting > 0) {
\n console.warn(‘Database pool has waiting connections!’);
\n }<\/p>\n
if (stats.idle === 0) {
\n console.warn(‘Database pool has no idle connections!’);
\n }<\/p>\n
if (this.metrics.slowQueries > 0) {
\n console.warn(`Detected ${this.metrics.slowQueries} slow queries`);
\n }
\n }<\/p>\n
\/\/ Enhanced query method dengan monitoring
\n async monitoredQuery(query, params = []) {
\n const start = Date.now();<\/p>\n
try {
\n this.metrics.totalQueries++;<\/p>\n
const result = await this.pool.query(query, params);<\/p>\n
const responseTime = Date.now() – start;
\n this.updateResponseTimeMetrics(responseTime);<\/p>\n
\/\/ Log slow queries
\n if (responseTime > 1000) {
\n this.metrics.slowQueries++;
\n console.warn(`Slow query (${responseTime}ms):`, query);
\n }<\/p>\n
return result;
\n } catch (error) {
\n this.metrics.errors++;
\n console.error(‘Database query error:’, error);
\n throw error;
\n }
\n }<\/p>\n
updateResponseTimeMetrics(responseTime) {
\n \/\/ Update min\/max
\n this.metrics.minResponseTime = Math.min(this.metrics.minResponseTime, responseTime);
\n this.metrics.maxResponseTime = Math.max(this.metrics.maxResponseTime, responseTime);<\/p>\n
\/\/ Update average (simplified)
\n this.metrics.avgResponseTime =
\n (this.metrics.avgResponseTime + responseTime) \/ 2;
\n }<\/p>\n
resetMetrics() {
\n this.metrics = {
\n totalQueries: 0,
\n slowQueries: 0,
\n errors: 0,
\n avgResponseTime: 0,
\n maxResponseTime: 0,
\n minResponseTime: Infinity
\n };
\n }<\/p>\n
\/\/ Health check endpoint
\n getHealthStatus() {
\n const stats = this.pool.getStats();
\n const utilizationRate = (stats.total – stats.idle) \/ stats.total;<\/p>\n
return {
\n status: this.determineHealthStatus(utilizationRate),
\n timestamp: new Date(),
\n pool: stats,
\n metrics: this.metrics,
\n recommendations: this.getRecommendations(utilizationRate)
\n };
\n }<\/p>\n
determineHealthStatus(utilizationRate) {
\n if (utilizationRate > 0.9) return ‘critical’;
\n if (utilizationRate > 0.7) return ‘warning’;
\n if (this.metrics.errors > 0) return ‘degraded’;
\n return ‘healthy’;
\n }<\/p>\n
getRecommendations(utilizationRate) {
\n const recommendations = [];<\/p>\n
if (utilizationRate > 0.9) {
\n recommendations.push(‘Consider increasing max pool size’);
\n recommendations.push(‘Check for slow queries and optimize them’);
\n }<\/p>\n
if (this.metrics.slowQueries > 0) {
\n recommendations.push(‘Investigate slow queries and add appropriate indexes’);
\n }<\/p>\n
if (this.metrics.errors > 0) {
\n recommendations.push(‘Review error logs and fix connection issues’);
\n }<\/p>\n
if (utilizationRate ${this.thresholds.queryTime}
\n ORDER BY mean_time DESC
\n LIMIT 10
\n `
\n },
\n {
\n name: ‘Most Frequent Queries’,
\n query: `
\n SELECT query, calls, total_time, mean_time
\n FROM pg_stat_statements
\n ORDER BY calls DESC
\n LIMIT 10
\n `
\n },
\n {
\n name: ‘High Variance Queries’,
\n query: `
\n SELECT query, calls, total_time, mean_time, stddev_time
\n FROM pg_stat_statements
\n WHERE stddev_time > mean_time * 0.5
\n ORDER BY stddev_time DESC
\n LIMIT 10
\n `
\n }
\n ];<\/p>\n
const results = {};<\/p>\n
for (const queryInfo of queries) {
\n try {
\n const result = await this.db.query(queryInfo.query);
\n results[queryInfo.name] = result.rows;
\n } catch (error) {
\n console.error(`Error getting ${queryInfo.name}:`, error);
\n results[queryInfo.name] = [];
\n }
\n }<\/p>\n
return results;
\n }<\/p>\n
\/\/ Get connection statistics
\n async getConnectionStats() {
\n try {
\n const query = `
\n SELECT
\n COUNT(*) as total_connections,
\n COUNT(*) FILTER (WHERE state = ‘active’) as active_connections,
\n COUNT(*) FILTER (WHERE state = ‘idle’) as idle_connections,
\n COUNT(*) FILTER (WHERE state = ‘idle in transaction’) as idle_in_transaction,
\n AVG(EXTRACT(EPOCH FROM (now() – backend_start))) as avg_session_duration
\n FROM pg_stat_activity
\n WHERE pid != pg_backend_pid()
\n `;<\/p>\n
const result = await this.db.query(query);
\n return result.rows[0];
\n } catch (error) {
\n console.error(‘Error getting connection stats:’, error);
\n return {};
\n }
\n }<\/p>\n
\/\/ Get index statistics
\n async getIndexStats() {
\n try {
\n const query = `
\n SELECT
\n schemaname,
\n tablename,
\n indexname,
\n idx_scan,
\n idx_tup_read,
\n idx_tup_fetch,
\n pg_size_pretty(pg_relation_size(indexrelid)) as index_size
\n FROM pg_stat_user_indexes
\n ORDER BY idx_scan DESC
\n LIMIT 20
\n `;<\/p>\n
const result = await this.db.query(query);
\n return result.rows;
\n } catch (error) {
\n console.error(‘Error getting index stats:’, error);
\n return [];
\n }
\n }<\/p>\n
\/\/ Get table statistics
\n async getTableStats() {
\n try {
\n const query = `
\n SELECT
\n schemaname,
\n tablename,
\n n_tup_ins as inserts,
\n n_tup_upd as updates,
\n n_tup_del as deletes,
\n n_live_tup as live_tuples,
\n n_dead_tup as dead_tuples,
\n last_vacuum,
\n last_autovacuum,
\n last_analyze,
\n last_autoanalyze
\n FROM pg_stat_user_tables
\n ORDER BY n_live_tup DESC
\n LIMIT 20
\n `;<\/p>\n
const result = await this.db.query(query);
\n return result.rows;
\n } catch (error) {
\n console.error(‘Error getting table stats:’, error);
\n return [];
\n }
\n }<\/p>\n
\/\/ Check performance alerts
\n checkAlerts(stats) {
\n const alerts = [];<\/p>\n
\/\/ Check slow queries
\n const slowQueries = stats.queries[‘Slow Queries’] || [];
\n if (slowQueries.length > 0) {
\n alerts.push({
\n type: ‘performance’,
\n severity: ‘warning’,
\n message: `Found ${slowQueries.length} slow queries`,
\n details: slowQueries.map(q => ({
\n query: q.query.substring(0, 100) + ‘…’,
\n avgTime: Math.round(q.mean_time) + ‘ms’
\n }))
\n });
\n }<\/p>\n
\/\/ Check connection utilization
\n const totalConnections = parseInt(stats.connections.total_connections || 0);
\n const activeConnections = parseInt(stats.connections.active_connections || 0);
\n const utilizationRate = totalConnections > 0 ? activeConnections \/ totalConnections : 0;<\/p>\n
if (utilizationRate > this.thresholds.connectionUtilization) {
\n alerts.push({
\n type: ‘capacity’,
\n severity: ‘warning’,
\n message: `High connection utilization: ${(utilizationRate * 100).toFixed(1)}%`,
\n details: {
\n total: totalConnections,
\n active: activeConnections,
\n utilizationRate: (utilizationRate * 100).toFixed(1) + ‘%’
\n }
\n });
\n }<\/p>\n
\/\/ Check for tables needing vacuum
\n const tablesNeedingVacuum = stats.tables.filter(table => {
\n if (!table.last_vacuum) return true;
\n const lastVacuum = new Date(table.last_vacuum);
\n const daysSinceVacuum = (Date.now() – lastVacuum) \/ (1000 * 60 * 60 * 24);
\n return daysSinceVacuum > 30; \/\/ 30 days
\n });<\/p>\n
if (tablesNeedingVacuum.length > 0) {
\n alerts.push({
\n type: ‘maintenance’,
\n severity: ‘info’,
\n message: `${tablesNeedingVacuum.length} tables need VACUUM`,
\n details: tablesNeedingVacuum.map(t => ({
\n table: `${t.schemaname}.${t.tablename}`,
\n lastVacuum: t.last_vacuum || ‘Never’
\n }))
\n });
\n }<\/p>\n
\/\/ Check unused indexes
\n const unusedIndexes = stats.indexes.filter(index =>
\n parseInt(index.idx_scan || 0) === 0
\n );<\/p>\n
if (unusedIndexes.length > 0) {
\n alerts.push({
\n type: ‘optimization’,
\n severity: ‘info’,
\n message: `${unusedIndexes.length} indexes are never used`,
\n details: unusedIndexes.map(idx => ({
\n index: idx.indexname,
\n table: `${idx.schemaname}.${idx.tablename}`,
\n size: idx.index_size
\n }))
\n });
\n }<\/p>\n
this.alerts = alerts;
\n return alerts;
\n }<\/p>\n
\/\/ Get performance report
\n async generateReport() {
\n const stats = await this.collectStats();<\/p>\n
return {
\n timestamp: new Date(),
\n summary: {
\n totalQueries: this.metrics.size,
\n alertsCount: this.alerts.length,
\n healthScore: this.calculateHealthScore(stats)
\n },
\n metrics: stats,
\n alerts: this.alerts,
\n recommendations: this.generateRecommendations(stats, this.alerts)
\n };
\n }<\/p>\n
\/\/ Calculate database health score
\n calculateHealthScore(stats) {
\n let score = 100;<\/p>\n
\/\/ Deduct points for slow queries
\n const slowQueries = stats.queries[‘Slow Queries’] || [];
\n score -= Math.min(slowQueries.length * 5, 30);<\/p>\n
\/\/ Deduct points for high connection utilization
\n const totalConns = parseInt(stats.connections.total_connections || 0);
\n const activeConns = parseInt(stats.connections.active_connections || 0);
\n const utilization = totalConns > 0 ? activeConns \/ totalConns : 0;
\n if (utilization > 0.8) score -= 20;<\/p>\n
\/\/ Deduct points for tables needing maintenance
\n const tablesNeedingMaintenance = stats.tables.filter(table => {
\n if (!table.last_vacuum) return true;
\n const lastVacuum = new Date(table.last_vacuum);
\n const daysSinceVacuum = (Date.now() – lastVacuum) \/ (1000 * 60 * 60 * 24);
\n return daysSinceVacuum > 30;
\n });
\n score -= Math.min(tablesNeedingMaintenance.length * 3, 20);<\/p>\n
return Math.max(0, score);
\n }<\/p>\n
\/\/ Generate optimization recommendations
\n generateRecommendations(stats, alerts) {
\n const recommendations = [];<\/p>\n
\/\/ Query optimization recommendations
\n const slowQueries = stats.queries[‘Slow Queries’] || [];
\n if (slowQueries.length > 0) {
\n recommendations.push({
\n category: ‘Query Optimization’,
\n priority: ‘high’,
\n action: ‘Analyze and optimize slow queries’,
\n details: [
\n ‘Use EXPLAIN ANALYZE to analyze query execution’,
\n ‘Add appropriate indexes for frequently queried columns’,
\n ‘Consider query restructuring’,
\n ‘Review JOIN operations’
\n ]
\n });
\n }<\/p>\n
\/\/ Index optimization recommendations
\n const unusedIndexes = stats.indexes.filter(idx =>
\n parseInt(idx.idx_scan || 0) === 0
\n );
\n if (unusedIndexes.length > 0) {
\n recommendations.push({
\n category: ‘Index Optimization’,
\n priority: ‘medium’,
\n action: ‘Remove unused indexes’,
\n details: [
\n `${unusedIndexes.length} indexes are never used`,
\n ‘Consider dropping indexes that add overhead’,
\n ‘Monitor index usage regularly’
\n ]
\n });
\n }<\/p>\n
\/\/ Maintenance recommendations
\n const tablesNeedingMaintenance = stats.tables.filter(table => {
\n if (!table.last_vacuum) return true;
\n const lastVacuum = new Date(table.last_vacuum);
\n const daysSinceVacuum = (Date.now() – lastVacuum) \/ (1000 * 60 * 60 * 24);
\n return daysSinceVacuum > 30;
\n });
\n if (tablesNeedingMaintenance.length > 0) {
\n recommendations.push({
\n category: ‘Maintenance’,
\n priority: ‘medium’,
\n action: ‘Perform regular maintenance’,
\n details: [
\n `${tablesNeedingMaintenance.length} tables need VACUUM`,
\n ‘Set up regular VACUUM and ANALYZE schedules’,
\n ‘Monitor table bloat’
\n ]
\n });
\n }<\/p>\n
\/\/ Connection pool recommendations
\n const totalConns = parseInt(stats.connections.total_connections || 0);
\n const activeConns = parseInt(stats.connections.active_connections || 0);
\n const utilization = totalConns > 0 ? activeConns \/ totalConns : 0;
\n if (utilization > 0.8) {
\n recommendations.push({
\n category: ‘Connection Management’,
\n priority: ‘high’,
\n action: ‘Optimize connection pool’,
\n details: [
\n `Connection utilization: ${(utilization * 100).toFixed(1)}%`,
\n ‘Consider increasing max pool size’,
\n ‘Optimize application connection handling’,
\n ‘Review long-running queries’
\n ]
\n });
\n }<\/p>\n
return recommendations;
\n }<\/p>\n
\/\/ Export metrics untuk external monitoring
\n async exportMetrics() {
\n const stats = await this.collectStats();<\/p>\n
return {
\n timestamp: new Date().toISOString(),
\n database: {
\n connections: {
\n total: stats.connections.total_connections,
\n active: stats.connections.active_connections,
\n idle: stats.connections.idle_connections,
\n utilization: stats.connections.total_connections > 0
\n ? (stats.connections.active_connections \/ stats.connections.total_connections * 100).toFixed(2)
\n : 0
\n },
\n queries: {
\n slowCount: (stats.queries[‘Slow Queries’] || []).length,
\n frequentCount: (stats.queries[‘Most Frequent Queries’] || []).length
\n },
\n health: {
\n score: this.calculateHealthScore(stats),
\n alerts: this.alerts.length
\n }
\n }
\n };
\n }
\n}<\/p>\n
module.exports = DatabasePerformanceMonitor;
\n“`<\/p>\n
Kesimpulan<\/p>\n
Database performance optimization adalah ongoing process yang membutuhkan continuous monitoring, analysis, dan optimization. Dengan implementasi comprehensive strategies dari query optimization, caching, connection pooling, hingga monitoring, kita dapat achieve significant performance improvements.<\/p>\n
Key optimization strategies:
\n\u2022 Query Optimization: Proper indexing, efficient query writing, dan execution plan analysis
\n\u2022 Caching Strategy: Multi-level caching dengan appropriate invalidation policies
\n\u2022 Connection Management: Optimal connection pooling configuration
\n\u2022 Monitoring: Comprehensive performance monitoring dengan alerting
\n\u2022 Maintenance: Regular database maintenance tasks (VACUUM, ANALYZE, etc.)<\/p>\n
Remember: Database optimization is about finding the right balance between read performance, write performance, storage efficiency, and maintenance overhead.<\/p>\n
Start with measuring performance, identify bottlenecks, implement optimizations iteratively, dan measure the impact of each change.<\/p>\n","protected":false},"excerpt":{"rendered":"
Database performance adalah critical factor yang menentukan success dari web applications. Slow database queries dapat menyebabkan poor user experience, high server costs, dan scalability issues. Di tahun 2025, dengan data volumes yang terus meningkat dan user expectations yang semakin tinggi, database optimization menjadi lebih penting dari sebelumnya. Artikel ini akan membahas comprehensive database optimization strategies […]<\/p>\n","protected":false},"author":6,"featured_media":4459,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"yoast_head":"\n