Connection Pooling & N+1 Queries

Pooling Is a Shared System Concern

Connection management stops being a local concern the moment you have several services all talking to the same Postgres instance. Fieldwork's gateway-facing services, worker processes, and occasional admin jobs could each behave politely in isolation and still overwhelm the database together. The first sign was familiar: latency spikes during deploys and burst traffic because every instance eagerly opened its own pool and kept idle connections around just in case. The database spent too much effort managing clients before it even started on query work.

We rejected the easy answer of "just increase max_connections." That moves the limit but does not improve the ratio of useful work to connection overhead. Postgres is happiest doing query execution, not acting as a very expensive connection broker for every tiny service instance. So Fieldwork put PgBouncer in front of every service and treated connection pooling as a shared infrastructure concern rather than as dozens of isolated application defaults.

PgBouncer Fixed Spikes, Not Bad Queries

Adding PgBouncer stabilized burst behavior quickly, but it also forced application discipline. Transaction pooling is the mode that gave us the biggest benefit, and it comes with constraints. Session-level features like temporary tables, prepared statements that assume connection affinity, or long idle transactions become trouble. We accepted those constraints because Fieldwork's request paths are short-lived and explicit. The cost of adapting a few application settings was lower than the ongoing cost of letting every service talk to Postgres as if it owned the server.

This is also where we rejected broad ORM habits. When a library silently opens large pools, holds transactions across network calls, or leans on driver-level prepared statement caches that do not play nicely with transaction pooling, it stops being a convenience. It becomes infrastructure coupling. Fieldwork already favored explicit pgx query code, so adjusting pool settings and statement behavior was straightforward. That was one of the practical payoffs of keeping the data layer boring.

N+1 Hid Inside Authorization Paths

Pooling solved pressure, but it exposed a second issue more clearly: N+1 query patterns inside supposedly small endpoints. The obvious place to look was list tasks, but the nastier version hid in authorization and enrichment. We would load a list of tasks for one project, then loop over them to fetch assignee membership details or comment counts one row at a time. The code was readable in the worst way possible: every step made intuitive sense, and together they quietly multiplied latency and connection usage.

We rejected the excuse that these were "small lists." Small lists become medium lists, then paginated lists, then batch job inputs. The correct fix was to change query shape, not to hope traffic stayed polite. For Fieldwork that meant batching by tenant and workspace scope, using WHERE ... ANY($n) lists or joins, and letting Postgres do set-based work in one round trip instead of teaching the application to rediscover relational algebra with for-loops.

What We Standardized

The end state was clear. PgBouncer sits between services and Postgres, application pools stay intentionally small, and query reviews look for N+1 behavior anywhere loops touch repositories. We batch lookups by tenant and workspace scope, prefer one explicit join or set-based fetch over a dozen polite little queries, and watch pool saturation metrics to catch regressions early. The point was not to make every SQL statement clever. It was to stop spending database capacity on avoidable connection churn and avoidable round trips.

That combination is why these two topics live in one lesson. Connection pooling and N+1 queries are not the same problem, but they amplify each other. Poor query shape consumes more connections for longer. Connection scarcity makes poor query shape hurt sooner. Fieldwork got steadier only when we addressed both: infrastructure-level pooling with PgBouncer, and application-level batching where the code had been serializing set operations into loops.

• Put PgBouncer between every service and Postgres so the database stops paying full price for client fan-out.
• Shrink application pool sizes once an external pooler is brokering connections.
• Review transaction scope and connection-affine features before relying on transaction pooling.
• Treat any repository call inside a row loop as suspect until proven safe.
• Batch related lookups with joins or ANY-based queries so Postgres does set work in one trip.