Database

Database performance is critical for application scalability. Here are proven optimization techniques. 1. Indexing Strategy When to Index -- Index frequently queried columns CREATE INDEX idx_user_email ON users(email); -- Index foreign keys CREATE INDEX idx_post_user_id ON posts(user_id); -- Composite indexes for multi-column queries CREATE INDEX idx_user_status_role ON users(status, role); When NOT to Index Columns with low cardinality (few unique values) Frequently updated columns Small tables (< 1000 rows) 2. Query Optimization Avoid SELECT * -- Bad SELECT * FROM users WHERE id = 123; -- Good SELECT id, name, email FROM users WHERE id = 123; Use LIMIT -- Always limit large result sets SELECT * FROM posts ORDER BY created_at DESC LIMIT 20; Avoid N+1 Queries // Bad: N+1 queries users.forEach(user => { const posts = db.query('SELECT * FROM posts WHERE user_id = ?', [user.id]); }); // Good: Single query with JOIN const usersWithPosts = db.query(` SELECT u.*, p.* FROM users u LEFT JOIN posts p ON u.id = p.user_id `); 3. Connection Pooling // Configure connection pool const pool = mysql.createPool({ connectionLimit: 10, host: 'localhost', user: 'user', password: 'password', database: 'mydb', waitForConnections: true, queueLimit: 0 }); 4. Caching Application-Level Caching // Cache frequently accessed data const cache = new Map(); async function getUser(id) { if (cache.has(id)) { return cache.get(id); } const user = await db.query('SELECT * FROM users WHERE id = ?', [id]); cache.set(id, user); return user; } Query Result Caching -- Use query cache (MySQL) SET GLOBAL query_cache_size = 67108864; SET GLOBAL query_cache_type = 1; 5. Database Schema Optimization Normalize Properly -- Avoid over-normalization -- Balance between normalization and performance Use Appropriate Data Types -- Use smallest appropriate type TINYINT instead of INT for small numbers VARCHAR(255) instead of TEXT when possible DATE instead of DATETIME when time not needed 6. Partitioning -- Partition large tables by date CREATE TABLE logs ( id INT, created_at DATE, data TEXT ) PARTITION BY RANGE (YEAR(created_at)) ( PARTITION p2023 VALUES LESS THAN (2024), PARTITION p2024 VALUES LESS THAN (2025), PARTITION p2025 VALUES LESS THAN (2026) ); 7. Query Analysis EXPLAIN Plan EXPLAIN SELECT * FROM users WHERE email = '[email protected]'; Slow Query Log -- Enable slow query log SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; 8. Batch Operations // Bad: Multiple individual inserts users.forEach(user => { db.query('INSERT INTO users (name, email) VALUES (?, ?)', [user.name, user.email]); }); // Good: Batch insert const values = users.map(u => [u.name, u.email]); db.query('INSERT INTO users (name, email) VALUES ?', [values]); 9. Database Maintenance Regular Vacuuming (PostgreSQL) VACUUM ANALYZE; Optimize Tables (MySQL) OPTIMIZE TABLE users; 10. Monitoring Monitor query performance Track slow queries Monitor connection pool usage Watch for table locks Monitor disk I/O Best Practices Index strategically Optimize queries Use connection pooling Implement caching Normalize appropriately Use appropriate data types Partition large tables Analyze query performance Batch operations Regular maintenance Conclusion Database optimization requires: ...

Sizing Pool size ≈ CPU cores * 2–4 per service instance; avoid per-request opens. For PgBouncer tx-mode: disable session features; avoid session-prepared statements. Timeouts & limits Set ConnMaxLifetime, ConnMaxIdleTime, MaxOpenConns, MaxIdleConns. Add statement timeouts; enforce context deadlines on queries. Instrumentation Track pool acquire latency, in-use/idle, wait count, timeouts. Log slow queries; sample EXPLAIN ANALYZE in staging for heavy ones. Hygiene Use prepared statements judiciously; reuse sqlx.Named/pgx prepared for hot paths. Prefer keyset pagination; cap result sizes; parameterize everything. Checklist Pool sized and monitored. Query timeouts set; slow logs reviewed. No per-request connections; connections closed via context cancellation.

Query hygiene Add composite indexes matching filters/order; avoid leading wildcards. Use EXPLAIN to verify index usage; watch for filesort/temp tables. Prefer keyset pagination over OFFSET for large tables. Config basics Set innodb_buffer_pool_size (50-70% RAM), innodb_log_file_size, innodb_flush_log_at_trx_commit=1 (durable) or 2 (faster). max_connections aligned with app pool size; avoid connection storms. Enable slow query log with sane threshold; sample for tuning. App considerations Reuse connections (pooling); avoid long transactions. Limit selected columns; cap payload sizes; avoid large unbounded IN lists. For read-heavy workloads, add replicas; route reads carefully. Checklist Indexes audited; EXPLAIN reviewed. Buffer pool sized; slow log enabled. Pagination and payloads bounded; connections pooled.

Pooling Use a pool (pg/pgbouncer); size = (CPU * 2–4) per app instance; avoid per-request connections. For PgBouncer in transaction mode, avoid session features (temp tables, session prep statements). Query hygiene Parameterize queries; prevent plan cache thrash; set statement timeout. Add indexes; avoid wild % patterns; paginate with keyset when possible. Monitor slow queries; cap max rows returned; avoid huge JSON blobs. App settings Set statement_timeout, idle_in_transaction_session_timeout. Use prepared statements judiciously; for PgBouncer, prefer server-prepared off or use pgbouncer session mode. Pool instrumentation: queue wait time, checkout latency, timeouts. OS/DB basics Keep Postgres on same AZ/region; latency kills. Tune work_mem, shared_buffers, effective_cache_size appropriately (DB side). Use EXPLAIN (ANALYZE, BUFFERS) in staging for heavy queries. Checklist Pool sized and monitored; PgBouncer for many short connections. Query timeouts set; slow logs monitored. Key indexes present; pagination optimized. App-level metrics for pool wait, query latency, error rates.

The “object references an unsaved transient instance” error is common in Hibernate. Here’s how to fix it. Understanding the Error This error occurs when you try to save an entity that references another entity that hasn’t been persisted yet. // Error scenario User user = new User(); // Transient (not saved) Post post = new Post(); post.setUser(user); // References unsaved user postRepository.save(post); // Error! Solutions 1. Save Parent First // Save user first User user = new User(); user = userRepository.save(user); // Now managed Post post = new Post(); post.setUser(user); postRepository.save(post); // Works! 2. Use Cascade Types @Entity public class Post { @ManyToOne(cascade = CascadeType.PERSIST) private User user; } // Now saving post will save user too Post post = new Post(); post.setUser(new User()); postRepository.save(post); // Works! 3. Use @OneToMany with Cascade @Entity public class User { @OneToMany(mappedBy = "user", cascade = CascadeType.ALL) private List<Post> posts; } User user = new User(); Post post = new Post(); user.getPosts().add(post); post.setUser(user); userRepository.save(user); // Saves both Common Patterns Bidirectional Relationships @Entity public class User { @OneToMany(mappedBy = "user", cascade = CascadeType.ALL) private List<Post> posts = new ArrayList<>(); } @Entity public class Post { @ManyToOne @JoinColumn(name = "user_id") private User user; // Helper method public void setUser(User user) { this.user = user; if (user != null && !user.getPosts().contains(this)) { user.getPosts().add(this); } } } Best Practices Save parent entities first Use appropriate cascade types Maintain bidirectional relationships Use helper methods for relationships Handle null references Conclusion Fix Hibernate transient instance errors by: ...

The N+1 problem is a common performance issue in JPA. Here’s how to solve it in Spring Data JPA. Understanding N+1 Problem // N+1 queries: 1 for users + N for each user's posts List<User> users = userRepository.findAll(); for (User user : users) { List<Post> posts = postRepository.findByUserId(user.getId()); // N queries } Solutions 1. Eager Fetching @Entity public class User { @OneToMany(fetch = FetchType.EAGER) private List<Post> posts; } 2. Join Fetch (JPQL) @Query("SELECT u FROM User u JOIN FETCH u.posts") List<User> findAllWithPosts(); 3. Entity Graphs @EntityGraph(attributePaths = {"posts"}) @Query("SELECT u FROM User u") List<User> findAllWithPosts(); 4. Batch Fetching @Entity public class User { @BatchSize(size = 10) @OneToMany(fetch = FetchType.LAZY) private List<Post> posts; } Best Practices Use lazy loading by default Fetch associations when needed Use entity graphs Monitor query performance Use DTO projections Conclusion Solve N+1 problems with: ...

Redis is powerful for caching. Here are effective caching strategies. Cache-Aside Pattern async function getUser(id) { // Check cache let user = await redis.get(`user:${id}`); if (user) { return JSON.parse(user); } // Cache miss - fetch from DB user = await db.query('SELECT * FROM users WHERE id = ?', [id]); // Store in cache await redis.setex(`user:${id}`, 3600, JSON.stringify(user)); return user; } Write-Through Pattern async function updateUser(id, data) { // Update database const user = await db.update('users', id, data); // Update cache await redis.setex(`user:${id}`, 3600, JSON.stringify(user)); return user; } Cache Invalidation async function deleteUser(id) { // Delete from database await db.delete('users', id); // Invalidate cache await redis.del(`user:${id}`); } Best Practices Set appropriate TTL Handle cache misses Invalidate properly Monitor cache hit rate Use consistent key patterns Conclusion Implement effective Redis caching strategies! 🔴

Optimizing MongoDB queries is essential for performance. Here’s how. Indexing Create Indexes // Single field index db.users.createIndex({ email: 1 }); // Compound index db.users.createIndex({ status: 1, createdAt: -1 }); // Text index db.posts.createIndex({ title: "text", content: "text" }); Explain Queries db.users.find({ email: "[email protected]" }).explain("executionStats"); Query Optimization Use Projection // Bad: Fetches all fields db.users.find({ status: "active" }); // Good: Only needed fields db.users.find( { status: "active" }, { name: 1, email: 1, _id: 0 } ); Limit Results db.users.find({ status: "active" }) .limit(10) .sort({ createdAt: -1 }); Best Practices Create appropriate indexes Use projection Limit result sets Avoid $regex without index Monitor slow queries Conclusion Optimize MongoDB for better performance! 🍃

Elasticsearch is powerful for search. Here’s how to get started. Basic Operations Create Index await client.indices.create({ index: 'users', body: { mappings: { properties: { name: { type: 'text' }, email: { type: 'keyword' }, age: { type: 'integer' } } } } }); Index Document await client.index({ index: 'users', id: '1', body: { name: 'John Doe', email: '[email protected]', age: 30 } }); Search const result = await client.search({ index: 'users', body: { query: { match: { name: 'John' } } } }); Best Practices Design mappings carefully Use appropriate analyzers Optimize queries Monitor cluster health Backup regularly Conclusion Master Elasticsearch for powerful search! 🔍