pgcache-bench-dba

Side-by-side benchmark of pgcache — a transparent caching proxy for PostgreSQL — against a stock Postgres origin, using a real-world schema and real-world data: the public dba.stackexchange.com data dump.

The benchmark drives both lanes — direct-to-origin and through pgcache — with identical workloads and shows the divergence in latency, throughput, and origin CPU as concurrency ramps up. The workload models page rendering (a "page" is a sequence of queries, not an isolated SELECT) with a configurable write rate; see driver/README.md.

It runs in two topologies:

• Local (this README): everything on one machine, wired through make — origin Postgres, pgcache, and Prometheus in Docker Compose; the workload driver built and run on the host.
• AWS: RDS PostgreSQL origin, an EC2 pgcache box, and an EC2 driver box — see aws/README.md for setup and aws/BENCHMARK.md for the measurement runbook.

Prerequisites

• Docker with the compose plugin, with roughly 8 GB of memory available to it (pgcache is capped at 4 GB and the origin, Prometheus, and loader run alongside)
• A Rust toolchain, 1.85 or newer (the driver is edition 2024) — the workload driver builds and runs on the host
• 7z for extracting the dump (any p7zip package)
• curl, make
• The pinned pgcache image is multi-arch (amd64 + arm64); set PGCACHE_IMAGE only to benchmark a different pgcache release.

The AWS topology has its own prerequisites (OpenTofu/Terraform, AWS credentials) — see aws/README.md.

Quickstart

One-time setup (downloads the dump and loads it into the origin, ~10-15 min):

make all

Then the benchmark:

make bench-up                                  # origin + pgcache + Prometheus + postgres-exporter
make bench-warmup                              # 30s warmup so the cached lane starts hot
make bench-run DURATION=8m CONCURRENCY=200     # drive both lanes
make bench-down                                # tear down (drops the replication slot too)

bench-run also accepts WRITE_RATE= (default 0.02), SEED=, and TAG_GROW_FRACTION= overrides.

Reading the results

The driver has no end-of-run summary; results are Prometheus metrics, labeled by lane (direct vs cached). Open http://localhost:59091 and try:

# Pages served per second, per lane
sum by (lane) (rate(driver_pages_total[1m]))

# p99 page latency, per lane
histogram_quantile(0.99, sum by (lane, le) (rate(driver_page_latency_seconds_bucket[1m])))

Per-query latency is in driver_query_latency_seconds. Origin-side signals (pg_stat_*) come from postgres-exporter, and pgcache's own metrics (hit rate, registrations, CDC queue) are scraped from http://localhost:59090/metrics.

The 30s bench-warmup is enough to see the lanes diverge, but for numbers worth quoting, warm until pgcache reaches steady state — registration plateaued, hit rate ≥ ~0.99, CDC queue drained. The methodology and the signals to check are in bench/README.md.

Step-by-step alternative

make data       # download + extract the XML dump (~305 MB → ~1.7 GB)
make up         # start origin Postgres on localhost:55432
make load       # run the loader against the running origin
make up-pgcache # add the pgcache lane on localhost:55433

Connection details

Setting	Default	Override env var
host	localhost	PGHOST
port	55432	PGPORT
user	pgcache	PGUSER
password	pgcache	PGPASSWORD
database	dba_se	PGDATABASE

Non-default port (55432) is chosen to avoid colliding with any local Postgres already listening on 5432. The pgcache lane listens on 55433 for the same reason.

The origin is started with wal_level=logical so the same instance can be the source for pgcache's CDC replication slot.

Layout

pgcache-bench-dba/
├── docker-compose.yml          origin + pgcache + observability + loader
├── compose.aws.yml             override for the AWS (RDS origin) topology
├── Makefile                    orchestration entry point
├── AGENTS.md                   context for coding agents: invariants + gotchas
├── data/                       downloaded dump + extracted XML (gitignored)
├── driver/                     Rust workload driver (its own crate)
├── load/                       containerized XML loader
├── bench/                      benchmark orchestrators + methodology notes
├── ycsb/                       stock YCSB workload + bench harness
├── aws/                        OpenTofu/Terraform for the AWS benchmark topology
└── observability/
    ├── prometheus.yml          local scrape config
    └── prometheus.aws.yml      scrape config for the AWS topology

How the loader works

The XML loader is Networks-Learning/stackexchange-dump-to-postgres. We run it in a small container (load/Dockerfile, python:3.12-slim plus libarchive13 and postgresql-client) that talks to the origin service over the compose network. The container clones the loader at a pinned SHA into load/stackexchange-dump-to-postgres/ (bind-mounted from the host) and loads each XML file as its own table; afterwards post_load.sql adds indexes and the normalized post_tags table.

The loader excludes Posts.Body by default; we pass --with-post-body so the question-page query has something to render.

Resetting between runs

• make reset — drop and recreate the database, container stays up
• make clean — stop containers, drop volumes, remove extracted XML and loader

Going further

• aws/README.md — provision and run the AWS topology
• aws/BENCHMARK.md — the AWS measurement runbook
• bench/README.md — warm-to-steady-state methodology and the run orchestrators
• ycsb/README.md — stock YCSB against both lanes
• driver/README.md — the page-load workload driver
• AGENTS.md — context for coding agents: measurement invariants and operational gotchas

License

Code is dual-licensed under Apache-2.0 or MIT, at your option. The data is the Stack Exchange Data Dump under CC BY-SA 4.0 — attribution is required when displaying any of the loaded content.