A few years ago I had an API that got slower and slower as traffic went up, which is normal, except the database was sitting at about 20% CPU the whole time. I stared at query plans for two days. Added indexes that didn’t need adding. Eventually someone looked at my code for thirty seconds and asked why I was opening a new database connection inside the request handler.

So. Connection pooling.

The basic idea

A connection pool is a bag of already-open database connections that your app borrows from and puts back. That’s the whole thing. The trick is not throwing the connection away when you’re done with it.

Without one, every single query costs you:

  1. Open a TCP socket
  2. TLS handshake
  3. Authenticate
  4. Postgres forks a whole backend process for you
  5. Run the query, the thing you actually wanted
  6. Tear it all down

Everything except step 5 is overhead. On localhost it’s a couple of milliseconds and you’ll never notice. Over a network with TLS it’s more like 50ms. My query took 2ms. You can see why the database looked bored.

With a pool you grab an open connection in microseconds, run the query, hand it back.

The handshake isn’t really the point

That’s the reason everyone gives and it’s the least interesting one.

The real constraint is that databases cap how many connections they’ll accept. Postgres ships with max_connections = 100, and every connection is a separate OS process with its own memory. Few hundred idle connections will eat gigabytes of RAM doing nothing at all. MySQL uses threads so it’s cheaper, but the wall still exists. And when you hit it your app doesn’t degrade, it starts throwing “too many connections” at users.

Too many connections is bad, and too few is also bad

Both directions hurt. They produce completely different symptoms, which is why people fix one and then quietly create the other.

Big pool

Everyone starts out assuming more connections means more parallelism means more throughput. That holds up for a while and then it goes into reverse.

Your database can only do as much work as it has cores and disk for. Eight cores means eight queries genuinely running at any instant. Twenty connections or eight hundred, that number is the same. What changes is how much of the machine goes to managing everyone instead of doing work.

Memory is usually the first thing to break. Each Postgres backend holds 5-10MB just to exist, and then work_mem gets allocated per sort or hash operation per query, so one connection running a query with three sorts can take three times work_mem. Multiply by 400 connections doing something moderately complex and you’re one bad plan away from the OOM killer.

The rest is death by a thousand cuts. The kernel is juggling hundreds of runnable processes across eight cores. Backends fight over shared buffers and pile up in LWLock waits. Hundreds of different working sets thrash the buffer cache, so you go to disk for pages you had in memory five minutes ago. Look at pg_stat_activity on a database in this state and most of what you see is sessions waiting on each other.

What you end up with is throughput that plateaus and then falls off, with latency climbing for everybody. And that last part is worse than it sounds. With 20 connections and a queue, 20 requests run fast and the rest wait a moment. With 800 connections all running at once, all 800 are slow. Same amount of work, much worse p99, and a queue you could have managed has turned into a pile-up.

Then there’s the hard failure. Hit max_connections and new connections get rejected, including the one you’re trying to open to figure out what’s going on. Postgres holds back a few slots via superuser_reserved_connections for exactly that reason, but don’t count on it being enough.

Small pool

People read an article like this one, cut their pool to 5, and create a different problem.

The main one is that your requests queue up while the database sits idle. Pool of 2, fifty concurrent requests, forty-eight of them are waiting in line for nothing while the database runs at 5% CPU. You’re paying for capacity you can’t reach. Every individual query is fast, every database metric looks perfect, and your p99 is terrible. Genuinely one of the more annoying things to debug because the evidence all points somewhere else.

Related, and more confusing: callers eventually time out waiting for a connection, and the error looks database-shaped. So you go dig through the database, which is exactly where the problem isn’t.

Worse than either is the deadlock case, if any code path needs two connections at once. Say a function opens a transaction and calls a helper that grabs its own connection from the same pool. Pool of N, N concurrent requests, every request holding one connection and waiting on a second that nobody is ever going to release. Nothing times out cleanly, the pool is just stuck. If your code does this anywhere, the pool needs to be at least double your worst-case concurrency. Better to not do it.

And a small pool can put you right back where you started on handshakes. If it can’t keep enough connections warm it just opens and closes them constantly, which is the thing you built a pool to avoid. Go has a specific version of this and I’ll get to it.

Finding the middle

Little’s Law is the mental model that made this click for me:

connections needed = throughput × average query duration

500 requests per second at 5ms a query is 500 × 0.005, which is two and a half connections busy on average. That number is small enough to be alarming the first time you compute it, and it’s the reason “just use 20” works so often. You size above the average to absorb variance and slow queries, not to match your request concurrency.

For a ceiling, the HikariCP formula gets quoted everywhere:

connections = (core_count × 2) + effective_spindle_count

Eight cores on SSDs lands around 16-20. Start there, per app instance, and let the two failure modes tell you which way to move. Wait time climbing while the database has headroom, go up. Database CPU pinned and queries degrading, go down.

Multiply before you commit to a number, though. Ten app instances at 20 each is 200 connections, and that has to fit under max_connections with room left over so you can still get in when things are on fire.

When you need one

Long-running process that talks to a database? You want a pool. Web servers, workers, all of it. Not really a judgment call.

Scripts are the obvious exception. A migration that opens a connection, runs, and exits doesn’t need pooling.

Serverless is the trap. Every Lambda instance gets its own pool, and you might have five hundred instances live. Five hundred times a pool of ten is five thousand connections against a database that allows a hundred. The pool isn’t helping, it’s multiplying the damage. Put an external pooler in the middle (PgBouncer, RDS Proxy, whatever your provider has) and set the in-function pool to 1.

And if your code holds a connection open for two minutes, no amount of pool tuning is going to save you. Fix the transaction.

Go

database/sql is already a pool, which confuses people because there’s no separate pool object anywhere. sql.DB is the pool. You create one and share it for the life of the process. You do not open one per request.

package main

import (
	"context"
	"database/sql"
	"time"

	_ "github.com/jackc/pgx/v5/stdlib"
)

func newDB(dsn string) (*sql.DB, error) {
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		return nil, err
	}

	db.SetMaxOpenConns(20)
	db.SetMaxIdleConns(20)
	db.SetConnMaxLifetime(30 * time.Minute)
	db.SetConnMaxIdleTime(5 * time.Minute)

	// sql.Open is lazy and doesn't connect to anything, so ping to find out
	// now rather than on your first real request
	ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel()
	if err := db.PingContext(ctx); err != nil {
		return nil, err
	}
	return db, nil
}

SetMaxIdleConns defaults to 2. Two! So if you bump MaxOpenConns to 50 and leave idle alone, you burst to 50 under load and then slam 48 of them shut the second things quiet down. Over and over. You’re doing TLS handshakes constantly and stacking up sockets in TIME_WAIT, which is the small-pool failure mode wearing a big-pool costume. Set idle equal to max open unless you’ve got a reason not to.

Normal usage, pool invisible:

var email string
err := db.QueryRowContext(ctx, "SELECT email FROM users WHERE id = $1", id).Scan(&email)

QueryRow borrows and returns for you. Query does not, not until you close the rows. So this leaks a connection every time it runs:

rows, err := db.QueryContext(ctx, "SELECT id, email FROM users")
if err != nil {
	return err
}
// nothing closes rows: that connection is never coming back

defer rows.Close(), always. Transactions hold a connection for their whole life and need the same care:

engine = create_engine(
    "postgresql+psycopg://user:pass@host/db",
    pool_size=10,
    max_overflow=5,
    pool_timeout=10,
    pool_recycle=1800,
    pool_pre_ping=True,
)

with engine.connect() as conn:
    result = conn.execute(text("SELECT 1"))

The defer tx.Rollback() immediately after BeginTx is the idiom worth burning into muscle memory. Every early return between there and the commit releases the connection on its own.

Quickly, for comparison, SQLAlchemy:

tx, _ := db.BeginTx(ctx, nil)
user := fetchUser(tx, id)
extra := callThirdPartyAPI(user)  // 2 seconds
tx.ExecContext(ctx, "UPDATE ...")
tx.Commit()

and HikariCP:

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://host:5432/db");
config.setMaximumPoolSize(20);
config.setConnectionTimeout(5_000);
config.setMaxLifetime(1_800_000);

HikariDataSource ds = new HikariDataSource(config);

try (Connection conn = ds.getConnection()) {
    // ...
}

Same idea everywhere. Let the language’s scoping construct release the connection so it isn’t something a human has to remember.

Lifetime settings

ConnMaxLifetime (or pool_recycle, or maxLifetime) needs to be shorter than any idle timeout your database, load balancer, or NAT gateway imposes. If the infrastructure closes a connection first, your pool doesn’t find out, and the next caller gets a “connection reset by peer” that makes no sense in context. I lost an afternoon to that one with an AWS NLB.

Acquire timeouts mostly take care of themselves in Go, since it’s all context-driven. A request with a 5 second deadline will stop waiting for a connection when that deadline hits instead of hanging around forever. Pass real contexts everywhere and you get this for free.

PgBouncer modes, briefly

Session pooling holds a server connection for a client’s whole session. Safe, not efficient. Transaction pooling hands one out per transaction and takes it back after, which is what most people actually want. Statement pooling releases after every statement and is aggressive enough that multi-statement transactions stop working.

Transaction pooling is where the “why did my prepared statements break” bug reports come from. Consecutive transactions can land on different backends, so anything holding session state (prepared statements, SET, advisory locks, LISTEN/NOTIFY) gets confused. With pgx you want default_query_exec_mode=simple_protocol in the connection string or statement_cache_capacity=0. psycopg has prepare_threshold=0, JDBC has prepareThreshold=0.

How it breaks in practice

The classic is a leaked connection. Everything’s fine, then six hours in every request times out waiting for a connection that isn’t coming. Some code path took one and walked off with it. In Go it’s nearly always an unclosed *sql.Rows or a transaction that returned early without rolling back. db.Stats() makes this obvious: InUse climbs and never comes back down.

The one I run into more often is holding a connection across slow I/O:

tx, _ := db.BeginTx(ctx, nil)
user := fetchUser(tx, id)
extra := callThirdPartyAPI(user)  // 2 seconds
tx.ExecContext(ctx, "UPDATE ...")
tx.Commit()

Two seconds of a pooled connection doing absolutely nothing while you wait on someone else’s API. Thirty concurrent requests through that path will starve a pool of 20 and take the whole process down with it. Get the external call out of the transaction.

Last thing: export db.Stats(). InUse, Idle, WaitCount, WaitDuration. Rising WaitCount means the pool is too small for your load. WaitDuration growing while database CPU sits low means definitely too small. Both flat with database CPU pinned means you overshot the other way. That’s your whole tuning loop right there, and you’ll see it long before anyone files a ticket about timeouts.

The thing I’d want past-me to know is that there’s no safe default here. The pool fails in two directions and they pull against each other, so you have to actually watch it. Start at 20 per instance, give connections back religiously, keep max lifetime under whatever timeout the network is going to impose anyway, and adjust from there.