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 likeSELECT ... 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: 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