# Usage

> Leave `SC_DB_HOST` empty to automatically start a local PostgreSQL server inside
> the Docker container:

## Standalone Mode

```bash
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 harness tunes the local server at startup, based on the identified vCPU
count and system memory size, using the following algorithm, implemented in
[`pgtune_leopard.py`](https://github.com/SpareCores/sc-images/blob/main/images/benchmark-pgbench-postgres/pgtune_leopard.py):

- Take the host system memory (Docker `--memory` limits are ignored), `M`, from
  `MemTotal` in `/proc/meminfo`, rounded down to whole GiB (at least `1`).
- Take the vCPU count, `C`, from the optional `SC_DB_VCPUS` environment
  variable, which defaults to the host's logical CPU count, even when Docker
  limits the container's CPUs.
- Generate PostgreSQL settings as [`pgtune`](https://pgtune.leopard.in.ua/) does
  with its form defaults: PostgreSQL 18, Linux, web application, SSD storage,
  database size mid-RAM, and an automatic connection count.
- Set `shared_buffers` to `M/4` and `effective_cache_size` to `3M/4`.
- Set `huge_pages` to `try` when `shared_buffers` is at least 2 GB, otherwise
  `off`.
- Set `maintenance_work_mem` to `M/16`, capped at 8 GB. Set
  `autovacuum_work_mem` to 2 GB when `maintenance_work_mem` reaches 2 GB.
- Set `work_mem` to `(M - shared_buffers) / (3 × (200 + W))`, with a 4 MB
  minimum, where `W` is `C` when `C` ≥ 4 and 1 otherwise.
- Set `wal_buffers` to 3% of `shared_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 GB`
    - `max_wal_size`: `4 GB`
    - `checkpoint_completion_target`: `0.9`
    - `default_statistics_target`: `100`
    - `random_page_cost`: `1.1`
    - `effective_io_concurrency`: `200`
    - `jit`: `off`
    - `wal_compression`: `lz4`
    - `io_method`: `io_uring`

- When `C` ≥ 4, set `max_worker_processes` and `max_parallel_workers` to `C`,
  and `max_parallel_workers_per_gather` and `max_parallel_maintenance_workers`
  to `C`/2 rounded up, capped at 4.
- Set `autovacuum_max_workers` to 4 when `C` ≥ 16 and to 5 when `C` ≥ 32;
  otherwise, keep the PostgreSQL default.
- Override `pgtune`'s `max_connections` of 200 with 3,122 (the highest client
  count, 3,072, plus 50 reserved connections). The `work_mem` setting is still sized
  for 200 connections.
- Set `synchronous_commit` to `off` when `SC_DURABILITY=async`, and `on`
  otherwise.

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](#workload-settings) on top of these.

## Remote Mode

Set `SC_DB_HOST` to benchmark an existing PostgreSQL 18 server, such as a DBaaS
(Database as a Service) instance, from the same or a separate machine. Before
running, check the following prerequisites:

- The `SC_DB_USER` role can create databases (`CREATEDB`) and terminate other
  sessions on the benchmark database: it must be a superuser, a member of
  `pg_signal_backend`, or the role those sessions run as.
- The benchmark database, `SC_PGBENCH_DB` (default `pgbench`), 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_USER` role owns the benchmark database or is a superuser, so it can
  set the `pgbench_ro` [workload settings](#workload-settings).
- The client can reach `SC_CDN_BASE_URL` over HTTPS. 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 CDN (Content Delivery Network) is unreachable.
- `SC_DB_VCPUS` is 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 points for the wrong server size.

Set the host, credentials, and server size:

```bash
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](#workload-settings) on the benchmark
database.

## Workload Settings

In both modes, `pgbench_ro` sets the following defaults
([`benchmark.py`](https://github.com/SpareCores/sc-images/blob/main/images/benchmark-pgbench-postgres/benchmark.py)) on its benchmark database with `ALTER
DATABASE ... SET`, after building or restoring the dataset:

- `jit`: `off`
- `work_mem`: `64MB`
- `max_parallel_workers_per_gather`: `0`

They apply to every `pgbench` session and override the server-level values,
including the standalone `pgtune` settings. The `pgbench_tpcb` workload changes
no settings.

## Run Duration

A run prepares the dataset, then measures throughput at a series of concurrency
points in the following steps:

1. Restore the dataset from a cached dump on the CDN, or build it if no dump
   exists for it.
2. Warm up once for `SC_WARMUP_SECONDS` (default: 120s) at the first concurrency
   point. Before each later point, run a settle period of `SC_SETTLE_SECONDS`
   (default: 60s) at that point's client count. Neither is measured. With
   `SC_WARMUP_ONCE=false`, every point gets the full warmup.
3. Measure each concurrency point for `SC_RUN_SECONDS` (default: 300s), sampling
   1% of transaction latencies.
4. Report the highest TPM (Transactions Per Minute) of all concurrency points
   (and scale factors) as the score.

For `pgbench_ro`, [`benchmark.py`](https://github.com/SpareCores/sc-images/blob/main/images/benchmark-pgbench-postgres/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 AWS (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](https://github.com/SpareCores/sc-inspector-data/blob/main/data/aws/m9g.24xlarge/0/pgbench_postgres_ro_durable/stdout)).
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_VUS` replaces the default concurrency points for `pgbench_ro`.
- Points above the server's `max_connections` minus 50 are dropped, which can
  shorten runs against a remote server with a low connection limit.
- `pgbench_tpcb` prepares and measures each scale factor in `SC_SCALEFACTORS` in
  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
BenchBase's Wikipedia workload on three GCP (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, OS, and noisy-neighbor effects
rather than from too short an average. See
[Measurement duration](https://docs.sparecores.com/images/benchmark-pgbench-postgres/design-history.md#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 CV (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](https://docs.sparecores.com/overview#how-it-works) 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 OS tweaks. The container runs
privileged with the following settings:

- host networking
- `seccomp=unconfined`
- high `nofile` and unlimited `memlock` ulimits (unlocking huge pages and
  `io_uring`)

In standalone mode, the harness itself starts `postgres` under `nice -n -20`.
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

IaaS (Infrastructure as a Service) runs place the client and database on the
same node. DBaaS runs use a separate client VM that always reaches the database
over private VPC (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` | SSL (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 points. 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 URL 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](https://docs.sparecores.com/images/benchmark-pgbench-postgres.md#results) for the output fields.
