Latency overhead benchmark Mar 2026: pgBouncer vs PgDog vs SPQR vs Citus vs Multigres vs bare Postgres — 64 vCPU, two workloads

Proxy Latency Benchmark — SPEC v2.0

Version: 2.0 — Mar 2026 Purpose: Measure raw CPU overhead of PostgreSQL connection proxies on localhost. Two workloads: simple SELECT-only (continuity with v1) and a complex recursive CTE (parser/planner stress test). No network. No disk I/O. Pure proxy tax.

Deliverables:

1. CSV with TPS/latency per proxy × concurrency level × workload
2. Flamegraphs (perf) for each proxy at a representative concurrency, both workloads
3. GitLab issue on postgres-ai/postgresql-consulting/tests-and-benchmarks with analysis
4. All configs, raw pgbench outputs, and flamegraphs pushed to repo branch

---

1. Targets Under Test

Target	Port	Language	Auth (client→proxy)	Auth (proxy→PG)	Notes
Direct PG	5432	—	trust (localhost)	—	Baseline
PgBouncer	6432	C	auth_type=any	trust	Single-threaded by design
PgDog	6433	Rust/Tokio	SCRAM (PGPASSWORD=bench)	trust	workers = nproc
SPQR	6434	Go	auth_method=ok	trust	Needs distribution config
Citus	6436	C (Docker)	password (PGPASSWORD=postgres)	—	Coordinator + worker via Docker Compose
Multigres	15432	Go/gRPC	SCRAM (PGPASSWORD=bench)	trust	etcd + pgctld + multipooler + multigateway

Port Map (fixed)

5432   PostgreSQL (direct)
6432   PgBouncer
6433   PgDog
6434   SPQR
7000   SPQR admin console
6436   Citus coordinator (Docker)
6437   Citus worker (Docker)
15432  Multigres (multigateway)
2379   etcd (Multigres)

---

2. Hardware & OS

• VM: AWS EC2 c6id.16xlarge — 64 dedicated AMD EPYC vCPU, 128GB RAM, 2×1.9TB NVMe (instance store)
• Procurement: Spot instance (--instance-market-options MarketType=spot)
• OS: Ubuntu 24.04 LTS
• Region: us-east-1 (or us-east-2 — wherever spot availability is best)
• Estimated cost: ~$0.86/hr spot → ~$3 total for full run

Why bigger VM vs v1 (CCX43, 16 vCPU): PgDog and SPQR were still scaling at c32 in v1. 64 vCPU lets us extend to c64 and observe where multi-threaded proxies actually plateau. Also allows Workload B (complex CTE) without PG CPU becoming the bottleneck.

---

3. PostgreSQL Configuration

-- Performance
ALTER SYSTEM SET shared_buffers = '32GB';
ALTER SYSTEM SET work_mem = '256MB';
ALTER SYSTEM SET maintenance_work_mem = '2GB';
ALTER SYSTEM SET effective_cache_size = '96GB';
ALTER SYSTEM SET max_connections = '300';

-- Benchmark-specific
ALTER SYSTEM SET jit = 'off';
ALTER SYSTEM SET ssl = 'off';
ALTER SYSTEM SET synchronous_commit = 'off';
ALTER SYSTEM SET checkpoint_timeout = '30min';

-- Minimal WAL (no replication)
ALTER SYSTEM SET wal_level = 'minimal';
ALTER SYSTEM SET max_wal_senders = '0';

-- Security
ALTER SYSTEM SET listen_addresses = 'localhost';
ALTER SYSTEM SET log_connections = 'on';
ALTER SYSTEM SET log_disconnections = 'on';

-- Password for SCRAM-requiring proxies
ALTER USER postgres PASSWORD 'bench';

pg_hba.conf — trust for local connections only:

local   all   all                 trust
host    all   all   127.0.0.1/32  trust
host    all   all   ::1/128       trust

pgbench init: pgbench -i -s 100 postgres (~1.6GB, fits comfortably in shared_buffers)

NVMe setup: Mount instance store NVMe for PostgreSQL data directory (lower latency than EBS):

# Format and mount NVMe instance store
mkfs.xfs /dev/nvme1n1
mkdir -p /mnt/nvme
mount /dev/nvme1n1 /mnt/nvme
chown postgres:postgres /mnt/nvme

# Move PG data dir to NVMe
systemctl stop postgresql
rsync -av /var/lib/postgresql/17/main/ /mnt/nvme/main/
# Update postgresql.conf: data_directory = '/mnt/nvme/main'
systemctl start postgresql

---

4. Proxy Configurations

PgBouncer (/etc/pgbouncer/pgbouncer.ini)

[databases]
postgres = host=127.0.0.1 port=5432 user=postgres

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = any
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 200
reserve_pool_size = 20
server_reset_query =

Note: server_reset_query MUST be empty. default_pool_size raised to 200 for c64 runs.

PgDog (/opt/pgdog/pgdog.toml)

[general]
host = "127.0.0.1"
port = 6433
shutdown_timeout = 5000
query_timeout = 30000
checkout_timeout = 5000
connect_timeout = 5000
idle_timeout = 30000
prepared_statements = "disabled"
default_pool_size = 200
min_pool_size = 10
workers = 64  # MUST equal nproc

[admin]
password = "pgdog"

[[databases]]
name = "postgres"
host = "127.0.0.1"
port = 5432
role = "primary"

PgDog users (/opt/pgdog/users.toml):

[[users]]
name = "postgres"
password = "bench"
database = "postgres"
pool_size = 200

SPQR (/opt/spqr-router.yaml)

host: "127.0.0.1"
router_port: "6434"
admin_console_port: "7000"
grpc_api_port: "7001"
router_mode: PROXY
log_level: error
world_shard_fallback: true
memqdb_backup_path: /tmp/spqr_memqdb
frontend_rules:
  - usr: postgres
    db: postgres
    pool_mode: TRANSACTION
    auth_rule:
      auth_method: ok
      password: ""
  - usr: postgres
    db: spqr-console
    pool_mode: TRANSACTION
    auth_rule:
      auth_method: ok
      password: ""
backend_rules:
  - usr: postgres
    db: postgres
    pool_default: true
    connection_limit: 300
    pool_size: 200
shards:
  sh1:
    db: postgres
    usr: postgres
    type: DATA
    tls:
      sslmode: disable
    hosts:
      - "127.0.0.1:5432"

Build from source: Requires Go 1.26+ and CGO:

git clone https://github.com/pg-sharding/spqr.git
cd spqr && make build
cp ./cmd/spqr-router/spqr-router /usr/local/bin/

SPQR distribution setup (required before pgbench will work):

psql -h 127.0.0.1 -p 7000 -U postgres -d spqr-console << 'SQL'
CREATE DISTRIBUTION ds1;
CREATE KEY RANGE kr1 FROM 0 ROUTE TO sh1 FOR DISTRIBUTION ds1;
ALTER DISTRIBUTION ds1 ATTACH RELATION pgbench_accounts DISTRIBUTION KEY aid;
ALTER DISTRIBUTION ds1 ATTACH RELATION pgbench_branches DISTRIBUTION KEY bid;
ALTER DISTRIBUTION ds1 ATTACH RELATION pgbench_tellers DISTRIBUTION KEY tid;
ALTER DISTRIBUTION ds1 ATTACH RELATION pgbench_history DISTRIBUTION KEY tid;
SQL

Note for Workload B: SPQR routes queries based on distribution key. The recursive CTE query uses $1 bound to aid — same key as pgbench_accounts. This should route correctly via sh1. If not, world_shard_fallback: true should catch it.

Citus (/opt/citus/docker-compose.yml)

services:
  citus_coordinator:
    image: citusdata/citus:13
    container_name: citus_coordinator
    environment:
      POSTGRES_PASSWORD: postgres
    depends_on:
      citus_worker:
        condition: service_healthy
    volumes:
      - ./citus-postgresql.conf:/etc/postgresql/postgresql.conf
      - ./citus-init-coordinator.sql:/docker-entrypoint-initdb.d/init-coordinator.sql
    command:
      - postgres
      - "-c"
      - "config_file=/etc/postgresql/postgresql.conf"
    ports:
      - "127.0.0.1:6436:5432"

  citus_worker:
    image: citusdata/citus:13
    container_name: citus_worker
    environment:
      POSTGRES_PASSWORD: postgres
    volumes:
      - ./citus-postgresql.conf:/etc/postgresql/postgresql.conf
      - ./citus-init-worker.sql:/docker-entrypoint-initdb.d/init-worker.sql
    command:
      - postgres
      - "-c"
      - "config_file=/etc/postgresql/postgresql.conf"
    ports:
      - "127.0.0.1:6437:5432"

citus-postgresql.conf:

shared_preload_libraries = 'citus'
max_connections = 1000
citus.shard_count = 1
listen_addresses = '*'
ssl = off
jit = off

citus-init-coordinator.sql:

CREATE EXTENSION IF NOT EXISTS citus;
SELECT master_add_node('citus_worker', 5432);

citus-init-worker.sql:

CREATE EXTENSION IF NOT EXISTS citus;

pgbench init for Citus (separate from direct PG — coordinator port):

PGPASSWORD=postgres pgbench -i -s 100 -h 127.0.0.1 -p 6436 -U postgres postgres
# After init, distribute tables:
PGPASSWORD=postgres psql -h 127.0.0.1 -p 6436 -U postgres -d postgres << 'SQL'
SELECT create_distributed_table('pgbench_accounts', 'aid');
SELECT create_distributed_table('pgbench_tellers', 'tid');
SELECT create_distributed_table('pgbench_branches', 'bid');
SELECT create_distributed_table('pgbench_history', 'tid');
SQL

Note on Citus auth: pgbench must use PGPASSWORD=postgres (not bench) for Citus. It's a separate PG instance inside Docker.

Note on Workload B + Citus: Recursive CTEs with cross-shard joins may not be fully supported by the Citus coordinator. Run Workload B through Citus anyway and document the result (error or success). If it errors, record that as a finding.

Multigres

Dependencies: Go 1.25+, etcd 3.6+

Build:

useradd -m -s /bin/bash multigres
su - multigres -c "
  export PATH=\$PATH:/usr/local/go/bin
  git clone https://github.com/multigres/multigres.git
  cd multigres && git checkout <COMMIT_HASH> && make build
"

Pre-requisites:

CREATE SCHEMA IF NOT EXISTS multigres;
CREATE TABLE IF NOT EXISTS multigres.heartbeat (
  shard_id BYTEA PRIMARY KEY,
  leader_id TEXT NOT NULL,
  ts BIGINT NOT NULL
);

Start sequence (order matters):

# 1. etcd
su - multigres -c "etcd --name default \
  --data-dir /home/multigres/multigres_local/data/etcd-data \
  --listen-client-urls http://127.0.0.1:2379 \
  --advertise-client-urls http://127.0.0.1:2379 \
  --listen-peer-urls http://127.0.0.1:2380 \
  --initial-advertise-peer-urls http://127.0.0.1:2380 \
  --initial-cluster default=http://127.0.0.1:2380 \
  --initial-cluster-state new &"
sleep 3

# 2. Topology (one-time)
/home/multigres/multigres/bin/multigres createclustermetadata \
  --cells zone1 \
  --backup-location /tmp/multigres-backup \
  --global-topo-address 127.0.0.1:2379 \
  --global-topo-root /multigres/global

# 3. pgctld (MUST run as postgres user)
chmod 755 /home/multigres /home/multigres/multigres /home/multigres/multigres/bin
su - postgres -c "
  export PATH=/usr/lib/postgresql/17/bin:\$PATH
  /home/multigres/multigres/bin/pgctld server \
    --pooler-dir /home/multigres/multigres_local/data/pooler_test \
    --pg-port 5432 --grpc-port 15470 --http-port 15400 &"
sleep 2

# 4. multipooler (-grpc-consensus disabled for single-node)
su - multigres -c "
  export PATH=\$PATH:/usr/lib/postgresql/17/bin
  /home/multigres/multigres/bin/multipooler \
    --cell zone1 --database postgres --table-group default \
    --shard '0-inf' --pgctld-addr localhost:15470 --pg-port 5432 \
    --pooler-dir /home/multigres/multigres_local/data/pooler_test \
    --grpc-port 15270 --http-port 15200 \
    --topo-global-server-addresses 127.0.0.1:2379 \
    --topo-global-root /multigres/global \
    --service-map grpc-queryservice,-grpc-consensus &"
sleep 3

# 5. multigateway
su - multigres -c "
  /home/multigres/multigres/bin/multigateway \
    --cell zone1 --pg-port 15432 --grpc-port 15170 --http-port 15100 \
    --topo-global-server-addresses 127.0.0.1:2379 \
    --topo-global-root /multigres/global &"
sleep 2
# pgctld running as postgres detects primary and sets topology automatically

---

5. Workloads

Workload A — Simple SELECT (continuity)

pgbench -S

Query: SELECT abalance FROM pgbench_accounts WHERE aid = $1

• Parameterized index lookup — exercises full Parse/Bind/Execute protocol path
• Ensures query actually reaches PostgreSQL (no proxy short-circuit)
• Scale 100 (~1.6GB), fits in shared_buffers — effectively zero disk I/O
• Rejected alternatives: SELECT 1 (SPQR intercepts it), SELECT; (Multigres bug #6 (closed))

Workload B — Recursive CTE (parser/planner stress)

Designed to stress the proxy's SQL parser with a large, deeply-nested query AST. The query is intentionally complex: recursive traversal, 3 CTEs, window functions (ROW_NUMBER, SUM, AVG with frames), cycle detection, JOIN back to pgbench_branches. Returns a small result set — PostgreSQL executes quickly, proxy overhead dominates.

Save as /root/workload_b.sql:

WITH RECURSIVE account_chain AS (
  -- Base case: start from the given account
  SELECT
    a.aid,
    a.bid,
    a.abalance,
    0                   AS depth,
    ARRAY[a.aid]        AS path,
    a.abalance          AS chain_balance
  FROM pgbench_accounts a
  WHERE a.aid = :aid

  UNION ALL

  -- Recursive case: walk to adjacent accounts (deterministic traversal)
  SELECT
    a.aid,
    a.bid,
    a.abalance,
    ac.depth + 1,
    ac.path || a.aid,
    ac.chain_balance + a.abalance
  FROM pgbench_accounts a
  JOIN account_chain ac
    ON a.aid = ((ac.aid * 1103515245 + 12345) & 2147483647) % 10000000 + 1
  WHERE
    ac.depth < 8
    AND NOT (a.aid = ANY(ac.path))
    AND a.abalance IS NOT NULL
),

ranked AS (
  SELECT
    ac.*,
    ROW_NUMBER() OVER (
      PARTITION BY ac.depth
      ORDER BY ac.abalance DESC NULLS LAST
    )                                                          AS rn_in_depth,
    SUM(ac.abalance) OVER (
      PARTITION BY ac.depth
    )                                                          AS depth_total_balance,
    AVG(ac.abalance) OVER (
      ORDER BY ac.depth, ac.aid
      ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
    )                                                          AS rolling_avg_balance,
    COUNT(*) OVER (
      PARTITION BY ac.depth
    )                                                          AS depth_count,
    MAX(ac.chain_balance) OVER ()                              AS max_chain_balance,
    MIN(ac.abalance) OVER (PARTITION BY ac.bid)                AS min_balance_in_branch
  FROM account_chain ac
),

branch_stats AS (
  SELECT
    b.bid,
    b.bbalance,
    COUNT(r.aid)                                               AS chain_members,
    MAX(r.abalance)                                            AS max_account_balance,
    MIN(r.abalance)                                            AS min_account_balance,
    AVG(r.abalance)                                            AS avg_account_balance,
    SUM(r.abalance)                                            AS total_account_balance,
    MAX(r.depth)                                               AS max_depth_reached
  FROM ranked r
  JOIN pgbench_branches b ON r.bid = b.bid
  GROUP BY b.bid, b.bbalance
)

SELECT
  r.aid,
  r.bid,
  r.depth,
  r.abalance,
  r.chain_balance,
  r.rn_in_depth,
  r.depth_total_balance,
  r.rolling_avg_balance,
  r.depth_count,
  r.max_chain_balance,
  r.min_balance_in_branch,
  bs.bbalance                                                  AS branch_balance,
  bs.chain_members,
  bs.max_account_balance,
  bs.min_account_balance,
  bs.avg_account_balance,
  bs.total_account_balance,
  bs.max_depth_reached,
  r.path
FROM ranked r
JOIN branch_stats bs ON r.bid = bs.bid
WHERE r.rn_in_depth <= 3
ORDER BY r.depth ASC, r.rn_in_depth ASC
;

pgbench custom script (/root/workload_b_pgbench.sql):

\set aid random(1, 10000000)
SELECT
  r.aid, r.bid, r.depth, r.abalance, r.rn_in_depth,
  r.depth_total_balance, r.rolling_avg_balance,
  bs.chain_members, bs.avg_account_balance, r.path
FROM (
  WITH RECURSIVE account_chain AS (
    SELECT a.aid, a.bid, a.abalance, 0 AS depth, ARRAY[a.aid] AS path,
           a.abalance AS chain_balance
    FROM pgbench_accounts a WHERE a.aid = :aid
    UNION ALL
    SELECT a.aid, a.bid, a.abalance, ac.depth + 1, ac.path || a.aid,
           ac.chain_balance + a.abalance
    FROM pgbench_accounts a
    JOIN account_chain ac
      ON a.aid = ((ac.aid * 1103515245 + 12345) & 2147483647) % 10000000 + 1
    WHERE ac.depth < 8 AND NOT (a.aid = ANY(ac.path)) AND a.abalance IS NOT NULL
  ),
  ranked AS (
    SELECT ac.*,
           ROW_NUMBER() OVER (PARTITION BY ac.depth ORDER BY ac.abalance DESC NULLS LAST) AS rn_in_depth,
           SUM(ac.abalance) OVER (PARTITION BY ac.depth) AS depth_total_balance,
           AVG(ac.abalance) OVER (ORDER BY ac.depth, ac.aid ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS rolling_avg_balance,
           COUNT(*) OVER (PARTITION BY ac.depth) AS depth_count,
           MAX(ac.chain_balance) OVER () AS max_chain_balance,
           MIN(ac.abalance) OVER (PARTITION BY ac.bid) AS min_balance_in_branch
    FROM account_chain ac
  ),
  branch_stats AS (
    SELECT b.bid, b.bbalance,
           COUNT(r.aid) AS chain_members,
           MAX(r.abalance) AS max_account_balance,
           MIN(r.abalance) AS min_account_balance,
           AVG(r.abalance) AS avg_account_balance,
           SUM(r.abalance) AS total_account_balance,
           MAX(r.depth) AS max_depth_reached
    FROM ranked r JOIN pgbench_branches b ON r.bid = b.bid
    GROUP BY b.bid, b.bbalance
  )
  SELECT r.aid, r.bid, r.depth, r.abalance, r.rn_in_depth,
         r.depth_total_balance, r.rolling_avg_balance, r.path,
         bs.chain_members, bs.avg_account_balance
  FROM ranked r JOIN branch_stats bs ON r.bid = bs.bid
  WHERE r.rn_in_depth <= 3
  ORDER BY r.depth, r.rn_in_depth
) r
JOIN pgbench_branches bs ON r.bid = bs.bid
;

Design rationale:

• ~80 lines, 3 nested CTEs, recursive with cycle detection (ARRAY path)
• 6 window functions (ROW_NUMBER, SUM, AVG with frame, COUNT, MAX, MIN)
• JOIN back to pgbench_branches inside recursive CTE + outer JOIN
• Returns small result set (≤27 rows at depth 8) — PostgreSQL work is trivial
• Every proxy must fully parse the query AST before routing — no shortcutting possible
• \set aid random(1, 10000000) — different aid per invocation, prevents caching

---

6. Fairness Checklist (VERIFY BEFORE RUNNING)

echo "=== 1. SSL must be OFF ==="
sudo -u postgres psql -c "SHOW ssl"

echo "=== 2. PgBouncer: no reset query ==="
grep server_reset_query /etc/pgbouncer/pgbouncer.ini

echo "=== 3. PgDog workers = nproc ==="
grep workers /opt/pgdog/pgdog.toml && echo "nproc = $(nproc)"

echo "=== 4. All services responding ==="
psql -h 127.0.0.1 -p 5432 -U postgres -c "SELECT 'direct_ok'"
psql -h 127.0.0.1 -p 6432 -U postgres -c "SELECT 'pgbouncer_ok'"
PGPASSWORD=bench psql -h 127.0.0.1 -p 6433 -U postgres -c "SELECT 'pgdog_ok'"
psql -h 127.0.0.1 -p 6434 -U postgres -c "SELECT 'spqr_ok'"
PGPASSWORD=postgres psql -h 127.0.0.1 -p 6436 -U postgres -c "SELECT 'citus_ok'"
PGPASSWORD=bench psql -h 127.0.0.1 -p 15432 -U postgres -c "SELECT 'multigres_ok'"

echo "=== 5. pg_stat_ssl (all direct connections should be ssl=f) ==="
sudo -u postgres psql -c "SELECT application_name, ssl, count(*) FROM pg_stat_ssl JOIN pg_stat_activity USING (pid) WHERE pid != pg_backend_pid() GROUP BY 1,2 ORDER BY 1;"

echo "=== 6. Workload B sanity check (direct PG, should return rows) ==="
psql -h 127.0.0.1 -p 5432 -U postgres -v aid=1 -f /root/workload_b.sql | head -5

---

7. Benchmark Execution

Concurrency Levels

8 equidistant levels: c4, c12, c20, c28, c36, c44, c52, c64

Rationale: extends v1's c4–c32 range to c64. Previous results showed PgDog/SPQR still scaling at c32 — need to find the ceiling.

Per-test procedure

DURATION=60
WARMUP=10

# Workload A
run_test_a() {
    local T=$1 P=$2 C=$3 E=${4:-""}
    eval $E pgbench -h 127.0.0.1 -p $P -U postgres -c $C -j $C -T $WARMUP -S postgres >/dev/null 2>&1 || true
    eval $E pgbench -h 127.0.0.1 -p $P -U postgres -c $C -j $C -T $DURATION -S postgres \
        > /root/results/A_${T}_c${C}.txt 2>&1 || true
}

# Workload B
run_test_b() {
    local T=$1 P=$2 C=$3 E=${4:-""}
    eval $E pgbench -h 127.0.0.1 -p $P -U postgres -c $C -j $C -T $WARMUP \
        -f /root/workload_b_pgbench.sql postgres >/dev/null 2>&1 || true
    eval $E pgbench -h 127.0.0.1 -p $P -U postgres -c $C -j $C -T $DURATION \
        -f /root/workload_b_pgbench.sql postgres \
        > /root/results/B_${T}_c${C}.txt 2>&1 || true
}

Full benchmark script

#!/bin/bash
set -uo pipefail
DURATION=60; WARMUP=10
OUTDIR=/root/results; mkdir -p $OUTDIR
CLIENTS="4 12 20 28 36 44 52 64"

run_test() {
    local WL=$1 T=$2 P=$3 C=$4 E=${5:-""}
    local WFLAG=""
    [ "$WL" = "B" ] && WFLAG="-f /root/workload_b_pgbench.sql" || WFLAG="-S"
    echo -n "${WL}_${T}_c${C}... "
    eval $E pgbench -h 127.0.0.1 -p $P -U postgres -c $C -j $C -T $WARMUP $WFLAG postgres \
        >/dev/null 2>&1 || true
    eval $E pgbench -h 127.0.0.1 -p $P -U postgres -c $C -j $C -T $DURATION $WFLAG postgres \
        > $OUTDIR/${WL}_${T}_c${C}.txt 2>&1 || true
    local TPS=$(grep "^tps" $OUTDIR/${WL}_${T}_c${C}.txt | head -1 | awk '{print $3}')
    local LAT=$(grep "^latency average" $OUTDIR/${WL}_${T}_c${C}.txt | awk '{print $4}')
    local STD=$(grep "^latency stddev" $OUTDIR/${WL}_${T}_c${C}.txt | awk '{print $4}')
    local FAIL=$(grep "^number of failed" $OUTDIR/${WL}_${T}_c${C}.txt | grep -oP '\d+(?= \()' || echo 0)
    echo "${WL},${T},${C},${TPS},${LAT},${STD},${FAIL}" >> $OUTDIR/results.csv
    echo "TPS=${TPS} lat=${LAT}ms fail=${FAIL}"
}

echo "workload,target,clients,tps,lat_avg_ms,lat_stddev_ms,failed" > $OUTDIR/results.csv

echo "=== WORKLOAD A: Simple SELECT ==="
for C in $CLIENTS; do run_test A direct    5432  $C ""; done
for C in $CLIENTS; do run_test A pgbouncer 6432  $C ""; done
for C in $CLIENTS; do run_test A pgdog     6433  $C "PGPASSWORD=bench"; done
for C in $CLIENTS; do run_test A spqr      6434  $C ""; done
for C in $CLIENTS; do run_test A citus     6436  $C "PGPASSWORD=postgres"; done
for C in $CLIENTS; do run_test A multigres 15432 $C "PGPASSWORD=bench"; done

echo "=== WORKLOAD B: Recursive CTE ==="
for C in $CLIENTS; do run_test B direct    5432  $C ""; done
for C in $CLIENTS; do run_test B pgbouncer 6432  $C ""; done
for C in $CLIENTS; do run_test B pgdog     6433  $C "PGPASSWORD=bench"; done
for C in $CLIENTS; do run_test B spqr      6434  $C ""; done
for C in $CLIENTS; do run_test B citus     6436  $C "PGPASSWORD=postgres"; done
for C in $CLIENTS; do run_test B multigres 15432 $C "PGPASSWORD=bench"; done

echo "=== DONE ==="
cat $OUTDIR/results.csv

Total: 6 targets × 8 concurrency levels × 2 workloads = 96 tests × ~70s each ≈ ~112 minutes (~2 hours)

Thread count

-j $C (threads = clients). pgbench caps threads at nproc (64) automatically.

Note on set -e

Do NOT use set -e — use set -uo pipefail with || true on pgbench/grep/awk commands.

---

8. Flamegraphs

Collect perf flamegraphs at c16 (representative, comparable to v1) for all proxies, both workloads.

apt-get install -y linux-tools-$(uname -r) linux-tools-common
git clone --depth=1 https://github.com/brendangregg/FlameGraph.git /root/FlameGraph

collect_flamegraph() {
    local NAME=$1 PID=$2 WL=$3
    perf record -F 99 -g -p $PID -o /tmp/${NAME}_${WL}.perf.data -- sleep 15
    perf script -i /tmp/${NAME}_${WL}.perf.data \
        | /root/FlameGraph/stackcollapse-perf.pl \
        | /root/FlameGraph/flamegraph.pl --title "${NAME} (Workload ${WL})" \
        > /root/flamegraphs/${NAME}_${WL}.svg
    # PNG for inline display
    rsvg-convert -w 1600 /root/flamegraphs/${NAME}_${WL}.svg \
        > /root/flamegraphs/${NAME}_${WL}.png 2>/dev/null || true
}

mkdir -p /root/flamegraphs

# During a steady-state c16 pgbench run for each proxy:
collect_flamegraph pgbouncer   $(pgrep pgbouncer) A
collect_flamegraph pgbouncer   $(pgrep pgbouncer) B
collect_flamegraph pgdog        $(pgrep pgdog)     A
collect_flamegraph pgdog        $(pgrep pgdog)     B
collect_flamegraph spqr         $(pgrep spqr-router) A
collect_flamegraph spqr         $(pgrep spqr-router) B
collect_flamegraph multipooler  $(pgrep multipooler) A
collect_flamegraph multipooler  $(pgrep multipooler) B
collect_flamegraph multigateway $(pgrep multigateway) A
collect_flamegraph multigateway $(pgrep multigateway) B
# Citus runs in Docker — use nsenter or docker exec + perf inside container
collect_flamegraph citus        $(docker inspect --format '{{.State.Pid}}' citus_coordinator) A
collect_flamegraph citus        $(docker inspect --format '{{.State.Pid}}' citus_coordinator) B

Key things to look for vs v1:

• PgBouncer: does SSL still dominate? (should be gone with ssl=off)
• PgDog: does kernel I/O pattern change under Workload B (bigger send buffers)?
• SPQR: does routing pipeline CPU increase with query complexity?
• Multigres: does protobuf GC % increase with larger query payload?
• Citus: where does the coordinator spend time when parsing a recursive CTE?

---

9. Results Format

CSV columns

workload,target,clients,tps,lat_avg_ms,lat_stddev_ms,failed

For issue/report — wide table format

One table per workload, concurrency as columns, targets as rows.

Graphs (per workload)

1. TPS vs concurrency (line chart, color = target)
2. Latency overhead vs direct PG (absolute ms added, color = target)
3. Workload A vs B comparison: latency ratio (B/A) per proxy — shows relative parser sensitivity

---

10. VM Lifecycle

Provision (AWS Spot)

export $(cat ~/.env.aws-benchmark | grep -v '^#' | xargs)

# Create key pair
aws ec2 create-key-pair --key-name bench-key --query 'KeyMaterial' \
    --output text > ~/.ssh/bench.pem && chmod 600 ~/.ssh/bench.pem

# Create security group (SSH only)
SG=$(aws ec2 create-security-group \
    --group-name bench-sg-$(date +%s) \
    --description "Benchmark SSH only" \
    --query 'GroupId' --output text)
aws ec2 authorize-security-group-ingress \
    --group-id $SG --protocol tcp --port 22 \
    --cidr 0.0.0.0/0

# Launch spot instance (c6id.16xlarge, us-east-1a)
INSTANCE=$(aws ec2 run-instances \
    --image-id ami-0c7217cdde317cfec \
    --instance-type c6id.16xlarge \
    --key-name bench-key \
    --security-group-ids $SG \
    --instance-market-options 'MarketType=spot' \
    --block-device-mappings 'DeviceName=/dev/sda1,Ebs={VolumeSize=50}' \
    --query 'Instances[0].InstanceId' --output text)

echo "Instance: $INSTANCE"
aws ec2 wait instance-running --instance-ids $INSTANCE
IP=$(aws ec2 describe-instances --instance-ids $INSTANCE \
    --query 'Reservations[0].Instances[0].PublicIpAddress' --output text)
echo "IP: $IP"

Pull results before destroying

scp -i ~/.ssh/bench.pem -r ubuntu@$IP:/root/results/ ./results/
scp -i ~/.ssh/bench.pem -r ubuntu@$IP:/root/flamegraphs/ ./flamegraphs/

Destroy (IMMEDIATELY after pulling results)

aws ec2 terminate-instances --instance-ids $INSTANCE
aws ec2 delete-security-group --group-id $SG
aws ec2 delete-key-pair --key-name bench-key
rm ~/.ssh/bench.pem
echo "VM destroyed."

---

11. GitLab Issue Format

Repo: postgres-ai/postgresql-consulting/tests-and-benchmarks Branch: proxy-latency-benchmark-mar-2026

Issue structure:

1. Environment table (hardware, versions)
2. Workload A Results — TPS + latency tables per concurrency
3. Workload B Results — TPS + latency tables per concurrency
4. Workload A vs B comparison — latency ratio per proxy (parser sensitivity)
5. Key observations — scaling behavior, bottlenecks, what changed vs Feb 2026
6. Flamegraph analysis — what's eating CPU, A vs B comparison per proxy
7. Flamegraph images — annotated PNGs inline, SVG download links
8. Raw data — CSV, link to branch

---

12. Known Gotchas (inherited from v1 + new)

From v1 (all still apply)

• SPQR requires distribution config — CREATE DISTRIBUTION + ATTACH RELATION or every query errors
• Multigres needs manual heartbeat table — multigres.heartbeat must exist before start
• Multigres -grpc-consensus must be disabled for single-node (--service-map grpc-queryservice,-grpc-consensus)
• Multigres SCRAM auth — every pgbench invocation needs PGPASSWORD=bench
• PgBouncer auth_type=trust broken in 1.25 — use auth_type=any
• PgDog workers default is low — always set workers = nproc (64 here)
• SSL on by default in Ubuntu PG packages — ALTER SYSTEM SET ssl = 'off'
• PgBouncer server_reset_query default — must be empty string
• Multigres breaks on SELECT; — use pgbench -S or explicit SELECT 1
• Multigres stale topology on restart — always clean etcd: etcdctl del /multigres/ --prefix
• Multigres pgctld must run as postgres user — otherwise pool dies every 5s

New in v2

• AWS NVMe instance store is ephemeral — pull results before stopping/terminating the instance
• Spot interruption — add --instance-interruption-behavior terminate and watch for interruption notice at 169.254.169.254/latest/meta-data/spot/termination-time
• Citus needs separate pgbench init — coordinator port (6436), not direct PG port
• Citus Workload B may error — recursive CTEs with cross-table joins may not be supported by Citus coordinator in single-shard mode; document result either way
• Pool sizes need increasing — c64 runs need default_pool_size ≥ 200 on all proxies; v1 used 100 which would cause connection queuing at c64
• SPQR Workload B routing — recursive CTE uses :aid parameter; verify world_shard_fallback: true handles it; if not, add explicit distribution for the subquery

---

13. Security — NON-NEGOTIABLE

1. AWS Security Group (SSH-only port 22) created BEFORE launching instance.
2. listen_addresses = 'localhost' — PG never binds to 0.0.0.0.
3. Citus Docker ports bound to 127.0.0.1 only — 127.0.0.1:6436:5432 not 6436:5432.
4. log_connections = on — always.
5. Destroy instance IMMEDIATELY after pulling results.
6. Do not reuse key pairs — create fresh, delete after.

We had a VM compromised in Feb 2026 because we skipped the firewall on a "temporary" box. See RCA_COMPROMISE.md. Never again.

---

14. Changelog

v1.0 — Feb 18, 2026 (Round 1 & 2, Hetzner CCX43)

• Initial spec. 3 concurrency levels (c4/c16/c64), expanded to 8 (c4–c32).
• Fairness fixes: SSL off, PgDog workers=nproc, PgBouncer server_reset_query empty.
• Single workload: pgbench -S.
• Targets: Direct PG, PgBouncer, PgDog, SPQR, Multigres.

v1.1 — Feb 18, 2026 (Round 3, Hetzner CCX43)

• Full single-pass run: all 5 proxies in one script.
• Topology setup: use multigres createclustermetadata binary.

v2.0 — Mar 2026 (AWS c6id.16xlarge, 64 vCPU)

• New hardware: AWS EC2 c6id.16xlarge Spot (64 vCPU, 128GB, 2×1.9TB NVMe). Spot ~$0.86/hr.
• Extended concurrency: c4–c64 (8 levels: 4, 12, 20, 28, 36, 44, 52, 64). PgDog/SPQR were still climbing at c32 in v1.
• New target: Citus — re-added (was in issue #63, dropped in v1). Docker Compose, port 6436.
• New workload: Workload B — recursive CTE, ~80 lines, 3 CTEs, 6 window functions. Stresses proxy SQL parser. Same pgbench harness, custom script file.
• Flamegraphs per workload — collected for both A and B, all proxies.
• Pool sizes increased to 200 for c64 headroom.
• NVMe instance store — PostgreSQL data dir on local NVMe for lowest I/O latency.

---

PostgresAI, Mar 2026