+1 (726) 227-3497

Connection Management for Amazon Redshift: Pooling, IAM Credentials and Connection Limits

Redshift is built for a modest number of heavy analytical queries, not thousands of short-lived application connections. Teams discover this when a dashboard refresh, a fleet of Lambda functions or an Airflow backfill hits the connection cap and new clients start failing with too many connections errors. This tutorial covers how to see who holds your connections, cap the damage per user, connect with short-lived IAM credentials, and choose between pooling and the Data API.

Step 1: See who is connected

Start with the current sessions. STV_SESSIONS works on provisioned clusters; on both provisioned and Serverless you can use SYS_CONNECTION_LOG for history.

-- Current sessions per user
SELECT user_name, COUNT(*) AS sessions
FROM stv_sessions
GROUP BY user_name
ORDER BY sessions DESC;

-- Connection history: who is connecting, and how often
SELECT user_name, event, COUNT(*) AS events
FROM sys_connection_log
WHERE record_time > DATEADD(hour, -24, GETDATE())
GROUP BY user_name, event
ORDER BY events DESC;

A user with hundreds of initiating session events per hour but few concurrent sessions is opening and closing connections constantly. A user with many long-lived idle sessions is leaking them. The fixes differ.

Step 2: Cap connections per user

The cluster-level limit depends on node type and configuration, so check the current documentation for your setup rather than assuming a number. What you control is the per-user limit, which stops one noisy service account from starving everyone else:

ALTER USER svc_airflow CONNECTION LIMIT 20;
ALTER USER svc_bi_tool CONNECTION LIMIT 30;

-- Verify
SELECT usename, useconnlimit FROM pg_user WHERE usename LIKE 'svc_%';

Give each application its own database user. Shared logins make both limits and the connection log useless.

Step 3: Use IAM credentials instead of long-lived passwords

The AWS-maintained Python driver, redshift_connector, can request temporary database credentials for you:

import redshift_connector

conn = redshift_connector.connect(
    iam=True,
    cluster_identifier="analytics-prod",
    database="dev",
    db_user="svc_airflow",
    region="us-east-1",
    # credentials come from the standard AWS chain (role, env, profile)
)
cur = conn.cursor()
cur.execute("select current_user, count(*) from stv_sessions group by 1")
print(cur.fetchall())
conn.close()

The calling role needs permission to obtain cluster credentials (for example redshift:GetClusterCredentials, scoped to the specific dbuser resource) or the Serverless equivalent. Scope it tightly: a wildcard on dbuser lets the role act as any database user.

Temporary credentials expire, but an already-open session keeps working after expiry. Expiry only matters when you open a new connection, so pools must fetch fresh credentials each time they create one rather than caching a password at startup.

Step 4: Decide between pooling and the Data API

WorkloadRecommended approach
Long-running ETL, a few workersDirect connections, one per worker, closed on completion
BI tools with many usersTool-side connection limits plus a dedicated user and WLM queue
Lambda, Step Functions, event-driven jobsRedshift Data API: no connection to hold, asynchronous, IAM-authenticated
Web app issuing many small queriesReconsider: cache results or serve from an operational store; if unavoidable, use a bounded pool

If you do pool, keep it small. A pool of 5 to 10 connections per application instance is plenty for a warehouse; remember total connections equals instances times pool size, and autoscaling multiplies it. Set a maximum lifetime on pooled connections so they pick up fresh credentials, and always return connections in a finally block.

Step 5: Clean up leaked sessions

Find idle sessions and terminate the stale ones as a superuser:

SELECT process, user_name, db_name, starttime
FROM stv_sessions
WHERE user_name = 'svc_bi_tool'
ORDER BY starttime;

SELECT pg_terminate_backend(12345); -- process id from above

Termination rolls back any open transaction, so confirm the session is not mid-load first. Also make sure clients commit or roll back: an idle session holding an open transaction can block maintenance and hold locks, which is worse than the connection it consumes.

Checklist

  • One database user per application, each with a CONNECTION LIMIT.
  • Alert on session counts per user from STV_SESSIONS or SYS_CONNECTION_LOG.
  • IAM temporary credentials, scoped to specific database users.
  • Data API for bursty, serverless callers; small bounded pools elsewhere.
  • Pool connection max lifetime shorter than your credential duration.

If connection storms or workload isolation are a recurring problem in your warehouse, our Redshift Administration and Performance Optimization services cover exactly this kind of review.