PostgreSQL load testing: query latency under load, connections and read-only reads

Albiferro Yazılım ve Sistem Limited Şirketi (Albiferro Software & System Limited Company) · Published 1 November 2026

Detailed documentation →

On PostgreSQL, slowness under load usually comes from a few queries, the connection limit (max_connections) or lock waits. Skipping the application and sending queries straight to the database, with realistic parameters at the target concurrency, tells which one it is. This page covers the PostgreSQL-specific part; the general method is in the database load testing guide.

The PostgreSQL connection

1. Click Connections → Add connection, pick PostgreSQL as the Type and give it a name (e.g. orders-db).
2. Enter Host, Port (empty means 5432), Database / service, Username and Password. If you can, use a separate user with SELECT only on the tables you need; the password is stored encrypted and the test names only the connection.
3. Max open connections (per runner): 50 by default. Total connections ≈ number of runners × this value; set it so the database's connection limit is not exceeded.
4. Save, then Test. If the database can only be reached through a bastion, add an SSH tunnel to the connection.

A SQL step for PostgreSQL

Write the query in Query as fixed text; give the values with the $1 placeholder under Parameters. Parameters can contain {{__VU}}, {{$randInt 1 100000}}, extracted variables or values from a CSV data file; templates cannot go inside the query text, so there is no SQL injection. In the Checks tab add e.g. Row count = 1, run one iteration with Try it to see the rows returned, then Save.

{"id": "order", "name": "Get order", "protocol": "sql", "connection": "orders-db",
 "sql": {"query": "SELECT id, status, total FROM orders WHERE id = $1", "params": ["{{$randInt 1 100000}}"]},
 "extract": [{"var": "status", "from": "jsonpath", "expr": "$[0].status"}],
 "checks": [{"type": "rowCount", "op": "eq", "value": 1}]}

A read's duration covers the wait for a pooled connection, the query and fetching every row. Rows go to checks and extraction as an array of JSON objects (such as $[0].status). Errors are told apart by kind: timeout, refused connection, permission, missing table, constraint violation, syntax and read-only violation.

Protecting the data

Spitfire works out whether a query reads or writes and counts anything it cannot recognise as a read as a write. Writes cannot be saved without an explicit approval on the step, and once approved the test asks for confirmation again every time it starts, recorded in the audit log (security statement).

On PostgreSQL every query counted as a read also runs inside a read-only transaction that is rolled back. A query that looks like a read but changes data (such as a function with side effects) is refused by the database itself, and the run shows cannot execute … in a read-only transaction.

Which queries to load?

The pg_stat_statements extension tells which queries to load: it keeps each query pattern's call count (calls) and total time (total_exec_time). The few queries called most often and taking the most time in total are the test's skeleton.

You don't have to carry these statistics over by hand: the pg_stat_statements output is imported and the most-called queries become one SQL step weighted by their call counts; installation admins can also read the statistics straight from the connection (query mix).

Instead of writing the queries by hand, you can have them suggested from the database's own schema (installation admins; query suggestions from the schema). The privileges needed on PostgreSQL: a LOGIN role with CONNECT, USAGE, SELECT and default privileges; optionally pg_read_all_stats for statistics. Every suggestion is checked with EXPLAIN (never ANALYZE): only the plan is fetched, the query never runs; every suggestion has a row limit (LIMIT).

Then pick the load model: a steady load at the expected concurrency, or a stepped breaking point test to find the limit. Read the results with p95 and p99, not the average.

Common errors

• password authentication failed for user …: the username or password is wrong, or pg_hba.conf does not allow the controller's and runners' IPs.
• no pg_hba.conf entry … SSL off: the server requires an encrypted connection; add sslmode = require to Driver parameters.
• remaining connection slots are reserved: the number of runners × Max open connections (per runner) exceeds the database's max_connections; lower the value or raise the limit.

Frequently asked questions