PostgreSQL connection poolers sit between application clients and database servers. Their simplest job is to cap the number of backend PostgreSQL connections, but modern poolers can do much more: route reads to replicas, route writes to primaries, steer traffic to shards, fail around unhealthy servers, and mirror production queries into test clusters.
PgCat is a useful reference design because it combines PgBouncer-style pooling with query-aware routing. It supports session and transaction pooling, read load balancing, health-check based failover, sharding via explicit SQL directives or parsed keys, live configuration reloads, and Prometheus metrics. The important transferable pattern is not the specific project; it is how a database proxy safely multiplexes stateful SQL sessions while making routing decisions from protocol, transaction, and query context.
This topic focuses on the engineering mechanics behind that proxy layer: what state a pooler must preserve, when transaction pooling is safe, how read/write routing works, how shard routing can be made explicit, and which failure modes appear when a proxy hides many physical database connections behind many more logical clients.
In session pooling, one client connection is pinned to one server connection for the lifetime of the client session. This preserves PostgreSQL session state such as prepared statements, temporary tables, advisory locks, SET variables, and LISTEN/NOTIFY. The tradeoff is poor multiplexing: a mostly idle client still occupies a backend server connection.
In transaction pooling, a server connection is assigned only for the duration of a transaction, then returned to the pool. This provides a much higher multiplexing ratio for web workloads with short transactions, but it breaks assumptions that state persists on a particular backend connection. Applications must replace session-scoped features with transaction-scoped alternatives such as SET LOCAL and pg_advisory_xact_lock.
A pooler that understands enough SQL can route safe reads to replicas and writes to the primary. PgCat describes a built-in parser that interprets SELECT queries as replica candidates while routing writes and explicit transactions to the primary. Because SQL can hide side effects in functions, CTEs, locks, and transaction settings, a router must fail closed: ambiguous statements should go to the primary or the configured default role.
A database pooler is not just a TCP load balancer. It must understand authentication, transaction boundaries, server health, client state, backend state, and sometimes SQL intent. Every optimization that increases multiplexing also reduces how much session state can safely leak across transactions.
The pooler accepts client connections on the PostgreSQL wire protocol, authenticates them, and maps each client to a configured pool. In session mode, it borrows a backend connection once and keeps it until the client disconnects. In transaction mode, it waits for a transaction boundary, borrows a backend for the transaction, forwards messages, then returns the backend when the transaction ends.
type PoolMode string
const (
SessionMode PoolMode = "session"
TransactionMode PoolMode = "transaction"
)
func assignBackend(client *Client, pool *Pool, mode PoolMode) (*Backend, error) {
switch mode {
case SessionMode:
if client.backend != nil {
return client.backend, nil
}
backend, err := pool.Borrow(client.DefaultRole())
if err != nil {
return nil, err
}
client.backend = backend
return backend, nil
case TransactionMode:
role := client.TransactionRole()
return pool.Borrow(role)
default:
return nil, fmt.Errorf("unsupported pool mode: %s", mode)
}
}Routing begins with a target role: primary, replica, any, or default. The role may come from configuration, SQL parsing, or an explicit directive such as SET SERVER ROLE TO 'replica'. A separate shard target may come from an explicit shard id, a sharding key, a query comment, or automatic query parsing. PgCat uses PostgreSQL’s PARTITION BY HASH hashing function for sharding compatibility, which lets application-visible partition keys map to proxy-visible shard ids.
func routeQuery(q Query, state ClientState) Route {
route := Route{Role: state.DefaultRole, Shard: state.DefaultShard}
if q.HasDirective("SERVER ROLE") {
route.Role = q.ServerRole()
} else if q.IsReadOnlySelect() && !state.InExplicitTransaction {
route.Role = Replica
} else {
route.Role = Primary
}
if q.HasDirective("SHARD") {
route.Shard = q.ShardID()
} else if key, ok := q.ShardingKey(); ok {
route.Shard = hashPartition(key, state.ShardCount)
}
return route
}Application servers often open many idle database connections even when only a small fraction are active at any moment. A transaction pooler lets hundreds or thousands of clients share a smaller backend pool, reducing PostgreSQL process memory, scheduler overhead, and connection churn. This is especially valuable for serverless and horizontally scaled API fleets.
When a deployment has one primary and several read replicas, a query-aware pooler can route ordinary reads to replicas and preserve primary capacity for writes. The application keeps a single database endpoint while the pooler implements read distribution, health checks, and fallback. This works best for workloads that tolerate replica lag or explicitly mark consistency-sensitive reads for the primary.
A pooler can hide multiple physical PostgreSQL servers behind one logical endpoint while still letting clients select a shard. Explicit directives are often safer than implicit parsing because they make routing intent visible in code. Automatic sharding is attractive for legacy clients, but it requires conservative parsing and strong test coverage for query shapes the application actually emits.
SQL parsing for routing should be conservative. A statement that looks like a read may call a volatile function, acquire locks, depend on session state, or run inside a transaction with write-after-read dependencies. When the parser cannot prove a query is replica-safe, route it to the primary.
func classify(q Query) Role {
if q.StartsTransaction() || q.CommitsTransaction() || q.RollbacksTransaction() {
return Primary
}
if q.IsSelect() && !q.HasVolatileFunction() && !q.HasLockingClause() {
return Replica
}
return Primary
}PgCat describes checking servers with a fast query before use and banning unreachable servers for a configurable period. That keeps traffic away from failing replicas without permanently removing them. The safety valve is important: if all servers become banned, clearing the ban list can prevent a false-positive health wave from turning into a total outage.
Run application integration tests through the pooler, not only against direct PostgreSQL. Transaction pooling changes semantics around prepared statements, session variables, advisory locks, temp tables, and long transactions. A migration that works against direct PostgreSQL may fail or leak state behind a proxy.
SET, temp tables, and advisory locks may work in direct PostgreSQL but fail once each transaction can land on a different backend.