Database Connections from Python

Scenario

Example app: a Python API uses PostgreSQL on Amazon RDS through psycopg. To avoid reconnecting on every request, the connection is opened at module level.

What you see in production

After a routine Multi-AZ failover (or a database restart), a large share of requests fail for several minutes with psycopg.OperationalError: server closed the connection unexpectedly, until the affected execution environments are recycled. During traffic spikes the database also hits its connection limit.

The naive code

import os, psycopg

conn = psycopg.connect(os.environ["DATABASE_URL"])     # opened once, never checked

def handler(event, context):
    with conn.cursor() as cur:
        cur.execute("select name, price from products where id = %s", (event["id"],))
        return dict(zip(("name", "price"), cur.fetchone()))

Reproduce it

class Connection:
    def __init__(self, generation): self.generation = generation
    def query(self, db_generation):
        if self.generation != db_generation:
            raise ConnectionError("server closed the connection unexpectedly")
        return "row"

def run(reconnect_on_error):
    db_generation, conn, results = 1, Connection(1), []
    for request in range(6):
        if request == 2:
            db_generation = 2                     # failover: old connections are dead
        try:
            results.append(conn.query(db_generation))
        except ConnectionError as e:
            if not reconnect_on_error:
                results.append("ERROR"); continue
            conn = Connection(db_generation)      # reconnect once and retry
            results.append(conn.query(db_generation) + " (after reconnect)")
    return results

print("no reconnect:", run(False))
print("reconnect:   ", run(True))

no reconnect: ['row', 'row', 'ERROR', 'ERROR', 'ERROR', 'ERROR']
reconnect:    ['row', 'row', 'row (after reconnect)', 'row', 'row', 'row']

Root cause

A module-level connection lives as long as the execution environment, often for hours. Failovers, restarts, idle timeouts and network changes kill it silently; nothing re-establishes it, so every request in that environment fails. And because each environment holds a connection, total connections scale with Lambda concurrency.

The fix

• Keep a module-level connection for reuse, but validate and reconnect: catch connection errors, reconnect once and retry idempotent operations (or use a small pool with health checks, such as psycopg_pool with max_size=1).
• Put RDS Proxy in front of the database to pool connections across environments, speed up failover, and use IAM authentication; cap the function's concurrency to protect the database.
• Keep transactions short; never hold a transaction open across invocations.

import os, psycopg

_conn = None

def connection():
    global _conn
    if _conn is None or _conn.closed:
        _conn = psycopg.connect(os.environ["PROXY_URL"], autocommit=True, connect_timeout=3)
    return _conn

def query_product(product_id):
    for attempt in (1, 2):
        try:
            with connection().cursor() as cur:
                cur.execute("select name, price from products where id = %s", (product_id,))
                return cur.fetchone()
        except psycopg.OperationalError:
            global _conn
            _conn = None                     # drop the dead connection and retry once
            if attempt == 2:
                raise

Trade-offs

• Retrying is only safe for idempotent operations; for writes, use idempotency keys or let the error surface.
• RDS Proxy adds cost and some latency, and session features can pin connections.
• DynamoDB or HTTP data APIs avoid connection management entirely, at the cost of a different data model.

Detect it in production

• Error bursts after failovers or maintenance windows, concentrated in long-lived environments.
• Database connection counts relative to Lambda concurrency.
• RDS Proxy metrics (client and database connections, pinning).

Key points

• Module-level connections are reused but can die silently; detect and reconnect.
• Use RDS Proxy and cap concurrency for relational databases.
• Retry only idempotent operations; keep transactions short.

The connection-storm side of this problem is covered in the Node.js course's RDS Proxy lesson.

Further reading: Using Lambda with Amazon RDS · Amazon RDS Proxy