Last Tuesday at 2:47 AM, I watched our payment processing API grind to a halt because a single query decided to table scan 40 million rows instead of using the perfectly good index sitting right there. The query planner had outsmarted itself into stupidity, and our customers were getting timeout errors while trying to buy concert tickets. This is the moment when database hints stop being theoretical and become your best friend.
Most developers treat database optimization like a black box. Write query, hope for best, curse when it’s slow. But there’s a middle ground between blind faith and building your own storage engine. Today we’re looking at the techniques that actually move the needle when your database starts showing its age.
Index Strategy Beyond the Obvious
Everyone knows about indexes. Everyone thinks they’re using them correctly. Most people are wrong. The trick isn’t creating indexes; it’s creating the right ones and avoiding the wrong ones. I’ve seen production databases with 47 indexes on a single table because developers kept adding “just one more” instead of understanding what they actually needed.
Composite indexes are where the magic happens. If you’re regularly querying by user_id and created_at together, create an index on (user_id, created_at) rather than separate indexes on each column. PostgreSQL’s partial indexes are particularly elegant here. Instead of indexing every row in your orders table, index only WHERE status = ‘pending’. Your pending orders queries will fly, and you won’t waste space indexing completed orders that rarely get searched.
Here’s the counterintuitive part: sometimes removing indexes improves performance. Every index you create slows down writes. If you have an index that’s never used (check your database’s index usage stats), drop it. Your INSERT statements will thank you.
Query Planning and the Art of Gentle Persuasion
Query planners are smart but not telepathic. They make educated guesses based on statistics, but sometimes their education is wrong. This is where hints come in, and this is where your ORM starts screaming. Most ORMs can’t handle database-specific hints gracefully, which is why they pretend hints don’t exist.
In PostgreSQL, you can force an index with something like /*+ IndexScan(orders ix_orders_user_created) */. MySQL has USE INDEX hints. SQL Server has WITH (INDEX=index_name). The syntax varies, but the concept is universal: sometimes you know better than the planner. Use this power responsibly. Hints are like sudo commands for your database.
The real trick is understanding when to step in. Run EXPLAIN ANALYZE on your slow queries. Look for table scans where you expected index seeks. Look for nested loop joins when you wanted hash joins. The query planner will tell you exactly what it’s thinking, and once you understand its reasoning, you can correct its assumptions.
Connection Pooling That Actually Works
Most applications treat database connections like cheap date night restaurants: grab one when you need it, abandon it when you’re done. This approach works until it doesn’t, usually right around the time you hit 100 concurrent users and your database starts rejecting connections like a bouncer at an exclusive club.
PgBouncer for PostgreSQL is criminally underused. It sits between your application and database, maintaining a pool of actual database connections while your application thinks it has dedicated connections. Configure it in transaction pooling mode, and you can serve 1000 application connections with 20 database connections. The performance improvement is dramatic because PostgreSQL connections are expensive to create and destroy.
For MySQL, ProxySQL does similar magic with more advanced routing options. It can send read queries to replicas and write queries to the primary automatically. Set up different connection pools for different types of queries. Send your heavy analytics queries to a separate read replica so they don’t interfere with user-facing operations.
The Sneaky Performance Killers
N+1 queries are the classic villain, but they’re not the only performance killer lurking in your codebase. DISTINCT is often unnecessary and always expensive. If you’re using DISTINCT to fix duplicate results, fix the JOIN instead. COUNT(*) on large tables without WHERE clauses will lock up your database while it counts every single row.
Subqueries in SELECT clauses execute once per row. A query that looks innocent can trigger thousands of additional queries under the hood. Replace correlated subqueries with JOINs or window functions. Your database will process the same data with a fraction of the work.
JSON columns are incredibly convenient until they’re not. PostgreSQL’s JSONB is fast for simple key lookups but terrible for complex nested queries. If you’re regularly querying deep into JSON structures, consider normalizing that data into proper columns with proper indexes. Sometimes old-school relational design beats modern convenience.
Monitoring That Matters
Most database monitoring focuses on the wrong metrics. CPU usage and memory consumption tell you when you have a problem, not why you have a problem. Query execution time distributions tell a better story. pg_stat_statements in PostgreSQL shows you exactly which queries are eating your resources. Enable it, and suddenly you have data instead of guesses.
Slow query logs are your database’s way of telling you what hurts. In MySQL, set long_query_time to 1 second and log queries that don’t use indexes. In PostgreSQL, log_min_duration_statement does the same thing. Don’t just collect this data; actually read it. The patterns will surprise you.
Lock contention monitoring reveals problems that don’t show up in typical metrics. Queries might complete quickly individually but spend most of their time waiting for locks. PostgreSQL’s pg_locks view and MySQL’s performance_schema.data_locks table expose what’s really happening when your application suddenly gets slow for no apparent reason.
Database optimization isn’t about memorizing magic tricks or following generic best practices. It’s about understanding your specific workload and giving your database the information it needs to make good decisions. The next time you’re staring at a slow query at 3 AM, remember that your database wants to be fast. Sometimes it just needs a little help figuring out how.