Optimizing PostgreSQL chevron_right Benchmarking and Load Testing Basics for PostgreSQL

Benchmarking and Load Testing Basics for PostgreSQL

calendar_today schedule 6 min read

Learning Objectives: By the end of this lesson, you will be able to:

  • Explain the importance of benchmarking and load testing in database optimization
  • Use tools like pgbench to measure PostgreSQL performance
  • Design and interpret meaningful benchmarks, avoiding common pitfalls

1. Welcome and Introduction (5 minutes)

Icebreaker Activity

Have you ever made a change you thought would speed up your database, only to find it made things worse? How do you know if a change is really an improvement?

Lesson Overview

  • Duration: 90 minutes
  • Prerequisites: Basic PostgreSQL usage, ability to run terminal commands
  • Materials Needed: PostgreSQL instance (test/dev), terminal access, pgbench installed

2. Learning Objectives and Session Outline (5 minutes)

What We’ll Cover Today:

  1. Why benchmark and load test?
  2. Introduction to pgbench and other tools
  3. Designing realistic benchmarks
  4. Running and interpreting tests
  5. Avoiding common benchmarking mistakes

Success Criteria:

By the end of this session, you should be able to run, analyze, and act on benchmark results to guide PostgreSQL optimization.

3. Foundation Knowledge (15-20 minutes)

Core Concepts

What is Benchmarking?

Benchmarking is the process of measuring the performance of your database under controlled conditions, usually before and after a change.

Real-World Analogy: Like timing a car on a test track before and after an engine upgrade to see if it’s actually faster.

Why This Matters: Without benchmarks, you’re guessing about performance. Data-driven decisions lead to better outcomes.

What is Load Testing?

Load testing simulates real-world usage by generating concurrent queries and transactions, helping you understand how your system behaves under stress.

Key Terminology

  • Throughput: Number of transactions or queries per second (TPS/QPS)
  • Latency: Time taken to complete a single transaction or query
  • Baseline: The initial measurement before any changes
  • Workload: The mix of queries and operations simulated during the test

4. Detailed Explanation and Examples (25-30 minutes)

Why and How to Benchmark

  • Establish a baseline before making changes
  • Compare performance before and after optimizations
  • Use realistic data and workloads for meaningful results

Introduction to pgbench

  • pgbench is a standard benchmarking tool for PostgreSQL
  • Can run built-in or custom scripts
  • Simulates multiple clients and transactions

Example: Running a Simple Benchmark

# Initialize the test database (run once)
pgbench -i -s 10 mydb

# Run a simple benchmark for 60 seconds with 10 clients
pgbench -c 10 -T 60 mydb

Explanation:

  • -i initializes the database with sample data
  • -c sets the number of clients (simulated users)
  • -T sets the duration in seconds

Example: Custom Workloads

# Create a custom script (custom.sql)
\set id random(1, 100000)
SELECT * FROM accounts WHERE aid = :id;

# Run pgbench with the custom script
pgbench -c 10 -T 60 -f custom.sql mydb

Practical Applications

  • Test the impact of schema changes, indexes, or configuration tweaks
  • Simulate production-like workloads for capacity planning

Common Misconceptions

  1. Misconception: “Any benchmark is good enough.” Reality: Only realistic, repeatable benchmarks are useful for decision-making.
  2. Misconception: “Higher TPS always means better performance.” Reality: Latency, error rates, and system stability also matter.

5. Live Coding Demonstration (20-25 minutes)

Example 1: Baseline and After-Change Comparison

# Baseline
pgbench -c 10 -T 60 mydb
# Make a schema or config change
# Rerun
pgbench -c 10 -T 60 mydb

Explanation:

  • Compare TPS and latency before and after the change

Example 2: Avoiding Common Pitfalls

# Running on an empty database (not realistic)
pgbench -c 10 -T 60 mydb
# Use -s to scale up data for more realistic tests
pgbench -i -s 100 mydb

Key Points:

  • Always use enough data to avoid caching effects
  • Run tests multiple times for consistency

Example 3: Analyzing Results

# Sample output
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 10
duration: 60 s
number of transactions actually processed: 50000
tps = 833.33 (including connections establishing)
latency average = 12.0 ms

Debugging Demonstration

Show a common mistake:

# Changing multiple things at once
# Not knowing which change caused the improvement or regression
# Corrected: Change one variable at a time and document results

6. Hands-On Practice (30-40 minutes)

Exercise 1: Basic Practice (10 minutes)

Objective: Run a baseline benchmark

Instructions:

  1. Initialize a test database with pgbench
  2. Run a 1-minute test with 5 clients
  3. Record TPS and latency

Starter Code:

pgbench -i -s 5 mydb
pgbench -c 5 -T 60 mydb

Solution:

# Record the TPS and latency from the output

Exercise 2: Intermediate Challenge (15 minutes)

Objective: Test the impact of an index or config change

Requirements:

  • Run a baseline test
  • Add an index or change a config (e.g., work_mem)
  • Rerun the test and compare results

Hints:

  • Only change one thing at a time
  • Use the same test parameters for fair comparison

Exercise 3: Creative Application (15 minutes)

Objective: Design a custom workload for your application

Challenge:

  1. Write a custom pgbench script simulating a real query pattern
  2. Run the test and analyze the results
  3. Propose optimizations based on findings

Extension Ideas:

  • Automate benchmarking as part of CI/CD
  • Visualize results over time

7. Common Pitfalls and Troubleshooting (10 minutes)

Frequent Mistakes

  1. Problem: Using too little data (everything fits in RAM) Cause: Unrealistic results due to caching Solution: Scale up data with -s to match production size

  2. Problem: Not controlling for background activity Cause: Other processes affect results Solution: Run tests on a dedicated or quiet system

Debugging Tips

  • Run each test multiple times and average results
  • Monitor system metrics (CPU, RAM, disk I/O) during tests
  • Document all test parameters and environment

8. Best Practices and Professional Tips (5 minutes)

Industry Standards

  • Always benchmark before and after changes
  • Use production-like data and workloads
  • Automate and document your benchmarking process

Next Steps in Your Learning Journey

  • Learn about other tools (sysbench, JMeter, custom scripts)
  • Explore advanced pgbench options (custom scripts, latency histograms)
  • Read PostgreSQL documentation on performance testing

9. Summary and Review (5 minutes)

Key Takeaways

  1. Benchmarking and load testing are essential for data-driven optimization
  2. Use realistic, repeatable tests for meaningful results
  3. Analyze both throughput and latency, not just one metric

Quick Review Questions

  1. What is the difference between benchmarking and load testing?
  2. Why is it important to use realistic data and workloads?
  3. How do you avoid common benchmarking pitfalls?

For Instructors

Teaching Tips

  • Use analogies (car test track) to explain benchmarking
  • Demonstrate with live tests and real data
  • Discuss the importance of changing one variable at a time

Assessment Ideas

  • Have students run and document a benchmark before and after a change
  • Quiz on benchmarking concepts and pitfalls
  • Peer review of custom workload scripts

Adaptation Notes

  • For beginners: Focus on basic pgbench usage and interpreting output
  • For advanced: Explore custom workloads and automation
  • Online: Use cloud PostgreSQL or Docker for safe experimentation