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.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

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.

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?

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:

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.

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.

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:

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.

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:

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 :

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.

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:

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.

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

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.

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.

Practice quiz

What is the key difference between EXPLAIN and EXPLAIN ANALYZE?

  • EXPLAIN runs the query; EXPLAIN ANALYZE only estimates
  • They are identical
  • EXPLAIN shows the planned strategy without running; EXPLAIN ANALYZE actually runs it and reports real timings
  • EXPLAIN ANALYZE only works on SELECT statements

Answer: EXPLAIN shows the planned strategy without running; EXPLAIN ANALYZE actually runs it and reports real timings. EXPLAIN shows the intended plan without running the query. EXPLAIN ANALYZE executes it and adds real timings and actual row counts.

What does a large gap between estimated rows and actual rows usually indicate?

  • Stale statistics — run ANALYZE on the table
  • The query is syntactically wrong
  • The server is out of memory
  • Too many indexes

Answer: Stale statistics — run ANALYZE on the table. A big estimated-vs-actual gap means the planner's statistics are stale, so it's guessing badly. Running ANALYZE refreshes them.

Why should writes be wrapped in a rolled-back transaction when using EXPLAIN ANALYZE?

  • Because it locks the whole table permanently
  • Because it returns no output otherwise
  • It isn't necessary — EXPLAIN ANALYZE never runs writes
  • Because EXPLAIN ANALYZE actually executes the query, including INSERT/UPDATE/DELETE

Answer: Because EXPLAIN ANALYZE actually executes the query, including INSERT/UPDATE/DELETE. EXPLAIN ANALYZE really runs the query, including mutations, so wrap writes in a transaction you roll back to avoid changing your data.

What does a load test simulate that a single fast query does not?

  • A syntax error
  • Many concurrent clients competing for CPU, disk, and locks
  • A single user on a tiny dataset
  • The query plan

Answer: Many concurrent clients competing for CPU, disk, and locks. A load test simulates many clients hitting the database at once — a server can collapse under concurrency even when one query is fast.

Which tool ships with PostgreSQL for load testing?

  • pgbench
  • sysbench
  • EXPLAIN
  • pg_dump

Answer: pgbench. pgbench ships with PostgreSQL; sysbench is the MySQL/MariaDB equivalent (and also covers Postgres).

Why discard the first run when benchmarking?

  • It always errors out
  • The first run uses a different query plan
  • The first run reads from disk (cold cache); later runs read from RAM, so mixing them isn't apples-to-apples
  • It counts double in the average

Answer: The first run reads from disk (cold cache); later runs read from RAM, so mixing them isn't apples-to-apples. The first run reads from disk and is much slower; later runs read from a warm cache. Discard the throwaway first run so comparisons are consistent.

Why does the lesson say to change only one thing at a time in a benchmark?

  • To save disk space
  • So you can attribute any difference to that single change
  • Because the tools only support one change
  • To keep the cache warm

Answer: So you can attribute any difference to that single change. If you change the index AND the data together, you can't tell which caused the difference — isolate one variable per run.

What does p99 latency mean?

  • The average of all requests
  • The fastest 1% of requests
  • The median latency
  • 99% of requests were at least this fast — the slowest 1% tail

Answer: 99% of requests were at least this fast — the slowest 1% tail. p99 is the tail: the slowest 1% of requests. Averages hide it, which is why you chase the p99, not the average.

An average of 8ms with a p99 of 410ms most likely indicates what?

  • A perfectly healthy query
  • A tail-latency problem — often lock contention or a missing index on a hot path
  • That the average is wrong
  • That p99 should be ignored

Answer: A tail-latency problem — often lock contention or a missing index on a hot path. A p99 far above the average is a classic tail-latency problem, usually a lock wait, cold cache, or a missing index firing on certain inputs.

A tall p99 tail while CPU and disk are idle usually points to which bottleneck?

  • CPU-bound work
  • I/O-bound work
  • Lock contention
  • Stale statistics

Answer: Lock contention. A fat p99 tail over a calm average with idle hardware almost always means lock contention — transactions waiting on each other's row locks.