Performance

This page collects the performance measurements recorded during QueryPilot development, with the date and test conditions stated next to every number.

Proxy overhead

Design targets for the Router (binary querypilot): under 25% overhead at p50 and under 50% at p99 for simple queries versus a direct connection, under 10% throughput loss on large results, and more than 5x speedup from connection pooling versus unpooled clients.

Measured comparison against direct PostgreSQL and PgBouncer (recorded 2026-02-22; 10 iterations per query via psql, local PostgreSQL 16, transaction pooling mode on both proxies):

Readings from this run:

• The Router tracks PgBouncer across all query sizes. On the 100K-row result, where per-connection savings stop dominating, the Router measured +5.3% over direct versus PgBouncer's -3%.
• The Router was faster than PgBouncer on the 10K-row case (-28% versus -14%) in this run.

Why pooled results beat direct connections

The negative overhead on small and medium queries is not a measurement error. In this test each psql iteration opens a fresh connection, so the direct-PostgreSQL column pays connection establishment on every query. Both proxies hold pooled backend connections, so a client "connection" costs a pool checkout instead of a PostgreSQL backend fork and handshake. Any workload that opens connections frequently (serverless functions, short-lived scripts, per-request connections) sees the same effect; a long-lived application connection pool sees the proxy's true per-statement overhead instead, which is where the internal latency budget below matters.

Internal latency budget

Per-statement costs inside the Router, from the internal microbenchmark baseline recorded 2026-01-18 on local development hardware:

Everything is nanoseconds against query times measured in hundreds of microseconds or milliseconds; the routing engine is not where your latency goes. One asymmetry worth knowing: under read/write splitting, read routing (~177 ns) costs more than write routing (~38 ns) because a SELECT must be scanned for FOR UPDATE/FOR SHARE before it can be declared a read. That is by design; see the read/write splitting tutorial.

Plugins add cost only when enabled: with no plugin blocks configured, the plugin chain is empty and adds zero per-query overhead. Guard evaluates under an explicit per-decision budget (max_decision_latency_ms, default 50 ms) and fails open for reads when it is exceeded; see the Guard guide.

Large results and streaming

Result-set handling scales linearly with rows and columns. Internal streaming benchmarks (recorded 2026-01-18; mock backend, measuring result construction without network I/O) put the proxy-side cost at:

No superlinear pathologies: 100x the rows costs roughly 100x the time, and 4x the columns costs roughly 3x. End to end, the 100K-row case in the 2026-02-22 comparison above measured +5.3% over a direct connection.

For pools fronting a vanilla PostgreSQL backend, raw_passthrough removes the decode and re-encode step for prepared-statement results entirely, streaming backend bytes to the client unchanged. It must stay off for cross-protocol pools and pools fronting Analytics, where rows genuinely need re-encoding. See the Router guide.

Analytics routing speedups

Routing analytical queries to Analytics (binary querypilot-analytics) trades a wire hop for a columnar execution engine. Internal comparisons of a production MySQL workload whose queries were translated to DuckDB recorded 10-20x speedups on JSON_OVERLAPS-heavy queries rewritten to DuckDB's list_has_any. These numbers are strongly workload-dependent: the win comes from aggregation-heavy, scan-heavy query shapes on replicated tables, and an indexed point lookup on the source database will not get faster by moving. Route the shapes that benefit and keep the rest where they are; the HTAP tutorial shows the pattern-rule setup, and GET /api/stats/digests on the admin API shows per-shape execution statistics to decide with.

Analytics snapshot throughput

Initial snapshot speed determines how long a fresh Analytics takes to come online. From the snapshot benchmarking series recorded in 2026 on Azure AKS, Standard_E4s_v6 (4 vCPU, 32 GB RAM), Premium SSD LRS storage, snapshotting a 1M-row, 68-column PostgreSQL table over COPY BINARY:

Two operational rules fall out of the same benchmark matrix:

• Never set more Parquet writers than vCPUs. On the same 4-vCPU host, 16 readers / 8 writers collapsed throughput to 34,773 rows/sec, worse than half the 4-writer optimum. The worker auto-detect defaults respect this cap.
• These figures are at 1M-row scale; burst-credit storage classes (like Premium SSD LRS) taper toward their sustained baseline on much larger snapshots.

The appender default wins on small tables through lower startup cost; the Parquet strategies win on large tables and on storage with slow fsync, at the cost of scratch disk. Strategy selection and tuning live in the Analytics guide and the analytics configuration reference.

Measuring your own deployment

Reproduce the overhead comparison against your own database in a few minutes:

# Direct to PostgreSQL
psql "host=db.internal port=5432 dbname=mydb" \
  -c '\timing on' -c "SELECT * FROM orders LIMIT 10000" | tail -1

# Through the Router
psql "host=proxy.internal port=5433 dbname=mydb" \
  -c '\timing on' -c "SELECT * FROM orders LIMIT 10000" | tail -1

Run each query several times and compare medians, not single samples; first runs pay cache warming on both paths. Test the query shapes and result sizes your application actually produces, and test with your application's real connection behavior (pooled versus per-request) since that decides whether pooling savings apply to you.

Under sustained load, the Prometheus metrics separate proxy cost from backend cost:

• sqp_query_duration_seconds is end-to-end query latency through the Router, labelled by pool.
• sqp_pool_acquire_duration_seconds is time spent waiting for a backend connection; if this grows, raise pool limits before blaming query paths (see Operations).
• sqp_routing_decisions_total and sqp_routing_cache_operations_total confirm where statements went and whether decision caching is hitting.
• GET /api/stats/digests on the admin API gives per-query-shape latency statistics without external tooling.

The full series list is in the metrics reference.