[🐛 Bug]: Critical database connection issues under high alert load

[Viste]

Critical database connection issues under high alert load

Description

Keep backend has multiple critical database connection issues that make it unusable in high-throughput production environments (1000+ alerts/minute). These issues occur both with direct PostgreSQL connections AND through PgBouncer.

Environment

• Keep version: 0.48.0
• PostgreSQL: 15.x (direct connection, VM)
• Python: 3.13
• psycopg2: latest
• SQLAlchemy: 2.0.36
• Deployment: 1 pods, 4 gunicorn workers + 4 ARQ workers each

Configuration tested

DATABASE_POOL_SIZE: "20"
DATABASE_MAX_OVERFLOW: "40"
KEEP_DB_PRE_PING_ENABLED: "true"

---

Issue 1: Connection pool exhaustion (Direct PostgreSQL)

Symptoms

Under moderate load (~50 alerts/second), the connection pool becomes exhausted within minutes:

sqlalchemy.exc.TimeoutError: QueuePool limit of size 20 overflow 40 reached, 
connection timed out, timeout 30.00

Analysis

Connections are acquired but never returned to the pool. Even with pool_pre_ping=True, dead connections accumulate. PostgreSQL shows connections in idle state that are never reused by the application.

Expected: 60 connections (20 + 40 overflow) should be recycled
Actual: All 60 connections become "stuck", new requests timeout

---

Issue 2: Deferred loader failure after INSERT

Error

KeyError: "Deferred loader for attribute 'id' failed to populate correctly"

File "keep/api/core/db.py", line 2716, in create_deduplication_event
    "deduplication_event_id": deduplication_event.id,

Root cause

In db.py:2716, code accesses deduplication_event.id immediately after session.add() but before session.flush() or session.commit(). The ORM hasn't populated the auto-generated ID yet.

Fix required

# Current (broken):
session.add(deduplication_event)
return {"deduplication_event_id": deduplication_event.id}  # ID not populated!

# Should be:
session.add(deduplication_event)
session.flush()  # Force INSERT to get ID
return {"deduplication_event_id": deduplication_event.id}

---

Issue 3: PgBouncer incompatibility

When using PgBouncer (even in session mode), psycopg2 produces protocol-level errors:

psycopg2.DatabaseError: error with status PGRES_TUPLES_OK and no message from the libpq
sqlalchemy.exc.NoSuchColumnError: Could not locate column in row for column 'workflow.id'
IndexError: tuple index out of range

PgBouncer config tested

pool_mode = session
server_reset_query = DISCARD ALL
ignore_startup_parameters = extra_float_digits

Result: Still fails. psycopg2 binary protocol has fundamental incompatibility with PgBouncer.

---

Issue 4: Stale connections not detected

psycopg2.OperationalError: server closed the connection unexpectedly
This probably means the server terminated abnormally before or while processing the request.

PostgreSQL closes idle connections (via idle_session_timeout), but SQLAlchemy pool doesn't detect this despite pool_pre_ping=True.

---

Summary table

Setup	Result	Primary error
Direct PostgreSQL	❌ Fails	Pool exhaustion + deferred loader
Direct + pre_ping	❌ Fails	Same issues
PgBouncer session mode	❌ Fails	Protocol corruption
PgBouncer + DISCARD ALL	❌ Fails	Same protocol errors

This blocks enterprise adoption where connection pooling is mandatory.

[coderabbitai]

📝 CodeRabbit Plan Mode

Generate an implementation plan and prompts that you can use with your favorite coding agent.

• Create Plan

Examples

• Example 1
• Example 2

---

🧪 Issue enrichment is currently in open beta.

You can configure auto-planning by selecting labels in the issue_enrichment configuration.

To disable automatic issue enrichment, add the following to your .coderabbit.yaml:

issue_enrichment:
  auto_enrich:
    enabled: false

💬 Have feedback or questions? Drop into our discord!

[dosubot]

Here’s what’s happening and what you can do:

1. Connection pool exhaustion:
The code uses SQLAlchemy’s connection pool, with pool size and overflow controlled by environment variables. However, only DATABASE_POOL_SIZE, DATABASE_MAX_OVERFLOW, and KEEP_DB_PRE_PING_ENABLED are tunable—other important options like pool_recycle (to proactively close stale connections) are not exposed, so dead connections can accumulate if the DB closes them due to timeouts. This matches the symptoms you’re seeing, especially under high concurrency. The code opens a new session for each operation and should return connections, but if any session is left open (e.g., due to uncommitted/unfinished transactions or long-running tasks), connections can get stuck in idle state and exhaust the pool.
See code and config details
See related issue

2. Deferred loader failure after INSERT:
In create_deduplication_event, the code adds a new event and immediately accesses its .id before flushing or committing. This causes the KeyError you reported, because the ORM hasn’t populated the ID yet. The fix is to call session.flush() after session.add() and before accessing .id. This pattern is already used elsewhere in the codebase (e.g., create_workflow_execution).
See code example

3. PgBouncer incompatibility:
There’s no evidence in the code or docs that Keep is tested or tuned for PgBouncer. SQLAlchemy/psycopg2 can have protocol issues with PgBouncer, especially in transaction pooling mode, and some features (like server-side cursors or prepared statements) are known to break. The errors you’re seeing are consistent with these incompatibilities. There’s no current workaround in the codebase.

4. Stale connections not detected:
Even with pool_pre_ping=True, SQLAlchemy may not always detect when PostgreSQL closes idle connections (e.g., due to idle_session_timeout). Since pool_recycle isn’t exposed, you can’t force SQLAlchemy to proactively recycle connections before the server closes them, which leads to the OperationalError you’re seeing.
See related issue

What you can do now:

• For the deferred loader bug, add session.flush() after session.add() in any function that needs the auto-generated ID before commit.
• For pool exhaustion and stale connections, consider patching the code to expose and set pool_recycle and pool_timeout in the SQLAlchemy engine config. This will help recycle connections before they go stale.
• For PgBouncer, there’s no official support or workaround in Keep as of v0.48.0. If you must use PgBouncer, stick to session pooling mode and avoid features that require server-side state, but be aware that protocol errors may persist.
• For high-throughput, follow the scaling and tuning recommendations in the docs: increase backend/database resources, use Redis for ingestion, and horizontally scale API/digest workers as needed.
  See deployment and tuning docs

There are no merged PRs or official docs addressing these specific backend connection bugs or PgBouncer issues yet. Local code/config changes are the best immediate path forward.

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}