The Index Mythology That Won’t Die
Every week, some well-meaning developer discovers database performance optimization and promptly starts adding indexes to everything that moves. I’ve seen production tables with fifteen indexes on twelve columns, each one added by someone who read that indexes make queries faster. The performance didn’t improve. Sometimes it got worse.
Here’s what actually happens when you throw indexes at a wall. Each additional index needs maintenance during writes. Your INSERT statements slow down because the database engine has to update multiple index structures. Your disk usage explodes. Query planner gets confused by too many options and starts making bad choices. I’ve debugged systems where removing indexes improved performance more than adding them ever did.
The real work happens in understanding query execution plans. Not the pretty diagrams that database tools generate, but the actual cost estimates and row counts. When PostgreSQL tells you it’s doing a sequential scan on a million-row table, that’s not always wrong. Sometimes the selectivity is so poor that an index scan would need more disk seeks than just reading the whole table linearly.
Connection Pool Cargo Culting
Connection pooling has become the database equivalent of “have you tried turning it off and on again.” Teams implement HikariCP or pgbouncer without understanding what problem they’re actually solving. I’ve seen applications with connection pools configured for 200 connections hitting databases that perform best with 20.
Database servers aren’t web servers. They don’t scale horizontally by adding more connections. PostgreSQL, for instance, creates a separate process for each connection. More processes mean more context switching, more memory overhead, and more lock contention. Oracle handles this differently with its shared server architecture, but the principle remains: connections are expensive resources.
The sweet spot for connection count depends on your storage subsystem, not your application load. If you’re running on spinning disks, you probably want fewer connections than you think. SSDs change the equation, but not in the way most people assume. I’ve seen properly tuned single-connection workloads beat multi-connection chaos by orders of magnitude.
The Query Optimization Arms Race
Query optimization advice tends to focus on SQL gymnastics while ignoring fundamental data modeling problems. You can’t optimize your way out of a poorly normalized schema or wrong data types. I’ve watched teams spend weeks crafting elaborate CTE structures when the real issue was storing JSON in text fields and parsing it at query time.
Subquery rewriting gets treated like some kind of dark art. Move the subquery to a JOIN. Convert the EXISTS to an IN. Use window functions instead of correlated subqueries. These transformations work sometimes, but modern query optimizers already do most of these rewrites automatically. PostgreSQL’s optimizer has been doing subquery flattening since version 8.4.
The performance gains come from understanding data distribution and cardinality estimation. When your query planner estimates 1,000 rows but gets 100,000, no amount of SQL rewriting will fix the resulting performance disaster. Accurate statistics matter more than clever syntax. Run ANALYZE. Update your histogram bounds. Understand how your optimizer makes decisions based on the statistics it has available.
Hardware Theater and Configuration Mythology
Database performance discussions always turn into hardware specifications and configuration parameter tuning. More RAM, faster SSDs, increase shared_buffers, tune work_mem. These changes might help, but they’re treating symptoms rather than causes.
I’ve seen databases perform poorly on high-end hardware because the application was making fundamentally inefficient data access patterns. Fetching records one at a time in a loop instead of using batch operations. Running thousands of identical queries with different parameters instead of using prepared statements properly. No amount of RAM will fix an N+1 query problem.
Configuration tuning follows the same pattern. Teams obsess over buffer pool sizes and checkpoint intervals while ignoring obvious inefficiencies in their data access patterns. PostgreSQL’s default configuration works reasonably well for many workloads. MySQL’s defaults are more conservative but rarely the bottleneck. The real performance problems usually live in the application layer, not the database configuration.
Measuring What Actually Matters
Database performance monitoring tools generate impressive dashboards full of metrics that don’t relate to user experience. Query throughput, connection counts, buffer hit ratios. These numbers look scientific, but they often miss the actual performance problems.
Response time distribution tells you more than average query duration ever will. Your 95th percentile latency might be fine while your 99th percentile is terrible. A handful of bad queries can make aggregate metrics look healthy while users experience timeouts and errors. I’ve debugged production systems where average query time was 50 milliseconds, but 1% of queries took over 30 seconds.
Lock contention shows up in wait event analysis, not in CPU graphs. Hot blocks, buffer busy waits, and lock acquisition patterns reveal bottlenecks that don’t appear in traditional system monitoring. PostgreSQL’s pg_stat_statements extension and MySQL’s Performance Schema provide this level of detail, but most teams never look beyond basic resource utilization.
Real performance optimization takes discipline and measurement. It means profiling your actual workload, understanding your data access patterns, and making targeted changes based on evidence rather than folklore. The database optimization world is full of silver bullets that turned out to be blanks.
What’s your experience been with database performance optimization? Have you run into other examples of conventional wisdom that didn’t hold up under scrutiny? I’m particularly interested in hearing about cases where counterintuitive approaches actually worked in production environments.