QueryPilot Optional Configurations

Provided artifacts and short recipes that do not belong to a single component page. The Router is the SQL proxy (binary querypilot). Everything here assumes the metrics/admin API is on ([metrics] enabled = true), which serves Prometheus on 9090 and the admin API on 9091 by default.

Grafana dashboard

A ready-made Router overview dashboard (JSON) is available from your Readyset contact. Panels: Backend Health (sqp_backend_health by pool), Queries (query volume), Routing decisions (by pool), Pool connections, and Pool durations p99.

It expects a Prometheus datasource scraping the Router metrics port. A minimal scrape config:

scrape_configs:
  - job_name: querypilot
    static_configs:
      - targets: ["<router-host>:9090"]

Point the scrape target at the metrics port; 9091 is the admin API and does not serve /metrics. Import the dashboard either way:

• Grafana UI: Dashboards, New, Import, upload the JSON file, select your Prometheus datasource.
• Provisioning: copy the JSON into your Grafana provisioning dashboards directory alongside a dashboard provider definition.

Prometheus alert rules

Load alert rules through a rule_files glob in your Prometheus config. Thresholds below are conservative defaults to tune against your SLOs:

groups:
  - name: querypilot-router
    rules:
      - alert: QueryPilotBackendUnhealthy
        expr: sqp_backend_health == 0
        for: 2m
        labels: { severity: page }
        annotations:
          summary: "Backend {{ $labels.backend }} in pool {{ $labels.pool }} is unhealthy"

      - alert: QueryPilotPoolSaturated
        expr: sqp_pool_utilization_ratio > 0.9
        for: 10m
        labels: { severity: warning }
        annotations:
          summary: "Pool {{ $labels.pool }} above 90% utilization"

      - alert: QueryPilotPoolTimeouts
        expr: increase(sqp_pool_timeouts_total[5m]) > 0
        for: 5m
        labels: { severity: warning }
        annotations:
          summary: "Sessions timing out waiting for connections in pool {{ $labels.pool }}"

The full metric catalog is in the metrics reference.

Annotated example configuration

A fuller querypilot.toml showing the optional knobs beyond the minimal configs in Getting Started and Deployment:

[general]
pool_mode = "transaction"      # session | transaction | statement
worker_threads = 0             # 0 = one per CPU core
shutdown_timeout = "30s"       # drain window on SIGTERM

[[listeners]]
protocol = "postgres"
bind = "0.0.0.0:5433"

# MySQL clients are served from a second listener (preview).
#[[listeners]]
#protocol = "mysql"
#bind = "0.0.0.0:6433"

[pools.primary]
role = "primary"
username = "<your_pg_user>"
password = "<your_pg_password>"
database = "<your_database>"
min_connections = 2            # kept open when idle
max_connections = 20           # hard cap per pool
acquire_timeout = "30s"        # max wait for a connection before erroring
idle_timeout = "10m"           # prune idle connections after this
connect_timeout = "10s"        # backend TCP/auth budget
# query_timeout = "60s"        # per-statement deadline; unset = none
# raw_passthrough = true       # vanilla PG-to-PG pools only; see the
                               # Router guide before enabling

backends = [
    { host = "127.0.0.1", port = 5432 }
]

[routing]
default_pool = "primary"

[stats]
max_entries = 10000            # distinct query fingerprints tracked

[metrics]
enabled = true
bind = "0.0.0.0:9090"          # admin API always binds metrics port + 1

[logging]
level = "info"                 # trace | debug | info | warn | error
format = "pretty"              # pretty | json

Key-by-key detail lives in the Router configuration reference. Longer walkthroughs are tutorials: read/write splitting, Accelerator caching, and Guard policies.

How-to: add a pattern routing rule and test it

Steer one query shape to a specific pool. Pattern rules match by normalized query shape; three types exist: template (a literal example query whose values are parameterized away), regex (over the query text), and hash (a fingerprint hash). Get the fingerprint of any query from the admin API first:

curl "http://localhost:9091/api/routing/test?sql=SELECT%20*%20FROM%20orders%20WHERE%20user_id%20=%2042"
# note "fingerprint_hash" and "normalized_sql" in the response

Add the rule to querypilot.toml:

[[routing.pattern_rules]]
name = "order_lookups_to_replica"
type = "template"
pattern = "SELECT * FROM orders WHERE user_id = 42"
pool = "replica"
priority = 10

Apply without a restart, then confirm the decision changed:

curl -X POST http://localhost:9091/api/config/reload
curl "http://localhost:9091/api/routing/test?sql=SELECT%20*%20FROM%20orders%20WHERE%20user_id%20=%207"
# "decision": "pattern", "target_pool": "replica"

Any literal value matches: the template is normalized, so user_id = 7 hits the rule created from the = 42 example. Rules can also be created at runtime by fingerprint via POST /api/pattern-rules; those are ephemeral and cleared on config reload, so put durable rules in the config file.

How-to: tune pool sizing and timeouts

The knobs, per [pools.<name>]: min_connections (kept open), max_connections (hard cap), acquire_timeout (how long a session waits for a free connection), idle_timeout (idle connections above the minimum are closed), connect_timeout (backend dial deadline), and optional query_timeout. A steady-service starting point:

[pools.primary]
min_connections = 2
max_connections = 10
acquire_timeout = "30s"
idle_timeout = "60s"
connect_timeout = "10s"

Verify under load: sqp_pool_utilization_ratio near 1.0 with growing sqp_pool_queue_depth and sqp_pool_wait_duration_seconds means the cap is too low, or backend capacity is the real limit. sqp_pool_timeouts_total and sqp_pool_acquire_total{outcome="timeout"} count requests that gave up after acquire_timeout. sqp_pool_acquire_total{outcome="created"} churning alongside a short idle_timeout means you are paying reconnect cost; raise min_connections or idle_timeout. Transaction pool mode (pool_mode = "transaction", the default) lets one backend connection serve many clients, so size against concurrent transactions, not client count.

How-to: connect from application frameworks

No driver changes are needed; point existing drivers at the Router and authenticate with the pool's credentials (see Security for the auth model).

Go (pgx v5) against a PostgreSQL listener:

connStr := fmt.Sprintf("postgres://%s:%s@%s:%s/%s", user, pass, host, port, db)
conn, err := pgx.Connect(ctx, connStr)

pgx defaults to prepared statements and the binary protocol; both are supported through the Router, including numeric/decimal round-trips.

Python (psycopg 3) against a PostgreSQL listener:

import psycopg
conn = psycopg.connect(host="proxy.internal", port=6432,
                       user="app", password="<your-password>", dbname="mydb")
conn.autocommit = True   # or manage transactions explicitly

Python (PyMySQL) against a MySQL listener:

import pymysql
conn = pymysql.connect(host="proxy.internal", port=6433,
                       user="app", password="<your-password>", database="mydb")

One cross-cutting caution: leave raw_passthrough off for any pool that is not vanilla same-protocol PostgreSQL. Cross-protocol pools (MySQL client to PostgreSQL backend) need row re-encoding.