Making PostgreSQL Connections More Resilient with TCP Keepalive
The Problem: Silent Connection Failure
PostgreSQL connections often fail quietly rather than catastrophically.
Firewalls, NAT devices, and load balancers routinely terminate idle TCP connections without notifying either side. PostgreSQL still believes the client exists, and the client still believes it is connected until the next query fails in a confusing and disruptive way.
TCP keepalive exists to prevent exactly this class of failure.
What TCP Keepalive Does
TCP keepalive sends lightweight probe packets on idle connections. If the peer responds, the connection remains open. If the peer does not respond, the operating system marks the socket as dead.
This serves two purposes:
- It prevents network equipment from dropping idle connections
- It allows dead peers to be detected earlier and more reliably
Visualization 1: Failure Without Keepalive
sequenceDiagram
participant App
participant Network
participant PostgreSQL
App->>PostgreSQL: Open connection
PostgreSQL-->>App: Connection established
Note over App,PostgreSQL: Connection idle
Network--x PostgreSQL: Firewall drops idle connection
App->>PostgreSQL: Execute query
PostgreSQL-->>App: Connection errorIn this scenario, the failure only becomes visible when work resumes long after the real cause.
Visualization 2: Stability With Keepalive
sequenceDiagram
participant App
participant Network
participant PostgreSQL
App->>PostgreSQL: Open connection
loop Periodically while idle
PostgreSQL->>Network: TCP keepalive probe
Network-->>PostgreSQL: Acknowledgement
end
Note over Network: Connection remains activeSmall, periodic probes keep the connection visible and verifiable.
PostgreSQL Keepalive Parameters
PostgreSQL exposes TCP keepalive tuning through three parameters:
| Parameter | Meaning |
|---|---|
| tcp_keepalives_idle | Seconds of inactivity before probes begin |
| tcp_keepalives_interval | Seconds between probes |
| tcp_keepalives_count | Failed probes before the connection is closed |
These are typically configured in postgresql.conf.
Why Defaults Often Fail
On many systems, the default idle time before keepalive probes begin is measured in hours. Many firewalls expire idle connections in minutes.
This mismatch is one of the most common causes of intermittent PostgreSQL disconnects in real-world environments.
Client and OS-Level Configuration
Some PostgreSQL clients allow keepalive tuning directly. Others only allow enabling keepalive without control over timing.
When client-level tuning is unavailable, adjusting operating system TCP defaults ensures consistent behavior across all PostgreSQL connections on the host.
Long-Running Queries and Lost Clients
Even if the operating system detects a dead socket, PostgreSQL may continue executing a query that has already started.
PostgreSQL 14 introduced the following setting:
client_connection_check_intervalThis causes PostgreSQL to periodically verify that the client connection still exists and to cancel work if it does not.
When Keepalive Tuning Is Necessary
Keepalive tuning is particularly important when:
- Connections pass through firewalls or load balancers
- Long-lived or pooled connections are used
- Unexplained connection resets occur
- Fast cleanup of dead sessions matters
Takeaway
TCP keepalive does not make PostgreSQL faster.
It makes PostgreSQL more predictable.
By aligning PostgreSQL, the operating system, and the network’s expectations of connection liveness, you avoid silent failures and late surprises and gain calmer, more reliable database behavior.