Posted inDatabase

PostgreSQL Benchmarking with pgbench: Measuring Real-World TPS

Database tutorial - IT technology blog
Database tutorial - IT technology blog

The 2:15 AM Performance Alert

The monitoring alerts started screaming at 2:15 AM on a Tuesday. Production API latency had suddenly spiked from a steady 50ms to a painful 4 seconds. On the dashboard, the PostgreSQL CPU usage was pinned at 99%. My first instinct was the “cloud solution”: just upgrade the instance and add more vCPUs. However, hardware is usually just a temporary band-aid for deep-seated configuration or query bottlenecks.

Fixing the issue required a baseline, not just a bigger server. I needed to know exactly how many Transactions Per Second (TPS) the current setup could handle before it broke. This is where pgbench becomes your primary diagnostic tool. If your database crawls under pressure despite having 64GB of RAM, you need to stop guessing and start benchmarking.

The Metrics That Actually Matter

When measuring PostgreSQL performance, two metrics are the gold standard: TPS and Latency. You cannot look at one without the other.

  • TPS (including connections): This counts how many transactions the database completes per second. It includes the time spent establishing a new connection for every request.
  • TPS (excluding connections): This is your “pure” throughput. It measures how fast the engine processes data once the connection is already active.
  • Latency: This is the round-trip time for a single transaction. High TPS is useless if your average latency hits 5 seconds, as your users will have already given up.

PostgreSQL includes pgbench by default. It runs a TPC-B-like benchmark using five SELECT, UPDATE, and INSERT commands per transaction. Its true power, however, lies in running custom scripts that mimic your specific application workload.

Preparing the Test Environment

Avoid running benchmarks on a live production database. I always spin up a staging environment that mirrors production hardware exactly. To start, we initialize a test database with a specific “scale factor.”

# Create a test database
createdb benchmark_test

# Initialize pgbench with a scale factor of 50
# This generates roughly 750MB of data (5,000,000 rows in the accounts table)
pgbench -i -s 50 benchmark_test

The scale factor (-s) is vital. A scale of 1 is too small for modern systems. For a realistic test, I aim for a dataset larger than the shared_buffers. This ensures we are testing disk I/O performance rather than just hitting RAM.

Establishing a Baseline

Let’s run a standard test. I’ll simulate 10 concurrent clients (-c) using 2 worker threads (-j) for a 60-second duration (-T).

pgbench -c 10 -j 2 -T 60 benchmark_test

When the results finish, check the final line. You might see tps = 450.23. That is your floor. If you tweak a setting in postgresql.conf and that number drops to 300, you have immediate proof that the change failed.

Custom Scripts for Real-World Queries

Standard TPC-B tests are helpful, but your application probably isn’t just updating a pgbench_accounts table. Real-world apps usually struggle with heavy SELECT joins or massive INSERT spikes. I create .sql files to isolate these specific bottlenecks.

If a reporting query is slow, I put it in a file named read_heavy.sql:

-- read_heavy.sql
BEGIN;
SELECT abalance FROM pgbench_accounts WHERE aid = (random() * 100000 * :scale)::int;
SELECT count(*) FROM pgbench_branches;
END;

Run pgbench using that specific file with the -f flag:

pgbench -c 20 -j 4 -T 120 -f read_heavy.sql benchmark_test

I often deal with messy legacy exports when preparing these test scripts. When I need to convert CSV to JSON for quick data imports, I use toolcraft.app/en/tools/data/csv-to-json. It runs entirely in the browser. This saves me from writing a Python parser for a one-off test.

Validating Your Optimizations

I recently worked on a server with 16GB of RAM where shared_buffers was still at the default 128MB. This is a classic performance killer. I increased shared_buffers to 4GB and set effective_cache_size to 12GB.

After restarting the service, I re-ran the same pgbench command. The improvement was immediate:

  • Before Tuning: 450 TPS, 22ms Avg Latency
  • After Tuning: 1,200 TPS, 8ms Avg Latency

You can also test Prepared Statements using the -M prepared flag. This usually provides a 10-20% performance boost. It removes the need for the database to re-parse the SQL for every execution. These numbers give me the confidence to push config changes to production at 3 AM.

Don’t Ignore the Jitter

TPS isn’t the only story. Look closely at the Standard Deviation of the latency. If your average latency is 10ms but the standard deviation is 50ms, your users are experiencing “jitter.” Some requests are fast, but others are lagging badly. This often points to disk I/O contention or autovacuum processes interfering with your queries.

The Benchmarking Habit

Testing isn’t a one-time event. You should run pgbench every time you upgrade PostgreSQL, change cloud instance types, or deploy a massive new feature. It moves the conversation from “I think the database is slow” to “I know this instance handles 1,500 users before latency hits 100ms.”

When you find yourself staring at a frozen dashboard in the middle of the night, don’t guess. Initialize your test data, run a baseline, and use custom scripts to find the pain point. Data is the only thing that will get you back to sleep.

Share:
Tags: