The pool was full of dead connections

Two months. The on-call rotation had been eating this one for two months, logging it as “intermittent database connectivity issues, self-resolving,” and moving on. Every week, same pattern: burst of errors, thirty to sixty seconds, back to normal. No DB errors on the database side. No network alerts. Database health checks green. The service would throw “broken pipe” or “connection reset by peer” a few dozen times, clients would retry, most of the retries would succeed, and the incident would close itself before anyone finished typing the postmortem title.

I got paged on a Tuesday because it happened during a demo. Not because the engineers finally had time to investigate — because it was visible to a customer. That’s always the threshold, and I have stopped being annoyed about it.

I was annoyed about it.

The one detail nobody had written down

The error messages had been logged. Faithfully. Two months of “broken pipe” entries, timestamped, attributed to specific query attempts, sitting in the aggregated log store waiting to be read carefully. Nobody had read them carefully because the errors self-resolved and careful reading takes time and the queue is always full.

I read them.

The broken-pipe errors were always on the first query of a connection. Never mid-transaction. Never on the second query of a connection that had already done work. Always the first.

That distinction matters. A broken pipe mid-transaction means something interrupted an active, working connection — a network blip, a timeout, a DB restart. A broken pipe on the first query of a connection means the connection was already dead when the application tried to use it. The application thought it had a valid connection. The other end disagreed.

The connection pool was handing out corpses.

How a connection dies without telling anyone

TCP connections are stateful. You probably know this. What’s easier to forget is that “state” is maintained independently by each endpoint, and there is nothing in the protocol that guarantees those states stay synchronized when no traffic is flowing.

A connection sitting idle in a pool looks, from the application’s perspective, exactly like a connection that’s alive and waiting. Same socket. Same file descriptor. Same everything. The pool has no reason to doubt it. It’s not doing anything wrong by holding it. TCP doesn’t have a heartbeat by default — the protocol doesn’t send packets just to confirm someone’s still home.

NAT devices do not share this patience.

The environment in question was a cloud deployment. The application nodes lived in one subnet. The database lived in another. Between them: a stateful NAT device (the cloud provider’s virtual network plumbing, but the specifics don’t matter — this is every cloud, every NAT, same principle). The NAT device maintains a table mapping internal connections to external ports. That table has finite size and finite memory. Idle entries get evicted.

The timeout was 350 seconds.

I didn’t know the timeout was 350 seconds when I started. I found out by doing what you do when the documentation doesn’t exist: timing it.

# on the app node
python3 -c "
import socket, time
s = socket.socket()
s.connect(('db-host', 5432))
print('connected')
time.sleep(360)
try:
    s.send(b'\\x00')
    print('still alive')
except OSError as e:
    print(f'dead after 360s: {e}')
"

Dead. Tried 300 seconds: alive. Tried 330: alive. Tried 340: alive. Tried 350: dead. Tried 355: dead. The NAT entry was being evicted somewhere between 340 and 350 seconds of idle time.

After the entry is evicted, what happens depends on the NAT device. In this case: the cloud provider’s virtual NAT drops the packet and sends a TCP RST back to the sender. The application node sends the first byte of a query, gets a RST in return, kernel delivers ECONNRESET, the pool wrapper translates it to “broken pipe” or similar, and the application sees a failed query.

The pool’s idle connection timeout was set to 600 seconds. Default value, never changed. This meant any connection that had been idle for more than 350 seconds was already dead, but the pool would hold it for another 250 seconds before discarding it. For connections in that 350-to-600-second window, the pool was handing them out as valid and the first query was guaranteed to fail.

Why nobody noticed for two months

The error rate was low and the failures were fast. A broken-pipe on a first query fails immediately — you don’t wait for a timeout. The pool catches the error, marks the connection bad, discards it, opens a new one, and retries. If the pool has enough slack and the burst is short, most client requests succeed on retry and the aggregate success rate stays high enough that no alert fires.

The retry mask a lot of failures that deserve to be surfaced. If a failure is fast and retryable, the monitoring tends to see the successful retry and count the event as a success. The underlying failure never gets a column in the dashboard. This is a design choice that optimizes for aggregate uptime numbers at the cost of visibility into the substrate — and it’s the wrong tradeoff, but it’s the one most systems make by default.

The other thing nobody noticed: the burst timing correlated with traffic valleys. The errors peaked in early-morning hours when query load was low and connections were more likely to have been idle for long stretches. That’s exactly backwards from what people expect — most “database connectivity issues” peak under heavy load, not light load. The on-call engineers kept looking for a load correlation and not finding one, which added to the “flaky, unknown cause” classification.

If you look at TCP socket state with ss -tnp on the app node during a burst, you see nothing obviously wrong — ESTABLISHED everywhere. The kernel doesn’t know the NAT entry is gone. The kernel thinks the connection is fine. The connection is not fine. The kernel will find out when it tries to send something.

Three fixes, not one

Fix one: shorten the pool’s idle timeout. Set it to 290 seconds — shorter than the NAT timeout, with margin. The pool discards idle connections before the NAT does, so there’s no window where the pool holds connections the NAT has already killed. This is the simplest fix and the right starting point. Its downside is connection churn: connections that would have been reused get discarded and recreated. In most deployments this cost is small; if you’re in a regime where connection setup is expensive (TLS + certificate verification + auth handshake), you feel it.

Fix two: TCP keepalives on the connection. SO_KEEPALIVE tells the kernel to periodically send empty ACK packets on idle connections. If the other side doesn’t respond — because the NAT has evicted the entry and a RST comes back, or because the endpoint is down — the kernel marks the connection dead and subsequent send calls fail immediately with ECONNRESET rather than blocking indefinitely.

The relevant sysctls:

# default values — adjust to taste
net.ipv4.tcp_keepalive_time = 7200    # seconds before first keepalive
net.ipv4.tcp_keepalive_intvl = 75     # seconds between keepalive probes
net.ipv4.tcp_keepalive_probes = 9     # probes before declaring dead

The defaults are useless here — 7200 seconds before the first keepalive is two hours, and the NAT evicts at five and a half minutes. You need tcp_keepalive_time below 300 to catch evicted NAT entries before the pool hands them to a client. Setting this system-wide affects every TCP socket, which is usually fine, but if you need per-connection granularity, most connection pool libraries expose a SO_KEEPALIVE socket option you can set on pool-managed sockets directly.

Fix three: test-on-borrow. Most connection pool libraries have a configuration option — variously called test-on-borrow, validation-query, connection-test-query, or heartbeat-query — that sends a lightweight query (usually SELECT 1) before handing a connection to the caller. If the query fails, the pool discards the connection and tries the next one (or opens a new one). This catches dead connections at checkout time, before the application sees them.

The downside is latency: every connection checkout now includes a round trip to the database. For high-throughput systems where connections are checked out and returned quickly, this adds up. For our use case — a background processing service that held connections for seconds to minutes at a time — the overhead was negligible.

We deployed all three fixes, not just one. Belt, suspenders, and also don’t trust the pants. The idle timeout covers the steady-state case. The keepalives give the kernel visibility into connection state without requiring a full query round trip. The test-on-borrow is the last line of defense for connections that slipped through.

The number 350

I spent an hour trying to figure out why the NAT timeout was 350 seconds specifically. The cloud provider’s documentation said “five minutes” for idle connection timeouts, which is 300 seconds, not 350. The observed timeout was 350.

I filed a support ticket. They confirmed that the actual timeout for our specific network configuration was 350 seconds due to a grace period their implementation adds. The 300 seconds in the docs is the “eviction window starts here” value; connections aren’t necessarily evicted at exactly 300 seconds but within some window after that. The “five minutes” in the docs is aspirationally accurate in a way that is not operationally useful.

This is not unusual. Cloud provider documentation frequently describes intended behavior rather than implemented behavior. The only way to know the actual timeout is to measure it, which I did with a loop and a socket and a lot of sleep. The number I measured — 350 — was what mattered for configuring our keepalives. The documented number was what someone’s product manager approved to put in the docs, and it was wrong.

I set tcp_keepalive_time to 240. That gives a 110-second margin against a 350-second NAT timeout, which is enough to account for timing jitter and the grace period behavior and whatever other slop lives in the cloud networking stack. If the NAT timeout changes without warning — and it might; cloud providers update their network plumbing — the keepalives may not save us. But they’re significantly better than relying on the pool’s idle timeout alone.

What I actually think about connection pools

I’ve been hostile to connection pool abstractions for most of my career. They’re a leaky abstraction: they pretend to give you a stable, reliable database connection, but what they actually give you is a socket that might be connected to a database and might be dead and you won’t find out until you try to use it. They hide the complexity of TCP connection management behind an API that looks simple but requires a non-trivial amount of configuration to be safe.

That’s still true.

But this incident changed my thinking about what specifically is wrong. The pool isn’t the problem. The pool was behaving exactly as configured — holding connections up to 600 seconds, handing them out on request. The problem was that the configuration assumed TCP connections are durable in the absence of traffic, which is only true if there’s nothing between you and the database with an opinion about idle connections. There is always something with an opinion. It’s called the network.

The pool is a tool. The tool needs to be configured for the environment it operates in. In an environment with NAT — which is every cloud deployment, and many on-premises deployments — “idle connection timeout shorter than the NAT timeout” is not a bonus optimization. It is a correctness requirement. Getting this wrong doesn’t cause obvious failures; it causes intermittent failures that look random, get classified as flaky, and persist for two months.

The monitoring didn’t catch this either, because the alert was on error rate and the error rate stayed low because retries masked the failures. The right alert is on “errors that are always the first operation on a connection” — which is a derived metric nobody thinks to build until they’ve seen this incident. Now I build it first.

How to find this yourself

If you suspect dead connections in your pool, the fastest check is to look at the timestamps on your error logs and ask: are the failures clustered in low-traffic periods? Is the error always on a connection’s first query? If yes to both: NAT timeout, almost certainly.

You can confirm with a ss -tnp inspection during a burst — ESTABLISHED sockets from your app to your DB that immediately produce errors are the tell. You can measure the NAT timeout with the sleep-and-send loop above. You can find what timeout your pool is configured with by reading the docs or, more reliably, by reading the configuration file and trusting neither until you’ve tested both.

Set tcp_keepalive_time below your measured NAT timeout. Set pool idle timeout below your measured NAT timeout. Add test-on-borrow. In that order, because the first two are free and the third costs a round trip.

Then document what NAT timeout you measured, where you measured it, and when. Because the next person who touches this configuration will not know that 240 seconds is not an arbitrary number — it’s 110 seconds of margin against a 350-second NAT eviction window that the cloud provider documents as “five minutes” and implements as “somewhere around there.” Write that down. If you don’t, the number becomes load-bearing magic that survives four infrastructure migrations and bites someone at 2am in 2049.

I know how this goes.