Database Performance Optimization — Fix the Queries Before You Scale the Hardware

Database performance problems follow a predictable pattern. The application works fine with 100 users. At 1,000 users, response times creep up. At 10,000 users, the database CPU pegs at 100% and everything grinds to a halt. The team's instinct is to upgrade the instance — more CPU, more RAM. This works temporarily, but the underlying problems remain and the costs escalate.

The most common bottlenecks we find, in order of frequency:

Missing indexes: A table scan on a 10-million-row table takes seconds. An index lookup takes milliseconds. Adding the right index is a one-line migration that can reduce query time by 1000x. But knowing which indexes to add requires analyzing actual query patterns, not guessing.

N+1 queries: Your ORM loads a list of 100 users, then fires 100 individual queries to load each user's profile. That is 101 queries where one JOIN would suffice. ORMs make this trivially easy to introduce and difficult to detect without query logging.

Connection pool exhaustion: Your application opens a new database connection for every request, hits the connection limit, and requests start queuing. Connection pooling (PgBouncer, ProxySQL) reuses connections and handles thousands of requests with a fraction of the connections.

Unoptimized queries: Queries that work correctly but inefficiently — using SELECT * when only two columns are needed, filtering in the application instead of the database, sorting without indexes, or using correlated subqueries that execute once per row.

Lock contention: Long-running transactions holding locks that block other queries. The typical cause is a migration that adds a column with a default value, locking the entire table for the duration of the rewrite.

Query Analysis: We enable pg_stat_statements (PostgreSQL) or the performance_schema (MySQL) to identify the most expensive queries by total execution time, frequency, and mean duration. The top 10 queries by total time usually account for 80% of database load. We analyze each one with EXPLAIN (ANALYZE, BUFFERS) to understand the execution plan and identify optimization opportunities.

Index Strategy: We design indexes based on actual query patterns, not theoretical best practices. Composite indexes are ordered by selectivity. Partial indexes cover common WHERE conditions. Covering indexes eliminate table lookups for frequently queried columns. We also identify unused indexes that slow down writes without benefiting reads — dropping them frees up storage and improves insert/update performance.

Connection Pooling: For PostgreSQL, we deploy PgBouncer in transaction pooling mode between your application and the database. This reduces the number of active database connections from hundreds (one per application instance) to tens (shared across all instances). For MySQL, we configure ProxySQL with connection multiplexing and query caching.

Caching Layer: For read-heavy workloads, we add Redis or Memcached as a caching layer. We implement cache-aside patterns in your application: check cache first, query database on miss, write to cache. Cache invalidation uses TTL-based expiry for eventually-consistent data or explicit invalidation on writes for strongly-consistent requirements. We configure the cache size, eviction policy, and key naming conventions.

Read Replicas: When the primary database handles both reads and writes, read replicas offload read queries. We configure replicas via Terraform, set up your application's connection routing (writes to primary, reads to replica), and configure monitoring for replication lag. For frameworks with ORM support (Rails, Django, Prisma), we configure the read/write split at the ORM level.

A comprehensive database performance optimization:

• Query audit — top expensive queries identified with execution plans and optimization recommendations
• Index strategy — new indexes added, unused indexes removed, composite indexes designed for your query patterns
• Connection pooling — PgBouncer or ProxySQL deployed and configured
• Caching layer — Redis/Memcached setup with cache-aside patterns implemented
• Read replicas — replica provisioned with application-level read/write routing
• Configuration tuning — PostgreSQL/MySQL parameters optimized for your workload profile
• Monitoring — query performance dashboards, slow query alerts, connection pool metrics
• Before/after metrics — documented performance improvement with p50/p95/p99 latency comparisons