pgbench

pgbench
Name	pgbench
Developer	PostgreSQL Global Development Group
Initial release	1997
Programming language	C (programming language)
Operating system	Linux, FreeBSD, macOS, Windows
License	PostgreSQL License

pgbench pgbench is a benchmarking tool associated with PostgreSQL designed to evaluate database performance under simulated transactional loads. It originated as a lightweight harness to exercise client-server interaction patterns, providing standard and scriptable workloads for comparative analysis across versions, hardware, and configurations. Widely used by system administrators, database developers, and performance engineers at organizations like Red Hat, Amazon Web Services, Microsoft, and academic groups, pgbench has influenced tuning practices and contributed to benchmarking literature alongside tools such as sysbench and HammerDB.

History

pgbench was created to provide a simple way to measure PostgreSQL throughput and latency across differing storage stacks and configurations. Early development tracked changes in PostgreSQL releases and community needs, integrating features to reflect evolving transaction models seen in enterprise settings like Goldman Sachs and cloud providers such as Google Cloud Platform. Over time, contributions from companies including EnterpriseDB and volunteers from the PostgreSQL Global Development Group expanded scriptability and statistics collection. The tool's evolution paralleled research published at venues like the USENIX Annual Technical Conference and implementations tested against benchmarks from the Transaction Processing Performance Council.

Purpose and features

pgbench serves to measure transactional workload characteristics for PostgreSQL deployments, supporting features that include multi-client simulation, configurable transaction mix, and custom scripting via a built-in scripting language. It provides initialization utilities to generate sample data, options to control transaction types, and support for measuring latency percentiles used by performance teams at Netflix, Facebook, and Twitter. Features such as non-default scale factors, ramp-up periods, and reporting align with practices recommended in performance manuals from vendors like Oracle Corporation and IBM. pgbench also integrates with monitoring stacks employing tools from Prometheus and Grafana for time-series visualization.

Usage and command-line options

pgbench is invoked from command lines on systems including Ubuntu, CentOS, Debian, and Arch Linux. Common options include initialization flags to set scale, client and thread counts for concurrency, duration or transaction-count limits, and verbosity levels used by site reliability engineers at organizations like Google and Dropbox. Command-line switches allow enabling prepared statements, latency reporting intervals, and script file selection often documented alongside psql and pg_ctl utilities. Administrators typically combine pgbench options with kernel tuning tips advocated by groups at Intel and AMD for storage and CPU optimization.

Benchmarking workloads and scripts

pgbench ships with a standard TPC-B-like script and supports custom workloads defined in its scripting dialect, enabling emulation of business logic seen in firms such as Visa, Mastercard, and PayPal. Users craft scripts to simulate read-heavy, write-heavy, or mixed OLTP patterns resembling workloads analyzed in research from ACM SIGMOD and IEEE Transactions on Knowledge and Data Engineering. Complex scenarios incorporate foreign key interactions, large-text updates, and stored-procedure calls, paralleling case studies from SAP and Salesforce. Scripted transactions can be combined with external tooling like Ansible and Terraform for reproducible deployment and measurement.

Performance metrics and reporting

pgbench reports metrics including transactions per second (TPS), average latency, and percentile latency values commonly used by performance teams from Bloomberg and Goldman Sachs. These output values feed into capacity planning models at institutions such as Morgan Stanley and JPMorgan Chase and guide tuning of parameters like shared buffers and work_mem described in PostgreSQL documentation. Combining pgbench results with system metrics from iostat, vmstat, and perf enables correlation of TPS and latency with I/O wait, CPU utilization, and lock contention issues reported in studies at Carnegie Mellon University and MIT.

Examples and common use cases

Administrators run simple tests to compare SSD and HDD performance in data centers run by companies like Equinix and DigitalOcean or to validate configuration changes before rolling them out in production at Airbnb and Uber. Common commands initialize test data with a scale factor, launch concurrent clients to simulate peak load, and collect latency percentiles for SLAs followed by teams at Spotify and LinkedIn. pgbench is also used in continuous integration pipelines at GitLab and Jenkins to prevent regressions in transaction throughput across code changes, and in benchmarks accompanying research from Stanford University and UC Berkeley.

Limitations and alternatives

pgbench focuses on OLTP-style transactional workloads and does not natively model complex analytical queries typical of data warehouses used at Snowflake and Teradata. It provides limited modeling for multi-statement transactions with complex dependencies, which has led teams to use alternatives like sysbench, HammerDB, and custom harnesses built on JMeter or Gatling. For full compliance with industry standards, organizations sometimes adopt TPC-C or TPC-H specifications from the Transaction Processing Performance Council or commercial benchmarking services offered by vendors such as Percona and Quest Software.

Category:Benchmarking tools