Usage
Standalone Mode
Leave SC_DB_HOST empty to automatically start 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 server inside
the Docker container:
docker run --rm ghcr.io/sparecores/benchmark-pgbench-postgres:main
Note: Docker --memory, --cpus, and --cpuset-cpus limits do not change
the tuning. With --memory, PostgreSQL is still sized for the host's memory and
can be killed for running out of memory; with --cpus or --cpuset-cpus, set
SC_DB_VCPUS to match.
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 tunes the local server at startup, based on the identified 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 and system memory size, using the following algorithm, implemented in
pgtune_leopard.py:
-
Take the host system memory (Docker
--memorylimits are ignored),M, fromMemTotalin/proc/meminfo, rounded down to whole GiB (at least1). -
Take the vCPU count,
C, from the optionalSC_DB_VCPUSenvironment variable, which defaults to the host's logical 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 count, even when Docker limits the container's CPUs. -
Generate PostgreSQL settings as
pgtunedoes with its form defaults: PostgreSQL 18, Linux, web application, SSDSSD: Solid-State DriveA storage device that keeps data in flash memory instead of on spinning disks, so it reads and writes much faster than an HDD, especially at random locations. A controller with its own firmware maps logical blocks to flash pages, and many SSDs write incoming data to a fast cache first, then move it to slower flash. Cloud servers use SSDs as local disks and behind network block storage.WikipediaGlossary storage, database size mid-RAM, and an automatic connection count. -
Set
shared_bufferstoM/4andeffective_cache_sizeto3M/4. -
Set
huge_pagestotrywhenshared_buffersis at least 2 GB, otherwiseoff. -
Set
maintenance_work_memtoM/16, capped at 8 GB. Setautovacuum_work_memto 2 GB whenmaintenance_work_memreaches 2 GB. -
Set
work_memto(M - shared_buffers) / (3 × (200 + W)), with a 4 MB minimum, whereWisCwhenC≥ 4 and 1 otherwise. -
Set
wal_buffersto 3% ofshared_buffers, with a 32 kB minimum and a 16 MB cap; values between 14 and 16 MB are rounded up to 16 MB. -
Use fixed values for the following settings:
min_wal_size:1 GBmax_wal_size:4 GBcheckpoint_completion_target:0.9default_statistics_target:100random_page_cost:1.1effective_io_concurrency:200jit:offwal_compression:lz4lz4A lossless compression algorithm and format by Yann Collet from the LZ77 family, built for speed rather than compression ratio. It compresses and especially decompresses much faster than gzip, at a lower ratio; its higher levels use a slower high-compression mode that keeps decompression fast.LZ4Glossaryio_method:io_uring
-
When
C≥ 4, setmax_worker_processesandmax_parallel_workerstoC, andmax_parallel_workers_per_gatherandmax_parallel_maintenance_workerstoC/2 rounded up, capped at 4. -
Set
autovacuum_max_workersto 4 whenC≥ 16 and to 5 whenC≥ 32; otherwise, keep the PostgreSQL default. -
Override
pgtunepgtuneA web tool that suggests PostgreSQL settings, such asshared_buffersandwork_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'smax_connectionsof 200 with 3,122 (the highest client count, 3,072, plus 50 reserved connections). Thework_memsetting is still sized for 200 connections. -
Set
synchronous_committooffwhenSC_DURABILITY=async, andonotherwise.
The output records the generated settings in postgres.requested_gucs and a
pgtune form link in pgtune_share_url. The link shows pgtune's default of
200 connections, not the 3,122 override. The pgbench_ro workload applies
further workload settings on top of these.
Remote Mode
Set SC_DB_HOST to benchmark an existing PostgreSQL 18 server, such as a 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) instance, from the same or a separate machine. Before
running, check the following prerequisites:
- The
SC_DB_USERrole can create databases (CREATEDB) and terminate other sessions on the benchmark database: it must be a superuser, a member ofpg_signal_backend, or the role those sessions run as. - The benchmark database,
SC_PGBENCH_DB(defaultpgbenchpgbenchThe 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), holds no data that is needed. Restoring a cached dump drops and recreates it; building the dataset drops and recreates its benchmark tables. - The
SC_DB_USERrole owns the benchmark database or is a superuser, so it can set thepgbench_roworkload settings. - The client can reach
SC_CDN_BASE_URLover 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. The image restores a cached dataset dump from there and builds the dataset itself only when no dump exists for it; the run fails if the 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) is unreachable. SC_DB_VCPUSis set to the database server's vCPU count. Without it, the image uses the logical CPU count of the machine it runs on, so on a separate client machine it picks 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 for the wrong server size.
Set the host, credentials, and server size:
docker run --rm \
-e SC_DB_HOST=<postgres-host> \
-e SC_DB_USER=<username> \
-e SC_DB_PASSWORD=<password> \
-e SC_DB_VCPUS=<database-vcpus> \
-e SC_DB_MEM_GIB=<database-memory-gib> \
-e SC_WORKLOAD=pgbench_ro \
ghcr.io/sparecores/benchmark-pgbench-postgres:main
SC_DB_MEM_GIB is only recorded in the output; for a remote server it is null
unless it is set.
The image does not tune a remote server's configuration; the only settings it changes are the workload settings on the benchmark database.
Workload Settings
In both modes, pgbench_ro sets the following defaults
(benchmark.py) on its benchmark database with ALTER DATABASE ... SET, after building or restoring the dataset:
jit:offwork_mem:64MBmax_parallel_workers_per_gather:0
They apply to every pgbench session and override the server-level values,
including the standalone 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 settings. The pgbench_tpcb workload changes
no settings.
Run Duration
A run prepares the dataset, then measures throughput at a series of 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 in the following steps:
- Restore the dataset from a cached dump on the CDN, or build it if no dump exists for it.
- Warm up once for
SC_WARMUP_SECONDS(default: 120s) at the first concurrency point. Before each later point, run a settle period ofSC_SETTLE_SECONDS(default: 60s) at that point's client count. Neither is measured. WithSC_WARMUP_ONCE=false, every point gets the full warmup. - Measure each concurrency point for
SC_RUN_SECONDS(default: 300s), sampling 1% of transaction latencies. - Report the highest 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
pgbenchreport transactions per second (TPS), which is 1/60 of TPM.WikipediaGlossary (Transactions Per Minute) of all concurrency points (and scale factors) as the score.
For pgbench_ro, benchmark.py sets the concurrency points
to {1, V/2, V, 2·V}, where V is SC_DB_VCPUS and V/2 is rounded down.
Duplicates collapse on small servers, so the default duration depends on V as
follows, excluding dataset preparation:
V | Concurrency points | Duration |
|---|---|---|
| 1 | 1, 2 | 13 minutes (120 + 60 + 2 × 300s) |
| 2 | 1, 2, 4 | 19 minutes (120 + 2 × 60 + 3 × 300s) |
| 3 | 1, 3, 6 | 19 minutes (120 + 2 × 60 + 3 × 300s) |
| 4 or more | 1, V/2, V, 2·V | 25 minutes (120 + 3 × 60 + 4 × 300s) |
For example, a production run on an AWSAWS: Amazon Web ServicesThe cloud computing platform of Amazon, launched in 2006 and the largest public cloud by market share. It offers hundreds of services, from virtual machines (EC2) and object storage (S3) to managed databases and machine learning.AWSGlossary (Amazon Web Services) m9g.24xlarge server
with 96 vCPUs measured 1, 48, 96, and 192 clients, with 25 minutes of warmup
and measurement (recorded
output).
Preparing the dataset and starting the server add to the total run time.
The following settings also change the number of concurrency points, and so the duration:
SC_PROFILE_VUSreplaces the default concurrency points forpgbench_ro.- Points above the server's
max_connectionsminus 50 are dropped, which can shorten runs against a remote server with a low connection limit. pgbench_tpcbprepares and measures each scale factor inSC_SCALEFACTORSin turn, with a settle period before every point after the first warmup.
Each pgbench call times out after its duration plus 600 seconds. Each dataset
build or restore command times out after 4 hours; the optional dump upload has
no timeout.
Why 5 Minutes
This is deliberately not a long-running benchmark. We tested 5-, 10-, 15-, and
30-minute measurement windows with five interleaved trials each, using
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's Wikipedia workload on three GCPGCP: Google Cloud PlatformThe cloud computing platform of Google. It offers virtual machines (Compute Engine), object storage, managed databases, Kubernetes and machine learning services, among others.Google CloudGlossary (Google Cloud Platform) server types
and pgbench -S on a fourth.
Longer windows moved mean throughput by less than 2% and did not reduce run-to-run variation, which comes from load, OSOS: operating systemThe software that manages a computer's hardware and runs programs on it, such as Linux or Windows. A cloud server runs the OS of the image it was started from.Glossary, and noisy-neighbor effects rather than from too short an average. See Measurement duration for the results.
As a result, each score is a single 5-minute sample. Repeated runs on the same
server type varied by a CVCV: Coefficient of VariationThe standard deviation of a set of measurements divided by their mean, often given as a percentage. As it is scale-free, it compares the variability of measurements of different magnitudes, such as how much repeated runs of different benchmarks vary.WikipediaGlossary (Coefficient of Variation) of about 0.5-4% in these
tests, so treat smaller differences between server types as noise. These
figures come from runs of BenchBase's Wikipedia workload and pgbench -S, not
from pgbench_ro.
Production Setup
The Inspector of Spare
Cores Navigator runs this image with the following setup. A plain
docker run differs from it as noted below.
Container Settings
Production runs apply no sysctl or other host OSOS: operating systemThe software that manages a computer's hardware and runs programs on it, such as Linux or Windows. A cloud server runs the OS of the image it was started from.Glossary tweaks. The container runs
privileged with the following settings:
- host networking
seccomp=unconfined- high
nofileand unlimitedmemlockulimits (unlocking huge pages andio_uring)
In standalone mode, the harness itself starts postgres under 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.
The higher priority takes effect only when the container has CAP_SYS_NICE, as
under --privileged; with a plain docker run, the server runs at normal
priority.
Topology
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) runs place the client and database on the same node. DBaaS runs use 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 that always reaches the database over private VPCVPC: Virtual Private CloudAn isolated virtual network within a public cloud account, with its own IP address ranges, subnets, routing and firewall rules. Resources such as virtual machines and databases are placed in it to control how they reach each other and the internet.WikipediaGlossary (Virtual Private Cloud) addresses. On AWS, both are in the same availability zone; other vendors may place them in different zones of the same region.
Environment Variables
| Variable | Meaning | Default |
|---|---|---|
SC_WORKLOAD | Workload: pgbench_ro or pgbench_tpcb. | pgbench_ro |
SC_DB_HOST | Remote database host; empty starts local PostgreSQL. | Empty |
SC_DB_PORT | Database port. Remote mode only: leave it unset in standalone mode, which always uses 5432. | 5432 |
SC_DB_USER | Database role. Remote mode only: leave it unset in standalone mode, which always uses postgres. | postgres |
SC_DB_PASSWORD | Database password. | postgres |
SC_DB_NAME | Admin database used for setup and settings queries. | postgres |
SC_PGBENCH_DB | Database used by this benchmark; its contents are dropped and recreated. | pgbench |
SC_DB_SSLMODE | 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, pg_dump, and the connection that drops and recreates the benchmark database. The pgbench tool and the setup queries do not use it. | prefer |
SC_CPU_SCALE | pgbench_ro transaction work multiplier. | 1 |
SC_SCALEFACTORS | Comma-separated pgbench_tpcb scale factors. | Unset |
SC_SCALEFACTOR | pgbench_tpcb scale factor when SC_SCALEFACTORS is unset or empty. | 65 |
SC_PROFILE_VUS | Comma-separated pgbench_ro 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. For pgbench_tpcb, it only sets the default SC_PROFILE_MAX_CLIENTS. | Derived from database vCPUs |
SC_PROFILE_SEARCH | Allow adaptive concurrency search for pgbench_tpcb; forced off for pgbench_ro. At the default scale factor, it takes effect only when SC_PROFILE_MAX_CLIENTS is above the highest anchor. | True for pgbench_tpcb; false for pgbench_ro |
SC_PROFILE_IMPROVE_PCT | Throughput improvement threshold for pgbench_tpcb search. | 5.0 |
SC_PROFILE_MAX_CLIENTS | Client-count limit for the planned profile. The pgbench_tpcb search can extend past it while throughput improves, up to the scale factor. Raising it above the highest anchor lets pgbench_tpcb search add points only when a ladder rung lies between that anchor and the scale factor. | Highest anchor |
SC_PROFILE_HARD_MAX_CLIENTS | pgbench_tpcb only: the highest client count search can reach, also capped by the scale factor and by max_connections minus 50. Has no effect on pgbench_ro. | Highest anchor for pgbench_ro; 3072 for pgbench_tpcb |
SC_RUN_SECONDS | Measurement duration per concurrency point. | 300 |
SC_WARMUP_SECONDS | Initial warmup duration. | 120 |
SC_SETTLE_SECONDS | Settle duration between concurrency points. | 60 |
SC_WARMUP_ONCE | Use one full warmup, then settle between concurrency points. | true |
SC_DB_VCPUS | Database vCPU count used for concurrency points and local tuning. Set it for remote databases: the default is the client host's logical CPU count. | os.cpu_count() or 2 |
SC_CLIENT_VCPUS | Client vCPU count recorded in output. | os.cpu_count() or 2 |
SC_DB_MEM_GIB | Database memory in GiB recorded in output. Set it for remote databases. | Detected locally; unset for remote databases |
SC_DURABILITY | Local server durability; async disables synchronous_commit. | durable |
SC_TOPOLOGY | Topology recorded in output. | single_vm locally; otherwise multi_vm |
SC_CDN_BASE_URL | CDN base URLURL: Uniform Resource LocatorThe address of a resource on the web or a network, such as https://sparecores.com/, made of a scheme, a host, and a path.Glossary for cached dataset dumps. | https://cdn.sparecores.net/sc-inspector |
SC_CDN_DATASET_POST_B64 | Optional base64-encoded presigned upload configuration for dataset dumps. | Unset |
SC_CDN_UPLOAD | Permit dataset upload when a valid upload configuration is supplied. | 1 |
See Results for the output fields.