Router

What the Router does

The Router (binary querypilot) is QueryPilot's SQL proxy and the only mandatory component of the platform. It terminates PostgreSQL and MySQL wire-protocol connections, pools connections to backend databases, and decides per query which pool a statement runs on, optionally rewriting it on the way. This page covers pooling, routing, translation, prepared statements, and shutdown behavior. The key-by-key schema of querypilot.toml lives in the Router configuration reference.

The Router loads its configuration from --config / -c, the QUERYPILOT_CONFIG environment variable, or querypilot.toml in the current directory, in that order. Validate a file without starting the proxy:

querypilot --check -c querypilot.toml

Unknown keys anywhere in the file are rejected at startup, so typos fail loudly instead of silently using a default.

Listeners and wire protocols

Each [[listeners]] entry binds one address and speaks one protocol:

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

[[listeners]]
protocol = "mysql"
bind = "0.0.0.0:6433"

Clients connect with unmodified drivers; no application changes are required. The Router authenticates clients against pool credentials, so avoid exposing a listener without credentials configured (startup refuses this combination on non-loopback binds unless you explicitly opt in with allow_anonymous).

The Router does not terminate TLS. Terminate TLS in front of the Router (load balancer, mTLS sidecar, or tunnel) and keep listeners on trusted network segments. See Security.

Connection pooling

Backend connections live in named [pools.<name>] sections. Each pool has its own backends, credentials, protocol, sizing, and timeouts; a client session borrows a connection from whichever pool the routing decision selects.

Pooling modes

general.pool_mode sets the default; pools.<name>.mode overrides it per pool. The mode controls when a borrowed backend connection returns to the pool:

In every mode, statements inside an open transaction are pinned: the routing engine reuses the connection the transaction started on before any rule is evaluated.

Per-pool overrides

A pool can override what the client session would otherwise supply:

[pools.analytics]
mode = "transaction"
protocol = "postgres"        # backend wire protocol, regardless of client protocol
username = "analytics_user"  # backend credentials, regardless of client credentials
password = "<your-password>"
database = "analytics"
backends = [
    { host = "replica1.db.local", port = 5432, weight = 2 },
    { host = "replica2.db.local", port = 5432, weight = 1, max_replica_lag = "10s" },
]

• protocol forces the backend wire protocol. See Cross-protocol pools.
• username, password, and database replace the client's credentials on backend connections.
• Each backend entry accepts a weight key (validated to be non-zero), but the current release opens new connections round-robin across healthy backends; weights do not yet bias the selection.
• A backend with max_replica_lag set is marked unhealthy, and skipped, whenever its measured replication lag exceeds the threshold. It returns automatically when the lag recovers.

Sizing and timeouts

Per pool: min_connections (default 1), max_connections (default 10), acquire_timeout (default 30s), idle_timeout (default 600s), connect_timeout (default 10s), and optional query_timeout (unset by default; no per-query deadline).

Sessions that cannot get a connection within acquire_timeout receive an error rather than queueing forever. Under load, watch sqp_pool_queue_depth, sqp_pool_wait_duration_seconds, and sqp_pool_timeouts_total: sustained queue depth with utilization near 1.0 means the cap is too low or the backend is the real limit. In transaction mode, size max_connections against concurrent transactions, not client count. See the metrics reference.

Cross-protocol pools and raw_passthrough

Setting protocol on a pool lets a client on one protocol be served from a backend speaking another, for example a MySQL client reading from Analytics, which speaks PostgreSQL wire. The Router decodes result rows from the backend protocol and re-encodes them for the client.

raw_passthrough = true skips that decode/re-encode step for prepared-statement results and streams the backend's bytes to the client unchanged. It is only correct when both sides speak identical encodings, meaning a vanilla PostgreSQL client talking to a vanilla PostgreSQL backend, and is therefore rejected in combination with a protocol override. When a statement cannot be passed through (DDL, session commands), the Router falls back to the decoded path for that statement. Leave it off for any pool fronting Analytics or any cross-protocol pool.

The routing decision

For every statement, the routing engine walks these steps in order and stops at the first one that produces a decision:

1. Transaction pinning. Inside a transaction, reuse the pinned backend connection. Nothing else is evaluated.
2. Translation rules. The query is parameterized (literals lifted out) and its pattern hash is looked up in the translation registry. On a hit, the stored rewritten SQL is filled in with the incoming literal values and sent to the translation's target_pool.
3. Dynamic pattern rules. Rules registered at runtime, through POST /api/pattern-rules or by the Accelerator, matched against the query's normalized fingerprint.
4. Routing-decision cache. If an identical query shape was routed before and routing.cache is enabled (it is by default), the cached decision is reused.
5. Pattern rules ([[routing.pattern_rules]]), in ascending priority order, first match wins.
6. Regex rules ([[routing.rules]]), in ascending priority order, first match wins. These can also match on session user, database, and listener protocol.
7. Read/write split or default pool. With read_write_split = true, reads go to read_pool and everything else to write_pool. Otherwise the query goes to default_pool. Connections that arrived on a MySQL listener fall back to mysql_default_pool when it is set.

Rule-based and default decisions are cached per query fingerprint (translation and dynamic-rule hits are not); the cache is invalidated on config reload and rule changes. A matched rule either names a pool or names a reject message returned to the client as an error.

Dry-run any query without sending traffic:

curl "http://localhost:9091/api/routing/test?sql=SELECT%20*%20FROM%20users%20WHERE%20id%20=%201"

The response includes the decision, target pool, fingerprint hash, and matched rule.

Read/write classification

Classification looks at the first keyword after leading comments, with refinements:

Under read/write splitting, only reads go to read_pool. Writes, DDL, admin, and unknown statements all go to write_pool: when in doubt, the Router routes to the primary rather than risk a write on a replica. A worked setup is in the read/write split tutorial.

Pattern rules and query normalization

Pattern rules match a query by shape rather than raw text. Three types exist:

• template: you write a concrete example query; the Router normalizes it into a fingerprint and matches any query with the same shape, regardless of literal values. The recommended type for routing specific query shapes.
• regex: a regular expression evaluated against the raw query text. Most flexible, slowest; good for keyword- or table-based routing.
• hash: a precomputed fingerprint hash (decimal u64), matched by equality. Fastest; obtain the hash from GET /api/routing/test?sql=....

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

[[routing.pattern_rules]]
name = "analytics_tables"
type = "regex"
pattern = "(?i)FROM analytics_"
pool = "analytics"
priority = 20

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

Normalization replaces literals (numbers, strings, booleans, NULL) with placeholders and hashes the result. routing.cache.normalization_strategy selects how:

• string (default): regex-based literal replacement; fast and dialect-agnostic.
• ast: parses the SQL and rewrites literals in the syntax tree; more precise, several times slower, and currently applied to SELECT statements only.
• auto: tries AST, silently falls back to string when parsing fails.

Statements the AST strategy does not cover (including all non-SELECT statements) always use string normalization.

Query translation

Translation rules go one step further than routing: they replace the query's SQL and pin it to a pool. The canonical use is redirecting specific hot queries to Analytics when the analytics dialect differs from the source dialect, for example rewriting MySQL JSON_OVERLAPS(col, '[1,2]') into DuckDB list_has_any(...).

A rule is three strings: a concrete original_sql example, the translated_sql to run instead, and the target_pool:

[[translation.rules]]
original_sql = "SELECT id FROM tags WHERE JSON_OVERLAPS(labels, '[1,2]')"
translated_sql = "SELECT id FROM tags WHERE list_has_any(labels, [1,2])"
target_pool = "analytics"

At registration time both sides are parameterized and the literal positions are matched up. At query time, any incoming query with the same shape as original_sql, with any literal values including IN lists of any length, is rewritten by substituting its actual literals into the translated pattern and sent to the target pool.

Translations come from two places with different lifetimes: [[translation.rules]] in querypilot.toml is re-registered from the file on every config reload; POST /api/translations at runtime is ephemeral, and a config reload clears it. See the HTAP analytics tutorial for a full Router-plus-Analytics translation setup.

Prepared statements

The Router supports the PostgreSQL extended protocol and MySQL prepared statements end to end, including binary result formats. Prepared statements are tracked per client session under their original SQL, so execute-time routing (including translation rules) still applies. Because pooling means the backend connection under a session can change between executes, the Router transparently re-prepares a statement on the backend it acquires when needed; clients never see the difference. When a translation rule matches a prepared statement, the rewritten SQL is what gets prepared on the target pool's backend.

Graceful shutdown

On SIGTERM or SIGINT, the Router stops accepting new connections, keeps serving active sessions while they drain, and then force-closes any connections still open after a bounded drain window. In-flight statements on drained sessions complete normally; plan rolling restarts so long-running transactions finish first or accept that stragglers are cut at the deadline. See Operations for deployment patterns.

Metrics and the admin API

With [metrics] enabled = true, the Router serves Prometheus metrics on metrics.bind (default port 9090) and a REST admin API on the same IP at the metrics port plus one (9090 gives 9091). The admin API manages translations, dynamic pattern rules, pool status, config reload, and per-query-shape execution statistics (GET /api/stats/digests); the full surface is in the admin API reference.

Plugin REST surfaces mount on the same listener: /api/v1/plugins/accelerator for the Accelerator and /api/v1/plugins/guard for Guard.

Routing and pooling export Prometheus series under the sqp_ prefix, notably sqp_routing_decisions_total (labeled by decision type and target pool), sqp_routing_cache_operations_total, sqp_pool_connections, sqp_pool_acquire_total, sqp_pool_queue_depth, and sqp_pool_timeouts_total. GET /api/routing/test is the fastest way to answer "where would this query go and why" without sending traffic. See the metrics reference.