Database Connection Architectures: Pooling, Proxies, and Serverless

A Node API that had been running perfectly well was moved from ECS to Lambda. Traffic had not increased, yet that night the database reported too many connections. The investigation found that Lambda concurrency is effectively the instance count. When cold starts hit, every execution environment created its own connection pool, and hundreds of concurrent functions suddenly hammered PG with thousands of connections. max_connections=100 was exhausted immediately. This problem would never arise with a long-running service; Serverless turned something that was not previously a problem into the number-one challenge.

This article breaks down "how applications connect to databases" into three architectures: in-process pools, connection proxies, and Serverless connection pools. If you do not understand where each one applies, connection limits will catch up with you sooner or later.

1. Why Connections Are a Bottleneck

Many beginners treat a database connection as "just a regular TCP socket" and assume it is cheap. PostgreSQL's architecture makes its connections expensive: for every connection, PG forks a separate backend process. Not a thread, but a process.

Each connection carries real costs:

• Memory: Each backend process has its own work_mem, cache context, and temporary space. An idle connection uses 5~10 MB, while a complex query can easily consume tens of MB. Process metadata alone for 300 connections can take several GB.
• CPU: Processes do not share caches, and the overhead from context switches and TLB misses rises linearly with the number of connections.
• Slow connection setup: A TCP handshake + authentication + fork + backend initialization means creating a connection takes at least tens of milliseconds. Under high concurrency, that latency goes straight into P99.

And establishing the connection itself is a major expense: TCP three-way handshake + TLS (if enabled) + database authentication (password verification, SCRAM) + process fork + session state initialization. On PG, this routinely takes 30~100ms. If you open a new connection for every request, each request waits roughly 50ms before doing any actual work.

PostgreSQL's official default of max_connections=100 is not stinginess; it is an empirical default. MySQL uses a thread model, so connections are relatively lightweight. PG's process model makes it inherently sensitive to connection count: a few hundred connections can leave a capable machine struggling to keep up. Connection count is the ceiling on PG throughput.

2. Architecture One: In-Process Connection Pools

Since connections are expensive, they should be reused. Every production-grade driver includes a connection pool. The core mechanism is simple: prewarm a set of connections in a pool; when a request arrives, borrow one and return it after use, instead of closing it and creating another next time.

// node-pg(pg): Pool is the connection pool
const { Pool } = require('pg');
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,                  // Pool size
  idleTimeoutMillis: 30000, // Reclaim after 30s idle
  connectionTimeoutMillis: 2000,
});

// Prisma: use connection_limit to control connections per instance
// postgresql://user:pass@host:5432/db?connection_limit=10&pool_timeout=10

# HikariCP (default pool for Java/Spring)
hikari:
  maximum-pool-size: 10
  connection-timeout: 2000
  idle-timeout: 30000

An in-process pool has this topology:

[App Instance A] ── pool(10) ─┐
[App Instance B] ── pool(10) ─┼──> [PostgreSQL  max_connections=100]
[App Instance C] ── pool(10) ─┘

Best for: long-running services (traditional ECS / container deployments, PM2, and systemd processes). The process creates the pool when it starts and reuses it throughout its lifetime, amortizing connection setup costs to almost zero.

But it has an unavoidable limitation: every instance has an independent pool, and instances do not share connections. total connections = pool_size × instance count. In the topology above, three instances with 10 connections each use 30 connections in total. Scale to 15 instances and the total becomes 150, already exceeding PG's limit of 100. An application pool solves "reuse within one process"; it cannot solve "horizontal scaling across many instances." Once the instance count rises, the bottleneck immediately shifts from CPU to connection count.

3. Architecture Two: Connection Proxies (PgBouncer / ProxySQL)

When there are many application instances, or multiple services share one database, in-process pools are no longer enough. The solution is to add a connection proxy between the application and the DB. It presents itself as the database to clients while using only a small number of connections to the actual database.

[App A] ──┐                          ┌── backend conn 1 ──┐
[App B] ──┼──> [PgBouncer] ──────────┼── backend conn 2 ──┼──> [PostgreSQL]
[App C] ──┘    1000+ client connections └── ... 25 total ────┘    max_connections=100

The left side can support thousands of client connections while the right side opens only 25 real backend connections to PG. This layer decouples the number of client connections from the number of database connections, which is its greatest value.

The de facto standard in the PG ecosystem is PgBouncer; the MySQL counterpart is ProxySQL. PgBouncer has three pooling modes, and choosing the right one is critical:

• session mode: One client connection equals one backend connection, and they remain bound for the entire session. It saves only connection setup costs and does not reduce connection count. It is basically useless.
• transaction mode (key): A backend connection is borrowed for the duration of a transaction and returned to the pool immediately after COMMIT or ROLLBACK. This is the mode that truly handles high concurrency: one backend connection can serve hundreds or thousands of clients.
• statement mode: A connection is borrowed for every SQL statement, and even multi-statement transactions are unsupported. It is too restrictive and rarely used.

# pgbouncer.ini: core configuration for transaction mode
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction        # Key: borrow one connection per transaction
max_client_conn = 1000         # Maximum client connections (can be large)
default_pool_size = 25         # Backend connections per db/user pair
reserve_pool_size = 5          # Temporarily add capacity during peaks

The cost of transaction mode: session-level state cannot be retained across transactions. Session-level features such as SET, temporary tables, LISTEN/NOTIFY, and server-side prepared statements can fail or cause surprises in transaction mode. The most common problem is prepared statements. Many drivers use prepared statements by default to cache execution plans, but under PgBouncer transaction mode, a prepared statement may be created on the connection borrowed by transaction A. Transaction B may receive another connection, so the statement cannot be found and the request fails immediately. The pitfalls section later explains how to configure this.

Best for: multiple services sharing one database, highly concurrent short requests, and tight connection budgets. One PgBouncer instance lets hundreds of application processes share a few dozen DB connections, which an in-process pool cannot do.

4. Architecture Three: Serverless Connections

Serverless (Lambda / Edge Functions / Cloudflare Workers / Vercel) raises the connection problem to a new level because its execution model completely overturns the assumption of a "long-running process":

1. Short-lived functions: A Lambda instance may live for only a few hundred milliseconds before being frozen or destroyed after handling a request.
2. Instance count = concurrency: When traffic rises, the platform instantly starts hundreds of concurrent instances, each with its own pool, so connection count explodes linearly with concurrency.
3. Cold starts: Every cold start must establish a new connection, repeatedly paying the 50ms connection cost described above.

In-process pooling fails outright here. You cannot predict the number of instances, so you cannot keep pool_size × instance count within max_connections. A Serverless application connected directly to PG is effectively playing Russian roulette with PG.

There are two solutions, and both push pooling down to the database layer:

Solution one: self-hosted or managed PgBouncer (transaction mode). Have every Lambda instance connect to PgBouncer so it can consolidate connections centrally. AWS RDS now includes RDS Proxy, which is essentially a managed PgBouncer-like proxy that can simply be enabled.

Solution two: use the pooler built into a Serverless-native database. This has become the prevailing trend in recent years, and mainstream Serverless PG services provide built-in pooler endpoints:

• Neon: Its compute and storage are separated, and it provides connection endpoints with a -pooler suffix that use transaction mode out of the box. Connecting Serverless functions to the pooler endpoint is the officially recommended approach.
• Supabase: It provides a pooler through its in-house Supavisor, separating port 6543 (transaction mode) from port 5432 (direct connection). Serverless applications use 6543.
• PlanetScale: It is built on Vitess and already uses a proxy architecture. Serverless applications use its serverless driver / database.js, making connection management almost transparent.

Prisma has a parameter you must set when connecting to this type of pooler:

# Supabase transaction mode: port 6543 + pgbouncer=true + prepared statements disabled
DATABASE_URL="postgresql://user:[email protected]:6543/postgres?pgbouncer=true&connection_limit=1"

pgbouncer=true makes Prisma disable prepared statements, avoiding the transaction-mode issue described above. connection_limit=1 is appropriate because one connection per Lambda execution environment is enough. Setting more provides no benefit and instead consumes the pooler's client quota.

One more Serverless detail: do not call new Pool() inside the handler. Declare the pool at module scope as a singleton. When Lambda reuses an execution environment, it can also reuse the pool and its connection, substantially reducing connection setup. Creating the pool inside the handler means creating a new one for every invocation and wasting the reuse mechanism.

5. Calculating Connection Counts and Sizing Pools

Connection count is arithmetic you must know how to do. Every architecture must satisfy:

sum( per-application pool_size × instance count )  +  cron/migrations/BI/replication  ≤  max_connections × 80%

Reserve 20% for superuser, operations, monitoring, and replication connections. If the total exceeds the limit, expect a late-night incident. For example, with PG max_connections=100, 80% means 80 connections. Deploying eight application instances with a pool of 10 connections each already consumes all 80, leaving no capacity for migrations or cron jobs. At that point, either add PgBouncer to consolidate connections, reduce the number of instances, or increase max_connections (but increasing it is not free, as the pool-sizing discussion below explains).

Pool size is the most counterintuitive setting: larger is not better; too large is slower. Reasons include:

• With more connections, internal PG lock contention (ProcArrayLock, etc.) intensifies, context switches become frequent, and CPU is consumed by scheduling.
• Disk IO does the actual work. When hundreds of connections contend for IO, queuing can be slower than serial execution.
• Both HikariCP guidance and the PG community use this rule of thumb: pool size ≈ (core count × 2) + effective disk count, typically in the 10~30 range. For most workloads, 10 connections are enough.

I have seen too many people set a pool size of 100 "just in case," only to overwhelm PG and increase P99 instead. Pool size should be configured for what is enough, not for a sense of safety. The same applies to PgBouncer's default_pool_size: around 25 is enough for most scenarios, so do not start at 200.

6. Selection and Common Pitfalls

The boundaries among the three architectures can be summarized in one table:

In practice, all three are often combined: a small in-process pool → PgBouncer → PG. This is not overengineering; each manages a different layer. The application pool handles connection setup costs, while PgBouncer consolidates connections.

Common pitfalls (all from real incidents):

1. Connection leaks: Using a low-level client but forgetting to call release(). Eventually every connection in the pool is borrowed and never returned, so new requests all block at pool_timeout. Always use try/finally or a framework-managed API instead of managing the lifecycle manually.
2. A pool that is too small: With only three connections, the fourth concurrent request queues immediately and P99 soars. Monitor the wait_time and pool_timeout metrics.
3. Serverless direct connections exhaust the database: Connecting Lambda / Edge directly to PG is the number-one anti-pattern. A pooler is mandatory (PgBouncer / Neon pooler / Supabase 6543). There are no exceptions.
4. transaction mode conflicts with prepared statements: Prisma / node-pg use prepared statements by default and can fail under PgBouncer transaction mode. The solution is pgbouncer=true (Prisma) or ?pgbouncer=true, which makes the driver disable prepared statements.
5. Running migrations through the pooler: DDL, long transactions, and operations such as CREATE INDEX CONCURRENTLY must connect directly to PG rather than using PgBouncer in transaction mode. Session state can move between connections, making DDL easy to interrupt. Both Supabase and Neon recommend sending application traffic through the pooler port and migrations through the direct-connection port.

Summary

1. Connections are heavyweight resources, not free: PG uses one process per connection, and a few hundred can consume all available resources. Establishing a connection itself takes tens of milliseconds. Connection count is the ceiling on PG throughput.
2. Each architecture handles a different layer: An in-process pool provides reuse within one process for long-running services; a connection proxy consolidates multiple instances, with PgBouncer transaction mode handling high concurrency; a Serverless pooler consolidates connections from short-lived functions through Neon / Supabase / PlanetScale.
3. Serverless must use a pooler: Function instance count equals concurrency, so direct connections will inevitably exhaust the database. In Lambda, also declare the pool as a module-level singleton instead of creating it inside the handler.
4. Connection count is arithmetic: pool_size × instance count + operations ≤ max_connections × 80%. If the total exceeds the limit, use PgBouncer to consolidate connections.
5. A pool size of 10~30 is enough; larger is not better: More connections increase lock contention and context switches, slowing PG down. Configure for what is enough, not for a sense of safety.

Key takeaway: How applications connect to databases determines how much concurrency they can handle. The real bottleneck is often not CPU or IO, but the connection that looks least significant. Choose the right architecture and a low-cost machine can remain stable; choose the wrong one and even top-tier hardware can still be taken down by OOM in the middle of the night.