Skip to content

AWS Updated 2025-07-06

RDS Proxy in front of write-heavy Postgres or MySQL — when it cuts latency and when it adds it

RDS Proxy solves the thundering-herd-after-failover and the Lambda connection-storm problems. It also adds 1–3ms per query and a layer to debug. Here is when it pays off, the exact config, and the IAM auth that bites you on day two.

The customer’s Postgres on RDS sits at 60% CPU and max_connections=300, and the application is throwing FATAL: sorry, too many clients already during the daily 09:00 traffic bump. The reflex is to crank max_connections to 600 and move on. Six weeks later the database is at 90% CPU with most of the cycles burned on Postgres process spawn and context switching, and the application is still timing out. The actual fix is a connection pool in front of the database, and on RDS the path of least resistance is RDS Proxy. It is a managed pooler that sits between the application and the cluster, multiplexes connections, and transparently handles failover. It also adds 1–3ms per query and a layer of IAM that gets misconfigured roughly half the time.

What’s happening

A direct application-to-Postgres connection model assumes the application keeps a small pool, holds connections for the duration of a request, and releases them cleanly. That assumption breaks in three common topologies:

  • Lambda or Fargate fan-out. Hundreds of short-lived workers each opening a fresh connection. Postgres spends more time forking backends than running queries.
  • Per-request connect/disconnect. PHP-FPM, some Ruby setups, anything without persistent connections. Each request pays the TCP + TLS + auth handshake cost.
  • Failover thundering herd. When the writer fails over, every application instance reconnects at once. The new writer accepts the connection storm and immediately hits max_connections while it is still warming buffer cache. The application sees FATAL errors for 30–60 seconds after the failover supposedly completed.

RDS Proxy solves all three. It maintains a warm pool of backend connections to the database, accepts up to MaxConnectionsPercent of the cluster’s max_connections from clients, and multiplexes them onto a smaller pool. During failover, RDS Proxy holds the application’s client-side connection open, queues the in-flight transactions briefly, and resumes against the new writer once it is reachable — the application sees a 5–15 second pause instead of a FATAL storm.

The cost is real. Every query routes through Proxy, which adds latency — typically 1–3ms for in-region calls, more if the application crosses AZ to reach Proxy. For workloads where the typical query is itself 0.5ms, that overhead is meaningful. For workloads where the typical query is 20ms, it is invisible.

The other cost is operational. RDS Proxy uses IAM auth or Secrets Manager rotation, which means a new layer of trust policy to debug. About half of the Proxy onboardings we do hit a Secrets Manager rotation IAM permission gap before the second week.

When Proxy is the right answer:

  • Lambda or serverless workers hitting Postgres or MySQL on RDS / Aurora.
  • Applications that cannot run a local pgbouncer (managed runtimes, Fargate without a sidecar pattern).
  • Write-heavy workloads with frequent failovers (e.g. Multi-AZ on Postgres with weekly engine patches).
  • Workloads where the application’s connection behavior is bursty and hard to control.

When Proxy is the wrong answer:

  • Latency-critical sub-millisecond queries where 2ms of pooler overhead is a 30% latency increase.
  • Single-tenant connection patterns with steady-state connection counts well under max_connections — adding a pooler buys nothing.
  • Applications that rely on session-pinned features Postgres-side (SET LOCAL, advisory locks, prepared statements created session-wide) and cannot tolerate Proxy’s session-pinning behavior.

How to verify

Confirm the problem first. The pg_stat_activity view tells you what your connection pattern actually looks like:

SELECT state, count(*), 
       max(now() - state_change) AS max_age
FROM pg_stat_activity
WHERE datname = 'production'
GROUP BY state
ORDER BY count(*) DESC;

If the idle count is large relative to active, the application is holding connections it does not need — fix the application pool first. If the idle in transaction count is non-zero and growing, the application is leaking transactions — fix that first. RDS Proxy will not save a workload that leaks transactions; Proxy pins to the same backend for the duration of a pinned session and the leak just moves up a layer.

Check the connection count over time:

aws cloudwatch get-metric-statistics \
  --namespace AWS/RDS \
  --metric-name DatabaseConnections \
  --dimensions Name=DBInstanceIdentifier,Value=prod-db-1 \
  --start-time $(date -u -v-7d +%Y-%m-%dT%H:%M:%S) \
  --end-time $(date -u +%Y-%m-%dT%H:%M:%S) \
  --period 60 \
  --statistics Maximum \
  --query 'Datapoints[].Maximum' \
  --output text | sort -n | tail -1

If the maximum 1-minute count is approaching max_connections, you have either a pooling problem or a tuning problem. If it is spiky (200 to 800 in 30 seconds), you have a herd problem and Proxy is a fit.

Look at recent failovers in the RDS event log:

aws rds describe-events \
  --source-identifier prod-db-1 \
  --source-type db-instance \
  --duration 10080 \
  --query 'Events[?contains(Message, `failover`)].[Date,Message]' \
  --output table

If failovers in the last quarter produced application incidents (check your alerting backlog), the failover-transparency value of Proxy is real for you.

The fix

The Proxy setup, end to end. Assume Postgres on RDS, IAM auth, and Secrets Manager for the actual database password.

Create the Secrets Manager secret with the database credentials:

aws secretsmanager create-secret \
  --name rds/prod-db-1/proxy-creds \
  --secret-string '{"username":"app","password":"REDACTED"}'

Create the Proxy IAM role with the trust policy for the RDS service:

{
  "Version": "2012-10-17",
  "Statement": [{
    "Effect": "Allow",
    "Principal": {"Service": "rds.amazonaws.com"},
    "Action": "sts:AssumeRole"
  }]
}

Attach a policy that lets the role read the secret:

{
  "Version": "2012-10-17",
  "Statement": [{
    "Effect": "Allow",
    "Action": [
      "secretsmanager:GetSecretValue",
      "secretsmanager:DescribeSecret"
    ],
    "Resource": "arn:aws:secretsmanager:us-east-1:123456789012:secret:rds/prod-db-1/proxy-creds-*"
  }]
}

Create the Proxy:

aws rds create-db-proxy \
  --db-proxy-name prod-db-1-proxy \
  --engine-family POSTGRESQL \
  --auth '[{"AuthScheme":"SECRETS","SecretArn":"arn:aws:secretsmanager:us-east-1:123456789012:secret:rds/prod-db-1/proxy-creds-XXXXX","IAMAuth":"REQUIRED"}]' \
  --role-arn arn:aws:iam::123456789012:role/rds-proxy-prod-db-1 \
  --vpc-subnet-ids subnet-aaa subnet-bbb subnet-ccc \
  --vpc-security-group-ids sg-proxy \
  --require-tls

Register the target:

aws rds register-db-proxy-targets \
  --db-proxy-name prod-db-1-proxy \
  --db-instance-identifiers prod-db-1

The application IAM role needs rds-db:connect for the Proxy resource:

{
  "Version": "2012-10-17",
  "Statement": [{
    "Effect": "Allow",
    "Action": "rds-db:connect",
    "Resource": "arn:aws:rds-db:us-east-1:123456789012:dbuser:prx-XXXXX/app"
  }]
}

The connection string on the application side switches from the RDS endpoint to the Proxy endpoint, and the password switches from the static secret to a short-lived IAM token generated at connect time:

TOKEN=$(aws rds generate-db-auth-token \
  --hostname prod-db-1-proxy.proxy-XXXXX.us-east-1.rds.amazonaws.com \
  --port 5432 \
  --region us-east-1 \
  --username app)

PGPASSWORD="$TOKEN" psql \
  "host=prod-db-1-proxy.proxy-XXXXX.us-east-1.rds.amazonaws.com \
   port=5432 dbname=production user=app sslmode=require"

Three things that bite on day two:

Session pinning. Proxy operates in two modes per transaction — multiplexed (a connection is shared between requests) and pinned (a connection is held for one client until they disconnect). Anything session-scoped pins: SET without LOCAL, LISTEN/NOTIFY, temp tables, advisory locks, WITH HOLD cursors. A workload that pins on every request gets zero pooling benefit. The Proxy CloudWatch metric ClientConnectionsCurrentReused versus ClientConnections tells you what fraction of work is multiplexing.

Failover behavior with long transactions. Proxy holds the client connection during failover, but only for connections inside a transaction that has not done any work yet, or outside a transaction. A long-running transaction at failover time is dropped — the application sees an error. Keep transactions short; do not run a 30-second batch update inside a transaction in production.

The RequireTLS flag. Required for IAM auth. If the application connection string omits sslmode=require, the connection fails with a vague error. Default it to sslmode=require everywhere; do not let the connection string drift.

After the cutover, the metric to watch is ClientConnections versus DatabaseConnections on the Proxy resource. A healthy ratio is 5:1 or better — the application opens many connections to the Proxy, and the Proxy multiplexes them onto fewer backend connections. A ratio close to 1:1 means Proxy is not pooling and the workload is mostly pinned. That is a sign to investigate the application’s session behavior before declaring the Proxy a win.

Stack Harbor designs and operates production RDS / Aurora topologies as part of Managed AWS and the ongoing Managed Cloud Ops layer. If your database posture is “Multi-AZ checkbox + hope,” start with an infrastructure consult.