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

Source: https://www.techinterview.org/post/3233469729/lld-database-proxy/
Updated: 2026-04-17 · techinterview.org

## 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.

See also: [Netflix Interview Guide 2026: Streaming Architecture, Recommendation Systems, and Engineering Excellence](https://www.techinterview.org/post/3233460274/netflix-interview-guide-2026-streaming-architecture-recommendation-systems-and-engineering-excellence/)

See also: [Scale AI Interview Guide 2026: Data Infrastructure, RLHF Pipelines, and ML Engineering](https://www.techinterview.org/post/3233460275/scale-ai-interview-guide-2026-data-infrastructure-rlhf-pipelines-and-ml-engineering/)

See also: [Stripe Interview Guide 2026: Process, Bug Bash Round, and Payment Systems](https://www.techinterview.org/post/3233460268/stripe-interview-guide-2026-process-bug-bash-round-and-payment-systems/)