We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
System Design and AI Engineering, by Roni Das. 984 interactive lessons.
Start LearningFoundation · Database Fundamentals
A cache of reusable database connections that avoids the overhead of opening and closing a new connection for every query. Critical for high-throughput applications.
In short
A connection pool is a cache of open, ready-to-use database connections that an application borrows for a query and returns instead of closing. It removes the cost of opening a new TCP and authentication handshake for every request, which is what lets one app server handle thousands of queries per second against a database that can only hold a few hundred connections.
In short
A connection pool is a cache of open, ready-to-use database connections that an application borrows for a query and returns instead of closing. It removes the cost of opening a new TCP and authentication handshake for every request, which is what lets one app server handle thousands of queries per second against a database that can only hold a few hundred connections.
Learn it in the course
This page is the free part.
₹1,111 in India$25 everywhere elseonce, for the whole course
The System Design course covers Connection Pool across a run of lessons, not one page. These 4 alone are about 98 minutes of step-by-step reading, 1 of them with code you run in the browser, all with a quiz.
PgBouncer, HikariCP, and why opening a new database connection per request is a recipe for disaster.
Reuse threads instead of creating them, the pattern that makes web servers handle thousands of concurrent connections.
Pool and reuse connections across your application to maximize throughput and minimize overhead.
Reduce latency and bandwidth usage, compression, connection pooling, and protocol tuning.
all part of
System Design Masterclass
770 lessons · about 287 hours · 18 free to read
₹1,111in India, by UPI
$25everywhere else, by PayPal
It is really nice and good Please join the course
You own this course
Continue with Database Connection Poolingpay once,
yours for life
Opening a database connection is expensive. The client opens a TCP socket, runs a TLS handshake if encryption is on, authenticates the user, and the database forks or assigns a backend process to serve it. On Postgres that handshake plus backend startup can take 20 to 50 milliseconds, which is often longer than the query itself. Doing that for every request would waste most of your time on setup.
A connection pool solves this by opening a fixed set of connections once, keeping them alive, and lending them out. When your code needs to run a query, it asks the pool for a connection (a checkout), uses it, and hands it back (a checkin). The connection stays open the whole time, so the next request skips the handshake entirely.
The pool is just a managed list of connection objects plus a small amount of bookkeeping: which ones are idle, which are in use, and a queue of callers waiting when none are free. It lives inside your application process or in a separate proxy that sits between the app and the database.
A pool is configured with a minimum and maximum size. On startup it may open the minimum number of connections so the first requests are fast. When traffic rises and all connections are busy, it opens more up to the maximum. When a caller asks for a connection and the pool is at its max with none idle, that caller waits in a queue until one is returned or a timeout fires.
Each idle connection is health-checked before it is handed out, often with a cheap query like SELECT 1, because databases and network gear drop idle TCP connections after a few minutes. Pools also enforce a max lifetime, recycling a connection after, say, 30 minutes so a long-lived connection cannot accumulate server-side state or memory leaks.
The key tuning numbers are pool size, checkout timeout, idle timeout, and max lifetime. Pool size is the one that bites people: a database has a hard cap on total backends (Postgres defaults to 100), and every app instance multiplies its own pool against that single limit. Ten app servers each holding a pool of 20 means 200 connections, which already exceeds the default.
Use a connection pool for essentially any application that talks to a relational database under real traffic. The benefit is largest for short queries at high request rates, where handshake cost would otherwise dominate. It is also how you protect the database itself: the pool caps concurrency so a traffic spike queues inside your app instead of opening thousands of connections and crashing the database under memory pressure.
The main trade-off is sizing. Too small and requests queue and time out even though the database is idle. Too large and you exhaust the database's connection limit, and because each Postgres connection holds work_mem and a backend process, you can run the server out of memory. A common rule of thumb is to keep the total across all instances comfortably under the server max and let it queue rather than overrun.
The other trap is serverless and many-instance deployments. Functions that scale to hundreds of instances each open their own pool, and the math explodes. The fix is an external pooler such as PgBouncer or a managed equivalent, which multiplexes thousands of client connections onto a small number of real database connections.
Picture an API serving 2,000 requests per second, each running one 3 millisecond query. Without pooling, every request pays roughly 30 milliseconds of handshake, so a single connection could serve only about 30 requests per second and you would need to open and tear down 2,000 connections a second, which no database tolerates.
With a pool, you might hold 25 open connections. Each can run 3 millisecond queries back to back, so 25 connections handle far more than 2,000 queries per second, and the handshake happens 25 times total at startup rather than 2,000 times per second. Latency drops from tens of milliseconds to near the raw query time, and the database sees a stable, bounded number of backends.
This is why HikariCP, the default pool in Spring Boot, and node-postgres' Pool, and SQLAlchemy's QueuePool ship pooling on by default. The connection pool is invisible plumbing, but it is the difference between a database that survives production and one that falls over on the first traffic spike.
PgBouncer
A lightweight external pooler for Postgres that multiplexes thousands of client connections onto a few server connections, widely used in front of Neon, Supabase, and self-hosted Postgres.
HikariCP (Spring Boot)
The default JDBC connection pool in Spring Boot, known for very low checkout overhead and aggressive connection health checks.
Amazon RDS Proxy
A managed pooler from AWS that sits in front of RDS and Aurora so Lambda functions and autoscaling fleets do not each open their own pool and exhaust the database.
node-postgres (pg.Pool)
The standard Postgres client for Node.js, whose Pool class hands out and recycles connections for high-throughput web servers.
A single connection serves one query at a time and pays the open and close cost each time you reconnect. A pool keeps several connections open permanently and lends them out, so concurrent requests can run in parallel and none of them pay handshake cost after startup.
Start small. A good baseline for a CPU-bound database is roughly the number of CPU cores times two, plus a little for disk waits, which often lands between 10 and 30 per instance. Then make sure the total across all app instances stays under the database's max connection limit, and increase only if you see checkout timeouts while the database itself is not saturated.
That usually means the pool is exhausted, not the database. Every connection is checked out and a new caller is waiting past the checkout timeout. It is often caused by code that borrows a connection and forgets to return it (a leak) or by long-running queries holding connections too long. Set a max pool size large enough for your concurrency and audit for connections that are never released.
Yes, but an in-process pool can backfire because each function instance opens its own. Hundreds of cold-started instances times a pool of 10 will blow past the database limit. Use a small per-instance pool combined with an external pooler like PgBouncer or RDS Proxy that consolidates everything into a bounded number of real connections.
Yes, but be careful with transaction-level poolers like PgBouncer in transaction mode. They can hand the same physical connection to different clients between transactions, so session-level features such as prepared statements, temporary tables, and SET commands may not behave as expected. For full session features use session mode or an in-process pool.
Thread Pooling
Reuse threads instead of creating them, the pattern that makes web servers handle thousands of concurrent connections
Connection Reuse
Pool and reuse connections across your application to maximize throughput and minimize overhead
Network Optimization
Reduce latency and bandwidth usage, compression, connection pooling, and protocol tuning
Ambassador Container Pattern
A specialized sidecar that handles outbound connectivity, connection pooling, retries, and protocol translation for external services
Database
An organized collection of data that can be easily accessed, managed, and updated. The backbone of almost every application.
ORM
Object-Relational Mapping: a library that lets you interact with a database using your programming language's objects instead of raw SQL. Drizzle, Prisma, and SQLAlchemy are ORMs.
Throughput
The number of operations a system can handle per unit of time. Think of it as how many cars a highway can move per hour.
Timeout
A maximum duration to wait for an operation to complete before giving up. Without timeouts, a stalled dependency can hang your entire system indefinitely.
SQL
Structured Query Language for managing relational databases. Tables, rows, columns, and powerful joins to query related data.
NoSQL
Databases that don't use traditional table-based relational models. Includes document stores, key-value, graph, and column-family databases.
This page explains the idea. The "Database Connection Pooling" lesson builds it up step by step, with diagrams. It is part of the System Design Masterclass course, which has 770 lessons. 18 of them are free to read. You pay once and keep it for life, with no subscription.
Open the Database Connection Pooling lessoncourse 1
From absolute beginner to principal engineer, drawn step by step.
Asked about Connection Pool in an interview?
Each question has why it is asked, a full answer, what the interviewer listens for, the follow-ups, the common mistakes and a diagram (268 diagrams in all). A PDF you keep, junior to staff level.
India: UPI, cards or netbanking through Razorpay. Elsewhere: card or PayPal. Download at once from My packs.
