pgbench is PostgreSQL's built-in benchmarking tool. It measures transactions per second (TPS) under controlled load, helping you validate configuration changes and capacity before they reach production.
Initialize Test Database
bash
# Create benchmark database
createdb pgbench_test
# Initialize with scale factor 100 (100 * 100K rows = 10M rows in accounts)
pgbench -i -s 100 pgbench_test
# Scale factors:
# -s 10 = ~150 MB
# -s 100 = ~1.5 GB
# -s 1000 = ~15 GBRun Standard TPC-B Benchmark
bash
# 8 clients, 4 threads, 60 seconds
pgbench -c 8 -j 4 -T 60 pgbench_test
# Output:
# tps = 1842.3 (including connections)
# tps = 1859.1 (excluding connections)
# latency average = 4.3 ms
# latency stddev = 1.2 msRead-Only Benchmark
bash
# SELECT-only workload (higher TPS, tests read path)
pgbench -c 16 -j 4 -T 60 -S pgbench_testCustom Script
bash
# test_orders.sql
# \set user_id random(1, 100000)
# SELECT * FROM orders WHERE user_id = :user_id ORDER BY created_at DESC LIMIT 10;
pgbench -c 8 -j 4 -T 60 -f test_orders.sql pgbench_testCompare Before and After a Config Change
bash
# Before change
pgbench -c 8 -j 4 -T 120 pgbench_test > before.txt
# Apply config change (e.g., increase shared_buffers)
# ...
# After change
pgbench -c 8 -j 4 -T 120 pgbench_test > after.txt
diff before.txt after.txtLatency Percentiles
bash
# Report latency distribution
pgbench -c 8 -j 4 -T 60 --report-per-command pgbench_test
# For percentile histogram (PostgreSQL 14+)
pgbench -c 8 -j 4 -T 60 --report-latencies pgbench_testKey Takeaways
- Use scale factor (
-s) large enough that the working set exceeds shared_buffers - Always run multiple benchmark iterations and take the median — first runs are often warmer
- Use
-Sfor read-only benchmarks to isolate read path performance - Custom SQL scripts (
-f) let you benchmark your actual query patterns, not just TPC-B
JusDB Can Help
Benchmarking is the only reliable way to validate PostgreSQL configuration changes. JusDB can run baseline benchmarks and help you interpret the results.