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

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.

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.

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.

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.

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.