Connection Pool
What it is
Opening a network connection to a database or another service takes time. A program that sends many requests a second would spend most of that time just connecting. A connection pool keeps a few connections open and shares them between callers. A caller borrows one, uses it, and gives it back, and the next caller uses the same connection again. Database drivers and HTTP clients almost always have one inside.
The core idea
A pool has a size limit, the most connections it will ever have open at once. When a caller asks for a connection, there are three cases:
- A free connection is sitting in the pool. Hand it out.
- No free connection, but fewer than the limit are open. Open a new one and hand it out.
- Every connection is busy and the pool is at its limit. Wait until a connection comes back, or until the caller's time runs out.
Giving a connection back does not close it. It goes back in the pool, still open, ready for the next caller. Connections are only really closed when the pool shuts down, or when one is thrown away.
The limit must count connections that are still being opened, too. If two callers both see "3 open, limit 4" and both start opening one, the pool ends up with 5.
Example
A pool with a limit of 2, and three callers, A, B and C:
A asks -> nothing free, 0 open -> open #1, give to A (1 open)
B asks -> nothing free, 1 open -> open #2, give to B (2 open, the limit)
C asks -> nothing free, 2 open -> C waits
A gives back #1 -> C gets #1 (still 2 open, nothing new)
pool shuts down -> #1 and #2 closedWhen every connection is in use, a new caller waits. This is backpressure: the pool slows callers down instead of opening more connections than the server can take.
When opening a connection fails
Networks fail for a moment all the time. A good pool tries again after a short pause, and keeps trying until the caller's time runs out. Waiting a very long time between tries wastes the caller's time, and trying with no pause at all sends too many requests to a busy server.
If the caller's time does run out, the pool must undo everything it did for that caller. It had counted a new connection against the limit before it started opening it. If it forgets to take that count back, the pool believes a connection is open that never existed. Do that enough times and the pool thinks it is full forever, so every later caller waits and fails, even once the network is fine again. People call this a leak, the same word used for a real connection that is opened and never closed.
When a connection attempt hangs
Sometimes a connection is accepted but the server behind it never answers. If one attempt may wait for the caller's whole time limit, that one stuck attempt uses up the whole call. So give each attempt its own shorter limit, a few hundred milliseconds, and try again when it runs out. Do not make that limit too short either. A server that takes 50 milliseconds to answer is slow, not dead, and a limit of 20 milliseconds turns a slow server into an outage.
At work, this looks like a load balancer that accepts connections for a server that has stopped. The dashboard says the pool is full with no live connections, and people hunt for a leak that is really a missing time limit on each connection attempt.
Where you meet this
- Go's
database/sql: aDBvalue is a pool of zero or more connections.SetMaxOpenConnscaps how many are open, and when all are busy, a caller waits until one comes back or its context ends. That is the same contract as this challenge. (database/sql) - PostgreSQL starts a new server process for every connection. (How connections are made) That makes each connection expensive. PgBouncer is a lightweight connection pooler that sits in front of PostgreSQL. (PgBouncer)
- HikariCP, a Java pool, explains why a small pool often beats a large one, and gives
(physical cores * 2) + effective spindle countas a starting size to test. The spindle count is about disks that must really spin and is zero when all the data fits in memory. The article says it has not been checked for SSDs. (About pool sizing)
When to choose it: opening a connection is slow (a network round trip, a login, maybe a new process on the server) and you make many short requests.
When not to: you make a request only now and then, so idle connections just use memory on both sides. Or one connection can already carry many requests at the same time, so one shared connection may be enough.
Common bugs
- Opening a new connection for every request, which ignores the limit and never reuses anything.
- Closing a connection when it is given back, but still keeping it in the pool, so the next caller gets a dead connection.
- Forgetting a connection when it is given back, neither keeping it nor closing it.
- Counting a new connection only after it opens, so several callers can go over the limit together.
- Forgetting to give back the space after a failed try, as described above.
- Letting one connection attempt wait for the caller's whole time limit, or giving each attempt a limit so short that a slow answer counts as a failure.
- Using a smaller fixed limit than the one the pool was created with.
- Counting every request as a new connection in your own numbers, when most of them reused one.
Reading a result
The tester counts real connections as they open and close. "Opened" and "closed" should match by the end. If more were opened than closed, something was left open. "Most open at once" should never go over the limit, and when many callers are busy it should reach the limit.
Keep it fast
Borrowing and giving back a connection should be a few cheap steps: take one from the free list, or put one on it. Avoid creating new objects or lists for every request, and hold any lock only for those few steps, never while a connection is being opened.
Learn more
The design notes for HikariCP (a Java pool) and the source of pgxpool (a Go pool for PostgreSQL) show how production pools work. They add health checks, and close connections that have sat unused for too long.