Load testing your database with your real query mix: from pg_stat_statements to a weighted load test

Most database load tests I have seen run one or two queries in a loop. That tells you how fast those queries are when nothing else is happening. It does not tell you what your database does on a normal Tuesday, when one lookup is called 120,000 times an hour, a customer listing 45,000 times and a report 400 times, all competing for the same buffer cache, the same connections and the same locks.

The good news: your database already knows that profile. This post shows how to export it and turn it into a weighted load test.

1. Export the query profile

PostgreSQL (the pg_stat_statements extension must be enabled), from psql:

\copy (SELECT query, calls, total_exec_time, rows
  FROM pg_stat_statements
  WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
  ORDER BY calls DESC LIMIT 200) TO 'stats.csv' WITH CSV HEADER

MySQL / MariaDB, save the result as CSV or TSV with its header row (mysql --batch gives TSV):

SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT
  FROM performance_schema.events_statements_summary_by_digest
  WHERE SCHEMA_NAME = DATABASE() AND DIGEST_TEXT IS NOT NULL
  ORDER BY COUNT_STAR DESC LIMIT 200;

A few things to know about this data before you use it:

• The queries are normalized. Constants are replaced by placeholders ($1 in PostgreSQL, ? in MySQL). To replay them you have to give each placeholder a value: ids from a CSV of real keys are best; random values in a realistic range are the next best thing. Never replay one constant: that measures the cache, not the database.
• Some rows are noise. BEGIN, COMMIT, SET and the statistics query itself show up. Drop them.
• Some rows are writes. Decide deliberately whether a write belongs in the test, and never run one against production.
• Some texts are lossy. MySQL collapses IN (1, 2, 3) to IN (...), and long statements get cut. Those need a manual edit.
• Call counts are your weights. You do not need to convert them to percentages; relative weights do the job.

2. Turn it into a weighted mix

The idea is simple: in each iteration, a virtual user picks one query at random, with probability proportional to its call count. Over a few minutes the database sees the same proportions it sees in production.

In Spitfire (from 0.18.0), a SQL step can hold such a mix directly:

{ "id": "orders", "name": "Order queries", "protocol": "sql", "connection": "orders-db",
  "sql": { "mix": [
    { "name": "order_by_id", "query": "SELECT id, status, total FROM orders WHERE id = $1",
      "params": ["{{$randInt 1 100000}}"], "weight": 120000 },
    { "name": "customer_orders", "query": "SELECT id, status, total, created_at FROM orders WHERE customer_id = $1 ORDER BY created_at DESC LIMIT 20",
      "params": ["{{$randInt 1 20000}}"], "weight": 45000 },
    { "name": "pending_count", "query": "SELECT count(*) FROM orders WHERE status = $1",
      "params": ["pending"], "weight": 6000 },
    { "name": "hourly_summary", "query": "SELECT date_trunc('hour', created_at) AS h, count(*), sum(total) FROM orders WHERE created_at > now() - interval '1 day' GROUP BY 1 ORDER BY 1",
      "weight": 400 }
  ] } }

Here order_by_id runs in about 70% of iterations and hourly_summary in about 0.2%. A step holds up to 200 queries.

You do not have to write that JSON by hand. In the editor, Bulk add / import takes the CSV/TSV export from step 1 (or, for admins, reads the statistics straight through the step's connection: only the statistics query, top N by calls, in a read-only transaction with a timeout). The review table shows each statement with its call count and its kind (read, write, lock, INTO, procedure), skips transaction and session commands, leaves writes unticked, and lets you bind every placeholder to a data column or a fake value. The same dialog also splits a pasted script or a .sql file, dialect-aware (PostgreSQL dollar quotes, MySQL DELIMITER, SQL Server GO, Oracle /), and turns repeated statements into weights.

3. Look at each query, not just the total

A mixed step has one latency distribution, and that is exactly the problem: a step p95 of 8 ms can hide a report query whose p95 went from 300 ms to 2 s, because it is 0.2% of the calls.

So measure each query on its own. Spitfire reports each query of a mix as its own series (sql_query_duration, sql_query_failed), and the run page and the HTML/PDF report list calls, share, p50, p95, p99 and error rate per query. Thresholds can target a single query by name:

"thresholds": [
  { "metric": "sql_query_duration", "filter": { "check": "order_by_id" }, "expr": "p(95)<20" },
  { "metric": "sql_query_duration", "filter": { "check": "customer_orders" }, "expr": "p(95)<50" },
  { "metric": "sql_query_duration", "filter": { "check": "hourly_summary" }, "expr": "p(95)<500" },
  { "metric": "req_failed", "expr": "rate<0.001" }
]

That makes the test usable as a gate in CI: the run fails when a specific query regresses, and the output names it.

4. Keep reads read-only

Replaying production queries against a database is only comfortable if you are sure nothing writes. A keyword search for INSERT is not enough: writes hide in CTE bodies (WITH d AS (DELETE … RETURNING *) SELECT …), behind EXPLAIN ANALYZE, and in row locks (FOR UPDATE, LOCK IN SHARE MODE, WITH (UPDLOCK)), while a harmless replace() call or a column named update looks like a write to a naive check.

Spitfire tokenizes each statement per dialect and counts keywords only where a statement starts (the beginning, a CTE body, after a CTE list or EXPLAIN, a subquery). On PostgreSQL and MySQL, reads also run in a read-only transaction that is rolled back, so the database itself refuses hidden writes such as a function with side effects. SQL Server and Oracle have no such transaction, so there only the statement check protects the data, and you should connect with a read-only database user. Writes need an explicit approval on the step and a confirmation every time the test starts.

5. Practical checklist

• Run against a copy of production with production-like data volume and distribution. A small table fits in memory and every plan looks great.
• Reset or snapshot pg_stat_statements (pg_stat_statements_reset()) before a representative window, so the counts describe a normal day and not last month's migration.
• Size concurrency from the application's real pool sizes (6 instances × 20 connections = 120 concurrent queries at most), and remember that the load generator's own pools add up across machines.
• Ramp up in steps and watch where p99 bends, not just where errors start.
• Read the database's own metrics (connections, lock waits, cache hit ratio, replica lag) alongside the load stages.

Spitfire is self-hosted: it runs on your own servers, so the query texts and statistics you import stay in your network. The full guide, with the single-query basics and the complete example test, is here: Database load testing: PostgreSQL, MySQL, SQL Server and Oracle.

---

Drafted with AI assistance and reviewed by the author.