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); Analytics is the analytics server (binary querypilot-analytics). 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 dashboards

Two ready-made dashboards ship as JSON under docker/grafana/provisioning/dashboards/:

• Router overview (SQP.json): Panels: Backend Health (sqp_backend_health by pool), Queries (query volume), Routing decisions (by pool), Pool connections, and Pool durations p99.
• Analytics snapshot and replication health (SQP-Snapshot.json): Panels: Active snapshots, Phase by table, Snapshot progress (rows), Throughput (rows/sec), the page-severity trio Counter mismatch / Writer panic / Swap failure, Orphan staging reaped, Ingest failures by reason, Soft-failure rates, Scratch bytes used, and Ingest queue depth (files and bytes).

Both expect a Prometheus datasource scraping the Router metrics port (9090) and the Analytics metrics port (9090 in-pod, 19090 in the Compose deployment). Import them either way:

• Grafana UI: Dashboards, New, Import, upload the JSON file, select your Prometheus datasource.
• Provisioning: copy the JSON files into your Grafana provisioning dashboards directory; docker/grafana/provisioning/ contains working datasource and dashboard provider definitions to adapt, and docker/grafana/prometheus.yml a scrape config. Point the scrape target at the metrics port; 9091 is the admin API and does not serve /metrics.

Prometheus alert rules

Alert rule examples ship under docker/grafana/alerts/ and load via a rule_files glob in the Prometheus config. The shipped file covers the Analytics snapshot pipeline; thresholds are conservative defaults to tune against your SLOs. The page-severity rules:

groups:
  - name: querypilot-snapshot
    interval: 30s
    rules:
      - alert: QueryPilotSnapshotCounterMismatch
        expr: sum(sqp_duckdb_parquet_snapshot_counter_mismatch_total) > 0
        for: 0s
        labels: { severity: page }
        annotations:
          summary: "Snapshot row counts disagreed across read/write/ingest legs"

      - alert: QueryPilotSnapshotWriterPanic
        expr: sum(increase(sqp_duckdb_parquet_snapshot_writer_panic_total[5m])) > 0
        for: 0s
        labels: { severity: page }
        annotations:
          summary: "Streaming snapshot writer thread panicked"

      - alert: QueryPilotSnapshotStalled
        expr: |
          rate(sqp_snapshot_rows_total[10m]) == 0
          and on(table) sqp_snapshot_phase{phase=~"copy|parquet_write|duckdb_ingest"} == 1
        for: 10m
        labels: { severity: warning }
        annotations:
          summary: "Snapshot for {{ $labels.table }} made no progress in 10m"

Pair them with Router-side rules built from the metrics in the monitoring guidance:

      - 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 }}"

      - alert: QueryPilotReplicationLagHigh
        expr: sqp_duckdb_replication_lag_seconds > 60
        for: 5m
        labels: { severity: warning }
        annotations:
          summary: "Analytics replication lag above 60s"

The full metric catalog is in the metrics reference.

Example configuration files

Key-by-key detail lives in the Router configuration reference and the Analytics configuration reference. Longer walkthroughs are tutorials: HTAP analytics, 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, or anything fronting DuckDB) need row re-encoding.