pg-bench-real

A realistic single-node PostgreSQL saturation benchmark. It answers one question, and answers it honestly:

At what committed-writes-per-second does a single PostgreSQL node start failing realistic ACID OLTP transactions — so I know when sharding or horizontal scaling becomes necessary?

This is not pgbench. It is not a blog-post benchmark. It does not measure SELECT 1 throughput. It does not measure unlogged bulk inserts. It does not use a three-column toy schema. It measures the exact thing you care about if you run a real enterprise write workload on Postgres: the rate at which committed, ACID, multi-statement, pool-backed transactions start being rejected.

What it does

• Open-loop driver. Requests arrive at a target rate regardless of how fast the DB drains them. When the server can't keep up, requests queue in a fixed-size connection pool (like AddDbContextPool / HikariCP / psycopg-pool in production) and eventually time out. This is the "couldn't serve the request" signal that matters — not throughput at saturation.
• Adaptive rate sweep. Probes at a known-safe low rate, then at a known-broken high rate, then binary-searches between them for the knee where error rate crosses 0.1% or P99 crosses 1 second. Converges to a ±5–10% bracket in ~6–8 probes. Spends compute budget around the interesting number, not on probes you already know the answer to.
• Realistic transaction shapes. Two of them, both ACID and both multi-statement:
  • standard_oltp_write — SELECT wallet state → SELECT recent count → INSERT new → INSERT audit → COMMIT (4 statements)
  • banking_transfer — SELECT FOR UPDATE ×2 → UPDATE debit → UPDATE credit → INSERT new → INSERT audit → COMMIT (6 statements with two row locks held simultaneously, canonical deadlock-avoidance lock ordering)
• Realistic schema. 12-column transactions table with jsonb metadata, 5 indexes, partitioned monthly by created_at. A wallets table with 10,000 accounts. An audit_log with its own two indexes. Every transaction writes to multiple tables.
• Zipfian access distribution. Wallet IDs picked from a Zipfian distribution (α=1.0 by default — top 1% of wallets absorb ~20% of traffic). Uniform random hides contention, which is the #1 single-node bottleneck. Zipfian surfaces it.
• Production timeouts. Every connection has statement_timeout=5s, lock_timeout=1s, idle_in_transaction_session_timeout=10s. A transaction that can't finish under those limits is counted as a specific failure category, not silently retried.
• Background metrics. 1 Hz sampling of pg_stat_activity, pg_stat_wal, pg_stat_database, OS CPU breakdown, and disk iostat during every probe. Goes to results/metrics.jsonl keyed by probe_id so you can post-hoc answer why a probe failed (WAL-bound? fsync-queue? lock-wait? CPU-saturated?).
• 7-minute probes. Each probe spans a full PostgreSQL checkpoint cycle by default. Shorter measurements lie about checkpoint-sensitive workloads.
• 50M-row prefill by default. On a 62 GB box with 15 GB shared_buffers, a 50M-row transactions table + its indexes exceeds cache. This is what moves the benchmark into the I/O-bound regime you'll see in production — smaller prefills fit in cache and give misleadingly optimistic numbers.
• No analysis. Output is raw CSV (results/results.csv) plus the metrics timeseries (results/metrics.jsonl). Interpretation is your job; this repo deliberately ships no report generator.

What it is NOT

• Not pgbench. pgbench is a closed-loop driver with a 4-table TPC-B-like schema. It cannot find a ceiling; only a plateau.
• Not sysbench. Same reason.
• Not YCSB / HammerDB. Those are different categories of tools (key-value, full TPC-C). Use them if that's what you need.
• Not a latency microbenchmark. Latency is reported, but the question is "at what rate do failures start", not "how fast is the P50".
• Not a read-path benchmark. Writes only. If reads are your bottleneck, this isn't the tool.
• Not a network / app-layer benchmark. All clients talk to localhost. Add your own network RTT and application overhead to translate the numbers to "what my API will see".

Requirements

• Linux (Fedora, RHEL, Rocky, AlmaLinux, Ubuntu, Debian)
• PostgreSQL 14 or later (tested on 18)
• Python 3.11 or later (tested on 3.14)
• Passwordless sudo (for tuning postgresql.conf + service restart)
• At least 30 GB of free disk for the default 50M-row prefill

Quick start

git clone https://github.com/YOUR-ORG/pg-bench-real.git
cd pg-bench-real

# Install PostgreSQL + Python deps + create benchuser/benchdb.
# Idempotent. See "Manual install" below if you'd rather do it by hand.
./install.sh

# Smoke test — ~3–4 minutes. Verifies the pipeline end-to-end before you
# commit to a real run. The numbers from a smoke run are NOT meaningful
# (probes too short, matrix too small), only the exit code is.
python3 -m pg_bench_real -c config.smoke.yaml

# Real run — ~2h 30m on modern hardware with default config.
python3 -m pg_bench_real

Results go to results/results.csv, metrics timeseries to results/metrics.jsonl, resume state to results/state.json. Interrupting mid-run and re-starting the same command resumes at the next incomplete sweep.

What's in results.csv

One row per probe (not per sweep). A sweep is a sequence of probes that binary-search the knee — you get 6–8 rows per sweep, and the interesting one is the highest target_rate where error_rate_pct is below threshold.

Column	Meaning
timestamp	Local time the probe finished
shape	standard_oltp_write or banking_transfer
table_type	partitioned or nonpartitioned
prefill_size	Rows in the transactions table when the probe started
index_count	Active indexes on the transactions table
pool_size	Connection pool size (fixed for this probe)
sync_commit	on or off (per-transaction via SET LOCAL)
target_rate	Target requests/sec the driver aimed for
achieved_rate	Committed-rows/sec actually achieved
error_rate_pct	failed / submitted × 100
committed_rows	Total committed in the measure window
submitted	Total arrivals in the measure window
avg_ms, p50_ms, p99_ms, p999_ms	End-to-end latency distribution for committed txns
pool_wait_p50_ms, pool_wait_p99_ms	Time spent waiting for a connection
inflight_max	Maximum concurrent in-flight tasks observed
failed_pool_wait_timeout	Requests that couldn't get a connection in time
failed_statement_timeout	Transactions killed by statement_timeout
failed_lock_timeout	Transactions killed by lock_timeout
failed_deadlock	Transactions rolled back from deadlock detection
failed_serialization	Serialization failures (rare outside SERIALIZABLE)
failed_idle_tx_timeout	Killed by idle_in_transaction_session_timeout
failed_inflight_overflow	Dropped by the driver's load-shed cap (driver couldn't keep up)
failed_other	Anything else (see metrics.jsonl / run.log for details)
measure_seconds	Actual measurement window duration
probe_id	Matches the probe_id in metrics.jsonl
host_cpu, host_ram_gb, host_disk	Hardware snapshot

What's in metrics.jsonl

One JSON line per second per probe, tagged with probe_id. Fields:

• activity — {active: N, "idle in transaction": M, ...} from pg_stat_activity
• wal_rate.bytes_per_sec, wal_rate.records_per_sec — WAL generation
• db_rate.commits_per_sec, db_rate.tup_inserted_per_sec — server-side commit/insert rates
• cpu.{user,system,idle,iowait} — /proc/stat delta
• disk.read_mb_s, disk.write_mb_s, disk.util_pct — /proc/diskstats delta

Use this to correlate the moment a probe broke with what was happening server-side. Example questions it answers:

• Was the breakpoint WAL-bound? → look at disk.write_mb_s around the failing probe's timestamp.
• Was it lock-bound? → look at activity["active"] climbing and transactions stuck waiting.
• Was it checkpoint-induced? → look at disk.util_pct spiking.

How to read the results

The simple version:

1. Open results.csv, filter to the shape you care about.
2. Sort by target_rate.
3. The highest rate where error_rate_pct < 0.1 AND p99_ms < 1000 AND achieved_rate ≥ 0.95 × target_rate is your single-node ceiling for that workload on your hardware.
4. If the highest healthy rate and the lowest broken rate are close together (which they should be after the binary search), the knee is between them.
5. Look at the broken probe's failed_* columns to understand why it broke:
   • failed_pool_wait_timeout dominates → pool is too small for the transaction time (try a larger pool in matrix.pool_sizes)
   • failed_statement_timeout / failed_lock_timeout dominates → real DB contention or WAL saturation (look at metrics.jsonl for root cause)
   • failed_deadlock climbs → lock ordering is falling apart under load (almost certainly irrelevant for the default workloads — they use canonical lock ordering — but could matter for a custom shape)
6. The standard_oltp_write ceiling is your "baseline enterprise write" capacity. The banking_transfer ceiling is the same workload under heavy row contention. The gap between them is what contention costs you.

What the ceiling actually tells you. Real API traffic is not a closed loop, and neither is this benchmark — at the ceiling rate, you can serve that many transactions per second if your users were hitting the API at exactly that rate. To translate to a user count: divide by the per-user request rate. If your users issue 1 write/sec on average, a 4,000 rps ceiling serves ~4,000 concurrent heavy users. If they issue 1 write/minute, it serves ~240,000 users. That math is yours — we give you the DB-side number.

Configuration

See config.yaml — every knob is commented in place. The defaults are what I'd run if I were benchmarking a new single-node PG before deciding whether to shard. Common changes:

You want…	Edit
Only one transaction shape	matrix.shapes: [standard_oltp_write] (cuts runtime in half)
Compare partitioned vs non-partitioned	matrix.table_types: [partitioned, nonpartitioned]
Compare pool sizes	matrix.pool_sizes: [20, 50, 100]
Faster, less precise probes	sweep.measure_seconds: 180 (3 min)
Don't touch postgresql.conf	postgresql.auto_tune: false
Different knee criteria	sweep.error_rate_threshold_pct / sweep.p99_threshold_ms
Test at 100M rows	matrix.prefill_sizes: [100_000_000] (adds ~20 min prefill)

Manual install

If install.sh doesn't work for you:

# 1. Install PostgreSQL and pip
#    Fedora / RHEL:
sudo dnf install -y postgresql-server postgresql-contrib python3-pip
#    Debian / Ubuntu:
sudo apt-get install -y postgresql postgresql-contrib python3-pip

# 2. Initialize the cluster (RHEL-family only; Debian does this for you)
sudo postgresql-setup --initdb
sudo systemctl enable --now postgresql

# 3. Create the role and database
sudo -u postgres psql <<'SQL'
CREATE ROLE benchuser LOGIN CREATEDB;
CREATE DATABASE benchdb OWNER benchuser;
SQL

# 4. Allow benchuser to connect from 127.0.0.1 (trust auth — it's a dev tool)
sudo -u postgres psql -c 'SHOW hba_file'   # find pg_hba.conf
# Add this line before any reject lines:
#   host all benchuser 127.0.0.1/32 trust
sudo -u postgres psql -c 'SELECT pg_reload_conf()'

# 5. Python deps
pip install --user 'psycopg[binary]>=3.1' 'psycopg-pool>=3.2' 'PyYAML>=6.0'

Known limitations

• Single host. Client and server share a machine. No network latency inflates the numbers; real clients will see higher per-request latency even at rates well below the ceiling.
• No retries. Real applications retry on deadlock / serialization failure. This benchmark doesn't — it counts them as rejections. The ceiling you measure is pessimistic by exactly the retry-success rate of your app.
• Row-count drift. Within a sweep, each probe commits rows — the 50M-row table grows slightly during measurement. The drift is small relative to the base (millions of rows over 8 probes on a 50M-row table) and doesn't meaningfully change the steady-state regime.
• banking_transfer vs a real bank. Our wallets table has flat balances; we don't check for insufficient funds (every wallet starts with a huge balance). Real banks add constraint checks, fraud checks, and multi-step saga logic. Our shape exercises the PG-side mechanics (row locking, multi-statement transactions) faithfully but is not a "banking simulator".
• No connection churn. Connections are created once at pool startup. Real apps sometimes recycle connections — this adds ~10-50ms per churn that this benchmark doesn't model.

License

MIT — see LICENSE.