How should a database bottleneck found during load testing be optimized?
Do not begin with Redis or sharding. Reproduce the load test and identify whether the real limit is a slow query, missing index, lock wait, exhausted connection pool, I/O, memory, write amplification, or capacity. Retest after correcting queries and transactions; scale only if a well-tuned node still misses the target.
Application threads, networking, serialization, or the load generator can fail first and still make requests appear to wait on the database. Unrealistic distributions and tests with no think time can manufacture hotspots. Wavesteam therefore fixes the software version, data volume, workload model, and pass criteria and observes both application and database before changing one factor at a time.
When decomposing features, data, and acceptance scenarios, also compare Is a project still custom development if it reuses an order or commerce module? and What data does reliable shipment tracking need, and how should it integrate with carriers?; the linked guidance adds context that should be considered in the same decision.
| Evidence | Likely cause | First action | Avoid initially |
|---|---|---|---|
| A few queries dominate time and scan far more rows than returned | Missing index, poor join, non-indexable predicate, or N+1 | Inspect the plan; change query, index, or batching | Hiding it with a larger server |
| Lock waits and deadlocks rise while throughput falls | Long transactions, inconsistent lock order, or hot rows | Shorten transactions and standardize update order | Using cache to solve write contention |
| Connections are exhausted while CPU and I/O remain low | Leaks, pool settings, or blocked requests | Trace lifecycle, timeouts, and pool queues | Raising limits without bounds |
| Storage latency or WAL/redo load is high | Random access, excess indexes, tiny writes, or weak storage | Batch writes and review indexes and storage | Read replicas for a primary-write problem |
| CPU remains saturated after useful queries are optimized | Heavy computation, sorting, aggregation, or insufficient instance | Remove work, pre-aggregate, or scale vertically | Unproven sharding |
For PostgreSQL, plans, statistics, slow-query extensions, and lock views are useful; its performance guidance explains plan costs. MySQL offers slow-query data, Performance Schema, and the official EXPLAIN documentation. Actual-execution analysis can change data or add production load, so reproduce it on an equivalent test copy where possible.
Indexes are not free. Column order in a compound index must match filtering, ordering, and joins and be verified with realistic cardinality. Every index adds write, storage, and maintenance cost. Prefer cursor pagination with a stable key over deep offsets, retrieve only needed columns, batch related reads, and eliminate looped N+1 calls.
Keep transactions short without sacrificing business consistency. Do not wait for users, slow third parties, or file processing inside a transaction. Commit recoverable batch jobs in smaller units, use one lock order, and make retries idempotent. Validate conditional updates, queues, or suitable concurrency control for inventory and payment hotspots rather than blindly increasing isolation or adding distributed locks.
Cache only frequently read data that may be stale for a defined interval. Include tenant and version in keys, specify invalidation, and protect against penetration and stampedes. Price, permission, balance, inventory, and settlement must not return an incorrect fact. Measure hit rate, database time saved, and invalidation incidents.
Vertical scaling is often a sensible intermediate step. Read replicas help eligible reads that tolerate lag; they do not increase primary write capacity, and read-after-write routing needs explicit handling. Sharding has no universal record-count threshold: node limits, growth, hotspots, cross-shard transactions, and team capability decide it.
A credible report records data size and distribution, arrival and scenario mix, cache state, instance and software versions, P50/P95/P99, throughput, errors, database resources, connections, locks, replication lag, and top queries by total time. Rerun the same workload after each optimization and retain peak and failure headroom. Wavesteam prioritizes reversible, evidenced improvements before architecture changes; the Google SRE Book provides broader reliability context.