Lesson 46 • Advanced
Performance Testing & Benchmarking
Benchmark your database with pgbench, generate realistic test data, and measure query performance.
✅ What You'll Learn
- • Using pgbench for throughput and latency testing
- • Generating millions of realistic test rows
- • Benchmarking queries before and after optimisation
- • Tracking and logging slow queries automatically
🏎️ Why Benchmark?
🎯 Real-World Analogy: You wouldn't launch a car without crash testing it. Database benchmarks are your crash tests — they reveal how your system behaves under real load before your users discover the problems.
Benchmarking answers critical questions: How many transactions per second can my database handle? Which queries become slow at scale? Will adding an index actually help?
💡 Pro Tip:
Always benchmark with production-sized data. A query that runs in 2ms on 1,000 rows might take 30 seconds on 10 million rows. Generate realistic volumes before making performance decisions.
Try It: pgbench Benchmarking
Use PostgreSQL's built-in tool to measure transactions per second
-- pgbench: PostgreSQL's built-in benchmarking tool
-- Generate test data and measure throughput
-- Step 1: Initialize test tables (scale factor 100 = ~1.6GB)
-- pgbench -i -s 100 mydb
-- Step 2: Run a benchmark (10 clients, 60 seconds)
-- pgbench -c 10 -T 60 mydb
-- Output:
-- tps = 1523.45 (transactions per second)
-- latency average = 6.57 ms
-- Step 3: Custom benchmark scripts
-- File: custom_test.sql
SELECT p.name, SUM(oi.quantity) AS sold
FROM products p
JOIN order_items oi ON p.pro
...Try It: Generate Test Data
Create millions of realistic rows with varied distributions
-- Generate realistic test data for load testing
-- Create millions of rows with varied, realistic patterns
-- Generate 1 million customers
INSERT INTO customers (email, name, created_at)
SELECT
'user' || n || '@example.com',
'User ' || n,
NOW() - (random() * INTERVAL '365 days')
FROM generate_series(1, 1000000) AS n;
-- Generate 5 million orders with realistic distribution
INSERT INTO orders (customer_id, order_date, total, status)
SELECT
(random() * 999999 + 1)::INT,
NOW(
...Try It: Query Benchmarking
Compare query performance before and after index optimisation
-- Benchmark specific queries with timing
-- Measure query performance before and after changes
-- Enable timing
-- \timing on
-- Benchmark 1: Full table scan (baseline)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT COUNT(*) FROM orders
WHERE total > 100 AND status = 'shipped';
-- Benchmark 2: Index-assisted query
CREATE INDEX idx_orders_status_total ON orders(status, total);
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT COUNT(*) FROM orders
WHERE total > 100 AND status = 'shipped';
-- Com
...📋 Quick Reference
| Tool | Type | Best For |
|---|---|---|
| pgbench | Load test | PostgreSQL throughput testing |
| sysbench | Load test | MySQL/multi-DB benchmarking |
| EXPLAIN ANALYZE | Query profiling | Individual query performance |
| pg_stat_statements | Monitoring | Track all query performance |
| generate_series | Data gen | Create test data at scale |
🎉 Lesson Complete!
You can now benchmark and stress-test your database! Next, learn about designing multi-tenant databases.
Sign up for free to track which lessons you've completed and get learning reminders.