## Latency Overhead Benchmark — Feb 2026 **pgBouncer vs PgDog vs SPQR vs Multigres vs bare Postgres** Follow-up to #63 (May–Jul 2025 benchmark by @Sarumyan9999). This round adds **Multigres** and re-tests all proxies on identical hardware with identical methodology. --- ## Environment | Component | Version / Details | |---|---| | **Hardware** | Hetzner CCX43 — 16 dedicated AMD EPYC vCPU, 64GB RAM, NVMe, Ashburn DC | | **OS** | Ubuntu 24.04 LTS, kernel 6.8.0-90-generic | | **PostgreSQL** | 17.8 (apt.postgresql.org) | | **PgBouncer** | 1.25.1 (apt.postgresql.org) | | **PgDog** | `main` branch, Docker `ghcr.io/pgdogdev/pgdog:main` | | **SPQR** | `master` branch, built from source (Go 1.26.0) | | **Multigres** | `main` branch, commit `66af929`, built from source (Go 1.25.1) | | **pgbench** | 17.8, scale 100 (~1.6GB), SELECT-only (`-S`) | All components on localhost — no network overhead. Pure proxy CPU overhead measurement. --- ## PostgreSQL Configuration ```sql listen_addresses = 'localhost' shared_buffers = '8GB' work_mem = '256MB' effective_cache_size = '48GB' max_connections = 200 jit = off wal_level = minimal max_wal_senders = 0 synchronous_commit = off ``` ## PgBouncer Configuration ```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 pool_mode = transaction max_client_conn = 1000 default_pool_size = 100 reserve_pool_size = 10 server_reset_query = DISCARD ALL ``` ## PgDog Configuration ```toml [general] host = "127.0.0.1" port = 6433 default_pool_size = 100 min_pool_size = 10 workers = 4 prepared_statements = "disabled" [[databases]] name = "postgres" host = "127.0.0.1" port = 5432 role = "primary" ``` ```toml # users.toml [[users]] name = "postgres" password = "bench" database = "postgres" pool_size = 100 ``` ## SPQR Configuration ```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 frontend_rules: - usr: postgres db: postgres pool_mode: TRANSACTION auth_rule: auth_method: ok backend_rules: - usr: postgres db: postgres pool_default: true connection_limit: 200 pool_size: 100 shards: sh1: db: postgres usr: postgres type: DATA tls: sslmode: disable hosts: - "127.0.0.1:5432" ``` SPQR requires distribution configuration via admin console: ```sql -- Connect to spqr-console on port 7000 CREATE DISTRIBUTION ds1 COLUMN TYPES integer; CREATE KEY RANGE kr1 FROM 0 ROUTE TO sh1 FOR DISTRIBUTION ds1; 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_accounts DISTRIBUTION KEY aid; ALTER DISTRIBUTION ds1 ATTACH RELATION pgbench_history DISTRIBUTION KEY tid; ``` ## Multigres Setup Multigres requires etcd + topology setup + 3 services (pgctld, multipooler, multigateway): ```bash # 1. etcd etcd --name default \ --data-dir /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 # 2. Topology (Go SDK — create cell, database, shard) # Must use BackupLocation protobuf struct (not a string) # 3. pgctld pgctld server --pooler-dir $PD --pg-port 5432 \ --grpc-port 15470 --http-port 15400 # 4. multipooler (NOTE: must disable grpc-consensus for single-node) multipooler --cell zone1 --database postgres \ --table-group default --shard 0-inf \ --pgctld-addr localhost:15470 --pg-port 5432 \ --pooler-dir $PD \ --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 # 5. multigateway 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 # 6. Set primary via Go SDK (gRPC SetPoolerType call) # 7. Pre-create heartbeat table: 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 ); ``` ## Benchmark Command ```bash # Warmup (10s, discarded) pgbench -h 127.0.0.1 -p $PORT -U postgres -c $CLIENTS -j $CLIENTS -T 10 -S postgres # Measured run (60s) pgbench -h 127.0.0.1 -p $PORT -U postgres -c $CLIENTS -j $CLIENTS -T 60 -S --progress=30 postgres ``` Ports: Direct PG=5432, PgBouncer=6432, PgDog=6433, SPQR=6434, Multigres=15432 --- ## Results All tests: **0 failed transactions**. All connections via TCP loopback (`-h 127.0.0.1`). Auth: PgBouncer and SPQR use `auth_type=any`/`auth_method=ok` (no password). PgDog and Multigres use SCRAM-SHA-256. This affects initial connection time only — not steady-state TPS (pgbench excludes initial connection time). ### 4 clients | Target | Lang | TPS | Avg Latency | Stddev | Overhead | |---|---|---|---|---|---| | **Direct PG** | C | 66,252 | 0.060 ms | 0.012 ms | — | | **PgBouncer** | C | 37,453 | 0.107 ms | 0.019 ms | +0.047 ms (+78%) | | **PgDog** | Rust | 30,828 | 0.130 ms | 0.026 ms | +0.070 ms (+117%) | | **SPQR** | Go | 25,105 | 0.159 ms | 0.056 ms | +0.099 ms (+165%) | | **Multigres** | Go | 8,261 | 0.484 ms | 0.151 ms | +0.424 ms (+707%) | ### 16 clients | Target | Lang | TPS | Avg Latency | Stddev | Overhead | |---|---|---|---|---|---| | **Direct PG** | C | 266,410 | 0.060 ms | 0.006 ms | — | | **PgBouncer** | C | 36,416 | 0.439 ms | 0.094 ms | +0.379 ms | | **PgDog** | Rust | 76,850 | 0.208 ms | 0.038 ms | +0.148 ms | | **SPQR** | Go | 47,969 | 0.333 ms | 0.147 ms | +0.273 ms | | **Multigres** | Go | 17,853 | 0.896 ms | 0.303 ms | +0.836 ms | ### 64 clients | Target | Lang | TPS | Avg Latency | Stddev | Overhead | |---|---|---|---|---|---| | **Direct PG** | C | 241,199 | 0.265 ms | 0.137 ms | — | | **PgBouncer** | C | 35,879 | 1.784 ms | 0.317 ms | +1.519 ms | | **PgDog** | Rust | 76,789 | 0.833 ms | 0.187 ms | +0.568 ms | | **SPQR** | Go | 80,247 | 0.797 ms | 0.678 ms | +0.532 ms | | **Multigres** | Go | 25,979 | 2.463 ms | 0.836 ms | +2.198 ms | ### Scaling summary | Target | c4 TPS | c16 TPS | c64 TPS | Scaling pattern | |---|---|---|---|---| | **Direct PG** | 66,252 | 266,410 | 241,199 | peaks at c16, contention at c64 | | **PgBouncer** | 37,453 | 36,416 | 35,879 | flat — single-threaded ceiling | | **PgDog** | 30,828 | 76,850 | 76,789 | scales to c16, then plateaus | | **SPQR** | 25,105 | 47,969 | 80,247 | linear growth to c64 | | **Multigres** | 8,261 | 17,853 | 25,979 | scales but 3-8x slower than others | --- ## Key Observations 1. **PgBouncer** is single-threaded — TPS stays flat at ~36-37k regardless of client count. Lowest latency overhead at c4 (+0.047ms), but doesn't scale. 2. **PgDog** (Rust) scales well from c4→c16 (2.5x TPS) then plateaus. At c16+, it's **2x faster than PgBouncer** in TPS. 3. **SPQR** (Go) scales linearly all the way to c64 — highest absolute TPS of any proxy at c64 (80k). But at c4, it's the slowest of the "fast" proxies (+0.099ms overhead). 4. **Multigres** (Go) is 3-8x slower than all other proxies at every concurrency level. 0.484ms overhead at c4 vs 0.047ms for PgBouncer. The architecture (etcd + pgctld + multipooler + multigateway) adds significant latency. 5. **Multi-threaded proxies win at scale** — PgDog and SPQR both overtake PgBouncer at c16+. This matches the Jul 2025 findings in #63. ### Comparison with #63 results (Jul 2025, Hetzner 16 vCPU AMD EPYC) | Proxy | Jul 2025 latency | Feb 2026 latency | Notes | |---|---|---|---| | PgBouncer | 0.573 ms | 0.107 ms | Different test params | | PgDog | 0.626 ms | 0.130 ms | Newer version | | SPQR | 1.222 ms | 0.159 ms | Newer version | | Multigres | N/A | 0.484 ms | New addition | *Note: #63 used different pgbench parameters and possibly different PG config. Latency numbers aren't directly comparable — only relative ordering matters.* --- ## Multigres-Specific Bugs During setup and benchmarking, several Multigres bugs were found: ### 1. `grpc-consensus` blocks single-node startup (HIGH) Without `--service-map grpc-queryservice,-grpc-consensus`, multipooler hangs forever waiting for consensus peers. No error message, no docs. ### 2. SCRAM-SHA-256 auth required but undocumented (MEDIUM) Commit #370 enforced SCRAM auth. The setup guide doesn't mention `ALTER USER postgres PASSWORD '...'` is required. ### 3. Heartbeat table not auto-created (MEDIUM) `multigres.heartbeat` table must be manually created when not using multiorch. No error mentions this — connections just fail. ### 4. BackupLocation API breaking change (LOW) Topology API now requires a protobuf struct for BackupLocation instead of a simple string. Old examples/docs broken. ### 5. MonitorPostgres pool closure bug (observed in earlier runs) Under sustained load (c16+), multipooler's `MonitorPostgres` enters a restart loop trying to `pg_ctl start` a healthy PostgreSQL. This closes the connection pool. In this benchmark run, Multigres completed all tests without this bug manifesting — it may be a race condition. --- ## Flamegraphs Flamegraphs collected with `perf record -F 99 -g -- sleep 15` during c4 SELECT-only load. *(Flamegraphs attached as comments below)* --- ## Reproduction Full reproduction requires: 1. Hetzner CCX43 (or equivalent 16-vCPU dedicated) 2. Ubuntu 24.04 3. PostgreSQL 17.x from apt.postgresql.org 4. PgBouncer 1.25.x from apt.postgresql.org 5. PgDog from `ghcr.io/pgdogdev/pgdog:main` (Docker) 6. SPQR built from source (`github.com/pg-sharding/spqr`, requires CGO) 7. Multigres built from source (`github.com/multigres/multigres`, commit `66af929`) 8. etcd 3.6.x for Multigres topology All configs listed above. All tests run on localhost. 10s warmup + 60s measured, SELECT-only.