Tutorial: Read/Write Splitting

Write querypilot.toml with a pool per role.

[general]
pool_mode = "transaction"

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

[pools.primary]
role = "primary"
username = "app"
password = "<your-password>"
database = "mydb"
min_connections = 2
max_connections = 50
backends = [{ host = "primary.db.local", port = 5432 }]

[pools.replica]
role = "replica"
username = "app"
password = "<your-password>"
database = "mydb"
min_connections = 2
max_connections = 25
backends = [
    { host = "replica1.db.local", port = 5432, weight = 2 },
    { host = "replica2.db.local", port = 5432, weight = 1, max_replica_lag = "10s" },
]

[routing]
read_write_split = true
read_pool = "replica"
write_pool = "primary"
default_pool = "primary"

[metrics]
enabled = true
bind = "0.0.0.0:9090"

The three keys that matter: read_write_split turns classification on, read_pool receives reads, and write_pool receives everything else.

Two backend-level knobs:

• max_replica_lag marks a backend unhealthy whenever its measured replication lag exceeds the threshold; the Router skips it when opening connections and brings it back automatically once lag recovers.
• weight is accepted and validated (must be non-zero), but the current release opens new connections round-robin across healthy backends; weights do not yet bias selection.

Run the Router.

docker run -d --name querypilot --network host \
  -v "$(pwd)/querypilot.toml:/etc/sqp/querypilot.toml:ro" \
  readysettech/querypilot:latest

Wait for docker ps --filter name=querypilot to report healthy. Clients authenticate with the pool credentials (app/app-password above).

Verify decisions without sending traffic.

GET /api/routing/test on the admin API (metrics port + 1) dry-runs the routing decision for any SQL:

curl -s "http://localhost:9091/api/routing/test?sql=SELECT%20*%20FROM%20users%20WHERE%20id%20=%201"
# ... "target_pool": "replica" ...

curl -s "http://localhost:9091/api/routing/test?sql=UPDATE%20users%20SET%20name%20=%20%27x%27%20WHERE%20id%20=%201"
# ... "target_pool": "primary" ...

curl -s "http://localhost:9091/api/routing/test?sql=SELECT%20*%20FROM%20users%20WHERE%20id%20=%201%20FOR%20UPDATE"
# ... "target_pool": "primary" ...

That last one is intentional: SELECT ... FOR UPDATE and SELECT ... FOR SHARE take row locks, so the Router classifies them as writes and routes them to write_pool. A lock taken on a replica would be useless to a later write on the primary.

The full classification table:

When in doubt, the Router routes to the primary rather than risk a write on a replica.

Confirm under real traffic.

Run a mixed workload through port 5433, then check the routing counter. Split decisions are recorded with decision_type="default" and the pool they landed on:

curl -s http://localhost:9090/metrics | grep sqp_routing_decisions_total
# sqp_routing_decisions_total{decision_type="default",target_pool="replica"} 812
# sqp_routing_decisions_total{decision_type="default",target_pool="primary"} 147

sqp_queries_total{pool="replica"} climbing alongside confirms the queries actually executed there. See the metrics reference.

Understand transaction pinning.

Transaction pinning runs before any routing rule: once a session is inside an explicit transaction, every statement reuses the backend connection the transaction started on. A SELECT between BEGIN and COMMIT therefore stays on the write connection rather than hopping to a replica:

BEGIN;
INSERT INTO orders (id, total) VALUES (1, 9.99);  -- primary
SELECT * FROM orders WHERE id = 1;                -- primary (pinned, sees the uncommitted row)
COMMIT;

Pinned statements show up as decision_type="pinned" in sqp_routing_decisions_total. This is what makes read-your-own-writes correct inside transactions. Outside a transaction, an autocommit SELECT immediately after a write may route to a replica and race replication lag; keep read-after-write sequences inside a transaction, or tighten max_replica_lag.