psycopg2 vs Redshift Data API:
when migrating actually pays off.

If your Lambda function talks to Redshift via psycopg2, sooner or later you'll see one of these warning signs: cold-start latency creeping up, intermittent connection timeouts, or the dreaded too many connections error during peak load. The instinct is to add a connection pool. The quieter, often better answer is to drop the connection-based driver entirely and use the Redshift Data API.

On a recent enterprise engagement we made this switch and shaved ~40% off Lambda execution time. But it's not a free lunch — there are real tradeoffs. This post is a no-nonsense guide to when migrating pays off and when it doesn't.

The two approaches, in one table

Why we migrated

The starting symptoms in the engagement that prompted this:

• Lambda functions running in a VPC for Redshift access — cold starts were heavy because of ENI provisioning
• Connection management code was getting complex (try/finally everywhere, retry logic, pool warmup)
• At peak load, we'd occasionally hit FATAL: connection limit exceeded on the Redshift cluster
• The psycopg2 layer added ~5MB to every Lambda deployment, slowing every cold start

None of these are dealbreakers individually. Together they made every Lambda invocation feel heavier than it needed to.

What changed after migration

• ~40% reduction in execution time on the affected functions
• Lambdas no longer required VPC configuration — cold starts dropped meaningfully
• Connection-limit errors disappeared (because there are no connections)
• Deployment package shrank by the size of the psycopg2 layer

The code, side by side

Before — psycopg2:

import psycopg2
import os

def get_connection():
    return psycopg2.connect(
        host=os.environ["REDSHIFT_HOST"],
        port=5439,
        database=os.environ["REDSHIFT_DB"],
        user=os.environ["REDSHIFT_USER"],
        password=os.environ["REDSHIFT_PASSWORD"],
    )

def handler(event, context):
    conn = get_connection()
    try:
        with conn.cursor() as cur:
            cur.execute("SELECT count(*) FROM events WHERE day = %s", [event["day"]])
            return {"count": cur.fetchone()[0]}
    finally:
        conn.close()

After — Redshift Data API:

import boto3
import os
import time

rs = boto3.client("redshift-data")

def execute(sql, params=None):
    resp = rs.execute_statement(
        ClusterIdentifier=os.environ["REDSHIFT_CLUSTER"],
        Database=os.environ["REDSHIFT_DB"],
        SecretArn=os.environ["REDSHIFT_SECRET_ARN"],
        Sql=sql,
        Parameters=[{"name": k, "value": str(v)} for k, v in (params or {}).items()],
    )
    sid = resp["Id"]
    deadline = time.monotonic() + 25
    while time.monotonic() < deadline:
        st = rs.describe_statement(Id=sid)
        if st["Status"] == "FINISHED": break
        if st["Status"] in ("FAILED", "ABORTED"):
            raise RuntimeError(st.get("Error", "query failed"))
        time.sleep(0.1)
    else:
        raise TimeoutError("Redshift statement did not finish in time")
    return rs.get_statement_result(Id=sid)

def handler(event, context):
    result = execute(
        "SELECT count(*) FROM events WHERE day = :day",
        {"day": event["day"]},
    )
    return {"count": int(result["Records"][0][0]["longValue"])}

When NOT to migrate

The Data API isn't always the right call. Stick with psycopg2 if:

• You need streaming or large result sets. The Data API has a 500MB compressed result limit and returns JSON, which is heavier than binary cursors. For analytical workloads pulling millions of rows, psycopg2 with server-side cursors is more efficient.
• You're using prepared statements heavily. The Data API supports parameters but not the same prepared-statement semantics as psycopg2.
• You're outside Lambda — say a long-lived ECS service or a Django web server. Persistent connections amortise their cost; the Data API's per-call HTTP overhead becomes a tax.
• Your query latency is dominated by Redshift execution time, not connection setup. If a query takes 3 seconds in Redshift itself, neither driver will help — you need to optimise the query.

Migration playbook

1. Audit first. List every function that uses psycopg2. For each, note: does it use cursors? transactions? large result sets? prepared statements? This tells you what's safe to migrate.
2. Build a thin abstraction. Create a small wrapper module that exposes execute(sql, params) and fetch_all(sql, params) — backed by either driver. Switch the implementation behind the interface, not in every callsite.
3. Move secrets to Secrets Manager. The Data API authenticates via Secrets Manager natively. Even if you delay migrating, this step alone is a security upgrade.
4. Migrate one function at a time. Behind a feature flag if possible. Start with the simplest read-only function.
5. Watch the metrics. CloudWatch + Lambda Insights will show duration and cold-start changes within a day or two of traffic.
6. Pull Lambdas out of the VPC only after every callsite is migrated. This is where you reclaim the cold-start savings.

One more pattern: the dual access layer

For some teams, neither driver is right alone — different functions have different needs. We've shipped systems with a dual database access layer: a single facade that chooses between psycopg2 and the Data API at runtime based on environment or query type. Long-running analytics functions stay on psycopg2; short, high-concurrency endpoints use the Data API. The application code doesn't know the difference.

This is more code to maintain, but it's the right pattern when you have a mix of workloads on the same cluster.

The takeaway

Migrate when connection management is the bottleneck. Stay when query semantics matter.

For most short-query, high-concurrency Lambda workloads, the Data API is a better fit than psycopg2 — and the migration usually pays for itself in a single sprint.

Working through a similar migration? Book a 30-minute discovery call — happy to share notes from the engagements where we've shipped this.