Detecting Accidental Primary Reads in Production Traffic

Problem statement: the replicas are healthy, the routing rules look right, nothing is erroring — and the primary’s CPU is still dominated by SELECT statements that should have gone somewhere else.

This is the silent half of read/write splitting. Write leakage announces itself with an error; an accidental primary read announces nothing at all. This page is about finding those reads, attributing each to a specific cause, and keeping the audit running afterwards. The correctness framework it belongs to is in testing read/write splitting correctness.

Symptom Identification

The characteristic pattern is a capacity puzzle rather than an error:

  • Primary CPU is high and dominated by SELECT in pg_stat_statements, while replica CPU sits well below it.
  • Adding a replica produced little or no relief on the primary.
  • The primary’s connection count is close to max_connections while the replicas’ pools are mostly idle.
  • Replica share of total read volume is well below what the architecture diagram implies — commonly 40–60% where the team believes it is above 90%.
  • No routing errors anywhere. read-only transaction errors are zero, which is often taken as evidence that routing is working correctly.

That last point is the trap: zero write-leakage errors proves safety, not completeness. A configuration that sends everything to the primary has a perfect error rate.

Root Cause Analysis

There are exactly four ways an eligible read ends up on the primary, and they need different fixes.

Transaction pinning. The statement ran inside an open transaction. A correct router must keep the whole transaction on one node, so once a BEGIN has been issued — explicitly, or implicitly by an ORM session — every subsequent statement follows it. Frameworks that wrap each request in a transaction by default turn every read in that request into a primary read.

Session state. The connection carries state that makes it ineligible: a SET (not SET LOCAL) that changed a GUC, a temporary table, a prepared statement bound to that backend, or an advisory lock. Routers pin such sessions rather than risk the state being absent on a replica.

Classification gaps in reverse. The classifier saw something it could not prove was a read and defaulted to the primary. A SELECT calling a function it cannot inspect, a statement beginning with a comment or with WITH, or a prepared statement whose text the proxy never saw all land here. This is a safe default and a costly one.

Freshness fallback. The router deliberately chose the primary because no replica met the freshness requirement, or because the session carried an unmet write watermark. This is the system working correctly, and it must be separated from the other three — otherwise you will “fix” a correct behaviour.

The four are separable, and separating them is the whole diagnostic exercise. Three are defects; one is a design decision.

Primary read volume by cause, with the legitimate share separated A horizontal bar broken into four segments sized by share of misrouted read volume. Transaction pinning is much the largest at around sixty percent. Session state is about twenty percent. Classification gaps are about twelve percent. A small remaining segment of about eight percent is legitimate freshness fallback, marked as a design decision rather than a defect. Below, each segment names its fix. reads executing on the primary, by cause transaction pinning ≈ 60 % — a BEGIN wraps the whole request session state ≈ 20 % classify ≈ 12 % fresh ≈ 8 % fix stop wrapping read-only requests in a transaction one framework setting; usually the entire problem SET LOCAL, not SET drop temp tables add rules for the shapes leave it by design The rightmost segment is not a bug. Removing it would trade correctness for a better-looking metric. The leftmost is usually one line of framework configuration and returns most of the replica tier's value. Attribute before fixing: three of these four segments look identical from the database alone.

Step-by-Step Resolution

Step 1 — Measure the replica share of reads

sql
-- Run on the primary AND on each replica, then compare.
-- tup_returned is a reasonable proxy for read work delivered.
SELECT current_setting('cluster_name', true) AS node,
       pg_is_in_recovery()                   AS is_replica,
       sum(tup_returned)                     AS tuples_returned,
       sum(xact_commit)                      AS transactions
  FROM pg_stat_database WHERE datname = current_database()
 GROUP BY 1, 2;

Inline verification: you have a single ratio — replica tuples returned divided by fleet total. Write it down; it is the number every subsequent fix should move.

Step 2 — Rank the read-shaped digests running on the primary

sql
-- On the PRIMARY. Excludes the statements that legitimately belong here.
SELECT queryid,
       left(regexp_replace(query, '\s+', ' ', 'g'), 90) AS statement,
       calls,
       round(total_exec_time)                           AS total_ms,
       round(100 * total_exec_time /
             sum(total_exec_time) OVER ())::int         AS pct_of_primary_time
  FROM pg_stat_statements
 WHERE query ~* '^\s*(--[^\n]*\n\s*)*select'
   AND query !~* 'for\s+(update|share|no\s+key)'   -- takes locks: primary is correct
   AND query !~* 'nextval|setval|pg_advisory'      -- writes state: primary is correct
 ORDER BY total_exec_time DESC
 LIMIT 25;

Inline verification: the result is a ranked work queue. The top five digests typically account for most of the misrouted volume.

Step 3 — Attribute each digest to a cause

Separating transaction pinning from the rest can be done entirely from the database:

sql
-- Snapshot: are read statements on the primary running inside transactions
-- that also performed writes? Sample repeatedly during peak.
SELECT a.pid,
       a.xact_start IS NOT NULL                    AS in_transaction,
       now() - a.xact_start                        AS txn_age,
       left(a.query, 70)                           AS current_statement
  FROM pg_stat_activity a
 WHERE a.backend_type = 'client backend'
   AND a.state = 'active'
   AND a.query ~* '^\s*select'
   AND NOT pg_is_in_recovery()
 ORDER BY a.xact_start;

A high proportion with in_transaction = true and a txn_age comparable to your request duration is the signature of a per-request transaction wrapper.

For the remaining causes you need the router’s own reason, recorded at selection time as described in tracing and SLO reporting for replica reads:

promql
# Share of primary-served reads by the router's stated reason.
sum by (reason) (rate(db_read_served_age_ms_count{role="primary"}[10m]))
  / ignoring(reason) group_left
    sum (rate(db_read_served_age_ms_count{role="primary"}[10m]))

Inline verification: every primary read is attributed to exactly one of pinned_txn, pinned_session, unclassified, or fallback_lag, and the four shares sum to one.

Step 4 — Fix the largest cause

For transaction pinning, the fix is almost always a framework default:

python
# Django — ATOMIC_REQUESTS wraps EVERY request in a transaction, which pins
# every read in every request to the primary. Turn it off globally and use
# an explicit atomic block around the code that genuinely needs one.
DATABASES = {
    "default": {"ATOMIC_REQUESTS": False, ...},   # was True
}

from django.db import transaction

def checkout(request):
    products = Product.objects.filter(...)        # now eligible for a replica
    with transaction.atomic():                    # only this part is a transaction
        Order.objects.create(...)
java
// Spring — a class-level @Transactional makes every method transactional,
// including pure reads. Mark reads readOnly so the routing datasource can
// send them to a replica.
@Transactional(readOnly = true)                    // was @Transactional
public List<Order> recentOrders(long userId) { ... }

Inline verification: re-run step 1. The replica share of reads should move substantially — a 20-point shift from this change alone is common.

Step 5 — Schedule the audit

yaml
# The digest comparison is not a one-off. Every release adds statement shapes,
# and a rule set that was complete last month usually is not now.
schedule: "0 7 * * 1"                 # Monday morning, before the week starts
job: primary-read-audit
outputs:
  - top_25_read_digests_on_primary
  - replica_share_of_reads
  - delta_vs_previous_week
alert_if:
  replica_share_drop_pct: "> 5"       # a regression, not a gradual drift

Inline verification: the first scheduled run produces the same top-25 list as your manual run, confirming the automation reads the same data.

Configuration Snippet

sql
-- A standing view that turns the audit into one query anyone can run.
-- Create on the primary; requires pg_stat_statements.
CREATE OR REPLACE VIEW ops.misrouted_reads AS
SELECT s.queryid,
       left(regexp_replace(s.query, '\s+', ' ', 'g'), 100) AS statement,
       s.calls,
       round(s.total_exec_time)                            AS total_ms,
       round(s.total_exec_time / nullif(s.calls, 0), 2)    AS mean_ms,
       round(100 * s.total_exec_time /
             sum(s.total_exec_time) OVER ())::int          AS pct_primary_time
  FROM pg_stat_statements s
 WHERE s.query ~* '^\s*(--[^\n]*\n\s*)*(with\s+\w+\s+as\s*\(\s*select|select)'
   -- Exclusions: statements that BELONG on the primary.
   AND s.query !~* 'for\s+(update|share|no\s+key)'
   AND s.query !~* 'nextval|setval|pg_advisory|pg_try_advisory'
   AND s.query !~* '^\s*select\s+1\s*$'              -- health checks
 ORDER BY s.total_exec_time DESC;

COMMENT ON VIEW ops.misrouted_reads IS
  'Read-shaped statements executing on the primary. Each row is either a '
  'routing defect or a documented exception — there is no third category.';

-- Reset the baseline after a fix so the next audit measures the new state.
-- SELECT pg_stat_statements_reset();

The exclusion list is the part that needs maintaining. Every pattern excluded is an assertion that those statements belong on the primary, and each should be justifiable. A growing exclusion list is a warning sign: it usually means the team is silencing the audit rather than fixing the routing.

How one transaction wrapper moves an entire request to the primary Two timelines of the same request. In the first, a transaction opens at the start of the request, so all twenty read statements and the single write execute on the primary. In the second, the transaction is scoped narrowly around the write, so the eighteen reads before it and the one read after it are eligible for replicas and only three statements touch the primary. ATOMIC_REQUESTS = True — transaction spans the request BEGIN … COMMIT — every statement inside must go to one node 21 statements → primary (the thick one is the only write) ATOMIC_REQUESTS = False — transaction scoped to the write BEGIN…COMMIT dashed = eligible for a replica · solid = primary. 19 of 21 statements moved.

Verification and Rollback

Confirm the share moved, using the same measurement as the baseline:

promql
# Replica share of read work. This is the number the whole exercise is about.
sum(rate(pg_stat_database_tup_returned{role="replica"}[10m]))
  / sum(rate(pg_stat_database_tup_returned[10m]))

Take the measurement over a full traffic cycle before and after. Comparing a Monday morning to a Saturday evening will show a change that has nothing to do with your fix.

Confirm correctness did not regress in the process:

promql
# Write leakage must remain flat zero. If moving reads off the primary
# introduced read-only-transaction errors, the transaction scoping is wrong.
sum(rate(app_db_errors_total{sqlstate="25006"}[5m]))

SQLSTATE 25006 is read_only_sql_transaction — the specific error a write on a replica produces. Watch it for a full day after any transaction-scoping change, because the offending code path may be rare.

Rollback. Transaction-scoping changes are the riskiest part of this work, because narrowing a transaction can expose code that silently depended on the wider one for atomicity. Roll back by restoring the framework setting:

python
DATABASES = {"default": {"ATOMIC_REQUESTS": True, ...}}   # revert

The safe sequence is to narrow the scope for one endpoint or one service first, watch for 25006 errors and for data-consistency complaints over a full business cycle, then widen the rollout. Reverting is immediate and complete; the risk is not in the revert but in not noticing that you needed one.

Edge Cases and Gotchas

Read-only transactions are still transactions

Marking a transaction READ ONLY (or @Transactional(readOnly = true)) tells the router the transaction contains no writes, which makes it eligible for a replica — but only if the router understands the marker. Many proxies see BEGIN and pin regardless. Check whether your router honours SET TRANSACTION READ ONLY before relying on it; if it does not, the fix is to avoid the transaction rather than to annotate it.

pg_stat_statements normalises away the distinction you need

Two statements with identical text but different execution contexts — one inside a transaction, one not — share a queryid. So the digest view identifies which statements are on the primary, and cannot tell you why. Pair it with the pg_stat_activity sampling in step 3, which does see transaction state, or with span attributes, which see the router’s reason.

Health checks and metrics queries pollute the count

Exporters, connection validators and readiness probes issue SELECT 1 and similar statements against the primary at a high rate. By call count they can dominate the audit; by execution time they are negligible. Rank by total_exec_time rather than by calls, and exclude the known probe statements explicitly, or the top of the list will be entirely noise.

FAQ

Why do accidental primary reads matter if nothing is failing?

Because they are the reason the replica tier does not deliver the capacity it was bought for. They consume primary CPU, buffer cache and connection slots — the resources that determine when write throughput hits its ceiling — while the replicas sit underused. Nothing errors, so the only symptom is that adding replicas fails to relieve the primary, which usually gets misdiagnosed as needing a bigger primary.

What is the most common cause?

An ORM or framework that opens a transaction at the start of every request. Once a transaction is open, a correct router must send every subsequent statement to the primary for coherence, so a request that performs one write and twenty reads sends all twenty-one to the primary. It is correct behaviour driven by an incorrect default, and it typically accounts for more misrouted volume than every other cause combined.

Can I tell the cause from the database alone?

Partly. The database can show which statements ran on the primary and whether they ran inside a transaction, which separates transaction pinning from the rest. Distinguishing a classification gap from a deliberate freshness fallback needs the router’s own reason for the decision, which only exists in application instrumentation — a span attribute recorded at selection time.

Should every eligible read go to a replica?

No — some reads legitimately belong on the primary: reads inside write transactions, reads that must reflect a just-committed write, and reads during a genuine freshness fallback. The goal is not 100% replica share but knowing which category each primary read falls into. An unexplained primary read is the defect; an explained one is a design decision.

← Back to Testing Read/Write Splitting Correctness