Pooled or direct: choosing the right Postgres endpoint

Every Balta service has two endpoints on a hostname that never changes, not even when the service moves to another host:

• Pooled — PgBouncer in transaction pooling mode. This is the default.
• Direct — straight to PostgreSQL, no pooler in the path.

Picking the wrong one produces a class of bug that is genuinely unpleasant to diagnose: everything works in development against a direct connection, and then a job framework silently loses its session state in production. So this is the whole compatibility story, in one place.

What transaction pooling does #

In transaction pooling, a server connection is assigned to a client for the duration of a transaction and returned to the pool at commit. Between transactions your client does not own a server connection at all.

That is what makes pooling worth having. A web application with 400 idle connections holds 400 backend processes open on a direct connection, each with its own memory, all of them counted against max_connections. Through a transaction pooler, the same application occupies server connections only while it is actually executing.

It is also the source of every incompatibility below, and they all have the same shape: anything that lives in a session rather than in a transaction has nowhere to live.

What works on pooled, including one thing people expect not to #

Prepared statements deserve the emphasis. Older guidance says they are broken in transaction pooling, and a lot of documentation still repeats it. Current PgBouncer supports protocol-level prepared statements in transaction mode when a prepared-statement cache is configured, which ours is. Documenting them as broken would push people onto the direct endpoint for no reason, so we do not.

What needs the direct endpoint #

These are session-scoped, and transaction pooling has no session to scope them to:

• Session-level SET or RESET outside a transaction
• LISTEN and NOTIFY
• Session-level advisory locks
• WITH HOLD cursors
• Temporary tables that need to persist across transactions
• SET SESSION AUTHORIZATION and session-scoped role changes
• Session state that some ORMs and job frameworks assume, often without documenting it

The last one is the one that bites. A background job framework that sets a session variable at checkout and reads it later is relying on session affinity it does not have. It will not error; it will read the wrong value, or the default one.

LISTEN and NOTIFY for job dispatch is a common and good pattern. That consumer needs the direct endpoint. The rest of your application can stay on the pooled one. Using both from the same service is normal and expected.

The pooler runs inside your service #

One design decision worth explaining, because it is not what every provider does.

PgBouncer runs per service, as a process inside that service's own cgroup, under that service's own user, terminating TLS and forwarding to PostgreSQL over a local Unix socket.

We rejected the alternative, one pooler per host shared by every tenant on it. It puts multiple tenants inside a single process, which is a cross-tenant boundary in a place we do not want one. It makes one tenant's connection storm into everybody's problem. And it creates a shared restart dependency, so a pooler restart becomes an incident for every service on the host.

The consequences of our choice are real and we accept them. The pooler's memory and CPU are charged to your allocation, which is why they are included in the configuration profile for each plan rather than pretended away. In exchange, a pooler failure affects one service, is restarted by systemd, and there is no shared pooler failure domain.

The pooler also runs with a lower kill priority than your database backends, so under memory pressure a backend is chosen before the pooler. The pooler cannot starve the database it is in front of.

Sizing #

Pool sizes come from the plan's connection allocation and its max_connections, with the default pool size set so that the pooled path cannot consume every backend slot and leave the direct path unusable. That is a real failure mode: a pooler configured to the full max_connections will happily take all of them, and then the direct endpoint you need for LISTEN stops accepting connections.

The pooler keeps active, idle and waiting client connections, server connection utilisation, pool saturation and wait time. Wait time is the one that indicates a problem you can feel — the others tell you how full something is, and only wait time tells you that a query sat still.

None of them is in your dashboard yet. The figures exist on the host and nothing collects them, so there is no pool chart and no alert on pool saturation. This paragraph said "we monitor" before the pooler existed; it says what is true instead, and it changes on the day the collector ships.

The short version #

Use the pooled endpoint. Move a specific consumer to the direct endpoint when it needs one of the seven things in the list above. Both endpoints are TLS-only, both are on the same stable hostname, and neither changes when we move your service.