Skip to content

Connection Pooling

Manage database connections efficiently with pooling to handle concurrency.

What you'll learn

  • Why database connections are expensive to create
  • How a connection pool reuses connections
  • Configuring pool size and timeout settings
  • Connection leaks and how to prevent them
  • Serverless and external pooling solutions (PgBouncer)

Concept

Why Connections Are Expensive

Opening a database connection is not cheap. It involves a TCP handshake, authentication, session setup, and memory allocation on both the client and server. A typical PostgreSQL connection uses ~10 MB of memory on the server. If every HTTP request opens a new connection, you will quickly exhaust the database's max connections (default 100 in PostgreSQL) and memory.

How a Pool Works

A connection pool maintains a set of open connections and lends them out as needed. When your code needs a query, it borrows a connection from the pool, runs the query, and returns the connection. The connection stays open and is reused for the next request. This amortizes the connection cost across many requests and limits the total number of connections to the pool size.

Configuring the Pool

Key settings: max connections (how many connections the pool holds open — typically 10–50 per application instance), idle timeout (close connections that have been idle for too long), connection timeout (how long to wait for a connection before erroring), and max lifetime (recycle connections periodically to prevent stale connections). The optimal pool size depends on your database's max connections and the number of application instances.

Connection Leaks

A connection leak occurs when code borrows a connection but never returns it — usually because an exception skipped the return/close call. The pool gradually empties and the application hangs. Prevent leaks by always using a try/finally block or the pool's built-in withConnection / transaction API that auto-releases connections.

Serverless and PgBouncer

In serverless environments (Lambda, Cloudflare Workers), each invocation is a short-lived process that cannot maintain a pool. Use an external pooler like PgBouncer (PostgreSQL) or ProxySQL (MySQL) that sits between your functions and the database, multiplexing many short-lived client connections onto a few persistent server connections. This is essential for serverless + relational database architectures.

Example

              -- PostgreSQL: check active connections
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;
--  state   | count
--  active  | 12
--  idle    | 38

-- Set max connections (in postgresql.conf)
-- max_connections = 100

-- PgBouncer config (external pooler, transaction mode)
-- [pgbouncer]
-- listen_port = 6432
-- pool_mode = transaction
-- max_client_conn = 1000
-- default_pool_size = 25

-- Application code pattern (Node.js with pg Pool)
-- const pool = new Pool({
--   host: 'localhost',
--   max: 20,              // pool size
--   idleTimeoutMillis: 30000,
--   connectionTimeoutMillis: 2000,
-- });
--
-- // Always use a try/finally to release
-- const client = await pool.connect();
-- try {
--   const res = await client.query('SELECT * FROM users WHERE id = $1', [42]);
-- } finally {
--   client.release();  // return to pool
-- }
            

Check active connections, set max_connections, configure PgBouncer for serverless, and the correct application pattern with try/finally to prevent leaks.

Try it

Common mistakes

The mistake

Opening a new connection per request without pooling

The fix

Always use a connection pool. Opening connections is expensive and you will exhaust the database's max connections under load. Configure the pool once at application startup.

The mistake

Forgetting to release connections (connection leaks)

The fix

Use try/finally or the pool's transaction API to guarantee connections are returned. Monitor pool stats and alert on connections in use approaching pool size.

Practice Connection Pooling

3 exercises

Practice Connection Pooling

Look up unfamiliar terms

A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.

View glossary

Build real projects

Apply what you learned by building guided projects with starter code and solutions.

View projects

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors