Stop database connections hanging for two hours when the network drops them #1382

Summary

Database connections have no TCP keepalive, so when the network silently drops one, the application does not find out for about two hours. Every other connection in the stack — Redis and the Celery broker — already has keepalive configured, and the settings block that defines it names this exact failure mode in its own comment. Postgres was simply never wired up to it.

The effect for operators is that a dropped database connection turns into a two-hour hang instead of a fast, visible error. In a recent production incident that hang was the precondition for a much larger outage: result-processing workers sat blocked on dead database sockets, and while they were in that state they failed to recover a routine broker reconnect and stopped consuming entirely. ML results were then accepted, queued, and discarded for hours without anything failing loudly.

This change is small and well-precedented in this codebase.

List of Changes

Detailed Description

TCP_KEEPALIVE_OPTIONS is defined in config/settings/base.py (~line 265) with 60s idle / 10s interval / 9 probes, and its comment explains the reasoning:

"Long-idle pooled connections are then vulnerable to silent drops by stateful cloud firewalls (the failure mode behind #1218, and the trigger for #1073)."

It is applied to the Redis cache (base.py ~283-285) and to the Celery broker (base.py ~465-469). It is not applied to the database. DATABASES is a bare env.db("DATABASE_URL") (base.py ~55-59) plus ATOMIC_REQUESTS, and config/settings/production.py adds only CONN_MAX_AGE. There are no OPTIONS and no keepalive settings anywhere on the database connection.

Consequence: Redis and RabbitMQ detect a dead flow in roughly 150 seconds. Postgres relies on the OS default, which on Linux is a two-hour idle timer before the first probe.

Observed behaviour (measured)

In a recent production incident, 35 process_nats_pipeline_result tasks ran for 7,263–7,276 seconds and then all returned within the same second with:

consuming input failed: server closed the connection unexpectedly

Simultaneous mass release points to one shared dependency rather than per-task slowness. The database server itself did not restart (uptime spanned the whole window) and statement_timeout is 0, so nothing server-side terminated them. The pattern is consistent with connections that had already been dropped by the network, with the client only discovering it when the OS keepalive timer finally expired.

Interpretation (not measured)

The two-hour block is what made those workers vulnerable to a separate failure: they were holding 10–32 tasks in flight when a routine broker reconnect occurred, and did not recover it. Workers on other queues, holding 1–3 tasks, recovered the same disconnect in ~11ms. Adding keepalive does not fix that reconnect race — it removes the condition that made it reachable. Those are separable problems and this issue only addresses the first.

What we still need to verify

• That a keepalive-triggered failure surfaces as a clean, retryable Django database error rather than a new failure mode, particularly inside a Celery task holding an open transaction.
• Whether 60s idle is right for the database specifically. It was chosen for Redis and the broker; the database has a different traffic profile and a pooled connection may legitimately idle longer. If it proves too aggressive, the env override exists.
• Whether CONN_MAX_AGE=60 interacts with keepalive in any way worth noting (it should not — that governs connection reuse, not liveness).

Proposed change

DATABASES["default"]["OPTIONS"] = {
    "keepalives": 1,
    "keepalives_idle": env.int("TCP_KEEPALIVE_IDLE", default=60),
    "keepalives_interval": env.int("TCP_KEEPALIVE_INTVL", default=10),
    "keepalives_count": env.int("TCP_KEEPALIVE_CNT", default=9),
}

Note these are psycopg connection parameter names, not the socket.TCP_* constants used in TCP_KEEPALIVE_OPTIONS, so the values are shared but the keys differ.

Update: this now looks like a cause, not just a precondition

Added after reading the celery/kombu/billiard source against the captured container logs.

The original framing above said keepalive "does not fix that reconnect race — it removes the condition that made it reachable." Further investigation suggests that understates it. The evidence now points at the blocked database connections being what prevents the reconnect from completing, which would make this change the fix for both problems rather than for one of them.

Where the worker actually blocks (measured). Consumer.start() handles a lost connection at celery/worker/consumer/consumer.py:359-364:

if blueprint.state not in STOP_CONDITIONS:
    if self.connection:
        self.on_connection_error_after_connected(exc)   # :361
    else:
        self.on_connection_error_before_connected(exc)
    self.on_close()                                     # :363
    blueprint.restart(self)                             # :364

The handler at :361 runs to completion. Its first statement logs CONNECTION_RETRY (:378) and its last logs the prefetch reduction (:402), and every stalled container emitted both and then produced no further MainProcess output at all. So the block is at on_close() or blueprint.restart(), not inside the handler.

In-flight task counts at the moment of the disconnect (measured). That final prefetch message prints len(tuple(active_requests)), which gives celery's own count rather than an inferred one:

Worker concurrency is 16, so the two containers holding 16 were at full pool saturation, and every one of those slots held a task blocked on a database socket with no keepalive. The three affected containers stopped within 3 seconds of each other, one hour after a previous successful reconnect on the same host — consistent with a connection-age ceiling firing across the pool at once.

Interpretation (not measured). A consumer restart has to close and re-register the worker pool. It is hard to see how that completes while every pool slot is occupied by a task inside a syscall that will not return for two hours. If that is what happened, the two-hour hang is not merely the state the workers happened to be in — it is what holds the resource the restart is waiting on, and removing it removes the wedge.

This should be treated as a hypothesis. What would confirm it is a py-spy dump of the parent process while wedged, which names the blocking frame directly and needs no restart or code change. That has not been captured yet because the recovery action destroys the state.

Related upstream

Celery discussion #9095 reports this signature — worker reconnects, the broker shows zero consumers, the process stays alive, only a restart recovers. Open and unanswered since June 2024, reported across celery 5.3–5.5; this project pins 5.4.0. Celery issue #8433 covers the adjacent problem that the broker_connection_retry* flags are not reliable indicators.

If the wedge itself is an upstream defect, then removing the condition that makes it reachable is the part within our control, which is a further argument for doing this one.

One adjacent finding

CELERY_WORKER_CANCEL_LONG_RUNNING_TASKS_ON_CONNECTION_LOSS = True is set in config/settings/base.py, but it is currently inert: the cancel loop at celery/worker/consumer/consumer.py:384-389 is guarded on request.task.acks_late, and acks_late is not set anywhere in config/ or ami/ — no per-task flag and no global CELERY_TASK_ACKS_LATE. So no in-flight task was cancelled on connection loss.

That is worth knowing because it cuts against the reasoning that closed #1324. Had acks_late been enabled as that PR proposed, this setting would have cancelled the blocked in-flight tasks at connection loss, plausibly freeing the pool slots the restart was waiting on. That interaction was not considered at the time. Flagging it for discussion rather than recommending the change, since cancelling in-flight result tasks has its own duplicate-and-loss semantics that need their own thought.

Correction to the premise (same day) — it is untuned keepalive, not absent keepalive

The framing above says the database connection "has no TCP keepalive." That is wrong, and the corrected version is better evidence for the same fix.

libpq defaults keepalives to 1, i.e. enabled. Checked in the running production container: psycopg reports no compiled override for keepalives, keepalives_idle, keepalives_interval or keepalives_count, so libpq's built-in defaults apply and the socket almost certainly has SO_KEEPALIVE set. What is missing is not the mechanism but the tuning — with keepalives_idle unset, the kernel's namespace default governs.

Measured in the production Django container's network namespace:

tcp_keepalive_time   = 7200
tcp_keepalive_intvl  =   75
tcp_keepalive_probes =    9

(The host these containers run on is tuned to 60, but that does not propagate into the container's network namespace.)

This explains the observed durations far more precisely than the original framing did. The 35 tasks ran 7,263–7,276 seconds. The first keepalive probe fires at 7,200 s, and the connection errored within about 60–75 seconds of it — consistent with the peer or an intermediate device answering the first probe with a reset, rather than the connection surviving full probe exhaustion at roughly 7,875 s. Under the original "no keepalive at all" model the hang would have been unbounded, not two hours, so that model actually failed to predict the data it was cited to explain.

Two consequences.

The proposed change is unaffected and now better justified: setting keepalives_idle to 60 with a 10-second interval moves detection from about two hours to about 150 seconds, matching what the Redis and broker connections already do. The keepalives: 1 line in the snippet is redundant but harmless, since that is already the default.

The description of the problem should change in one respect. This is not "a connection with no liveness checking." It is a connection whose liveness checking is on a two-hour schedule while every comparable connection in the stack is on a 150-second one. That is a tuning gap, which is a smaller claim but a firmer one.

Thanks are due to the review that caught this — the original wording would have sent someone looking for a missing SO_KEEPALIVE that is in fact present.