Understanding Database Connections: From Handshake to Connection Pooling

FATAL: sorry, too many clients already

The error message above is one of the most common issues that causes engineers to panic in the middle of the night during on-call shifts. This article explains what really happens behind the scenes of a database connection, why creating connections is expensive, and how to manage them properly so this error never happens in your system.

What is a database connection?

A database connection is a two-way communication channel between an application and a database server. Before any query can run, the application must open a connection first. The concept is similar to placing a phone call and waiting for the other person to answer before you can speak.

A detail that is often forgotten at work: creating a new connection is expensive.

Expensive here does not mean money. It means expensive in terms of time (latency) and hardware resources (CPU and memory). Misunderstanding why connections are expensive is often the root cause of major performance problems in production.

How does a connection happen?

When an application calls a function like createConnection() or similar through a database driver, several steps happen before the first query can be sent:

Loading diagram...

In detail, the connection steps include:

• Opening a TCP socket: the application opens a network connection to the database port. Common default ports include:

  Database	Default Port
  PostgreSQL	5432
  MySQL / MariaDB	3306
  SQL Server	1433
  Oracle	1521
  MongoDB	27017

• Protocol handshake: client and server exchange protocol versions, capability information, and session parameters.

• Authentication: the server verifies client identity using a username, password, or SSL/TLS certificate if encryption is enabled.

• Authorization: the server checks client permissions, such as which databases can be accessed and which operations are allowed.

• Resource allocation: the server allocates memory for session buffers. In PostgreSQL, the server even spawns a new backend worker process dedicated to handling that connection session.

All the steps above take tens to hundreds of milliseconds, especially when the connection travels across networks between servers or performs TLS/SSL negotiation.

Compare that to a simple query execution, which often finishes in just 1 to 5 milliseconds. This means creating a new connection can take tens to hundreds of times longer than running the query itself.

Mental Model

The high cost of creating connections is the main reason why connection pooling exists. Understanding this cost helps you design efficient application architecture.

Opening and closing connections

Opening a connection

Here is an example of opening a connection directly in Node.js using the pg library for PostgreSQL:

const { Client } = require("pg");

const client = new Client({
  host: "localhost",
  user: "app_user",
  password: "secret",
  database: "mydb",
});

// Open the TCP connection, authenticate, and allocate server resources
await client.connect();

// Execute a query on the active connection
const result = await client.query("SELECT * FROM users");

Closing a connection

After the query finishes running, the connection must be closed:

// Close the connection session and release server resources
await client.end();

Why is closing a connection just as important as opening it?

Every open connection holds resources on the database server:

• Memory: in PostgreSQL, each connection (even when idle) is a separate process that uses about 5 to 10 MB of RAM.
• File descriptors: the operating system on the server has a limit on open file descriptors that must be shared across all running processes.
• Thread or process management: the more open connections you have, the higher the OS overhead for scheduling and context switching.

If an application opens connections but fails to close them, a connection leak occurs. The application keeps opening new connections until the database server runs out of connection slots, and the server starts rejecting new connections with the error too many clients already.

Three timeouts you need to know

To keep connections stable in production, pay attention to these three timeouts:

Timeout	Definition
Connection timeout	How long the application will wait when trying to connect before giving up and throwing an error.
Idle timeout	How long a connection can sit unused in the pool before it is closed to save resources.
Wait timeout	How long the database server will wait for new requests on an idle connection before closing it automatically (MySQL default is 8 hours).

Trap: connection closed by the server

If the database server closes an idle connection because of wait_timeout, but the application still thinks the connection is open, the next query will fail with errors like connection terminated unexpectedly or broken pipe.

The solution: make sure your application validates connection health before using it, or use a connection pool with automatic health check mechanisms.

Connection pooling

The problem without pooling

Without connection pooling, the lifecycle of a connection for each HTTP request looks like this:

Request 1: Open connection (100 ms) -> Run query (5 ms) -> Close connection (5 ms)
Request 2: Open connection (100 ms) -> Run query (5 ms) -> Close connection (5 ms)
Request 3: Open connection (100 ms) -> Run query (5 ms) -> Close connection (5 ms)

Most of the time is wasted opening and closing connections rather than executing queries. This pattern is very inefficient and leads to high application latency.

How a connection pool works

A connection pool is a cache of database connections that remain open so they can be reused across multiple requests:

Loading diagram...

How it works:

1. When the application starts, the pool opens a few initial connections to the database and keeps them alive.
2. When a request needs to run a query, the application borrows an available connection from the pool (acquire).
3. After the query finishes, the connection is not closed; it is returned to the pool (release).
4. The next request can immediately reuse that existing connection without going through the handshake and authentication process again.

Implementation example

Here is an example using Pool in Node.js with the pg library:

const { Pool } = require("pg");

// Initialize the pool once when the application boots
const pool = new Pool({
  host: "localhost",
  user: "app_user",
  password: "secret",
  database: "mydb",
  max: 10, // maximum of 10 simultaneous connections from this application instance
  idleTimeoutMillis: 30000, // close idle connections after 30 seconds
  connectionTimeoutMillis: 5000, // wait at most 5 seconds for an available connection
});

// pool.query automatically borrows a connection, runs the query, and returns it to the pool
const result = await pool.query("SELECT * FROM users WHERE id = $1", [1]);

If you need to run multi-query transactions (BEGIN ... COMMIT), borrow a connection explicitly and make sure it is always returned in a finally block:

const client = await pool.connect();
try {
  await client.query("BEGIN");
  await client.query(
    "UPDATE accounts SET balance = balance - 100 WHERE id = $1",
    [1],
  );
  await client.query(
    "UPDATE accounts SET balance = balance + 100 WHERE id = $2",
    [2],
  );
  await client.query("COMMIT");
} catch (error) {
  await client.query("ROLLBACK");
  throw error;
} finally {
  // Always release the client back to the pool to prevent leaks
  client.release();
}

Important configuration parameters

Parameter	Description
max	Maximum number of connections this pool can open to the database server.
idleTimeoutMillis	How long an unused connection can stay in the pool before being closed.
connectionTimeoutMillis	Maximum time a request waits for an available connection before throwing a timeout error.
maxLifetime	Maximum lifespan of a connection before it is retired and replaced with a fresh one.

Common mistake: creating a pool inside request handlers

Do not create a new connection pool instance inside a request handler function. A connection pool must be created once when the application starts (as a singleton) and shared across the entire app. Creating a pool per request makes connection problems worse and quickly exhausts server memory.

Three numbers across three levels

When sizing connections, engineers often confuse application settings, database configuration, and hardware capacity. These three numbers operate at different levels:

Level	Parameter	Example Value	Description
Level 1: Application	pool_size	20	Configured in application code: maximum connections allowed for one application instance.
Level 2: Database Server	max_connections	100	Configured on the database server: total limit of connections accepted from all clients.
Level 3: Hardware Capacity	core_count × 2	8 (on a 4-core server)	Realistic hardware limit: estimated number of queries that the database CPU can run concurrently with high efficiency.

Restaurant analogy

To understand how these three levels interact, picture a restaurant:

• max_connections (100): total seating capacity in the dining room. If all 100 seats are full, new guests are turned away at the door.
• Application pool_size (20): number of seats reserved for a specific party or group booking.
• core_count × 2 (8): number of chefs working in the kitchen who can cook dishes in parallel.

A restaurant may hold 100 seated guests. But if the kitchen only has 4 chefs, trying to cook 100 orders at the exact same moment will cause kitchen chaos, exhaust the chefs, and make every dish come out late. Having 100 chairs does not mean you can cook 100 meals at once.

Where does the core × 2 formula come from?

A popular sizing formula was introduced by the team behind HikariCP (a high-performance connection pool in the Java ecosystem):

pool_size ≈ core_count × 2 + effective_spindle_count

On modern servers using SSD storage, there are no spinning hard drive spindles, so effective_spindle_count is close to 0. The practical formula becomes:

pool_size ≈ core_count × 2

The most critical point to remember: core_count refers to the CPU cores on the DATABASE SERVER, not your application server.

The database server CPU is the one doing the heavy lifting: parsing SQL, calculating query execution plans, joining tables, sorting rows, and scanning indexes.

The reasoning behind this:

1. Every query actively running consumes processing time on a database CPU core.
2. If concurrent running queries far exceed CPU cores, the operating system is forced into heavy context switching (constantly swapping between processes and threads).
3. Excessive context switching degrades CPU efficiency and reduces overall query throughput.

The × 2 multiplier acts as a buffer because active connections do not consume 100% of the CPU at every microsecond.

TIP

Keep in mind that this formula was originally designed for a single monolith application connecting to one database server. When multiple applications or instances connect to the same database, this number represents the total pool budget that must be shared.

Why does "active" not always mean using CPU?

Inside the database, queries in progress can be in different states:

Query State	Uses CPU?	Example Scenario
Actively executing	Yes	Query is running a sequential table scan or calculating aggregations in memory.
Waiting on disk I/O	No	Query is reading pages from disk storage because data is not in cache (buffer pool).
Waiting on locks	No	Query is waiting for another transaction to release a lock on a row or table.
Idle in transaction	No	Application opened a transaction (BEGIN), but has not sent COMMIT or ROLLBACK yet.

The risk of "idle in transaction"

Connections stuck in idle in transaction do not burn CPU, but they hold row locks and block background cleanup (such as autovacuum in PostgreSQL). Leaving connections in this state degrades table performance over time.

Because some queries spend time waiting on disk I/O or locks, the core × 2 formula balances CPU utilization and waiting queues. Making the pool much larger than this ratio will not speed up execution once the database CPU reaches capacity.

Why a larger pool does not make queries faster

Many developers assume that increasing the pool size (for example, setting pool_size = 20 on a 4-core database server) will help the application handle traffic faster. This assumption is incorrect.

A larger pool does not make queries run faster. If the database server only has 4 CPU cores, 20 incoming queries can still only be processed by the CPU about 4 at a time. The remaining queries wait in line inside PostgreSQL.

This creates several negative effects:

• Heavy context switching: the CPU spends significant time switching between dozens of processes, which lowers overall data processing throughput.
• Bloated memory usage: each connection holds allocated RAM on the database server (around 5 to 10 MB per backend worker in PostgreSQL).
• Spike in query latency: response times climb because queries compete for CPU cycles and disk I/O queues.

It is much better for requests to queue in the application than to pile up inside the database server.

When the pool size is bounded proportionally (such as 8 connections), extra requests wait calmly in the application connection pool queue. Queuing inside the application uses lightweight memory and remains controllable. Meanwhile, the database server continues running at peak efficiency without getting overwhelmed. As soon as a query finishes and releases its connection, the next request in line is dispatched to the database.

Case study: 3 applications and 1 database

Let us walk through an architecture scenario commonly seen in production: multiple application services connected to a single database server.

Architecture scenario:

• Database Server: PostgreSQL, 4 CPU cores, max_connections = 100.
• Applications: 3 backend service instances (such as Service A, Service B, and Service C).

Calculating the ideal pool allocation

First, calculate the total ideal pool capacity for the database server hardware:

Ideal database capacity = 4 CPU cores × 2 = 8 efficient parallel connections

This number 8 is the total pool budget for the entire database server, not a quota each application can take for itself. Because 3 services share the same database, divide this ideal capacity across the services:

pool_size per instance ≈ (core_count × 2) / total application instances
pool_size per instance ≈ 8 / 3 ≈ 2 to 3 connections per application

For this scenario, allocate the pool sizes as follows:

• Service A: pool_size = 3
• Service B: pool_size = 3
• Service C: pool_size = 2
• Total pool connections: 8 connections (matching the ideal capacity of a 4-core CPU)

What happens behind the scenes

Loading diagram...

Key takeaways from this ideal design:

1. The database server operates at peak efficiency: with 8 total open connections, the 4 CPU cores process queries in parallel with minimal context switching. Query latency stays low and predictable.
2. Queues remain in the application layer: if Service A experiences a sudden burst of requests, the extra requests queue inside Service A connection pool instead of flooding the database. As soon as preceding queries finish and release their connections, waiting requests take their turn.
3. Isolation between services: because pool sizes are capped per service, a busy Service A cannot monopolize all database resources or degrade performance for Service B and Service C.

What if traffic demands grow?

If your application traffic grows and waiting times in the application pool (acquire wait time) become too long, here are your options:

• Option 1: Optimize query duration: inspect slow queries and make sure indexes are used properly. If an average query duration drops from 20 ms to 2 ms, a single connection in the pool can serve 10 times more requests per second without increasing the pool size.
• Option 2: Scale up database CPU cores: if queries are already tuned but throughput needs to increase, upgrade the database server to 8 or 16 CPU cores. On an 8-core server, the total pool budget increases to ≈ 16 connections, allowing each of the 3 services to increase its pool_size to 5–6.
• Option 3: Use an external connection pooler: if the number of application instances grows large (such as dozens of microservices or serverless architectures), introduce a tool like PgBouncer so hundreds of services can share dozens of physical database connections dynamically.

Sizing principle: measure, do not guess

Use core × 2 as a healthy starting baseline. From there, monitor production metrics: connection acquire wait time, query latency, and active versus idle connection counts. Adjust pool sizes based on real data.

Do not forget headroom for administrators

A classic production incident happens when all connection slots are consumed during an emergency:

1. max_connections = 100.
2. All 100 slots are taken by application connections (for instance, due to a connection leak or sudden traffic spike).
3. The application starts failing with connection errors.
4. An engineer tries to connect to the database via terminal (psql) to investigate.
5. The login is rejected because no connection slots remain.

To prevent this nightmare scenario, modern databases offer reserved connection slots for administrators:

Database	Setting / Mechanism	Access Rights
PostgreSQL	superuser_reserved_connections (default: 3)	Reserved for roles with superuser privileges.
MySQL	Provides 1 extra connection slot beyond max_connections	Reserved for users with SUPER or CONNECTION_ADMIN privileges.

Example calculation in PostgreSQL:

max_connections = 100
superuser_reserved_connections = 3
------------------------------------------------------
Maximum connections for regular applications = 97
Reserved connections for superusers          = 3

Tasks that must be accounted for in your connection headroom:

• Incident response: inspecting active queries via pg_stat_activity and terminating stuck queries.
• Schema migrations: running ALTER TABLE or deployment scripts.
• Routine maintenance: database backups and maintenance jobs like VACUUM.
• Monitoring agents: tools like Datadog, Prometheus node exporter, or background cron workers.

Always ensure the sum of all application pools plus admin headroom stays well below max_connections.

Scaling for large workloads: external connection poolers

In architectures with hundreds of microservices or serverless platforms (such as AWS Lambda), application instances can scale out dynamically into hundreds of units.

If 100 Lambda functions each open a pool with 5 connections, the database faces 500 simultaneous connections. Because each PostgreSQL connection runs as an independent OS process with dedicated memory, raising max_connections to thousands will waste RAM and risk database instability.

The recommended solution for this architecture is an external connection pooler placed between applications and the database:

Loading diagram...

Common external poolers used in production:

• PgBouncer (PostgreSQL): supports transaction pooling, where a physical connection to the database is only held for the duration of a transaction and immediately returned for another client to use. Thousands of client connections can be served using only dozens of physical database connections.
• ProxySQL (MySQL): a high-performance proxy for MySQL that offers query routing, read/write splitting, and connection pooling.
• Managed Cloud Proxies: cloud-native solutions like AWS RDS Proxy or GCP Cloud SQL Auth Proxy that simplify connection management for serverless applications.

Practical checklist

Here is a summary of best practices for database connection management:

• Create the connection pool once when the application boots (singleton), never inside request handlers.
• Always release or close connections in a finally block to prevent connection leaks.
• Use (core_count × 2) / number of instances of the database server CPU as a starting baseline for application pool size.
• Ensure the combined connection limit of all instances plus maintenance headroom never exceeds max_connections.
• Reserve connection headroom for administrators so the database remains accessible during incidents.
• Configure appropriate timeout values (connection timeout, idle timeout, and wait timeout) to avoid zombie connections or dropped socket errors.
• Use encrypted connections (SSL/TLS) for production databases, especially cloud-hosted databases.
• Use an external connection pooler (such as PgBouncer or RDS Proxy) when using serverless or running many application instances.
• Measure performance regularly by monitoring pool acquire wait time, query latency, and active versus idle connection ratios.

Three numbers summary

Parameter	Nature	Role and Purpose
core_count × 2	Calculation recommendation	Guide for total efficient pool capacity on the database server so CPU query execution runs smoothly. This number is shared across all application instances.
pool_size	Application configuration	Maximum number of connections allowed for a single application instance.
max_connections	Database configuration	Hard limit on the total number of physical connections accepted by the database server.

Summary

• Database connections are expensive because they require TCP socket setup, protocol handshakes, authentication, and server-side process memory allocation.
• Connection pooling saves time and server resources by reusing connections that are already established.
• Query processing capacity is constrained by database CPU hardware, not by high pool numbers in application config. Sizing pools too large degrades performance through CPU context switching.
• Always keep administrative headroom for emergencies and monitoring, and use external poolers when application scale requires hundreds or thousands of connections.

References

• About Pool Sizing (HikariCP Documentation)
• PostgreSQL Documentation: Connections and Authentication (PostgreSQL Official Docs)
• PgBouncer Documentation (PgBouncer Official Docs)