Testing Read/Write Splitting Correctness

A read/write split is a piece of production logic that sits on the path of every query and is almost never tested. The operational problem is asymmetric: when the split is wrong in one direction you get loud errors — cannot execute UPDATE in a read-only transaction — and when it is wrong in the other direction you get nothing at all, just a primary quietly carrying read traffic that the replicas were provisioned to absorb. The first failure interrupts users; the second one wastes an entire tier and is usually discovered only when the primary saturates.

This page is about proving the split correct before it reaches production, and keeping it proven afterwards. It builds directly on the four-stage routing decision described in connection routing and pooling strategies: each stage has a distinct failure signature, and each needs a different kind of test.

What Correctness Means Here

A routing configuration is correct when three properties hold simultaneously.

Safety — no write reaches a replica. This includes statements that are not obviously writes: data-modifying CTEs, SELECT ... FOR UPDATE and FOR SHARE, procedure calls with side effects, SELECT over a VOLATILE function, and every statement inside a write transaction. Violations are loud, which makes them the easy half.

Completeness — every eligible read reaches a replica. A read that lands on the primary is not an error, so nothing reports it. Completeness failures are the reason a replica fleet can be at 8% utilisation while the primary is at 85%.

Coherence — a transaction never splits across nodes. Statements inside one transaction must all reach the same node. A router that classifies statement-by-statement without tracking transaction state will eventually route a BEGIN to the primary and a subsequent SELECT to a replica, producing a “transaction” with no isolation at all.

The three properties fail independently and need different tests. Safety is testable with a fixture of adversarial statement shapes. Completeness is not testable in isolation at all — it can only be measured against real traffic, because it depends on which statements your application actually issues. Coherence needs a stateful test that spans multiple statements.

Scope note: this page covers verifying where a statement went. Verifying that the data it read was fresh enough is a different question, answered by routing queries based on data freshness requirements. A statement can be routed perfectly and still return stale data.

Intended versus actual destination: the two ways routing goes wrong A two by two grid. Rows are the statement's intended destination, primary or replica. Columns are where it actually went. The two diagonal cells are correct. A write that reaches a replica produces an immediate read-only transaction error and is easy to detect. A read that reaches the primary produces no error at all, so it is detectable only by comparing traffic volumes, and it silently consumes primary capacity. actually served by primary actually served by replica write intended read eligible correct writes land where they must nothing to do write leakage ERROR: read-only transaction loud · caught by a fixture test detection cost: near zero accidental primary read no error, no log line silent · only visible in volumes cost: an idle replica tier correct reads spread across the pool still verify freshness separately Most teams test only the top-right cell. The bottom-left one is where the money goes.

Mechanism Deep-Dive: Asserting Where a Statement Went

The central technique is simple and frequently done wrong: ask the server which node executed the statement, not the router which node it selected. A router reports its decision; only the server reports the outcome. The two differ whenever a connection was pinned, a failover intervened, or a rule was loaded to runtime but not to disk.

sql
-- PostgreSQL: the two facts to assert on, from inside the same session
-- that ran the statement under test.
SELECT pg_is_in_recovery()  AS on_replica,   -- t on a standby, f on a primary
       inet_server_addr()   AS served_by,    -- the backend actually executing
       current_setting('transaction_read_only') AS read_only;
sql
-- MySQL equivalent.
SELECT @@hostname AS served_by, @@read_only AS on_replica, @@in_transaction;

Wrapping that into a test helper gives an assertion that cannot be fooled by router self-reporting:

python
# conftest.py — a fixture that answers "where did that actually run?"
import pytest, psycopg

ROUTER_DSN = "postgresql://app@router:6432/app"

def served_by(cur) -> str:
    cur.execute("SELECT pg_is_in_recovery()")
    return "replica" if cur.fetchone()[0] else "primary"

@pytest.fixture
def routed():
    """Yields a function: run a statement, return where it was served."""
    with psycopg.connect(ROUTER_DSN, autocommit=True) as conn:
        def run(sql: str, *, in_txn: bool = False) -> str:
            with conn.cursor() as cur:
                if in_txn:
                    cur.execute("BEGIN")
                cur.execute(sql)
                where = served_by(cur)          # same session, same connection
                if in_txn:
                    cur.execute("COMMIT")
                return where
        yield run

The served_by probe runs on the same connection immediately after the statement under test, so it observes the node that connection is bound to. On a statement-level router this is exactly right; on a connection-level router it also confirms the pinning behaviour, which is itself worth asserting.

The fixture that matters

The value of the test suite is entirely in the statement list. A list of plain SELECTs and INSERTs proves nothing. The list below is the minimum that distinguishes a real classifier from a keyword matcher:

python
CASES = [
    # (sql,                                              expected, note)
    ("SELECT id FROM users WHERE id = 1",                "replica", "baseline read"),
    ("UPDATE users SET seen = now() WHERE id = 1",       "primary", "baseline write"),
    ("SELECT id FROM users WHERE id = 1 FOR UPDATE",     "primary", "takes row locks"),
    ("SELECT id FROM users WHERE id = 1 FOR SHARE",      "primary", "takes row locks"),
    ("WITH d AS (DELETE FROM q RETURNING *) SELECT * FROM d",
                                                          "primary", "write that begins with WITH"),
    ("SELECT nextval('user_id_seq')",                    "primary", "VOLATILE, advances state"),
    ("SELECT audit_login(1)",                            "primary", "VOLATILE function writes"),
    ("CALL rebuild_summary()",                           "primary", "procedure with side effects"),
    ("SELECT count(*) FROM users",                       "replica", "aggregate read"),
    ("SET statement_timeout = '5s'",                     "primary", "session state must not split"),
]

Every entry except the first two is a case a regex classifier gets wrong by default. That is the point of the fixture: it is not a smoke test, it is a list of the specific ways classification fails.

Coherence needs a multi-statement test

python
def test_transaction_does_not_split(routed_conn):
    """All statements inside one transaction must land on the same node."""
    with routed_conn.cursor() as cur:
        cur.execute("BEGIN")
        cur.execute("UPDATE users SET seen = now() WHERE id = 1")
        first = served_by(cur)
        cur.execute("SELECT seen FROM users WHERE id = 1")   # must NOT go to a replica
        second = served_by(cur)
        cur.execute("COMMIT")
    assert first == second == "primary", (
        f"transaction split across nodes: {first} then {second}"
    )

A router without transaction awareness passes every single-statement test in CASES and fails this one. It is the highest-value test in the suite for exactly that reason.

Trade-off Comparison: Verification Methods

Method Catches Runs where Feedback speed Blind to
Fixture assertions (safety, coherence) Classification and transaction-splitting bugs CI, against a real primary/standby pair Seconds Statement shapes you did not think of
Digest audit (completeness) Reads stuck on the primary; unknown shapes Production or a traffic replay Minutes to hours Nothing about correctness of a single execution
Shadow traffic / replay Real-shape coverage without user risk Pre-production Hours Anything triggered by production-only data
Canary rollout Everything, on a small blast radius Production Minutes Rare shapes not present in the canary slice
Error-rate alerting Write leakage only, after it happens Production Seconds Every silent failure — the whole bottom-left cell

None of these is sufficient alone, and the combination that works is cheap: fixtures in CI for safety and coherence, a standing digest audit for completeness, and a canary in front of any rule change. Error-rate alerting is the backstop, not the strategy — by the time it fires, users have already seen the error.

Configuration Runbook

A two-node CI fixture

Routing tests need a genuine primary and standby. Docker Compose is enough:

yaml
# docker-compose.routing-test.yml — a real streaming pair for CI
services:
  primary:
    image: postgres:16
    environment:
      POSTGRES_PASSWORD: test
      # wal_level=replica and a slot are all a standby needs to attach
      POSTGRES_INITDB_ARGS: "-c wal_level=replica -c max_wal_senders=4"
  standby:
    image: postgres:16
    depends_on: [primary]
    # pg_basebackup -R writes the standby signal and primary_conninfo for us
    command: >
      bash -c "until pg_basebackup -h primary -U postgres -D /var/lib/postgresql/data -R -Fp -Xs -P;
               do sleep 1; done; postgres"
    environment:
      PGPASSWORD: test
  router:
    image: proxysql/proxysql:latest
    depends_on: [primary, standby]
    volumes: ["./proxysql.test.cnf:/etc/proxysql.cnf:ro"]

The standby is a real standby, which means pg_is_in_recovery() genuinely returns true and a write genuinely fails — the two behaviours a mock cannot reproduce.

Auditing production digests for completeness

Completeness cannot be unit-tested; it must be measured against real traffic. The comparison is between the digests seen on the primary and the set your fixture says belong on a replica:

sql
-- Run on the PRIMARY. Any read-shaped digest here is a completeness failure.
SELECT queryid,
       left(query, 90)         AS statement,
       calls,
       round(total_exec_time)  AS total_ms
  FROM pg_stat_statements
 WHERE query ~* '^\s*select'
   AND query !~* 'for\s+(update|share)'     -- these belong on the primary
   AND query !~* 'nextval|setval'           -- so do these
 ORDER BY calls DESC
 LIMIT 25;
sql
-- ProxySQL equivalent: which host group each digest actually reached.
SELECT hostgroup, digest_text, count_star, sum_time
  FROM stats_mysql_query_digest
 WHERE digest_text LIKE 'SELECT%'
   AND hostgroup = 10                       -- 10 = write host group
 ORDER BY count_star DESC LIMIT 25;

Run this weekly. Every release adds statement shapes, and a rule set that was complete last month rarely still is. Treat the top of that list as a work queue rather than an alert.

Canary rollout with an automatic revert

yaml
# Rule changes go out behind a share-based canary with a hard revert condition.
routing_canary:
  share: 0.05                 # 5 % of sessions use the new rule set
  duration: 30m
  promote_if:
    read_only_txn_errors: 0   # any write leakage at all aborts the rollout
    p99_read_latency_delta_pct: "< 10"
    replica_read_share_delta_pct: "> -2"   # completeness must not regress
  revert_on_any_failure: true

The third condition is the one teams forget. A rule change that eliminates write leakage by sending everything to the primary satisfies the first two conditions perfectly and destroys the value of the replica tier. Assert on the replica’s share of reads, not only on error counts.

Where each verification stage sits, and what it catches Four stages arranged from left to right by increasing realism and decreasing feedback speed. Fixture assertions in continuous integration catch classification and transaction-splitting bugs within seconds. Traffic replay in pre-production catches statement shapes the fixture missed. A canary slice in production catches everything on a small blast radius. A standing digest audit runs continuously and is the only stage that detects accidental primary reads. faster feedback more realistic fixture in CI real primary + standby seconds per run traffic replay yesterday's real shapes no user exposure canary slice 5 % of sessions auto-revert on leakage digest audit standing, not gated runs forever catches write leakage split transactions shapes the fixture never imagined production-data-only behaviour accidental primary reads — nothing else does The four are complementary, not alternatives: each stage's blind spot is the next stage's whole purpose.

Monitoring and Alerting Signals

promql
# 1. Write leakage — should be flat zero. Page on any non-zero value.
sum(rate(pg_stat_database_xact_rollback{role="replica"}[5m])) > 0

# 2. Replica share of reads — the completeness signal.
#    A sudden drop means a rule change quietly re-pinned reads to the primary.
sum(rate(pg_stat_database_tup_returned{role="replica"}[5m]))
  / sum(rate(pg_stat_database_tup_returned[5m])) < 0.6

# 3. Read-shaped statements executing on the primary, by call count.
sum(rate(pg_stat_statements_calls{role="primary", cmd_type="select"}[5m]))

# 4. Rules loaded to runtime but never saved to disk (ProxySQL) — survives
#    until the next restart, then silently reverts.
proxysql_stats_mysql_query_rules_unsaved > 0

Signal 2 is the one worth building a dashboard around, because it is the only continuous measure of completeness. Set the threshold from your own baseline rather than from a general rule — what matters is the change, since a stable 60% may be entirely correct for a write-heavy application and alarming for a read-heavy one.

Signal 4 catches a specific and common operational failure: an operator applies a rule change with LOAD ... TO RUNTIME during an incident, it works, and nobody runs SAVE ... TO DISK. The configuration is correct until the next proxy restart, at which point it reverts without any change having been deployed.

Failure Modes and Recovery Steps

  1. A data-modifying CTE reaches a replica. Diagnosis: ERROR: cannot execute DELETE in a read-only transaction on a statement that begins with WITH. Recovery: add an explicit rule matching ^\s*WITH to the write destination, ahead of the general read rule. Prevention: the CTE case in the fixture — this is the single most common classifier gap.

  2. Transactions split across nodes. Diagnosis: intermittent “row not found” immediately after a write inside the same transaction, or unexpected lock-timeout errors. Recovery: enable transaction persistence on the router (ProxySQL transaction_persistent=1 on the user) and confirm with the coherence test. Prevention: run the coherence test in CI; it is the only test that catches this.

  3. Reads silently pinned to the primary. Diagnosis: signal 2 sits far below expectation while everything appears healthy. Recovery: find the cause with the digest query above; the usual culprits are an ORM session that opens a transaction eagerly, a connection-level router that pinned at establishment, or a SET statement that made the session ineligible. Prevention: the standing digest audit, plus asserting replica share in the canary criteria.

  4. Rules verified in staging, wrong in production. Diagnosis: the fixture passes, production still leaks. Recovery: compare the runtime rule set, not the config file — SELECT * FROM runtime_mysql_query_rules ORDER BY rule_id. Rule order differing between environments produces exactly this. Prevention: export the runtime rule set as the artefact you deploy, so ordering is part of the deployed object.

  5. Canary passes, full rollout fails. Diagnosis: the canary slice never exercised a rare statement shape. Recovery: revert, then replay a longer traffic window against the new rules before retrying. Prevention: select canary traffic by shape coverage rather than by random share where you can — routing a specific low-volume service through the new rules first often covers more shapes than 5% of the busiest one.

Production-Readiness Checklist

In This Section

FAQ

Why is a smoke test of SELECT 1 not enough?

Because every classifier gets plain SELECT right. The statements that break routing are the ones with an unusual shape: SELECT ... FOR UPDATE, a data-modifying CTE that begins with WITH, a SELECT calling a VOLATILE function, a stored-procedure call, or the first statement of a lazily-opened transaction. A test suite that only exercises the easy shapes reports success on exactly the configuration that will fail in production.

How can I tell which node actually served a query?

Ask the server, not the router. On PostgreSQL, pg_is_in_recovery() returns true on a replica and false on a primary, and inet_server_addr() returns the address of the backend executing the statement. On MySQL, @@hostname and @@read_only serve the same purpose. Asserting on these values is the only way to catch a router that reports a routing decision it did not actually apply.

What is an accidental primary read and why does it matter?

It is a read that should have been eligible for a replica but was routed to the primary — the mirror image of write leakage. It matters because it is invisible: nothing fails, no error is logged, and the only symptom is that the primary carries load the replicas were bought to absorb. Fleets frequently discover that a third or more of their read traffic never left the primary, usually because a transaction wrapper or an ORM session default pinned it there.

Should routing tests run against a real replica or a mock?

Against a real pair. The behaviours you are testing — read-only transaction errors, recovery state, transaction pinning — are properties of a genuine primary and standby, and a mock reproduces the assumptions you already hold rather than the ones the database enforces. A two-container fixture with streaming replication is inexpensive to run in CI and catches the class of bug a mock cannot.

← Back to Connection Routing & Pooling Strategies