Connection Routing & Pooling Strategies

Effective database routing requires balancing latency, consistency, and operational resilience across a live data plane where misconfiguration has immediate user impact. Modern architectures distribute read traffic across read replicas while preserving strict write guarantees on the primary. A routing layer that silently misclassifies SELECT ... FOR UPDATE as a read, or a pool sized below the replica’s max_connections, triggers cascading failures — connection exhaustion, write fan-out to replicas, and stale-read windows that violate SLAs. This guide covers production-grade routing patterns, pool lifecycle design, and failure mitigation from endpoint discovery through observability.

Architecture Overview

The diagram below shows the data flow through a production connection routing stack: applications connect to a proxy tier that splits reads from writes, pools connections per replica, and monitors replication lag to gate session affinity decisions.

Routing Approach Trade-off Matrix

How a Query Becomes a Route

Every routing layer, whatever tier it lives in, runs the same four-stage decision for each statement. Understanding the stages in order is what lets you predict where a given tool will get a query wrong, because each tool implements the stages with different fidelity.

Stage 1 — classification. The statement is labelled read or write. A regex classifier looks at the leading keyword; an AST classifier parses the statement. The gap between them is where write leakage lives: SELECT ... FOR UPDATE acquires row locks and must run on the primary, WITH t AS (INSERT ... RETURNING *) SELECT * FROM t is a write that begins with WITH, and a SELECT calling a VOLATILE function may write through a side effect the parser cannot see. Regex classifiers get all three wrong by default.

Stage 2 — context override. Classification is overruled by session state. Inside an explicit transaction every statement must go to the same node, because a BEGIN on the primary followed by a SELECT on a replica is not a transaction at all. The same applies to temporary tables, advisory locks, session GUCs set with SET rather than SET LOCAL, and prepared statements bound to a specific backend. A router that is not transaction-aware will split a transaction across nodes and produce errors that look random.

Stage 3 — freshness gating. Reads that survive stages 1 and 2 are still not routable until the router decides which replica is fresh enough. This is where a lag threshold or an LSN watermark applies, and where the replication lag budget becomes a routing input rather than an alerting metric.

Stage 4 — selection and checkout. The router picks one replica from the eligible set and checks out a pooled connection to it. Selection strategy (round robin, least connections, weighted, zone-preferring) determines how evenly load lands; pool state determines whether the checkout blocks.

The practical consequence is that tool selection is really a question of which stages a tool implements natively. PgBouncer implements stage 4 superbly and stages 1–3 not at all — it is a pooler, and read/write splitting must come from somewhere else, usually separate ports or separate pool names. ProxySQL implements stages 1, 3 and 4 with rules and monitored lag, and stage 2 partially through transaction persistence. ORM middleware implements stages 1–3 with the most context but has to be re-implemented per language. Most production stacks end up combining two tools rather than finding one that does all four, which is the reasoning laid out in choosing a read replica routing proxy.

Configuration Baseline

The minimal working configuration for a PgBouncer read replica pool with separate primary and replica pools:

# pgbouncer.ini — minimal production baseline
[databases]
# Primary: receives all writes and urgent reads
app_primary = host=db-primary.internal port=5432 dbname=app pool_size=20

# Replica pool: receives all read-only queries
app_replica = host=db-replica-az1.internal port=5432 dbname=app pool_size=50 pool_mode=transaction

[pgbouncer]
# transaction mode reuses connections aggressively; use session mode only
# if your app relies on SET LOCAL or advisory locks per connection
pool_mode = transaction

# Hard cap — never exceed replica max_connections
max_client_conn = 500

# Validate connections before reuse (catches silent replica disconnects)
server_check_query = SELECT 1
server_check_delay = 15

# Idle connections beyond this interval are closed and recycled
server_idle_timeout = 30

# Refuse new connections rather than queue indefinitely
client_login_timeout = 5

# TLS to replicas (required in production)
server_tls_sslmode = require

-- ProxySQL routing rules (via admin interface on port 6032)
-- Rule 1: force SELECT FOR UPDATE and SELECT FOR SHARE to the primary
INSERT INTO mysql_query_rules
  (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES
  (1, 1, '(?i)select.*for\\s+(update|share)', 10, 1),
  (2, 1, '^(SELECT|SHOW|EXPLAIN)', 20, 1),  -- replicas (hostgroup 20)
  (3, 1, '.*', 10, 1);                       -- everything else: primary (hostgroup 10)

-- Raise to match your replica's max_connections
UPDATE global_variables SET variable_value='200'
  WHERE variable_name='mysql-max_connections';

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Endpoint Discovery & Network Topology

Clients must reliably locate primaries and replicas without introducing routing black boxes. Discovery mechanisms dictate how quickly topology changes propagate to application layers, a concern that interacts directly with how you handle replication lag during failover windows.

# Application DNS client tuning — resolv.conf options
resolver_options: "timeout:1 attempts:2 rotate"
dns:
  ttl_override: 30s          # override OS default; short TTL limits stale-record windows
  srv_resolution: true       # use SRV records for port-aware replica discovery
  fallback_endpoints:
    - db-primary.internal:5432
    - db-replica-az2.internal:5432
  health_check_interval: 10s # active probing supplements passive TTL expiry

Failure modes: DNS cache poisoning redirects traffic to decommissioned nodes. Network partitions trigger split-brain routing when clients resolve stale SRV records. Mitigate by pairing short TTLs (30 s) with circuit breakers and a static fallback list in application configuration.

Proxy-Layer Read/Write Splitting

Centralising routing logic in a dedicated data plane — as covered in depth by the proxy-layer read/write splitting guide — isolates topology awareness from application code. Proxies parse query syntax, classify operations, and distribute connections across healthy replicas.

Failure modes: Unbounded connection queues exhaust proxy memory during traffic spikes. Misclassified SELECT ... FOR UPDATE statements route to replicas and return errors or stale locks. TLS handshake bottlenecks emerge under high concurrency when CPU-bound proxies lack hardware acceleration. Enforce strict AST parsing and cap queue depths with backpressure signals.

Application-Level Query Routing

Embedding routing decisions within the application framework or ORM provides granular control over query execution paths. Developers gain visibility into routing logic but inherit framework coupling. The ORM middleware routing guide details how to configure context managers and database routers safely.

# SQLAlchemy async router — minimal production pattern
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker

read_engine  = create_async_engine("postgresql+asyncpg://replica-az1.internal/app",
                                    pool_size=20, max_overflow=10)
write_engine = create_async_engine("postgresql+asyncpg://primary.internal/app",
                                    pool_size=10, max_overflow=5)

ReadSession  = sessionmaker(read_engine,  class_=AsyncSession, expire_on_commit=False)
WriteSession = sessionmaker(write_engine, class_=AsyncSession, expire_on_commit=False)

async def get_session(operation: str):
    """Return the correct session for 'read' or 'write' operations."""
    if operation == "read":
        return ReadSession()
    return WriteSession()

Failure modes: Async context leakage routes subsequent requests to incorrect pools. Unhandled routing exceptions cascade into connection starvation. Enforce strict transaction boundaries, add explicit connection release hooks, and validate routing hints at test time.

Connection Pool Architecture & Lifecycle

Pools manage TCP reuse, authentication overhead, and resource allocation across distributed nodes. Improper sizing causes memory bloat or connection exhaustion during traffic spikes. The connection pool architecture guide covers replica-aware pool sizing and lifecycle design in detail.

{
  "pool_config": {
    "min_idle": 10,
    "max_active": 50,
    "idle_timeout_ms": 30000,
    "validation_query": "SELECT 1",
    "validation_interval_ms": 15000,
    "leak_detection_threshold_ms": 60000,
    "replica_affinity_weights": { "az1": 0.6, "az2": 0.4 }
  }
}

Failure modes: Pool exhaustion occurs when connection acquisition exceeds replica max_connections. Zombie connections hold locks during failovers. Authentication token expiration mid-lifecycle drops pooled connections silently. Implement connection validation, enforce idle timeouts, and rotate credentials via sidecar proxy.

To protect against exhaustion during a failover, see avoiding connection exhaustion during replica failover.

Read Consistency & Session Affinity

Distributed replicas introduce replication lag, threatening read-your-writes guarantees. Session affinity routes subsequent requests to the same replica until it catches up. The sticky sessions guide details how to track session state without leaking memory or dropping affinity across proxy restarts.

CONSISTENCY_MODE=sticky
SESSION_TOKEN_HEADER=X-DB-Session-ID
REPLICATION_LAG_THRESHOLD_MS=500
FALLBACK_TO_PRIMARY_ON_LAG=true
CACHE_INVALIDATION_SYNC=async

When replication lag exceeds REPLICATION_LAG_THRESHOLD_MS, the router falls back to primary for that session — preventing stale reads without requiring application changes.

Failure modes: Proxy restarts strip session affinity headers, routing users to stale replicas. High lag violates read-your-writes guarantees despite sticky routing. Session state tracking without eviction policies leaks memory. Propagate session tokens via HTTP headers, enforce lag thresholds, and implement automatic primary fallback on drift.

Observability, Failover & Debugging

Routing layers require continuous telemetry to detect degradation before it reaches users. Combine Prometheus metrics with distributed tracing to correlate routing decisions with application error rates.

observability:
  metrics:
    prometheus_exporter: true
    scrape_interval: 15s
    custom_labels: ["routing_path", "replica_lag_ms", "pool_utilisation"]
  tracing:
    propagation: w3c_tracecontext    # required across proxy hops
    sampling_rate: 0.1
  circuit_breaker:
    failure_threshold: 5
    timeout: 30s
    half_open_requests: 3            # probe health before full restore
  failover:
    auto_promote: true
    split_brain_detection: quorum_vote

Key Prometheus queries for routing health:

# Pool saturation: ratio of active to max connections per replica
pgbouncer_pools_cl_active / pgbouncer_pools_cl_maxwait

# Replication lag in seconds (PostgreSQL)
pg_replication_lag_seconds{replica="az1"}

# Routing error rate by path
rate(proxy_query_errors_total[5m]) by (routing_path)

Failure modes: Noisy metrics trigger alert fatigue, masking genuine routing degradation. Automated failover during network partitions causes split-brain scenarios. Tracing context loss across proxy hops obscures root-cause analysis. Implement quorum-based promotion and enforce W3C trace propagation end-to-end.

Degraded-State Behaviour by Approach

The trade-off matrix above ranks approaches when everything is healthy. What separates them in production is how each one behaves while degraded — during a partition, a write spike, or a misconfiguration that nobody has noticed yet. Each row below maps back to a row in that matrix.

DNS-based discovery under partition. The failure is silent and delayed. Clients hold a resolved address for the whole TTL, so a replica that has been removed from rotation keeps receiving traffic until every resolver cache expires. Worse, negative caching means a newly added replica may not receive traffic for the same interval. The degraded state is therefore asymmetric: removal is slow, addition is slow, and the two overlap during a failover so the fleet is briefly split between old and new topology. The mitigation is not a shorter TTL alone — resolvers routinely ignore TTLs below 30 seconds — but a static fallback list plus active health probing that bypasses DNS entirely.

Proxy-layer splitting under write spike. The proxy’s queue is the failure surface. When replicas slow down, checkouts take longer, cl_waiting grows, and the proxy accumulates client connections it cannot service. Because the proxy is shared, this backpressure reaches every service at once rather than degrading one caller. The degraded state is bimodal: fine until the queue depth crosses the point where wait time exceeds client timeouts, then a cliff where nearly every request fails. Cap queue depth explicitly and let the proxy refuse connections rather than queue them — a fast failure feeds a circuit breaker, a slow queue starves one.

Application-layer routing under misconfiguration. The failure is per-service and invisible to everyone else. If one deployment ships a routing context bug that leaks a write session into a read path, only that service misbehaves, and the symptom surfaces as read-only-transaction errors in its logs rather than as a fleet-wide event. This is simultaneously the best and worst property of in-process routing: blast radius is small, but there is no central place to observe or fix it. Push routing decisions into a shared library with its own metrics so the failure is at least uniform in shape across services.

Sticky sessions under replica loss. Affinity is a promise about a node that may cease to exist. When the pinned replica is ejected, every session bound to it must be re-pinned, and the naive implementation re-pins them all to the same replacement — converting one node’s failure into a thundering herd on the next node. Re-pin with jitter, and treat the primary as the fallback target for the sessions that carry an unmet write watermark rather than for all of them.

The shared failure across all four. Every approach degrades badly if the read pool can empty completely. Any lag or health rule strict enough to eject a replica is strict enough to eject every replica during a fleet-wide event such as a bulk import or a vacuum storm. Decide in advance whether an empty read pool means fall back to the primary (protects correctness, risks overloading the primary) or serve stale (protects the primary, breaks freshness guarantees) — and encode the choice explicitly. This is the subject of graceful degradation when all replicas exceed the lag budget.

Capacity Planning for the Routing Tier

The routing tier has its own capacity limits that are easy to overlook because they are not the database’s limits. Three numbers govern it.

Client-side ceiling. max_client_conn on PgBouncer, or the equivalent front-end limit on ProxySQL, bounds how many application connections can exist at once. This should be sized against the application fleet — replicas × workers × per-worker pool — not against the database, because its purpose is to absorb far more client connections than the database could ever hold. Undersizing it converts a healthy database into an unreachable one.

Server-side ceiling. The per-database pool_size bounds how many backend connections the pooler opens. This must be sized against the replica’s max_connections with headroom subtracted for superuser slots, replication slots, and monitoring agents. The sum of pool_size across every pooler instance pointing at one replica is what matters — three poolers each configured for 50 connections will open 150, and a replica configured for 100 will start refusing them.

Throughput ceiling. A single pooler process is usually single-threaded for query dispatch and becomes CPU-bound well before either connection ceiling is reached, particularly with TLS enabled on both sides. The symptom is rising latency at flat connection counts. Scale it horizontally behind a load balancer rather than by raising limits, and keep TLS session resumption enabled so handshakes do not dominate the CPU budget.

A useful sanity rule: the routing tier should never be the component that fails first, because its failure is indistinguishable from a database outage to every caller. Size all three ceilings so that the replica saturates before the pooler does, then alert on the pooler’s own saturation as a leading indicator — see monitoring PgBouncer pool saturation with Prometheus for the specific series to watch.

Section Index

Proxy-Layer Read/Write Splitting

Implementing Read/Write Splitting at the Proxy Layer walks through ProxySQL rule authoring, query classification edge cases (SELECT ... FOR UPDATE, multi-statement transactions), and TLS configuration for the proxy-to-replica path. It covers the write-leakage failure mode and how to detect it before it reaches production.

• How to Implement Read/Write Splitting in Spring Data JPA — Spring-specific AbstractRoutingDataSource wiring with transaction-aware context holders.

ORM Middleware for Automatic Query Routing

ORM Middleware for Automatic Query Routing covers database router configuration in Django, SQLAlchemy engine switching, Prisma middleware hooks, and the async boundary pitfalls that silently route writes to read-only pools.

• Laravel Eloquent Read/Write Connection Configuration — the sticky flag and transaction handling that decide your replica utilisation.
• Go Read Replica Routing with pgx and database/sql — routing without an ORM, with the context-carried hint Go requires.

Connection Pool Architecture for Read Replicas

Connection Pool Architecture for Read Replicas details pool sizing formulas per replica, idle timeout tuning, connection validation queries, and affinity weighting across availability zones.

• Configuring PgBouncer for Read-Only Connection Pools — transaction vs session mode tradeoffs with annotated pgbouncer.ini.
• Avoiding Connection Exhaustion During Replica Failover — pre-failover drain patterns, queue depth caps, and fast reconnect configuration.
• Sizing pool_size and max_client_conn for Replica Pools — deriving both ceilings from the fleet and the database rather than guessing either.

Sticky Sessions in Distributed Database Reads

Managing Sticky Sessions in Distributed Database Reads details session token propagation via HTTP headers, lag-threshold fallback logic, and memory-bounded session state eviction.

• Pinning Reads to the Primary After a Write in a Web Session — a short read-from-primary window keyed on the user session to guarantee read-after-write.

Choosing a Read Replica Routing Proxy

Choosing a Read Replica Routing Proxy compares PgBouncer, ProxySQL, HAProxy, and pgpool-II across SQL-awareness, pooling mode, lag-aware routing, and failover — so you pick the right tool before wiring it into the read path.

• PgBouncer vs ProxySQL for Read Replica Routing — connection pooling vs SQL-aware query routing, and when to combine both.
• HAProxy vs ProxySQL for Read/Write Splitting — port/backend splitting with health checks vs protocol-level query parsing.
• pgpool-II vs PgBouncer for PostgreSQL Read Scaling — the PostgreSQL-native pairing, and why it is a division of labour rather than a choice.

Load Balancing Reads Across a Replica Pool

Load Balancing Reads Across a Replica Pool covers what happens after a statement has been classified as a read: which replica serves it, and why a fleet with even connection counts can still have one node carrying most of the work.

• Weighted vs Least-Connections Balancing for Read Replicas — choosing the algorithm by measuring query-cost variance rather than guessing.
• Lag-Aware Replica Weighting with HAProxy Agent Checks — turning replication lag into a gradual traffic shift instead of a cliff.
• Zone-Aware Read Routing to Cut Cross-AZ Data Transfer — keeping reads local without making each zone a single point of failure.
• Why DNS Round Robin Unbalances Read Replica Traffic — resolver caching, address selection, and the pinning they produce.
• Draining a Read Replica for Maintenance Without Dropping Queries — the bounded drain that lets in-flight reads finish.

Testing Read/Write Splitting Correctness

Testing Read/Write Splitting Correctness treats the split as production logic that needs tests: safety (no write reaches a replica), completeness (every eligible read leaves the primary), and coherence (a transaction never splits across nodes).

• Detecting Accidental Primary Reads in Production Traffic — finding the read traffic that never left the primary, and attributing it to a cause.
• Writing Integration Tests That Assert Query Routing — a real primary/standby fixture and the adversarial statement list that makes it worth having.
• Auditing ProxySQL Query Digests for Misrouted Statements — turning the digest table into a standing correctness check.
• Canary Rollout Plan for a New Read/Write Split — shape-coverage slice selection and criteria that include a replica-share floor.

Production-Readiness Checklist

• DNS TTL set to ≤ 30 s for primary and replica endpoints; SRV records used where supported
• Proxy query classification rules cover SELECT ... FOR UPDATE and SELECT ... FOR SHARE (routed to primary)
• PgBouncer or ProxySQL deployed in HA mode (active-passive or active-active); no single proxy SPOF
• Pool max_active set below replica max_connections with headroom for admin connections
• server_check_query = SELECT 1 enabled; server_idle_timeout ≤ 30 s
• Connection leak detection threshold configured (leak_detection_threshold_ms)
• Session affinity tokens propagated via HTTP header; eviction policy configured
• REPLICATION_LAG_THRESHOLD_MS set and tested; primary fallback on lag breach verified
• Prometheus metrics scraping routing_path, replica_lag_ms, and pool_utilisation labels
• W3C trace context propagated across proxy hops; correlation between routing path and error rate confirmed
• Circuit breaker thresholds set; quorum-based promotion configured to prevent split-brain
• TLS enabled on all proxy-to-replica connections (sslmode = require)
• Runbook documented: how to drain a replica, promote a standby, and rotate pool credentials without downtime

FAQ

Related

← Database Read Replicas & Connection Routing Patterns — architecture overview, topology design, and replication mode selection.

• Replication Lag & Consistency Management — measure and bound lag; configure freshness-based query routing and fallback strategies.
• Implementing Read/Write Splitting at the Proxy Layer — ProxySQL and PgBouncer rule authoring, write-leakage prevention, and TLS setup.
• Connection Pool Architecture for Read Replicas — pool sizing, idle timeout tuning, and AZ affinity weighting.
• Managing Sticky Sessions in Distributed Database Reads — session token propagation, lag-threshold fallback, and eviction policies.
• ORM Middleware for Automatic Query Routing — Django, SQLAlchemy, and Prisma routing middleware with async boundary guidance.
• Choosing a Read Replica Routing Proxy — a decision guide comparing PgBouncer, ProxySQL, HAProxy, and pgpool-II for read-replica routing.