Designing a Database Proxy: Connection Pooling, Query Routing, and Read/Write Splitting at Scale

At some point, every production database system hits the same wall. Your application servers have scaled horizontally. Your database is still a single node. Each server keeps its own connection pool. The math catches up with you: 50 app servers times 20 connections each is 1,000 connections sitting on Postgres, most of them idle. The database spends more time managing connections than executing queries.

This is not a problem you solve in application code. You solve it at the infrastructure layer, with a database proxy.

A database proxy sits between your application and your database. It speaks the same wire protocol as the database, so your application does not know it is there. Behind that transparent interface, the proxy manages connections, routes queries to the right backend, and handles failover when nodes go down.

The three mainstream examples are PgBouncer (PostgreSQL only, connection pooling focus), ProxySQL (MySQL/MariaDB, full query routing), and Vitess (MySQL, horizontal sharding). They make different tradeoffs. Understanding why requires understanding what each layer of a proxy actually does.

The Connection Problem

Postgres and MySQL both have a hard limit on simultaneous connections. Each connection costs memory. Postgres forks a process per connection; at high connection counts, the scheduler overhead alone becomes measurable. MySQL is threaded but still allocates per-connection buffers.

The standard application-level answer is a connection pool. Your ORM or database driver maintains a pool of persistent connections and hands them to threads on demand. This works well until you have many application processes, each with its own pool.

The problem is that pools do not coordinate. If you have 30 app servers and each maintains a minimum pool of 10, your database sees 300 connections at minimum, regardless of actual query load. Connection slots are a finite resource. Running Postgres with max_connections = 500 and 30 app servers each with a pool of 20 leaves you 100 slots of headroom for migrations, monitoring, and admin queries. That headroom disappears quickly.

A database proxy solves this by being the single connection owner. Your app servers connect to the proxy, which maintains a smaller, shared pool to the actual database. The proxy multiplexes thousands of application connections onto dozens of database connections.

Pooling Strategies: What You Give Up at Each Level

There are three pooling modes, and the tradeoffs between them are real. Choosing the wrong one produces subtle correctness bugs that are painful to diagnose.

Session pooling assigns a database connection to a client for the duration of the client’s session. This is the safest mode. The client can use any PostgreSQL feature: prepared statements, advisory locks, SET configuration variables, LISTEN/NOTIFY, temporary tables. The proxy is just forwarding traffic. The downside is that the multiplexing benefit is minimal. If your client is connected but idle between queries, the database connection sits idle too.

Transaction pooling assigns a connection only for the duration of a transaction. The moment the client commits or rolls back, the connection returns to the pool. This gives much better utilization. A connection handling 10ms transactions can serve many clients per second. The downside is that session-level state breaks. Prepared statements cannot persist across transactions because the next transaction may land on a different backend connection. Advisory locks acquired with pg_advisory_lock() are silently lost. Any SET variable is reset. Applications built expecting session-level statefulness need changes before transaction pooling works correctly.

Statement pooling is the most aggressive mode. The connection returns to the pool after every individual statement, even within an explicit transaction. This mode is incompatible with multi-statement transactions because the statements may execute on different connections against different backends. PgBouncer supports this mode but its use cases are narrow (read-only analytics workloads where each query is self-contained).

The practical default for most web applications is transaction pooling, with a careful audit of any code that uses session-level features.

// This pattern breaks under transaction pooling.
// The SET only applies to the connection for that transaction.
// The next query may run on a different connection.

async function queryWithTimeout(pool: Pool, query: string): Promise<QueryResult> {
  const client = await pool.connect();
  try {
    // This SET is session-scoped in Postgres. Under transaction pooling,
    // the connection returns to the pool after the transaction ends,
    // and the next client gets a connection where the SET may not apply.
    await client.query("SET statement_timeout = '5s'");
    return await client.query(query);
  } finally {
    client.release();
  }
}

// Safe alternative: use query parameters or per-query options.
async function queryWithTimeoutSafe(pool: Pool, query: string): Promise<QueryResult> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    // SET LOCAL only applies within the current transaction.
    // This is safe under transaction pooling.
    await client.query("SET LOCAL statement_timeout = '5s'");
    const result = await client.query(query);
    await client.query("COMMIT");
    return result;
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

Read/Write Splitting and Query Classification

Once you have a primary with one or more read replicas, you want to route read queries to replicas to distribute load. The naive approach is to require the application to maintain two connection strings: one for writes, one for reads. This works but puts the routing logic in every service that touches the database.

A proxy can handle this automatically through query classification. The proxy inspects each query and routes it based on whether it is a write (INSERT, UPDATE, DELETE, DDL) or a read (SELECT). Writes go to the primary. Reads go to replicas, distributed across them by round-robin or weighted routing.

Query classification sounds simple. It is not. The edge cases are where production bugs come from.

Transactions containing writes must stay on the primary for the entire transaction. A transaction that issues a SELECT after an UPDATE needs to see its own uncommitted write. Replicas do not have it yet. The proxy must detect when a transaction starts and pin it to the primary until commit.

SELECT ... FOR UPDATE is a write. It acquires a row lock. It must route to the primary.

Stored procedures and functions may contain writes. Static analysis cannot reliably classify them without knowing the function body. Proxies typically let you annotate functions or default them to primary routing.

Replication lag affects read consistency. A replica may be 50-500ms behind the primary. If your application writes a record and immediately reads it back, the read may miss the write. Proxies like ProxySQL track replication lag per replica and can route reads only to replicas within a configured lag threshold. They can also route reads to the primary when the application is in a write-heavy context.

// Application-level hint to force primary routing when needed.
// Many proxy implementations respect comments as routing hints.

async function readAfterWrite(
  pool: Pool,
  userId: string,
  updates: UserUpdates
): Promise<User> {
  // Write goes to primary.
  await pool.query(
    "UPDATE users SET name = $1 WHERE id = $2",
    [updates.name, userId]
  );

  // This read needs to see the write. Force it to the primary.
  // ProxySQL and some other proxies respect the /*+ PRIMARY */ hint.
  const result = await pool.query(
    "/*+ PRIMARY */ SELECT * FROM users WHERE id = $1",
    [userId]
  );

  return result.rows[0];
}

Connection Multiplexing at the Wire Level

The proxy speaks the Postgres or MySQL wire protocol natively. When a client connects, the proxy completes the authentication handshake. Internally, it maps that client to a backend connection from the pool. The client never negotiates directly with Postgres.

This has an important consequence for prepared statements. Postgres prepared statements are stored on the backend connection. If the client sends PREPARE stmt AS SELECT ... and later EXECUTE stmt, both must reach the same backend connection. Under transaction pooling, the proxy cannot guarantee this.

PgBouncer’s solution is to track prepared statements at the proxy layer and re-issue them when a new backend connection is assigned. This adds latency on the first use after reassignment but is transparent to the client. Some proxies take the simpler approach: they disable prepared statement support in transaction pooling mode and let the client fall back to extended query protocol without persistence.

Vitess goes further. It rewrites queries at the proxy layer: rewriting queries to add keyspace routing, rewriting certain MySQL syntax for compatibility, or transparently handling cross-shard queries by issuing multiple queries and merging results. This query rewriting capability makes Vitess much more powerful but also means it cannot be dropped in transparently. It requires schema design alignment.

Health Checking and Failover

A proxy is only useful if it correctly tracks which backends are alive. Health checking has its own tradeoffs.

Passive health checking marks a backend as unhealthy after a configurable number of consecutive connection errors or query timeouts. This is cheap but slow to detect failure. If a backend is taking 30 seconds to respond before timing out, you are accumulating failing requests for 30 seconds before the backend is marked down.

Active health checking sends periodic lightweight queries (often SELECT 1) to every backend on a heartbeat interval. This detects failure within one heartbeat interval, typically 1-10 seconds. The cost is one extra connection and periodic query overhead per backend. For most setups this is acceptable.

Failover behavior on primary failure depends on whether the proxy is aware of the replication topology. A basic proxy just removes the failed node from the pool. A topology-aware proxy (or one integrated with a tool like Patroni, Orchestrator, or a cloud provider’s RDS failover) detects which replica was promoted to primary and updates its routing rules automatically.

// Simplified health-check loop as you might implement it
// in a custom proxy or connection manager.

interface BackendNode {
  host: string;
  port: number;
  role: "primary" | "replica";
  healthy: boolean;
  consecutiveFailures: number;
}

async function healthCheckNode(node: BackendNode, db: Pool): Promise<void> {
  const FAILURE_THRESHOLD = 3;

  try {
    await db.query("SELECT 1");
    // Reset on success.
    node.consecutiveFailures = 0;
    node.healthy = true;
  } catch {
    node.consecutiveFailures++;
    if (node.consecutiveFailures >= FAILURE_THRESHOLD) {
      node.healthy = false;
      console.error(
        `Backend ${node.host}:${node.port} marked unhealthy after ${FAILURE_THRESHOLD} consecutive failures`
      );
    }
  }
}

function startHealthChecks(
  nodes: BackendNode[],
  pools: Map<string, Pool>,
  intervalMs: number
): NodeJS.Timeout {
  return setInterval(async () => {
    await Promise.allSettled(
      nodes.map((node) => {
        const pool = pools.get(`${node.host}:${node.port}`);
        if (!pool) return Promise.resolve();
        return healthCheckNode(node, pool);
      })
    );
  }, intervalMs);
}

Query Caching at the Proxy Layer

Some proxies (ProxySQL is the most notable) support query caching. A TTL is configured per query pattern. Identical queries within the TTL window return the cached result without hitting the database.

Proxy-layer caching is simpler to operate than application-layer caching (no cache invalidation code in your services) but it is also less precise. The cache key is the full query string. It cannot account for row-level invalidation. If you cache SELECT count(*) FROM orders WHERE status = 'pending' for 5 seconds, every order creation during those 5 seconds is invisible to callers hitting the cache.

The use cases where proxy caching genuinely helps: dashboard aggregations that tolerate stale data, reference data queries (country codes, configuration rows) that change rarely, and read-heavy analytics paths where eventual consistency is acceptable.

For anything requiring strong consistency or fine-grained invalidation, application-level caching with explicit invalidation logic is the right tool.

Tradeoffs

Strategy	Benefit	Cost	When to Use
Session pooling	Full Postgres feature compatibility	Low multiplexing ratio	Long-lived connections, advisory locks, LISTEN/NOTIFY
Transaction pooling	High multiplexing, fewer DB connections	Breaks prepared statements, session-level state	Stateless web apps, short transactions
Statement pooling	Maximum throughput for read-only	Incompatible with multi-statement transactions	Analytics, read-only reporting queries
Read/write splitting	Scales reads horizontally	Replication lag visibility required	Read-heavy workloads with replicas
Proxy-layer caching	Reduces DB load for repeated reads	No fine-grained invalidation	Reference data, tolerable staleness
Application-level routing	Full control, no proxy dependency	Routing logic in every service	Microservices with distinct DB needs

Production Considerations

Pool sizing is not set-and-forget. The right pool size depends on your database’s max_connections, the number of proxy instances you run, and your query latency profile. A common formula for Postgres is to target roughly (2 * num_cores) + effective_disk_count server-side connections for CPU-bound workloads. Start there and adjust based on measured wait time in the pool.

Multiple proxy instances need to coordinate their pool limits. If you run 3 PgBouncer instances each with max_db_connections = 100, your primary sees up to 300 connections. Account for this when sizing.

Monitor pool wait time, not just utilization. A pool at 80% utilization is not a problem. A pool where requests wait more than 5ms for a connection is a problem. Expose pool_wait_time_ms as a metric and alert on it.

Proxy is a single point of failure if you run one instance. Run at least two instances behind a load balancer or use a sidecar pattern (one proxy per app pod in Kubernetes). The sidecar pattern eliminates network hops but increases the number of proxy processes; coordinate their connection limits carefully.

Test failover before an incident. Kill the primary with the system under load and verify that the proxy correctly routes to the new primary within your acceptable window. Do this regularly, not just once at setup. Replication topology changes (promotions, replica additions) can drift from what the proxy expects.

Prepared statements in transaction pooling mode require driver configuration. Most PostgreSQL drivers prepare statements automatically for performance. Under transaction pooling, this breaks. Configure your driver to disable server-side prepared statements (prepared_statements: false in node-postgres, prepareThreshold: 0 in JDBC) or use PgBouncer’s prepared statement tracking if your version supports it.

Query routing hints need discipline. If your application uses comment-based routing hints to force primary reads, those hints must be consistently applied. A single service that forgets the hint after a refactor introduces a read-after-write inconsistency that is hard to reproduce under test conditions.

Proxy vs. Application-Level Routing

The proxy approach centralizes routing logic and removes it from application code entirely. The application does not need to know about replicas, failover, or pooling strategy. That is a genuine operational win.

The cost is that the proxy becomes a critical path component with its own operational surface. You need to monitor it, size it, update it, and handle its failure. For small systems with one or two services, the overhead may not be worth it. Connection pooling in the application layer (pg-pool, HikariCP, etc.) is sufficient.

The proxy approach pays off when you have multiple services connecting to the same database, when connection count on the primary is a real constraint, or when you want to add read replicas without changing application code. At that point, centralizing the routing and pooling logic in a proxy is the right call.

The final design consideration is transparency. A proxy that works at the wire protocol level gives you zero application changes. A proxy that rewrites queries or requires schema annotations (Vitess, for example) gives you more capability but ties your schema design to the proxy’s semantics. Choose based on what you actually need to scale, not what might be useful later.