PostgreSQL uses one operating-system process per connection. That model is robust but means each connection carries real memory and setup cost, and the server performs poorly once connections climb into the high hundreds or thousands. Modern applications — especially many app instances each with their own pool, or serverless functions — can easily open far more connections than the database can efficiently serve. Connection pooling solves this.
PgBouncer is a lightweight connection pooler that sits between your applications and PostgreSQL. Applications connect to PgBouncer, which maintains a small pool of real connections to the database and multiplexes many client connections over them. Thousands of mostly-idle client connections can thus be served by a few dozen busy server connections, dramatically cutting the database's per-connection overhead.
The catch is that pooling changes connection semantics, and the pool mode you choose determines what application features still work. Transaction pooling, the most aggressive and common mode, breaks session-level features unless you account for them. Understanding the pool modes and their trade-offs is what makes pooling a performance win rather than a source of subtle bugs.
Analogy🏏Cricket
🏏 Think of it like cricket: Just as a selection decision can depend on a derived benchmark — 'pick batters whose average exceeds the squad's average', which itself must first be computed — a subquery computes an inner result that the outer query then uses. The insight is that some questions are inherently two-stage: you must establish the benchmark before you can judge against it, and composing queries is how SQL expresses that dependency. Watch the selector actually do it: first he tallies every batter's runs and computes the squad average — that inner computation stands alone, needing nothing from the final decision — and only then does he walk the list judging each player against the number he just derived. That independence is what makes it an uncorrelated subquery: the database can compute the benchmark once, keep it, and reuse it for every row, exactly as the selector does not recompute the squad average per player. The composition also comes in shapes: a benchmark producing one number slots in where a value goes (a scalar subquery in WHERE), while a computed shortlist of qualifying players is itself a table the outer query can select from — a subquery in FROM. Two-stage question, two nested queries, dependency flowing inward-out.
🏏 Showing the Cricket analogy — a Cricket version isn’t available for this concept yet.