Q36 of 38 · Performance
How do you test database query performance under realistic load?
Short answer
Short answer: Enable slow query logging during load tests, use EXPLAIN ANALYZE on queries that appear in the slow log, and measure query latency at both low and high concurrency. Database performance under load often differs dramatically from single-query benchmarks due to lock contention and connection pool exhaustion.
Detail
Single-query benchmarks (EXPLAIN ANALYZE) tell you how a query performs with no concurrency. They are necessary but not sufficient — a query that takes 5 ms alone can take 2,000 ms under 200 concurrent connections due to row locking, sequential scan under table-level lock, or connection pool exhaustion.
During load tests, capture:
- Slow query log with threshold set to 100 ms (reveals queries that degrade under load)
- Connection pool saturation (are all connections used? Is the pool queuing?)
- Lock wait events (using pg_stat_activity for PostgreSQL or SHOW PROCESSLIST for MySQL)
- Index hit rate and cache hit rate for the database
For regression detection at the query level: use a tool like pgBadger (PostgreSQL) to parse slow query logs from baseline and comparison runs and diff the top-N slow queries. A query that moves from 10 ms median to 800 ms median between releases is a clear regression even if overall p95 is still within SLO.