Performance Testing

By the end of this lesson you'll measure a single query with EXPLAIN ANALYZE , load-test a whole server with pgbench or sysbench , design a benchmark whose numbers you can actually trust, and read latency percentiles (p50/p95/p99) to find the slow 1% your users notice. This is how you turn "it feels slow" into a number you can fix.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn

🏎️ Why Benchmark At All?

Benchmarking is a car's crash test and dyno run combined. EXPLAIN ANALYZE is putting one part on a test bench to see exactly where it strains. A load test with pgbench is driving the whole car flat-out on a track with a stopwatch. You never ship the car because it "felt fine" in the driveway — and you never ship a query because it was fast on your tiny laptop dataset.

Benchmarking turns vague worry into evidence. It answers three questions precisely: how many transactions per second can this database sustain, which queries fall apart at production scale, and did that index I just added actually help — or did I just convince myself it did?

1. Measure One Query: EXPLAIN ANALYZE

Before you stress-test a whole server, profile the single query you suspect. EXPLAIN shows the plan the database intends to use without running it. EXPLAIN ANALYZE actually runs the query and reports two extra things: the real timings and the real row counts at every step.

Two pairs of numbers do all the work. Planning Time vs Execution Time tells you whether the database spent its time choosing a plan or running it — for normal queries it's nearly all execution. Estimated rows vs actual rows tells you whether the planner's statistics are accurate; a large gap means the planner is guessing badly and is likely picking a bad plan.

Here's a real plan. Read it bottom-up — the innermost (most indented) node runs first, and its rows flow up into the node above it:

The three signals to scan for

Seq Scan on a big table + many "Rows Removed by Filter" → the query read the whole table and threw most of it away. A well-chosen index usually fixes this.

Estimated rows=N far from actual rows=M → stale statistics. Run ANALYZE your_table; so the planner can choose better.

Execution Time > > Planning Time → the cost is in running the query (the normal case). If Planning Time is huge instead, you may have too many joins or partitions for the planner to reason about cheaply.

Your Turn: measure it, then read the rows

Turn the plain query into a measured one, then compare estimated vs actual rows. The expected output is in the comments so you can check yourself.

2. Load-Test the Whole Server: pgbench & sysbench

One fast query proves little. Real systems fail when many clients hit the database at once and start competing for CPU, disk, and locks. A load test simulates that crowd. Two tools dominate:

• pgbench ships with PostgreSQL. It builds a dataset ( -i -s scale ), then runs N concurrent clients ( -c ) for a fixed time ( -T ), reporting transactions per second and latency. Point it at your query with -f script.sql .
• sysbench is the MySQL/MariaDB equivalent (it also covers Postgres). Its oltp_read_write workload mimics a realistic mix of reads and writes and prints percentile latencies out of the box.

The headline number is TPS (transactions per second) — throughput. But the number that predicts complaints is the latency distribution , which both tools also print. You'll learn to read it in Section 4.

3. Designing a Fair Benchmark

A benchmark is only as good as its setup. The most common mistake is measuring something that has nothing to do with production and then making decisions on it. Five rules keep your numbers honest:

• Warm the cache. The first run reads from disk; later runs read from RAM and can be 100x faster. Decide whether you're measuring warm or cold, then discard the throwaway first run so every comparison is apples-to-apples.
• Use realistic data volume. A query that's instant on 1,000 rows can crawl on 10 million, because the planner switches strategies as tables grow. Generate production-sized data so the plan you benchmark is the plan prod will run.
• Use representative queries in their real proportions — mostly reads, some writes, your actual hot queries — not SELECT 1 .
• Repeat the runs (5+). A single measurement can be wrecked by a background backup or autovacuum. Report the median.
• Change one thing at a time. Add the index, re-run the same benchmark, compare. Change two things and you can't attribute the difference.

4. Read Percentiles, Not Averages

An average hides your worst experiences. If 99 requests take 5ms and one takes 2,000ms, the average is still a comfortable 25ms — but one user in a hundred waited two full seconds. That's why you read percentiles :

• p50 (median) — half of requests were at least this fast. The "typical" experience.
• p95 — 95% were this fast; the slow 5% were worse.
• p99 — the tail. The slowest 1% — and at a million requests a day, that's 10,000 angry users.

Below is the kind of summary pgbench and sysbench print, and which you can compute yourself from logged timings using percentile_cont :

Look at that row. The average (7.8ms) and median (6.1ms) look excellent — but p99 is 410ms , ~50x the average. Something is going badly wrong for 1% of requests, and the average completely hid it. Always report p95/p99 next to the average.

5. Find the Bottleneck: CPU vs I/O vs Locks

Once a benchmark shows a problem, you need to know which resource is the limit. There are three usual suspects, and each has a tell:

• CPU-bound — cores pinned near 100% ( top / htop ), TPS flat as you add clients. Cause: heavy sorts, aggregations, or function calls. Fix: better indexes so the engine touches fewer rows, or pre-compute.
• I/O-bound — high disk read wait, lots of buffer reads in EXPLAIN (ANALYZE, BUFFERS) , CPU idle while latency climbs. Cause: data doesn't fit in RAM, Seq Scans on big tables. Fix: indexes, more memory, or faster storage.
• Lock-bound — latency spikes (a tall p99 tail) while CPU and disk look idle . Cause: transactions waiting on each other's row locks. Inspect with SELECT * FROM pg_stat_activity WHERE wait_event_type = 'Lock'; . Fix: shorter transactions, the right isolation level, less contended update patterns.

The percentile shape is your fastest clue: a fat p99 tail over a calm average, with idle hardware, almost always means lock contention — not slow queries.

Your Turn: pick the slow query

Two queries were load-tested. Use the percentiles — not the averages — to decide which one to fix first.

Common Errors (and the fix)

• Benchmarking on a cold cache or tiny data: a 2ms result on 1,000 rows tells you nothing about 10 million rows on a warm server. Generate production-sized data and discard the first warm-up run before you measure.
• Reporting the average instead of percentiles: a healthy 8ms average can hide a 400ms p99. Always show p95/p99 — that tail is what users actually feel.
• Trusting one run: a single measurement is noise. A backup or autovacuum kicking off mid-test can double it. Run 5+ times and report the median.
• Testing on a laptop, not prod-like hardware: your SSD, RAM, and core count differ wildly from production, so plans and limits differ too. Benchmark on hardware that resembles prod, or you're measuring your laptop.
• Using EXPLAIN when you meant EXPLAIN ANALYZE : plain EXPLAIN shows only estimates and never runs the query, so it has no real timings or actual row counts. Add ANALYZE to get the truth (and remember it executes the query).
• Changing two things at once: adding an index and regenerating data in the same step means you can't tell which caused the change. Isolate one variable per run.

📘 Quick Reference

Tool / Term

What it measures / does

Frequently Asked Questions

Q: What's the difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN only predicts a plan with cost estimates and never runs the query, so it's instant and safe. EXPLAIN ANALYZE actually executes it and adds real timings and actual row counts — far more useful, but it runs the query (including writes), so wrap mutations in a rolled-back transaction.

Q: Why is my p99 so much higher than my average?

A long tail usually means lock contention, a cold cache on certain inputs, or a missing index that only bites for some queries. The average smooths it away; the p99 exposes the slowest 1% of requests, which is exactly what users complain about.

Enough that the query planner makes the same choices it will in production — usually millions of rows, sized to your real tables. The danger isn't "too much" data; it's too little, which lets a Seq Scan look fine and hides the plan switch that happens at scale.

For rough comparisons of two queries on the same machine, yes. For predicting production capacity, no — your laptop's CPU, RAM, and disk are nothing like the server. Benchmark on prod-like hardware before you size anything.

Mini-Challenge: Prove the Index Helped

Put it all together — measure, change one thing, re-measure fairly, and compare. Write it, then run it against real data in a playground to confirm.

🎉 Lesson Complete

• ✅ EXPLAIN ANALYZE runs a query and reports real timings + actual rows
• ✅ Estimated-vs-actual rows reveals stale stats; planning-vs-execution time tells you where the cost is
• ✅ pgbench and sysbench load-test the whole server, not just one query
• ✅ A fair benchmark warms the cache, uses realistic data, repeats runs, and changes one thing
• ✅ p50/p95/p99 expose the slow tail an average hides; idle hardware + a tall p99 means locks
• ✅ Next: designing Multi-Tenant Databases

Practice quiz

Continue this course

• Previous: Cross-Database Queries
• Next: Multi-Tenant Databases

Related lessons

• Query Optimization & Indexes