Skip to main content

PostgreSQL (pgbench)

This benchmark uses pgbenchpgbenchThe benchmark tool shipped with PostgreSQL. It runs a SQL script repeatedly over many concurrent client sessions, and reports the throughput in transactions per second and the latency. Its built-in script is modeled on TPC-B, and custom scripts can define any other workload.PostgreSQL docsGlossary to measure database performance, focusing on CPUCPU: Central Processing UnitThe general-purpose processor of a computer, which runs the operating system and programs. A CPU has one or more physical cores, and with SMT each core runs two or more hardware threads.WikipediaGlossary and memory rather than disk. It supports two modes:

  • In standalone mode, the image starts a local PostgreSQLPostgreSQLAn open-source object-relational database system, developed by a global community and descended from the POSTGRES project at UC Berkeley in 1986. It uses and extends SQL, with transactions, many index types, extensions, and JSON data.PostgreSQL docsGlossary 18 server.
  • In remote mode, it connects to a configured host without checking or enforcing the server version.

The image can run on the same node as the database or on a separate machine.

This benchmarking tool reports throughput in TPMTPM: Transactions Per MinuteA throughput measure of transaction processing systems: the number of transactions they complete per minute. TPC-C reports a variant of it, tpmC, which counts only new-order transactions, while tools such as pgbench report transactions per second (TPS), which is 1/60 of TPM.WikipediaGlossary (Transactions Per Minute) and latency at several client counts. Together, they show peak performance and how it scales with load, for self-managed and managed PostgreSQL on the same hardware.

Purpose​

Spare Cores Navigator publishes empirical performance measurements for more than 5,000 cloud server types. We needed an RDBMSRDBMS: Relational Database Management SystemA database system that stores data in tables of rows and columns, related through keys and queried with SQL. It keeps data consistent with constraints and transactions; PostgreSQL, MySQL and Oracle Database are examples.WikipediaGlossary (Relational Database Management System) benchmark that measures database throughput and latency directly rather than relying on proxies.

The Navigator previously relied on stand-ins: PassMarkPassMarkThe PerformanceTest benchmark suite of PassMark Software, for Windows, Linux, macOS, and mobile devices. It runs fixed CPU, memory, disk, and graphics tests, such as math workloads involving compression, encryption, and physics simulations, and combines the results into an overall PassMark Rating.PerformanceTestGlossary database operations, which do not scale above 32 vCPUsvCPU: virtual CPUA CPU of a virtual machine, the unit in which cloud vendors sell compute. It is usually one hardware thread of a physical core with SMT, so two vCPUs may share one physical core.WikipediaGlossary; Redis, which is not relational; and raw CPU speed and memory bandwidth, which are useful proxies but not database workloads.

The main design goal is one methodology that scales across server sizes, from small instances (for example, 1 vCPU and 2 GiB of RAMRAM: Random-Access MemoryThe main memory of a computer, which holds the running programs and their data. It is much faster than disk storage, and its content is lost when the power is off.WikipediaGlossary) to large nodes with hundreds of vCPUs.

This benchmark measures two deployment models:

  • IaaSIaaS: Infrastructure as a ServiceA cloud service model that rents out compute, storage and network resources, such as virtual machines and disks, on demand. The vendor runs the hardware, and the user manages the operating system and everything above it.WikipediaGlossary (Infrastructure as a Service): Self-hosted PostgreSQL, with the client and database on the same node.
  • DBaaSDBaaS: Database as a ServiceA cloud service model in which the vendor runs a database for its users, handling provisioning, backups, patching and replication. Examples are Amazon RDS and Google Cloud SQL.WikipediaGlossary (Database as a Service): Provider-managed PostgreSQL, with a separate client VMVM: virtual machineA computer emulated in software on a physical host, with its own OS and a share of the host's CPUs, memory, and storage. Most cloud servers are VMs.WikipediaGlossary; the provider provisions, manages, and tunes the database engine.

Limitations​

These results compare PostgreSQL server CPU and memory performance, not application throughput. The main limitations are:

  • Workload: pgbench_ro uses synthetic transactions and uniform data; the results do not specifically predict any given application's throughput, but aim to provide meaningful comparisons across different server types.

  • Disk and Network: The score excludes disk I/OI/O: Input/OutputThe transfer of data between a computer and the outside world, such as reading and writing disks or sending and receiving network packets. A workload limited by its speed rather than by the CPU is called I/O-bound.WikipediaGlossary (Input/Output) performance and does not measure network throughput or latency. See also Network Performance.

  • Memory: The dataset takes about 303 MiB, and production runs this benchmark only on servers with at least 2 GiB of RAM. The image enforces no minimum, so on smaller hosts disk reads can influence the score.

  • Engine Tuning: When SC_DB_HOST is unset, the image starts PostgreSQL locally and tunes it with pgtunepgtuneA web tool that suggests PostgreSQL settings, such as shared_buffers and work_mem, from the memory, CPU count, storage type and workload of a server. Its suggestions are a starting point for tuning rather than an optimum.pgtuneGlossary. A remote SC_DB_HOST target is not tuned by this benchmark. See also Engine Configuration for why managed engines are left untuned.

    Note: Only the PostgreSQL major version is fixed: minor versions may vary between builds of the image and on DBaaS providers that do not support pinning. See Minor Engine Versions for details.

Usage​

Run the image via Docker. Without SC_DB_HOST, it starts a local PostgreSQL 18 server in the container (standalone mode):

docker run --rm ghcr.io/sparecores/benchmark-pgbench-postgres:main

Do not limit memory with --memory: the local server is tuned for the host's full memory. See Standalone Mode.

Production runs the container privileged, which lets the harnessharnessThe code that runs a benchmark tool. It prepares the inputs, starts the tool with fixed settings, often several times or in parallel, checks and collects the tool's output, and prints the results in one format. Its settings, not the tool's defaults, decide what the benchmark measures.WikipediaGlossary start postgres at a higher priority (nice -n -20niceA Unix command that runs a program with a changed scheduling priority, from -20 (highest) to 19 (lowest). Raising a process's priority above the default needs the CAP_SYS_NICE capability, which root has.man pageGlossary); with a plain docker run, it runs at normal priority. See Production Setup before comparing scores with the Navigator's.

Set SC_DB_HOST to benchmark a remote PostgreSQL server instead. A remote run needs more setup: database privileges, a disposable benchmark database, and the server's vCPU count. Follow Remote Mode for more details and settings.

Both modes need outbound HTTPSHTTPS: Hypertext Transfer Protocol SecureHTTP, the protocol of the web, encrypted with TLS. It protects requests and responses from eavesdropping and tampering, and its certificates let clients verify the identity of the server.WikipediaGlossary access to the Spare Cores CDNCDN: Content Delivery NetworkA network of distributed servers that cache content, such as web pages, images or video, and serve it from locations close to its users. It lowers latency for the users and offloads traffic from the origin server.WikipediaGlossary (Content Delivery Network) to download the dataset; the run fails if the CDN is unreachable.

A default run takes about 25 minutes on servers with 4 or more vCPUs: a 2-minute warmup, 1-minute settle periods, and 5 minutes of measurement at each of four client counts. See Run Duration for the steps and how to change them.

Settings​

Common settings for pgbench_ro are listed here; see the full environment-variable reference for all options and defaults:

  • SC_DB_HOST, SC_DB_PORT, SC_DB_USER, and SC_DB_PASSWORD configure a remote connection; leave SC_DB_PORT and SC_DB_USER unset in standalone mode. SC_DB_SSLMODE sets the SSLSSL: Secure Sockets LayerA protocol that encrypts network connections and verifies the identity of the server, and optionally of the client, with certificates. TLS has replaced it, but SSL is still the common name for both, including in PostgreSQL's settings.PostgreSQL docsGlossary (Secure Sockets Layer) mode for the dataset restore and dump connections and for the connection that drops and recreates the benchmark database. It does not set the mode for pgbench.
  • SC_DB_VCPUS sets the database vCPU count used to derive concurrency pointsconcurrency pointsThe client counts at which a database benchmark measures throughput and latency, each with that number of connections open for a fixed time. Measuring several concurrency points, from one client up to a count derived from the server's size, shows how throughput and latency change with load.Glossary and local server settings in standalone mode. For remote targets, set it to the database server's vCPU count. The default is the client host's logical CPU count.
  • SC_CPU_SCALE changes pgbench_ro transaction work.
  • SC_RUN_SECONDS, SC_WARMUP_SECONDS, and SC_SETTLE_SECONDS control measurement and warmup timing.

Results​

The process prints nothing until the run ends, then prints one JSONJSON: JavaScript Object NotationA text format for structured data, built from objects (key-value pairs), arrays, strings, numbers, booleans, and null. Most languages and tools read and write it, which makes it a common format for API responses and benchmark output.JSONGlossary object to stdoutstdoutThe standard output stream of a process, one of the three standard streams with stdin and stderr. Programs write their results to it; a shell can redirect it to a file or another program, such as > results.jsonl, while logs on stderr still reach the terminal.WikipediaGlossary; a default run can stay silent for about 25 minutes. Errors go to stderrstderrThe standard error stream of a process, one of the three standard streams with stdin and stdout. Programs write logs, progress, and error messages to it, so that they stay separate from the output on stdout when the output is redirected to a file or another program.WikipediaGlossary. In standalone mode, the PostgreSQL server log is written to /tmp/pg-server.log inside the container, so drop --rm to keep it after the run. The JSON object's main fields are the following:

  • score is the highest TPM result; score_unit is tpm, and peak_concurrency is the client count for that result.
  • Each sizes[] entry represents a workload size and has its own score and profile. Entries of pgbench_ro identify cpu_scale; entries of pgbench_tpcb identify scalefactor.
  • Each profile[] entry is one concurrency measurement. The concurrency field is the client count, and jobs is the worker count. For pgbench_ro, benchmark.py derives four fixed points from the database vCPU count (V): 1, V/2 (rounded down), V, and 2·V. It caps worker jobs at 32. For how pgbench_tpcb picks its client counts, see pgbench_tpcb.
  • latency_ms contains sampled p50, p95, and p99 (50th, 95th, and 99th percentile) latency, plus the average, in milliseconds. The harness samples 1% of transaction latency logs.
  • schema_gib (0.17) in pgbench_ro output is a legacy constant, not the dataset size; the restored database is about 303 MiB.

Workloads​

The image supports the following workloads with different transaction patterns:

  • pgbench_ro (default): Runs a custom, read-only transaction over a fixed schema, spreading work across eight PostgreSQL subsystems. The SC_CPU_SCALE variable changes transaction work without resizing the schema.
  • pgbench_tpcb: Runs pgbench's built-in tpcb-like transaction mix against a schema initialized at one or more scale factors. It is disk-limited and not used in production.

For more information on available workloads, see Workloads.

Design History​

We first compared sysbenchsysbenchA scriptable, multi-threaded benchmark tool built on LuaJIT. Its tests cover CPU, memory, file I/O, threads, and mutexes, and its Lua scripts run transactional database workloads against MySQL and PostgreSQL. It reports rates and latency percentiles.GitHubGlossary, HammerDBHammerDBAn open-source database benchmark engine. It runs standardized workloads, such as TPROC-C, with the same test definitions on SQL Server, Db2, Oracle, PostgreSQL, MariaDB, and MySQL, so that results are comparable across database systems. Runs can be scripted and repeated automatically.HammerDBGlossary TPROC-CTPROC-C: HammerDB's transaction processing workloadThe OLTP workload of the HammerDB benchmark tool, derived from the TPC-C specification of an order-entry system with warehouses, orders and payments. It is not an official TPC-C implementation, so its results are not comparable with published TPC-C results.HammerDB docsGlossary (HammerDB's transaction processing workload), BenchBaseBenchBaseA framework that benchmarks SQL databases, the successor to OLTPBench. It runs workloads such as YCSB and a Wikipedia workload against any relational database with a JDBC (Java Database Connectivity) driver, and collects per-transaction-type latency and throughput logs.GitHubGlossary, and pgbench. The experiments showed that write-heavy workloads got bottlenecked by storage and WALWAL: Write-Ahead LoggingA technique in which a database writes each change to a durable log before it changes the data files. After a crash, replaying the log restores a consistent state, and shipping it to other servers drives replication and point-in-time recovery. PostgreSQL, for example, calls its log the WAL, and on write-heavy workloads the speed of WAL writes often limits throughput.WikipediaPostgreSQL docsGlossary (Write-Ahead Logging).

On tmpfs, write-heavy OLTPOLTP: Online Transaction ProcessingA class of workloads made of many short, concurrent transactions that each read or update a few rows, such as orders, payments or bookings. OLTP databases are optimized for the latency and throughput of such transactions, unlike analytical databases built for large scans.WikipediaGlossary (Online Transaction Processing) results improved by 10-25%, showing how much disk behavior could influence a score. But tmpfs was unavailable for DBaaS, and sizing by warehouse count (TPROC-C's dataset-size unit) or scale factor could not cover the range from 1 vCPU to hundreds. pgbench looked promising as a simpler, more scalable basis for a cross-provider benchmark.

Early pgbench -S runs were dominated by network latency, and profiling found that the first custom transaction over-weighted regex work. A set of 21 PostgreSQL configuration experiments on a host with 32 vCPUs showed that tuning could improve throughput by about 20%.

The pgbench_ro redesign uses a fixed, cache-resident schema and spreads CPU work across PostgreSQL subsystems. pgbench_tpcb is kept as a conventional reference workload, but it is disk-limited and unused in production.

See the changelog for experiment results, validation, and calibration details.

References​

See the full list of references used in this documentation.