Database Proxy Low-Level Design: Query Routing, Connection Pooling, and Read-Write Splitting

Database Proxy Low-Level Design

A database proxy sits in the network path between application servers and the database. It multiplexes many application connections onto a smaller set of persistent backend connections, routes read queries to replicas, and routes writes to the primary — all transparently, without application code changes.

Proxy Role and Placement

Application instances connect to the proxy as if it were a database. The proxy maintains two connection pools internally: one to the primary and one to each replica. Benefits over direct connections:

Reduces total connections to the database (N app instances × M threads = many connections; proxy multiplexes to a fixed pool)
Centralizes read-write routing logic — no changes needed in application code
Provides a stable connection endpoint that survives backend failover

Query Parsing for Read-Write Routing

The proxy inspects each query to determine its type. Two approaches:

Keyword check: fast — check if query starts with SELECT. Misses edge cases like SELECT ... FOR UPDATE (which should go to primary).
Full SQL parse: accurate — parse the AST, check statement type and modifiers. Higher CPU cost but necessary for correctness.

Routing rules: SELECT without FOR UPDATE or FOR SHARE → replica pool. All others → primary.

Transaction Handling

Statements inside a transaction must all go to the same backend. When the proxy sees BEGIN, it pins the connection to the primary for the duration of the transaction. All statements until COMMIT or ROLLBACK are routed to that pinned primary connection. After commit, the connection returns to the pool and routing resumes normally.

Connection Pool Management

The proxy maintains a fixed-size pool of persistent connections to each backend. Application connections are multiplexed over this pool — many app-side connections share a smaller number of backend connections. This is the core value: a PostgreSQL server struggles with more than a few hundred connections; the proxy keeps backend count low while allowing thousands of application threads to connect.

Connection Lifecycle and Health

Before returning a connection from the pool, validate it with a lightweight ping (SELECT 1 or the driver's isValid() check). Discard connections that fail validation and open a replacement. Close connections that have been idle longer than a configurable timeout (e.g., 10 minutes) to release resources on the database side.

Prepared Statement Proxying

Prepared statements are bound to a specific backend connection. When a connection is returned to the pool and later reused for a different application session, that session's prepared statements are not present on the connection. The proxy tracks which statements are prepared on each backend connection and re-prepares them transparently when the connection is reused for a session that needs them.

Replication Lag Awareness

If a write is immediately followed by a read (read-your-writes pattern), routing the read to a replica risks returning stale data. The proxy tracks replication lag per replica by querying pg_stat_replication or a heartbeat table. If lag exceeds a configurable threshold (e.g., 1 second), route reads to the primary until replicas catch up.

Query Rewriting and Failover

Query rewriting: inject a statement timeout before each query (SET LOCAL statement_timeout = 5000) to prevent runaway queries from blocking the connection indefinitely
Primary failover: on primary connection failure, the proxy promotes a replica (or connects to a pre-promoted replica via a floating IP/DNS update) and redirects all write traffic automatically
Replica failover: remove a failed replica from the read pool; distribute its traffic across remaining replicas

Observability

The proxy emits metrics per backend:

Queries per second segmented by type (read/write) and destination
Latency histograms with p50, p95, p99 per query fingerprint
Connection pool utilization (active / max) per backend
Replication lag per replica
Circuit breaker state per backend

A circuit breaker opens per backend on repeated connection failures, preventing the proxy from hammering a failing database. It half-opens after a cooldown to test recovery.

{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “How does a database proxy implement read-write splitting, and what consistency risks does it introduce?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “The proxy inspects each incoming query's SQL verb: DML statements (INSERT, UPDATE, DELETE, CALL) and DDL are sent to the primary; SELECT statements are routed to a read replica selected by weighted round-robin or least-connections from the replica pool. The consistency risk is replication lag: a client that writes a row and immediately reads it may read from a replica that hasn't yet applied the write, seeing stale data. Mitigations: (1) Session stickiness — after a write in a session, pin that session to the primary for a configurable window (e.g., 500ms). (2) Read-after-write via GTID/LSN — track the LSN of the last write in the session, route reads to replicas only when they report having applied at least that LSN. (3) Explicit hint comments (/* read-primary */) that applications embed to force reads to the primary for critical paths.”
}
},
{
“@type”: “Question”,
“name”: “Design the connection pooling logic in a database proxy for 10,000 application threads connecting to a 500-connection Postgres primary.”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Implement a server-side connection pool (like PgBouncer in transaction mode): the proxy maintains a pool of M backend connections to Postgres (M <= 500), shared across all N application connections (N = 10,000). In transaction-mode pooling, a backend connection is leased to an application connection only for the duration of a transaction and returned to the pool immediately after COMMIT or ROLLBACK. This works because Postgres connections are expensive (8–10MB RAM each, one background process) while transactions are short-lived. Use a semaphore of size M to gate backend connection acquisition; application threads that cannot acquire a connection wait in a FIFO queue with a configurable timeout (e.g., 5 seconds) before receiving a 'pool exhausted' error. Set M conservatively below Postgres's max_connections to leave headroom for admin connections and replication."
}
},
{
"@type": "Question",
"name": "How does the proxy detect a primary failover and reroute traffic with minimal downtime?",
"acceptedAnswer": {
"@type": "Answer",
"text": "The proxy runs a health-check goroutine per backend that issues a lightweight SQL ping (SELECT 1) every 1–2 seconds. If the primary fails N consecutive health checks (e.g., N=3, so failure detected within 3–6 seconds), the proxy marks it unhealthy and queries a topology discovery endpoint — either a DNS name that your HA manager (Patroni, AWS RDS Multi-AZ) updates on failover, or a direct API call to the cluster manager to identify the new primary. Once the new primary is identified, the proxy drains in-flight transactions (waits for them to complete or times them out), then updates its routing table to point writes at the new primary. In-flight write transactions that were mid-flight to the old primary are returned a connection error; applications must retry with idempotency. Use exponential backoff on health-check reconnect attempts to avoid thundering herd against a recovering primary."
}
},
{
"@type": "Question",
"name": "What query routing features beyond read-write splitting are valuable to implement in a database proxy?",
"acceptedAnswer": {
"@type": "Answer",
"text": "Four high-value features: (1) Query firewall — parse the AST of incoming SQL and reject or log queries matching dangerous patterns (full-table scans without a WHERE clause on tables over N rows, SELECT * on wide tables, missing LIMIT on unbounded queries) before they reach the database. (2) Rate limiting per client identity — track query rate by application username or source IP and drop connections that exceed a threshold, protecting the database from a runaway application instance. (3) Query mirroring — send a copy of production read traffic to a shadow database (e.g., a candidate upgrade candidate or a new index configuration) asynchronously, comparing result sets or latencies without affecting production. (4) Statement timeout injection — transparently prepend SET statement_timeout=Nms to each query based on the client's configured SLA, overriding per-session defaults set by application code and ensuring no runaway query holds locks indefinitely."
}
}
]
}