# Database Optimization: Performance Tuning and Best Practices Database optimization is crucial for maintaining high-performance applications. Let's explore essential techniques and best practices for optimizing database performance. ## Query Optimization ### 1. Index Management Implement efficient indexing strategies: ```sql -- Create composite index for frequently used columns CREATE INDEX idx_users_email_status ON users(email, status); -- Create partial index for specific conditions CREATE INDEX idx_active_users ON users(created_at) WHERE status = 'active'; -- Create covering index for common queries CREATE INDEX idx_posts_author_date ON posts(author_id, created_at) INCLUDE (title, status); -- Analyze index usage SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes ORDER BY idx_scan DESC; ``` ### 2. Query Performance Analysis Analyze and optimize query execution: ```typescript // database/query-analyzer.ts import { Pool } from "pg"; export class QueryAnalyzer { constructor(private pool: Pool) {} async analyzeQuery(query: string): Promise { const explain = await this.pool.query(`EXPLAIN ANALYZE ${query}`); return this.parseExplainOutput(explain.rows); } async identifySlowQueries(threshold: number): Promise { const query = ` SELECT query, calls, total_time / calls as avg_time, rows / calls as avg_rows FROM pg_stat_statements WHERE total_time / calls > $1 ORDER BY avg_time DESC LIMIT 10 `; const result = await this.pool.query(query, [threshold]); return result.rows; } async suggestIndexes(): Promise { const query = ` SELECT schemaname, tablename, attname, seq_scan, idx_scan FROM pg_stat_user_tables t JOIN pg_stats s ON t.relname = s.tablename WHERE seq_scan > idx_scan AND n_distinct > 100 ORDER BY seq_scan DESC `; const result = await this.pool.query(query); return result.rows.map((row) => ({ table: row.tablename, column: row.attname, seqScans: row.seq_scan, suggestion: `Consider adding index on ${row.tablename}(${row.attname})`, })); } } // Example usage const analyzer = new QueryAnalyzer(pool); const analysis = await analyzer.analyzeQuery( "SELECT * FROM users WHERE email LIKE $1" ); console.log(analysis); ``` ## Database Schema Optimization ### 1. Table Partitioning Implement table partitioning for large tables: ```sql -- Create partitioned table CREATE TABLE events ( id SERIAL, event_type VARCHAR(50), created_at TIMESTAMP, payload JSONB ) PARTITION BY RANGE (created_at); -- Create partitions CREATE TABLE events_2024_q1 PARTITION OF events FOR VALUES FROM ('2024-01-01') TO ('2024-04-01'); CREATE TABLE events_2024_q2 PARTITION OF events FOR VALUES FROM ('2024-04-01') TO ('2024-07-01'); -- Create indexes on partitions CREATE INDEX idx_events_2024_q1_type ON events_2024_q1(event_type); CREATE INDEX idx_events_2024_q2_type ON events_2024_q2(event_type); ``` ### 2. Normalization and Denormalization Balance between normalization and denormalization: ```typescript // database/schema-optimizer.ts interface TableStats { tableName: string; rowCount: number; avgRowSize: number; totalSize: number; indexSize: number; } export class SchemaOptimizer { async analyzeTableStats(): Promise { const query = ` SELECT relname as table_name, n_live_tup as row_count, pg_total_relation_size(relid) as total_size, pg_indexes_size(relid) as index_size, pg_relation_size(relid) / n_live_tup as avg_row_size FROM pg_stat_user_tables WHERE n_live_tup > 0 ORDER BY total_size DESC `; const result = await this.pool.query(query); return result.rows; } async suggestDenormalization(): Promise { const query = ` SELECT relname, seq_scan, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables WHERE n_live_tup > 100000 ORDER BY seq_scan DESC `; const result = await this.pool.query(query); return result.rows.map((row) => ({ table: row.relname, suggestion: this.generateDenormalizationSuggestion(row), })); } private generateDenormalizationSuggestion(stats: any): string { if (stats.seq_scan > 1000 && stats.n_live_tup > 500000) { return `Consider denormalizing frequently accessed data from ${stats.relname}`; } return `Monitor query patterns on ${stats.relname} for potential denormalization`; } } ``` ## Query Caching ### 1. Redis Caching Implementation Implement efficient caching strategies: ```typescript // database/cache.service.ts import Redis from "ioredis"; export class CacheService { private redis: Redis; private readonly DEFAULT_TTL = 3600; // 1 hour constructor() { this.redis = new Redis({ host: process.env.REDIS_HOST, port: parseInt(process.env.REDIS_PORT!), password: process.env.REDIS_PASSWORD, }); } async get(key: string): Promise { const cached = await this.redis.get(key); return cached ? JSON.parse(cached) : null; } async set( key: string, value: any, ttl: number = this.DEFAULT_TTL ): Promise { await this.redis.set(key, JSON.stringify(value), "EX", ttl); } async invalidate(pattern: string): Promise { const keys = await this.redis.keys(pattern); if (keys.length > 0) { await this.redis.del(...keys); } } generateCacheKey(prefix: string, params: Record): string { const sortedParams = Object.entries(params) .sort(([a], [b]) => a.localeCompare(b)) .map(([key, value]) => `${key}:${value}`) .join(":"); return `${prefix}:${sortedParams}`; } } // Example usage with repository pattern export class UserRepository { constructor(private db: Pool, private cache: CacheService) {} async findById(id: string): Promise { const cacheKey = this.cache.generateCacheKey("user", { id }); const cached = await this.cache.get(cacheKey); if (cached) { return cached; } const result = await this.db.query("SELECT * FROM users WHERE id = $1", [ id, ]); const user = result.rows[0] || null; if (user) { await this.cache.set(cacheKey, user); } return user; } async update(id: string, data: Partial): Promise { const result = await this.db.query( "UPDATE users SET ... WHERE id = $1 RETURNING *", [id, ...Object.values(data)] ); const user = result.rows[0]; // Invalidate cache await this.cache.invalidate(`user:id:${id}`); return user; } } ``` ## Connection Pool Management ### 1. Database Pool Configuration Optimize connection pool settings: ```typescript // database/pool.config.ts import { Pool } from "pg"; export class DatabasePool { private static instance: Pool; static getInstance(): Pool { if (!this.instance) { this.instance = new Pool({ host: process.env.DB_HOST, port: parseInt(process.env.DB_PORT!), database: process.env.DB_NAME, user: process.env.DB_USER, password: process.env.DB_PASSWORD, // Pool configuration max: 20, // Maximum number of clients idleTimeoutMillis: 30000, connectionTimeoutMillis: 2000, maxUses: 7500, // Maximum number of uses before client is destroyed }); // Pool error handling this.instance.on("error", (err) => { console.error("Unexpected error on idle client", err); process.exit(-1); }); // Pool monitoring this.instance.on("connect", (client) => { console.log("New client connected"); }); this.instance.on("remove", (client) => { console.log("Client removed from pool"); }); } return this.instance; } static async getPoolStats(): Promise { const pool = this.getInstance(); return { totalCount: pool.totalCount, idleCount: pool.idleCount, waitingCount: pool.waitingCount, }; } } ``` ## Best Practices 1. **Regular Maintenance**: Schedule regular VACUUM and ANALYZE operations 2. **Monitor Query Performance**: Use tools like pg_stat_statements 3. **Optimize Indexes**: Create and maintain efficient indexes 4. **Connection Management**: Properly configure connection pools 5. **Caching Strategy**: Implement appropriate caching mechanisms 6. **Query Optimization**: Write efficient queries and use prepared statements 7. **Schema Design**: Design schemas with performance in mind 8. **Regular Monitoring**: Set up comprehensive database monitoring ## Implementation Checklist 1. Set up monitoring tools 2. Implement connection pooling 3. Configure query caching 4. Optimize database indexes 5. Implement partitioning strategy 6. Set up maintenance schedules 7. Configure backup solutions 8. Document optimization strategies ## Conclusion Database optimization is an ongoing process that requires regular monitoring and maintenance. Focus on implementing these practices to maintain high performance and scalability. ## Resources - [PostgreSQL Documentation](https://www.postgresql.org/docs/) - [Database Indexing Strategies](https://use-the-index-luke.com/) - [Redis Documentation](https://redis.io/documentation) - [Database Performance Monitoring](https://www.datadoghq.com/blog/database-monitoring/)

Blog & Insights

#sql

Database Optimization: Performance Tuning and Best Practices