Benchmark methodology¶

This page explains how the benchmarks are run, and why.

The aim is a fair comparison:

• every driver set up the way its own docs recommend;
• the same queries and the same data for every driver;
• every result checked;
• repeated, interleaved trials;
• every workload reported, including those where fastpg's lead is small.

The suite is bench/compare.py, with its code in bench/comparison/. The Benchmarks workflow runs it in GitHub Actions: on pushes to main that change the library or the benchmarks, weekly, on pull requests labelled benchmark, and on demand. Each run writes its results to the run's summary page, with the raw JSON as an artifact.

Running it¶

task db-up bench-ci                          # what CI runs: 3 trials of 3 s
task bench-ci TRIALS=5 DURATION=10           # longer and steadier
task bench-ci -- -k 'point-*' -d fastpg -d 'asyncpg*'
uv run yeet ./bench/compare.py --list        # list workloads and drivers

task bench-ci also takes DSN, WARMUP, OUT (the output directory) and SUMMARY (a file to append the Markdown summary to). Anything after -- goes to bench/compare.py. A full run takes about 25 minutes.

The suite creates its own fastpg_bench schema and fills it with generated data. It touches nothing else in the database.

On macOS, Docker Desktop adds a few hundred microseconds to every round trip. Use a Linux host, or a PostgreSQL that runs natively.

The setup in CI¶

• A GitHub-hosted ubuntu-24.04 runner: AMD EPYC 9V74, 4 vCPUs, 16 GB.
• PostgreSQL 18.6 on the same machine, with synchronous_commit=off.
• CPython 3.14.7 and uvloop.
• fastpg 0.1.0, asyncpg 0.32.0, psycopg 3.3.6 and psqlpy 0.12.1.
• 3 trials of 3 seconds for each workload and driver. The tables show the median.

Drivers¶

Every driver uses its own pool of 16 connections, in the same process, on the same event loop.

"Fastest other driver" in the results is the fastest of asyncpg, asyncpg-noreset, psycopg and psqlpy.

Why each driver is set up this way:

• asyncpg's session reset. By default, asyncpg's pool runs a reset query each time a connection goes back to the pool. That costs one extra round trip per query. fastpg's pool doesn't reset after single queries, and neither do psycopg's and psqlpy's. So asyncpg-noreset is the like-for-like comparison. It's safe here because no workload changes session state. fastpg's pool.transaction() does reset, inside the COMMIT's round trip, so on the transaction workload default asyncpg is like for like: there fastpg leads it by 1.27x.
• Queueing on busy connections (fastpg). fastpg's pool sends a query to an idle connection first. Only when all are busy does it send several queries to one connection. With 16 tasks on 16 connections there's always an idle one, so this never happens. With 64 tasks it does, so fastpg-inflight1 shows fastpg without it.
• Autocommit. Single queries run in autocommit on every driver. psycopg needs autocommit=True for this; otherwise it wraps each query in a transaction, which costs two extra round trips.
• Prepared statements. Every driver runs prepared statements on the server. psycopg prepares a statement after 5 runs; the warm-up runs each one far more often.
• Text or binary results. asyncpg, fastpg and psqlpy always use binary. psycopg's default text format was as fast or faster than binary here, so it keeps it.
• JSON. asyncpg and psycopg are given msgspec.json.decode to parse jsonb, the fastest decoder they allow. psqlpy and fastpg parse it natively.
• Bulk writes. executemany is all or nothing on every driver. COPY is binary COPY ... FROM STDIN on every driver. Every driver gets the same list of tuples.
• Nothing else is tuned.

Row types¶

The read workloads run twice:

• Native rows: each driver's own row type (fastpg and psycopg tuples, asyncpg Records, psqlpy dicts). This is the least work each driver can do.
• Structs (workloads ending in -typed): every driver returns the same msgspec Struct. fastpg uses into=Person. asyncpg uses Person(*record), psycopg row_factory=args_row(Person), and psqlpy Person(*row) on its tuple rows. This is what an app that wants typed objects really pays.

Checking results¶

Before a read workload is timed, each driver's rows are compared with the expected values. After a write workload, the table contents are checked. A wrong result fails the run, and the CI job fails with it.

Workloads¶

Bulk loads write to an unlogged table, so the runner's disk isn't part of the measurement. No workload reads or sends arrays.

How the numbers are computed¶

• Trials. Each trial runs every workload. Within a workload, the drivers run in a shuffled order, so drift over the run doesn't always hit the same driver.
• Each run opens a fresh pool, checks results, warms up, then measures. Before each run, the write tables are emptied and the server runs a CHECKPOINT.
• Throughput is operations per second of the measured phase. Tables show the median of the trials.
• Latency is measured around each await. Tables show the median of the trials.
• CPU per operation is the benchmark process's CPU time, all threads included, divided by the operations. The server's CPU isn't counted.
• Steady-rate latency. Requests arrive at random times, at a fixed average rate, whether or not earlier ones have finished. Latency counts from when a request was due, so a driver that keeps the event loop busy pays for the delay it causes.

Limits¶

• Shared, noisy machines. The runners are shared VMs, with PostgreSQL on the same machine. Compare drivers within one run, not across runs. Treat a few percent as a tie. An earlier run, on a different CPU, was 25-45% slower overall, with ratios within 0.3x of these.
• No network. Client and server talk over loopback, so round trips are short. Over a real network, the round trip adds the same time to every driver.
• Client-side only. The server's CPU isn't measured.
• One shape. One process, one event loop, a pool of 16, and small tables that fit in memory.
• Structs with plain fields. A Struct that needs msgspec validation is slower to build (see Structs).
• We wrote the suite. Run it on your own hardware, with your own queries. Pull requests that make another driver faster here are welcome.

Full results¶

From run 39671855945. "vs default asyncpg" compares with asyncpg's default pool, which resets the session after every query. CPU is the client's CPU time per operation.

Latency at a steady rate, with every configuration:

All 297 runs passed the result checks.