Database

So here’s the thing nobody tells you about JSONB in Postgres: it’s fast until it’s not. We migrated our user preferences table to use JSONB columns three months ago. The pitch was simple - no more ALTER TABLE every time marketing wants to track a new preference. Just stuff it in JSON and call it a day. Seemed smart at the time. The honeymoon phase First two weeks were great. Engineers loved it. Product loved it. We shipped features faster because we didn’t need schema migrations for every little thing. Our user_settings column grew from tracking 5 fields to 23. No problem, right? ...

Indexes are supposed to make queries fast. Sometimes they make them slower. Here’s what I wish someone told me before I tanked production performance trying to “optimize” our database. The query that started it all Support said the admin dashboard was timing out. Found this in the slow query log: SELECT * FROM orders WHERE customer_id = 12345 AND status IN ('pending', 'processing') AND created_at > '2025-12-01' ORDER BY created_at DESC LIMIT 20; Took 8 seconds on a table with 4 million rows. Obviously needs an index, right? ...

PostgreSQL’s work_mem parameter is one of the most impactful yet misunderstood configuration settings for database performance. This post explores how adjusting work_mem can dramatically improve query execution times, especially for operations involving sorting, hashing, and joins. Understanding work_mem work_mem specifies the amount of memory PostgreSQL can use for internal sort operations and hash tables before writing to temporary disk files. The default value is 4MB, which is conservative to ensure PostgreSQL runs on smaller machines. ...

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: ...

MongoDB performance optimization requires a systematic approach to identify bottlenecks, analyze query patterns, and implement effective indexing strategies. This guide covers profiling techniques, index optimization, and load testing methodologies. Understanding MongoDB performance MongoDB performance depends on several factors: Query patterns: How queries are structured and what operations they perform Index usage: Whether queries can leverage indexes effectively Data volume: Size of collections and documents Hardware resources: CPU, memory, and disk I/O capacity Connection pooling: How applications manage database connections Profiling MongoDB queries Enable profiling MongoDB’s profiler collects detailed information about query execution: ...