Database Load Testing: Validating Query Performance at Scale

The database is the most common bottleneck in web application performance. An application that handles 500 requests per second with a warm cache may collapse to 50 requests per second when cache misses force every request through to the database. A query that returns in 5ms on a development dataset may take 500ms on a production dataset with 100 million rows. Database load testing validates that your queries, indexes, and connection configuration perform correctly at production scale with production-like concurrency.

Database load testing is not the same as application load testing. Application load tests exercise the entire stack and reveal end-to-end performance characteristics. Database load tests isolate the data layer to answer specific questions: How many concurrent queries can the database sustain? At what concurrency level does lock contention become the dominant bottleneck? Does query performance degrade with data volume?

Data Volume Matters

The single most important factor in database load testing is data volume. Query performance characteristics change fundamentally with data size due to three mechanisms:

Generating Realistic Test Data

Test data must be realistic in three dimensions: volume (same row count as production), distribution (similar cardinality and skew in indexed columns), and relationships (foreign key chains and join paths that match production graph density).

Data Generation Approaches

-- PostgreSQL: Generate test data matching production distributions
-- Step 1: Analyze production distributions
SELECT
  status,
  COUNT(*) as count,
  ROUND(COUNT(*)::numeric / SUM(COUNT(*)) OVER() * 100, 2) as pct
FROM orders GROUP BY status;
-- active: 15%, completed: 72%, cancelled: 8%, refunded: 5%

-- Step 2: Generate synthetic data with matching distribution
INSERT INTO orders (user_id, status, total, created_at)
SELECT
  (random() * 100000 + 1)::int,
  CASE
    WHEN random() < 0.15 THEN 'active'
    WHEN random() < 0.87 THEN 'completed'
    WHEN random() < 0.95 THEN 'cancelled'
    ELSE 'refunded'
  END,
  (random() * 500 + 10)::numeric(10,2),
  NOW() - (random() * 365 || ' days')::interval
FROM generate_series(1, 10000000);  -- 10 million rows

-- Step 3: Update table statistics
ANALYZE orders;

Connection Pool Testing

Connection pool configuration is the most impactful database performance setting. Too few connections and queries queue waiting for a free connection. Too many connections and the database server spends more time context-switching between connections than executing queries. The optimal pool size depends on the hardware, workload mix, and query characteristics — there is no universal answer.

Finding Optimal Pool Size

Run a series of load tests with increasing pool sizes while holding the workload constant. Plot throughput (queries per second) and latency (p95 response time) against pool size. The optimal pool size is where throughput plateaus — adding more connections no longer increases throughput and may increase latency due to context switching.

-- PostgreSQL: Monitor connection and query activity during load test
-- Run these queries in a separate session during the test

-- Active connections and their states
SELECT state, COUNT(*) FROM pg_stat_activity
WHERE datname = 'mydb' GROUP BY state;

-- Queries waiting for locks
SELECT count(*) AS waiting_queries
FROM pg_stat_activity WHERE wait_event_type = 'Lock';

-- Connection pool utilization (if using PgBouncer)
-- SHOW POOLS; -- shows active, waiting, idle per pool

-- Average query time over the last minute
SELECT
  calls,
  ROUND(total_exec_time::numeric / calls, 2) AS avg_ms,
  ROUND(max_exec_time::numeric, 2) AS max_ms,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Connection Pool Sizing Rule of Thumb

A common starting point for PostgreSQL: pool_size = (core_count * 2) + effective_spindle_count. For an 8-core server with SSD storage (treat as 1 spindle): (8 * 2) + 1 = 17 connections. This formula reflects that most queries spend time waiting for I/O, so you can run roughly twice as many concurrent queries as CPU cores. Validate this starting point with actual load testing — your specific workload may require adjustment.

Write Contention Testing

Read-heavy workloads scale well with connection count and caching. Write-heavy workloads hit contention bottlenecks: row-level locks, table-level locks, WAL (Write-Ahead Log) serialization, and index maintenance. These bottlenecks are only visible under concurrent write load.

Lock Contention

When multiple transactions update the same rows, they acquire row-level locks. The second transaction waits until the first commits or rolls back. Under high concurrency, this creates a queue of blocked transactions. Test for lock contention by running concurrent updates to a small set of frequently-modified rows (counters, aggregates, status fields, inventory levels).

-- Simulate concurrent inventory updates (high contention)
-- Run from multiple concurrent sessions
BEGIN;
UPDATE products
SET stock = stock - 1
WHERE id = 42 AND stock > 0;  -- Hot row: many sessions update the same product
COMMIT;

-- Monitor lock waits during the test
SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query,
  now() - blocked.query_start AS wait_duration
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks gl ON gl.locktype = bl.locktype
  AND gl.database = bl.database
  AND gl.relation = bl.relation
  AND gl.pid != bl.pid AND gl.granted
JOIN pg_stat_activity blocking ON blocking.pid = gl.pid;

Write-Ahead Log Pressure

Every write operation generates WAL records. Under heavy write load, WAL generation can saturate the disk I/O bandwidth dedicated to the WAL directory. Monitor WAL generation rate during write-heavy load tests. If the WAL directory is on the same disk as the data directory (common in cloud instances), WAL writes and data reads compete for the same I/O bandwidth.

Read Replica Lag Testing

Applications that use read replicas to scale read traffic must account for replication lag. Under normal load, replication lag is typically under 100ms. Under heavy write load, lag can grow to seconds or even minutes if the replica cannot apply WAL records as fast as the primary generates them.

Test replication lag by running a write-heavy workload against the primary and simultaneously querying the replica. Measure the time between a write on the primary and its visibility on the replica. If your application reads from replicas for consistency-sensitive operations (checking inventory before placing an order), replication lag under write load may cause data inconsistency — an item appears in stock on the replica but is already sold on the primary.

Index Performance Under Load

Indexes that perform well for single-user queries may degrade under concurrent access. B-tree indexes handle concurrent reads efficiently, but concurrent inserts to the same index can cause page splits and contention on the rightmost leaf page (common with auto-incrementing primary keys).

Query-Level Load Testing

While application-level load tests reveal end-to-end behavior, query-level load tests isolate database performance. Use database benchmarking tools to run your actual slow queries under concurrency:

# pgbench custom script — test specific query patterns
# Save as custom-queries.sql

-- Read: product search with pagination
\set product_id random(1, 1000000)
SELECT p.*, c.name AS category
FROM products p
JOIN categories c ON c.id = p.category_id
WHERE p.id = :product_id;

-- Read: full-text search
\set search_term random(1, 5000)
SELECT id, title, ts_rank(search_vector, query) AS rank
FROM articles, plainto_tsquery('english', 'performance monitoring') AS query
WHERE search_vector @@ query
ORDER BY rank DESC LIMIT 20;

# Run pgbench with the custom script
# pgbench -c 50 -j 4 -T 300 -f custom-queries.sql mydb
# -c 50: 50 concurrent connections
# -j 4: 4 threads
# -T 300: 300 seconds (5 minutes)

Capacity Planning from Load Test Results

Load test results translate into capacity projections when you correlate throughput with resource utilization. Plot these relationships:

• Queries per second vs. CPU utilization: Identify the CPU percentage where throughput starts to plateau. If throughput plateaus at 70% CPU, your effective capacity is the QPS at that point, not the theoretical maximum.
• Concurrent connections vs. query latency: Find the connection count where latency begins to increase non-linearly. This is your practical concurrency limit.
• Write throughput vs. replication lag: Determine the write rate that causes unacceptable replication lag on read replicas.
• Data volume vs. query time: Project how query performance will change as data grows. If a query takes 50ms at 10M rows and 500ms at 100M rows, extrapolate to your 12-month data projection.

Monitoring During Database Load Tests

Instrument the database server with comprehensive monitoring during load tests. Key metrics to track continuously:

• Buffer cache hit ratio: Should stay above 99% for read-heavy OLTP workloads. A drop below 95% means the working set exceeds available buffer cache and disk I/O is becoming the bottleneck.
• Lock wait time: Track average and maximum lock wait time. If average lock wait exceeds query execution time, lock contention is the primary bottleneck.
• Disk I/O latency: Monitor read and write latency separately. Read latency spikes indicate buffer pool pressure. Write latency spikes indicate WAL or checkpoint pressure.
• Checkpoint duration: Long checkpoints cause I/O spikes that affect query latency. If checkpoints take more than 50% of the checkpoint interval, they overlap and create sustained I/O pressure.
• Dead tuple ratio: Heavy UPDATE/DELETE workloads create dead tuples. If autovacuum cannot keep up, tables and indexes bloat, degrading query performance.

Key Takeaways

• Data volume is the most critical factor: use production-scale data (matching row count, distribution, and relationship density) or your load test results will not predict production behavior.
• Test connection pool sizing empirically: plot throughput and latency against pool size to find the optimal configuration for your workload. Start with (cores × 2) + spindles as a baseline.
• Write contention testing requires concurrent access to the same rows. Test hot-row update patterns (counters, inventory, status fields) to find lock contention thresholds.
• Measure replication lag under write load. If your application reads from replicas for any consistency-sensitive operations, replication lag under stress may cause data inconsistency.
• Track buffer cache hit ratio during load tests — a drop below 95% means the working set exceeds available cache and disk I/O is the bottleneck.
• Project capacity from load test correlations: QPS vs. CPU, connections vs. latency, writes vs. replication lag, data volume vs. query time.