Skip to main content

Workloads

pgbench_ro​

The pgbench_ro workload is a custom read-only workload with one transaction composed of eight query blocks. They exercise joins, aggregation, indexes, full-text search, arrays, and other 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 subsystems to emphasize 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 work rather than 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). The setup script, ro_cpu_setup.sql, creates the following test data, which takes about 303 MiB including indexes on PostgreSQL 18:

  • 20,000 products
  • 50,000 customers
  • 250,000 orders
  • 750,000 order items

Entity-relationship diagram of the four pgbench_ro tables: ro_cpu_customer, ro_cpu_order, ro_cpu_order_item, and ro_cpu_product, with their columns, types, and foreign keys

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 invokes 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 with -D scale=N and -f ro_cpu_txn.sql. The -D scale=N option multiplies most block widths inside the transaction (wider slices, bigger joins) without touching the underlying dataset, so a single fixed schema can represent a range of CPU intensities. Total work grows sub-linearly, because some blocks have fixed costs.

The workload uses fixed 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 {1, V/2, V, 2·V}, set in benchmark.py, where V is the database vCPUvCPU: 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 count, instead of a geometric search. This choice came out of the latency and pipelining experiments.

The transaction is intentionally a single SQLSQL: Structured Query LanguageThe standard language to define, query and modify data in relational databases. Its declarative statements, such as SELECT, INSERT and UPDATE, describe what data to return or change, and the database plans how to do it.WikipediaGlossary (Structured Query Language) statement whose eight blocks each touch a different PostgreSQL subsystem:

  • q_idx: B-tree index scan, nested loop, and window aggregate
  • q_hashjoin: hash join and hash aggregate over a time slice
  • q_regex: regular expressions and md5()
  • q_fts: full-text search using tsvector and GINGIN: Generalized Inverted IndexA PostgreSQL index type that maps each element of a composite value, such as an array item, a key of a JSON document, or a word of a text, to the rows that contain it. It speeds up containment and full-text searches, at the cost of slower writes than a B-tree.PostgreSQL docsGlossary (Generalized Inverted Index)
  • q_array: array containment using GIN
  • q_stats: ordered-set and statistical aggregates
  • q_toast: TOASTTOAST: The Oversized-Attribute Storage TechniqueThe mechanism with which PostgreSQL stores values too large for a regular 8 kB page, by compressing them, moving them out of line into a separate table, or both. It applies automatically to large values, such as long texts or JSON documents.PostgreSQL docsGlossary (The Oversized-Attribute Storage Technique) fetch and decompression
  • q_seqscan: plain sequential scan and aggregate

The setup also creates a BRINBRIN: Block Range IndexA PostgreSQL index type that stores only a summary, such as the minimum and maximum value, of each range of consecutive table blocks. It is tiny compared to a B-tree, and works well on large tables whose values follow their physical order, such as timestamps of appended rows.PostgreSQL docsGlossary (Block Range Index) on the ro_cpu_order.ordered_at column; the planner may use it for the time-window predicate.

At the end of each transaction, the script combines the eight block outputs with UNION ALL and hashes them into one md5(string_agg(...)) checksum. The checksum makes the result depend on the block outputs and prevents the planner from optimizing away their work; it is not this benchmark's score. The harness then converts pgbench throughput to 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), the published headline score.

pgbench_tpcb​

This workload is pgbench's built-in tpcb-like script (-b tpcb-like) with a standard pgbench -i -s N schema. It is a standard 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) mix in the style of TPC-BTPC-B: Transaction Processing Performance Council Benchmark BA database benchmark of short, update-heavy banking transactions, each updating an account, a teller and a branch balance and logging the change. The TPC retired it in 1995, but the default script of pgbench is still modeled on it.TPCGlossary (Transaction Processing Performance Council Benchmark B): mostly write-heavy and sensitive to disk, network, and locking. Because it is disk-limited, it is kept for possible later use but not run in production.

The workload measures geometric anchors {1, V/4, V/2, V}, rounded to ladder rungs, where V is the smaller of the database vCPU count and the scale factor; client counts never exceed the scale factor. Its adaptive search adds points above the highest anchor while throughput keeps improving. At the default scale factor of 65, it adds points only when SC_PROFILE_MAX_CLIENTS is raised above the highest anchor.

See the official documentation for details.