Database load testing: why your app tests pass and your database still falls over

Last updated on
Monday
November
2026
Database load testing: why your app tests pass and your database still falls over
Your load test passed. p95 was green, error rate was zero, the release went out. Then peak traffic arrived, checkout slowed to a crawl, and the root cause was the database: a connection pool that ran dry, a lock queue on one hot row, or a query that was fast on 10,000 rows and slow on 40 million.
The database is the hardest tier to scale horizontally, so it is usually the first real bottleneck under load. It is also the tier most load tests treat as a black box behind an API.
This guide is for developers, QA engineers, and DevOps teams who already run application load tests and have never tested the data layer on purpose. By the end, you will have a repeatable method to find your database's saturation point, explain why it happens, and catch regressions before they ship.
What database load testing is (and isn't)
Database load testing means generating realistic concurrent database work and measuring latency, throughput, resource use, and failure behaviour as demand rises. It sits next to a few related practices that often get mixed up:
A benchmark score is useful for comparing instance types. It says little about your schema, your query mix, or your hot rows. HammerDB itself labels its workloads TPROC-C and TPROC-H to make clear they are derived from TPC standards, not official TPC results.
There are also two places to apply load:
- Through the application. You hit your API and the database receives load the way it does in production: through the ORM, the connection pool, and the cache. This tells you whether the service meets its SLO.
- Directly against the database. You send queries or transactions over the database driver. This isolates database capacity and lets you test one query, index, or configuration change at a time.
Most teams need both. We will come back to how to combine them.
What to measure
A database test needs two sets of numbers: what the client saw, and what the database was doing at the same moment. One without the other tells you something is slow, not why.
For query-level detail, enable the engine's own statistics before the run: pg_stat_statements on PostgreSQL, Performance Schema and the sys schema on MySQL, Query Store on SQL Server, the profiler on MongoDB.
Common bottlenecks to look for
Most database incidents under load come from a short list of causes. Knowing them in advance tells you which metrics to watch.
- Connection exhaustion. 10,000 virtual users usually map to 100 to 500 pooled connections. An undersized pool queues requests; an oversized one overwhelms the database with context switching.
- N+1 queries. An ORM issues one query per row instead of one join. Invisible at 10 users, dominant at 1,000.
- Missing or unused indexes. A sequential scan on a 50,000-row table is fast. On 50 million rows it is an outage.
- Lock contention on hot rows. Inventory counters, account balances, and sequence tables serialize every writer that touches them.
- Cache misses. When an application cache expires or restarts, the full read load lands on the database at once.
- Write amplification. Triggers, audit tables, and secondary indexes turn one logical write into several physical ones.
Designing a realistic test
The most precise result is useless if the workload is wrong. Realism matters more than tool choice.
- Use production-like data volume and skew. A 1 GB test dataset against a 1 TB production database turns an I/O-bound system into an in-memory benchmark. Keep the skew too: real traffic hits a few hot customers and recent rows, not uniformly random keys. If you cannot copy production for privacy reasons (GDPR, HIPAA), preserve its shape: cardinality, row sizes, null rates, and key distribution.
- Model the real query mix. Pull the top queries, their frequency, and the read/write ratio from
pg_stat_statements, the MySQL slow log, or Query Store. That list becomes your scenario. - Model arrival rate, not just concurrency. Production users keep arriving when the database slows down. Use an open workload model (users per second) so your test does the same. A closed model slows its own arrival rate when the system struggles, which hides the problem you are looking for.
- Mirror the connection setup. Same pool size, same pooler (PgBouncer, ProxySQL), same driver version as production.
- Plan data reset. Every run mutates data. Restore a snapshot or truncate between runs, and decide whether you are measuring a cold or a warm cache. Write it down.
- Record a baseline before changing anything. Same environment, same data, same load. Every later result is compared against it.
Choosing your approach
The hybrid sequence that works: run the API test, watch database metrics, reproduce the slowest query mix directly against the database, tune, then rerun the API test to confirm.
Where Gatling fits
Gatling covers both layers: realistic traffic through the endpoints that hit your database, and SQL load sent straight to the database over JDBC.
- Test as code. Scenarios are written in Java, Kotlin, Scala, JavaScript, or TypeScript and live in the same repository as the schema migrations they test.
- Open workload models.
constantUsersPerSec,rampUsersPerSec, andincrementUsersPerSeclet you hold an arrival rate steady while the database slows down, so tail latency is measured, not masked. - Real data from the database itself. The built-in JDBC feeder reads test data (customer IDs, order IDs, SKUs) straight from a database with any JDBC4 driver, so requests hit the same hot keys production does.
- Assertions as pass/fail gates. Fail the run on p99 latency or error rate, in code.
- Direct SQL load. For driver-level tests, Gatling Enterprise Edition includes a JDBC plugin built and maintained by Gatling, so you can load the database directly with the same scenarios, injection profiles, and reports as your API tests. Open-source users can use community JDBC plugins. Engine-native tools such as pgbench and sysbench are also a good complement for isolating the database.
A scenario that feeds real IDs from Postgres into a checkout API and steps the arrival rate up:
Five steps of five minutes each, from 20 to 100 new users per second, tell you exactly which level pushed the database past its knee.
{{card}}
Running the test
Run a sequence of tests, not one big one. Each answers a different question:
- Smoke. A few users for a couple of minutes. Confirms the script, data, and monitoring work.
- Baseline. Normal production load. The reference point for everything else.
- Step load to peak and beyond. Ramp in stages and hold each stage for 5 to 10 minutes so caches settle and queues reach steady state. Do not jump straight to maximum load: you will miss the point where latency starts to bend.
- Spike. A sudden jump in arrival rate. This is where connection storms, pool exhaustion, and autoscaling lag show up.
- Soak. Expected load for 4 to 24 hours. Autovacuum, table bloat, WAL or log growth, and memory leaks only appear with time.
While the test runs, watch database metrics on the same timeline as client results. If your load test reports in UTC and your database dashboard is in local time, fix that first. Also watch the load generator's own CPU and network: if the client saturates before the database does, you are testing the generator.
Reading the results
The output you want is a throughput-versus-latency curve, not a single TPS number.
Find the knee. As load increases, throughput rises roughly in line with it and latency stays flat. At some level, throughput flattens while p99 climbs sharply. That level is your practical capacity. Your safe operating point sits below it, with headroom for the peaks you expect.
Correlate the knee with a cause. At the moment latency bends, check which database signal moved first:
Separate database limits from everything else. If database CPU, I/O, locks, and pool waits all look healthy while API latency rises, the bottleneck is in the application, the network, or the load generator. Reproducing the same query mix directly against the database confirms it either way.
One rule for distributed tests: never average p99 values across load generators. Percentiles must be computed from the merged latency distribution.
[EDITOR NOTE: ADD SOURCED EXAMPLE RESULT: a real run's curve and the cause found at the knee. Not a hypothetical number.]
Making it repeatable in CI/CD
A database test you run once before a launch catches one regression. A test that runs on every schema migration catches all of them.
- Set performance budgets. Define them before you look at results: for example, p99 under 200 ms and errors under 0.1% at 1.5x expected peak. In Gatling, these live in the simulation as assertions, so a breach fails the build.
- Compare against a stored baseline. A 10% throughput drop on the same data and environment is a regression, even if absolute numbers still pass. Gatling Enterprise Edition tracks this across runs with run trends and run comparison.
- Split by cost. Run a short smoke test (a few minutes, low load) on every pull request that touches a migration, query, or ORM mapping. Run the full step-load and soak tests on a schedule or before a release.
- Version everything together. Keep the test, the schema migration, and the seed data scripts in the same repository, so a reviewer sees the performance test change alongside the query change.
Common mistakes
- Empty or tiny datasets. Everything fits in RAM, every query is fast, and the result says nothing about production.
- Averages instead of percentiles. A 40 ms average can hide a 2-second p99.
- A closed model for open traffic. When the database slows down, a closed-model test sends fewer requests and reports reassuring latency.
- Ignoring pool settings. Opening one connection per virtual user tests connection setup, not queries.
- A shared test database. Another team's batch job runs mid-test and your results become noise.
- Short tests on burstable cloud storage. Burst credits and T-class CPU credits make a 10-minute test look far better than a 2-hour one.
- Changing several variables at once. A new index, a bigger instance, and a driver upgrade in one run means no single result is attributable.
Conclusion
Your database will hit a limit. The only question is whether you find it in a test or in production. The method fits in four steps:
- Model the real workload: production-sized data, the real query mix, an open arrival rate.
- Load it in steps, through the API and directly, and watch database metrics on the same timeline.
- Find the knee and name its cause: pool, locks, CPU, I/O, or replication.
- Automate it: budgets as assertions, a baseline to compare against, a smoke test on every migration.
Start with the endpoints that hit your database hardest. Write the scenario in the language your team already uses, feed it real IDs from your database, and step the load until something bends.
{{card}}
About the author
FAQ
FAQ
It generates realistic concurrent queries and transactions against your data layer and measures latency, throughput, and resource use as load rises, so you find the point where performance degrades before production does.
Both. API tests show whether the service meets its SLO, including ORM, pooling, and caching. Direct tests isolate database capacity. Run the API test, reproduce the slowest query mix directly, tune, then rerun the API test.
p95 and p99 latency, throughput at each load level, connection pool wait time, lock waits, replication lag, and database CPU and I/O. Read client and database metrics on the same timeline to find the cause, not just the symptom.
Yes. Gatling Enterprise Edition includes a JDBC plugin built and maintained by Gatling for direct SQL load. Open-source users can use community JDBC plugins, and every edition includes a JDBC feeder for pulling test data from a database.
Related articles
Ready to move beyond local tests?
Start building a performance strategy that scales with your business.
Need technical references and tutorials?
Minimal features, for local use only




