Database Performance Testing: Find Slow Queries Early

A load test that only reports endpoint response times tells you that something got slow. It rarely tells you why, and very often the answer is in the database. Application servers are easy to scale out: add instances and traffic spreads across them. The primary database usually stays a single machine, so that is where the extra load lands.

Database performance testing, sometimes called database load testing, closes that gap. You run load against the system while watching the database itself, so a slow endpoint can be traced to the query, lock or connection limit behind it.

Why databases behave differently under load

Most database problems are invisible at low traffic. A developer clicks through a feature locally and every query returns in milliseconds. The same code behaves differently with hundreds of concurrent users and a production-sized table:

• Missing indexes. A full table scan on 1,000 rows is instant. On 20 million rows it takes seconds, and under concurrency those scans compete for the same disk and memory.
• N+1 queries. A page runs one query for a list, then one more for each item in it. Fifty items means fifty-one round trips per request, for every user.
• Lock contention. Two transactions updating the same rows wait for each other. At low traffic the wait is too short to notice. Under load, requests queue behind the lock.
• Connection pool exhaustion. Each app instance holds a fixed number of database connections. When all of them are busy, new requests wait for a free one and then time out.
• Plan changes. The query planner picks a strategy based on table size and statistics. As data grows, it can switch to a much slower plan without any code change.

Start with realistic data

When a database performance test passes and production still slows down, the cause is usually the data, not the load. A staging database with a few thousand rows hides every problem in the list above.

What matters is volume and shape:

• Volume close to production, or to where production will be in a year. Index problems only show up at scale.
• Realistic distribution. If one customer owns 40% of all orders, test with that customer. Queries filtered by a large customer behave very differently from queries for a small one.
• Realistic history. Old, archived and soft-deleted records still sit in the tables and still get scanned.

If production data can't leave production, generate synthetic data with the same row counts and distribution. The options are in test data management, and the reasons a small staging environment misleads are in staging environment parity.

Design the load to reach the database

Many load scripts replay read requests against a few popular pages. Those requests hit caches, and the database barely notices. To test the database, the scenario needs to:

• Mix reads and writes in roughly the proportion production sees. Writes take locks, update indexes and write to the transaction log.
• Vary the inputs. Searching for the same product a thousand times tests the cache. Use different IDs, filters and date ranges.
• Include background work. Reports, batch imports and scheduled jobs run alongside user traffic in production. Run them during the test too.
• Ramp up slowly, then hold. A gradual ramp shows the load level where latency starts to climb. A hold of an hour or more catches problems that build over time, like connection leaks and growing tables.

In k6, a read scenario and a write scenario running side by side, with a threshold on the writes, look like this:

export const options = {
  scenarios: {
    browse: {
      executor: "ramping-vus",
      exec: "browse",
      stages: [
        { duration: "10m", target: 200 },
        { duration: "60m", target: 200 },
      ],
    },
    checkout: {
      executor: "constant-arrival-rate",
      exec: "checkout",
      rate: 20,
      timeUnit: "1s",
      duration: "70m",
      preAllocatedVUs: 50,
    },
  },
  thresholds: {
    "http_req_duration{scenario:checkout}": ["p(95)<800"],
  },
};

browse and checkout are the exported functions that send the actual requests. The threshold uses p95 rather than the average, for the reasons in what load testing results actually mean.

What to measure during a database performance test

Response times from the load tool are only half the picture. During the run, also collect from the database:

• Latency per query, not just overall. In PostgreSQL that is pg_stat_statements, in MySQL the Performance Schema or the slow query log, in MongoDB the database profiler.
• Active connections compared to the pool limit.
• Lock waits and how long they last.
• CPU, memory and disk I/O on the database server.
• Cache hit ratio. When the working data no longer fits in memory, reads go to disk and latency jumps.
• Rows examined compared to rows returned. A query that reads a million rows to return ten usually needs an index.

Put all of this on the same timeline as the load test results. A latency spike in k6 at minute 25 should match something in the database at minute 25.

In PostgreSQL 13 and later, with the pg_stat_statements extension enabled, this lists the queries that cost the most in total:

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Sort by total time, not average time. A 5 ms query that runs 200,000 times costs more than a 2-second query that runs twice.

Reading the results

Common patterns and their usual causes:

• Latency rises steadily with load and database CPU is high: inefficient queries or missing indexes.
• Latency is flat, then drops off a cliff with timeouts: the connection pool is exhausted.
• Latency spikes in waves: lock contention or periodic maintenance jobs.
• One endpoint is slow even at low load: N+1 queries or a missing index.
• Results get worse over a long run: connection leaks, or tables and indexes growing faster than expected.

For each slow query, run EXPLAIN ANALYZE to see the actual plan and where the time goes. It executes the query, so wrap writes in a transaction and roll it back.

Fix one thing, then rerun the same test

Database fixes interact. An index that speeds up a query also slows down writes to that table. A bigger connection pool can move the bottleneck to CPU. Change one thing at a time, then rerun the same script against the same data, so the numbers before and after are comparable. Database optimization without a repeatable test means you can't tell whether a change helped.

Keep the scripts and the dataset in version control, and run them before releases that touch the schema or add heavy queries. A dropped index or a new report query on a large table passes code review easily and only shows its cost under production load.

Database performance testing is load testing with the database in view: production-sized data, a realistic mix of reads and writes, and query-level metrics on the same timeline as the load results. It is part of our performance testing work, alongside load, stress and spike tests.