db-proxy

How it is built

Architecture overview

A database connection is expensive on the server side. PostgreSQL gives each connection its own process, MySQL its own thread; each holds memory, takes a slot in a fixed limit, and costs a TLS handshake plus authentication to set up. An application with hundreds of instances, each wanting a pool, can ask for thousands of connections when the database only works well with tens of active ones. A connection pooler sits between clients and the database, accepts many cheap client connections and runs their work over few server connections. A proxy goes one step further: it understands the protocol well enough to route (reads to replicas, writes to the primary), balance, protect and observe traffic.

PgBouncer's feature page shows how little the pooler needs per client: low memory, 2 kB per connection by default, because it does not need to see full packets at once, and it is not tied to one backend server, so the databases behind it can sit on different hosts (source: PgBouncer features). The design question is how much of the protocol the proxy must understand. Pass bytes through and it is fast but blind. Parse everything and it can rewrite and route, but it becomes a second database front end that must track every protocol feature. Real poolers sit between: they parse the framing and the few messages that decide state (startup, authentication, transaction boundaries, parameter changes) and treat the rest as opaque bytes.

Parts:

• Network core — one thread serves thousands of sockets, splitting bytes into messages — epoll event loop, Length-prefix framing
• Protocol and query parser — reads startup, auth and query messages; classifies statements — Recursive-descent parsing
• Transport security and login — encrypts client links, checks credentials — TLS 1.3, Mutual TLS, scram-authentication (no page yet)
• Connection pool — reuses server connections across clients, queues waiters — connection-pooling (no page yet), built on MPMC bounded queue
• Router — sends writes to the primary and reads to replicas — read-write-splitting (no page yet), built on Health checking, Power of two choices and Primary-backup replication
• Fault isolation — stops sending to a failing backend — Circuit breaker
• Admission control — limits request rate and in-flight work — Token bucket, adaptive-concurrency-limits (no page yet)
• Timers — waiting, idle and lifetime limits for thousands of connections — Hierarchical timing wheel
• Operations — keeps the proxy serving, measured, tested and recoverable — hdr-histogram (no page yet), Graceful draining, Process supervision, Point-in-time recovery, Deterministic simulation testing, Coverage-guided fuzzing

 clients (many)                      proxy                                  backends (few)
 ┌──────────┐  TLS   ┌──────────────────────────────────────────┐   ┌─────────────────┐
 │ app inst │───────►│ event loop ─► framing ─► parser/auth     │   │ primary         │
 │ app inst │        │      │                      │            │   └─────────────────┘
 │   ...    │        │      ▼                      ▼            │   ┌─────────────────┐
 └──────────┘        │ admission (token bucket, limit)          │   │ replica 1       │
                     │      ▼                                   │   └─────────────────┘
                     │ router ─► pool queue ──checkout──► server│──►┌─────────────────┐
                     │ (rw split, p2c, health, breaker)         │   │ replica 2       │
                     │ timers (wait, idle, lifetime)            │   └─────────────────┘
                     └──────────────────────────────────────────┘

How the parts fit together

Network core

The proxy is almost pure I/O, so it uses an epoll event loop: sockets are non-blocking and registered once; each epoll_wait returns only those that are ready, so the cost tracks activity, not the number of connections. One loop per thread, each with its own listener via SO_REUSEPORT, scales across cores without sharing state; this is the layout Odyssey describes with worker threads that each handle authentication and proxying (source: Odyssey README), and PgCat's Tokio runtime does the same on async tasks (source: PgCat README). A single-threaded loop is simpler and needs no locking, and a pooler often saturates the network before a core.

Bytes on a stream are not messages. The PostgreSQL protocol frames them with a type byte and a 4-byte length (source: PostgreSQL protocol documentation), the standard length-prefix framing: read the header, then exactly that many bytes. Two rules from the framing entry apply directly. Enforce a maximum frame size before allocating, since the length is attacker-controlled input. And one corrupt length desynchronizes the stream for good, so close the connection. A proxy that does not buffer whole messages (hence PgBouncer's small per-client memory) forwards large result rows as they arrive, which keeps memory per connection flat.

Protocol and query parser

The parser has two jobs, with very different cost. The first is message-level: recognizing startup, authentication, query, parse/bind/execute, sync and terminate messages, and noticing transaction state (idle, in transaction, failed). That is a small state machine over framed messages, and is enough for session and transaction pooling.

The second is statement-level: reading the SQL text to decide where a statement can run. A recursive-descent parser fits: one function per grammar rule, linear time, no backtracking, order of 300 MB/s on a simple grammar (source: the parsing entry). A proxy does not need the full SQL grammar. It needs to find the first keyword (SELECT against INSERT, UPDATE, DELETE), spot statements that change session state (SET, LISTEN, PREPARE, advisory locks), and see through comments and WITH clauses to find data-modifying statements hidden in a SELECT. Enforce a nesting depth limit, as deep recursion grows the native stack. When the parser is unsure, the safe answer is "treat as a write, route to the primary".

Transport security and login

Clients connect over TLS 1.3: one round trip to set up, only forward-secret AEAD suites. The proxy terminates TLS for clients and opens a second, separately configured TLS session to the server, since the two links have different peers and certificates. TLS costs one handshake per new connection, which is another reason clients should connect to a nearby proxy and reuse connections.

On both links the proxy can require mutual TLS: the peer presents a certificate too, so a service connecting to the proxy, and the proxy connecting to a database node, each carry a verified machine identity. That lets the proxy map a client certificate to a pool and user without a password on the wire, and keeps an unknown host from opening a server-side session. Certificates are short-lived and rotated, so the proxy reloads them without dropping established sessions.

Authentication is where poolers and databases collide. SCRAM-SHA-256 (RFC 7677) lets the client prove it knows the password without sending it, which means a proxy that only forwards bytes cannot also see who the user is. The proxy has to run the authentication exchange itself, so it needs verifier material for each user: PgBouncer reads it from an auth_file or looks it up with an auth_query run as a configured auth_user (source: PgBouncer configuration). PgCat connects to Postgres using MD5 and SCRAM-SHA-256 and can use an auth query so no clear-text passwords sit in its config (source: PgCat README). The proxy then logs into the server with its own credentials for that user and pool. The cost of this arrangement: the proxy holds sensitive material, so it is a high-value target and belongs in the same trust zone as the database.

Connection pool

The pool maps client sessions to server connections. PgBouncer's three modes define the contract:

• Session pooling: a server connection belongs to a client for the whole connection. Supports every PostgreSQL feature.
• Transaction pooling: a server connection belongs to a client only during a transaction, then returns to the pool. Breaks session features by design: SET/RESET, LISTEN, PREPARE/DEALLOCATE, session-level advisory locks and WITH HOLD cursors do not work, and the application has to cooperate (source: PgBouncer features, SQL feature map).
• Statement pooling: transaction pooling with multi-statement transactions disallowed.

Transaction pooling gives the highest multiplexing, which is why it is the common choice for web back ends. In session mode the server connection is reset when it returns to the pool; PgBouncer's server_reset_query defaults to DISCARD ALL and runs only in session mode, since in transaction mode clients must not use session state (source: PgBouncer configuration). Odyssey adds a refinement: it tracks transaction state, and if a client disconnects unexpectedly it cancels and rolls back the abandoned transaction before the connection goes back to the pool (source: Odyssey README).

Sizing knobs: default_pool_size (default 20 per user/database pair), reserve_pool_size for bursts, max_client_conn (default 100) as the global cap on client connections, and query_wait_timeout, which limits how long a query may wait for a server connection (source: PgBouncer configuration). The waiting clients form a queue; with many threads producing checkouts and returns, the free list of server connections is a natural fit for a bounded MPMC queue: one CAS and a store per operation, order of 20-50 ns uncontended, bounded so a flood cannot grow memory. A smaller pool is usually better than a larger one: the database does more work per second at its optimum concurrency, and the queue in the proxy is cheap compared with contention inside the database.

Router

Read-write splitting (no page yet) sends writes and in-transaction statements to the primary, and eligible reads to replicas. PgCat load-balances read queries between replicas and the primary automatically, and reroutes around broken replicas, validated by regular health checks (source: PgCat README). The rules that keep it correct:

• Inside a transaction everything goes to one backend; once a transaction has written, everything stays on the primary.
• A client that just wrote must not read an old value from a lagging replica: route to the primary for a while after a write, or only choose replicas whose lag is under a bound.
• Anything the parser cannot classify goes to the primary.

The replicas the router reads from are fed by primary-backup replication: the primary orders every write and ships it to the standbys, so a replica is always some distance behind. The acknowledgement rule decides how far: with asynchronous standbys a replica read can lag, which is why the lag bound above exists, and a primary crash can lose the unshipped tail. The router never decides who the primary is. It follows the topology the database layer reports, and an external arbiter, not the proxy, promotes a standby after a failure.

Among healthy replicas, power of two choices picks: sample two at random, send to the one with fewer in-flight queries. It needs only a local, possibly stale load figure per backend, and keeps the maximum load far below random choice (1 million balls into 1 million bins: max load about 9 with one choice, 4 with two, in simulation; source: the P2C entry). Which replicas are eligible comes from health checking: active probes plus passive observation of real errors. HAProxy defaults to checks every 2 s with 3 failures to mark down and 2 successes to mark up; passive ejection in Envoy defaults to 5 consecutive 5xx, a 30 s ejection and at most 10 % of hosts (source: the health-checking entry).

Fault isolation

When a backend keeps failing, sending more queries makes it worse and ties up client connections on timeouts. A circuit breaker per backend counts failures, opens after a threshold (with a minimum call volume first, so one error does not trip it), rejects at once while open, then lets a few trial calls through before closing again. For a proxy, "reject at once" means the router tries another replica, or returns a clear error to the client, instead of holding a connection for the full timeout.

Admission control

Without limits, a burst turns into queueing delay for everyone. A token bucket per client, user or tenant enforces an average rate with a burst allowance in about 16 bytes of state per key. A limit on concurrent in-flight queries per backend is better when the cost of a query varies a lot: an adaptive limit raises the allowance while latency is flat and cuts it when latency climbs (adaptive-concurrency-limits, no page yet). When the limit is hit, requests wait in the bounded queue or are rejected with an error that tells the client to back off, so overload pushes back to the application instead of piling up in the proxy.

Timers

Every connection carries several deadlines: login timeout, wait timeout for a server connection, idle timeout, maximum server connection lifetime, query timeout. With tens of thousands of connections, a heap of timers costs log n per start and stop. A hierarchical timing wheel makes start and cancel O(1): timers are bucketed by expiry into wheels of slots, a 64-bit occupancy bitmap per level finds the next busy slot in one instruction, and Tokio's layout of 6 levels of 64 slots at 1 ms covers about 2.2 years (source: the timing-wheel entry). Most timers are cancelled before they fire, which suits the wheel.

Operations

Latency percentiles. hdr-histogram (no page yet) records the time a query spends waiting for a server connection and the time the proxy hop adds. A log-linear histogram keeps bounded relative error from microseconds to seconds, so p99 and p99.9 of the checkout wait are read from one cheap structure per pool. This is the signal that shows pool exhaustion early, and the one the adaptive concurrency limit and the wait timeout are tuned against.

Supervision and draining. A process supervisor starts the proxy, reaps its exit and restarts it under a delay and burst limit, so a crash loop does not hammer the database with reconnects. On a deploy or scale-in it sends SIGTERM, and the proxy uses graceful draining: stop accepting new clients, let in-flight transactions finish until a deadline, release server connections back, then exit. Clients reconnect to another proxy instance and resume, which is how an online restart stays an upgrade, not an outage.

Recovery. The proxy holds no durable data, but its job depends on a database that does. Point-in-time recovery (base backups plus an archived change log) lets the team rebuild the primary as of the moment before a bad migration or a runaway write that the proxy faithfully forwarded. After the restore, the proxy's pool is pointed at the rebuilt primary and its health checks admit it again.

Testing. Deterministic simulation testing runs the proxy, a fake client population and fake backends in one seeded thread with simulated network and clock, so a failover race such as a backend dying with a half-read transaction replays from its seed. Coverage-guided fuzzing feeds the framing and message parser with mutated byte streams, keeping any input that reaches a new code edge; it targets the attacker-controlled length fields and truncated startup messages described under the network core.

Data and control flow

A client connects:

1. Accept on the listener; the event loop registers the socket.
2. TLS handshake; read the startup message (user, database, parameters).
3. Authenticate (SCRAM exchange using stored verifier data); check connection caps and rate limits.
4. Assign the client to the pool for (user, database).

A query in transaction pooling mode:

1. Read a framed message from the client.
2. Parse enough to classify it; check that it uses no session-only features.
3. Pick the backend: primary for writes, a healthy replica by power of two choices for reads.
4. Check out a server connection from that pool; if none is free, wait in the queue under the wait timeout.
5. Forward the message; stream the response back frame by frame.
6. When the server reports the transaction is idle, return the server connection to the pool.

A backend fails:

1. Probes or live errors cross the failure threshold; the health state turns down and the circuit opens.
2. The pool closes its connections to that backend; queued and new requests go to the others.
3. Trial connections close the circuit when probes succeed again.

Scaling

1. Clients per proxy are bound by file descriptors and per-connection memory (2 kB each in PgBouncer). Raise ulimit -n, and set max_client_conn with the descriptor budget in mind.
2. Throughput per proxy is bound by one core's loop in a single-threaded design (the case when the proxy process is single-threaded). Run several proxy processes behind SO_REUSEPORT, or use a multi-threaded proxy (Odyssey, PgCat).
3. Server connections are bound by the database. Keep the total of all pools below the database's connection limit, with headroom for admin sessions.
4. Proxy availability: a single proxy is a single point of failure. Run two or more, with a health-checked virtual address or client-side balancing; supported online restart (PgBouncer's online restart and upgrade without dropping clients) keeps deploys from becoming outages.
5. Latency added is one extra hop, normally well under a millisecond on the same network; the queueing delay under overload is the larger risk.

Failure modes

Real implementations

The alternative: pooling inside the application (a library pool per process). It adds no hop and no new component, and is enough when the number of application processes is small and stable. Pick a proxy when instance counts are large or elastic (serverless, many pods), when you need one place to route reads or fail over, or when you cannot change the application.

Sources

• PgBouncer features and configuration documentation: pool modes, SQL feature map, memory per connection, defaults. features, config
• PostgreSQL documentation, Frontend/Backend Protocol: message framing. docs
• RFC 7677, SCRAM-SHA-256 and SCRAM-SHA-256-PLUS SASL Mechanisms. RFC
• Odyssey README. repo
• PgCat README. repo
• ProxySQL repository. repo