Skip to Content
Cloud Engineering Academy
6. Databases & Data Architecture

Connection pooling and RDS Proxy

Work out how many database connections a .NET fleet can open, size ADO.NET and Npgsql pools to stay inside the limit, and decide when to put Amazon RDS Proxy in front of RDS or Aurora, including what makes it stop helping.

intermediate⏱ 1 h 30 min🟢 FreeSAA-C03DVA-C03

Assumes: .NET 10 LTS · Npgsql 10.0.3 · Amazon.CDK.Lib 2.277.0 · Terraform AWS provider v6 · RDS for PostgreSQL 17 · Region us-east-1. Verified against official sources on 2026-11-18.

Learning objectives

By the end of this lesson you can:

  • Explain why a database has a hard connection limit, why every connection costs memory, and how that limit is hit by autoscaled containers and Lambda long before the CPU is busy.
  • Calculate a connection budget for a fleet and choose a Max Pool Size that survives scale-out and rolling deployments.
  • Describe what RDS Proxy does (multiplexing, failover handling, IAM and Secrets Manager integration), what it costs, and what pinning is.
  • Choose between application pool only, RDS Proxy, and Aurora Serverless v2 with a proxy, and write the decision down.
  • Create an RDS Proxy with the CLI, CDK (C#) and Terraform, and connect an Npgsql client that does not defeat it.
  • Run a local probe that proves your pool never exceeds its ceiling.

Prerequisites

Why this matters in banking

Month-end at Harbourline. The statements service autoscales from 4 to 40 containers at 02:00 to render PDFs, and every container's ADO.NET pool is left at its default ceiling of 100. Nothing is wrong with any single container. But 40 times 100 is 4,000 potential connections, and the db.r6g.large behind the ledger accepts a few thousand at the very most, with most of those already reserved. The first 25 containers connect happily. The 26th gets 53300: sorry, too many clients already. Retry logic kicks in, each retry opens another connection, and within a minute the payments API, which shares the database, is also failing with the same error. A reporting job has taken down card authorisation.

The same story is worse with Lambda. A burst of 1,000 concurrent invocations creates 1,000 execution environments, each with its own pool and its own connection. Every environment has to complete a TLS handshake and a login before it can run a query, and the database spends its CPU authenticating instead of serving requests.

Connection management is therefore not a tuning detail. It is a capacity and blast-radius decision, and one that auditors ask about under operational resilience: "what stops one noisy workload from starving the ledger?"

The idea in plain language

A database connection is not a free thing you "open" and "close". On PostgreSQL each connection is a separate operating-system process with its own memory (commonly several MB each, more once it caches query plans). On SQL Server each connection is a session with a worker thread, memory grant and lock state. Opening one involves a network handshake, a TLS handshake and an authentication round trip, which together typically cost several milliseconds on a good day and far more under load.

Two mechanisms exist to make that affordable:

  1. A pool in your application (ADO.NET SqlClient, Npgsql). Closing a connection in your code just hands it back to the pool; the next OpenAsync reuses a live one. This is what you already rely on in .NET.
  2. A pool outside your application (RDS Proxy, PgBouncer, or the Azure SQL gateway). Many client connections are funnelled into far fewer database connections.

Analogy: The database is a bank branch with 100 tellers. Each customer who walks in gets a teller for the whole visit, even while they search for their paperwork. An application pool is each company sending only a small team of messengers instead of every employee. RDS Proxy is a receptionist: hundreds of customers can queue inside, but the receptionist only lets one up to a teller for the few seconds when they actually have a transaction, then sends the next one.

Two words you will see everywhere:

  • Multiplexing: sharing one database connection between several client connections, switching at transaction boundaries.
  • Pinning: the proxy discovers a client has changed session state (a SET, a temporary table, a prepared statement). It cannot hand that database connection to anyone else, so it stays glued to that client until the client disconnects. If everything pins, the proxy is a very expensive no-op.

What problem the AWS service solves

The connection limit. On RDS for PostgreSQL, max_connections is derived from instance memory. The default parameter-group formula is LEAST({DBInstanceClassMemory/9531392}, 5000) ⚠ VERIFY for your engine version; the useful habit is to ask the database: SHOW max_connections;. A small db.t4g.micro ends up near a hundred, a db.r6g.large in the low thousands, and nothing goes above 5,000 without another design. SQL Server on RDS has a very high configured ceiling (32,767), but memory, not the number, is the real limit. Treat both as capacity you budget, not a number you should approach.

Connection storms. After a failover or restart, every client reconnects at once. A proxy absorbs that herd instead of the database.

Short-lived compute. Lambda and Fargate tasks that scale to zero and back make pools useless: a pool that lives for 30 seconds does not amortise anything.

Credential handling. RDS Proxy can authenticate clients with IAM tokens and fetch the real database password from Secrets Manager, so the application never sees the password.

What RDS Proxy is not:

  • It is not free (see Cost). It bills by database vCPU-hour (or per ACU-hour on Aurora Serverless).
  • It is not a cache or a query router. It never rewrites SQL. (Aurora read-only proxy endpoints exist, but you still choose reader or writer.)
  • It does not fix slow queries. If every query holds a connection for 400 ms, a proxy will queue callers politely and they will still be slow.
  • It lives inside your VPC and is not publicly accessible. Clients connect to the proxy endpoint, not the DB endpoint.
  • It adds a network hop (low single-digit milliseconds ⚠ VERIFY for your Region and engine, measure it) and a second component whose limits matter.

Defaults worth memorising (from the RDS Proxy documentation, checked 2026-11-18):

SettingDefaultMeaning
IdleClientTimeout1,800 s (30 min)How long a client connection may sit idle before the proxy closes it
MaxConnectionsPercent100 (of max_connections)Ceiling on database connections the proxy may open. Docs recommend headroom and give minimums (for example 20 for db.t3.medium and up)
MaxIdleConnectionsPercent50% of the max (5 for SQL Server)How many idle database connections it keeps warm
ConnectionBorrowTimeout120 sHow long a client waits for a free database connection before an error
Client connection max life24 h, not configurablePools must retire connections earlier

Because the proxy closes idle client connections at 30 minutes and everything at 24 hours, an application pool's Connection Idle Lifetime and Connection Lifetime must be shorter than those, or users will see sporadic "connection reset" errors that look like network faults.

Microsoft / Azure comparison

You know (Microsoft/Azure)AWS equivalentWhat's different
SqlClient pool, Max Pool Size=100 default, one pool per connection stringNpgsql pool, Maximum Pool Size=100 default, one pool per connection string / NpgsqlDataSourceSame idea. Npgsql pools per data source, and you should register one NpgsqlDataSource as a singleton
Azure SQL Database gateway (connection redirect vs proxy mode)RDS ProxyAzure's gateway is built in and free of charge. RDS Proxy is an opt-in resource you pay for and manage
Azure SQL serverless auto-pauseAurora Serverless v2v2 scales down to a minimum capacity, it does not pause to zero by default ⚠ VERIFY current minimum behaviour. Connections drop when it scales in, so pooling and retries still matter
PgBouncer on a VMRDS ProxyManaged and highly available. PgBouncer gives more knobs (session vs transaction mode, server_reset_query) and costs nothing but your time
Managed identity to Azure SQLIAM database authentication to the DB or the proxyA 15-minute signed token replaces the password. Supported for PostgreSQL, MySQL and MariaDB, not RDS for SQL Server
Connection resiliency (EnableRetryOnFailure)Same EF Core settingRetries cover the proxy's failover window too. See EF Core on AWS

Architecture

Security-boundary view: the proxy has its own security group. The database admits only the proxy (and perhaps an admin path for migrations). The application tier admits nothing inbound from the data tier. Each hop is TLS. The proxy needs a service role that can read exactly one secret per database user and decrypt it through Secrets Manager.

The connection budget

You can make the exhaustion problem concrete before anything is deployed. The rule:

worst case = (instances at peak, including old + new during a deployment) x (Max Pool Size)
budget     = max_connections - reserved for you and RDS (admin, monitoring, replication, migrations)
need:        worst case <= budget

Worked example. A db.t4g.micro offers about 100 connections ⚠ VERIFY with SHOW max_connections. Reserve 10, so 90 are for the applications. The ledger API autoscales to 4 tasks and a rolling deployment briefly runs half as many old tasks again, so plan for 6.

  • Default pool 100: 6 x 100 = 600 worst case. Over budget by far.
  • Pool 20: 6 x 20 = 120. Still over.
  • Pool 15: 6 x 15 = 90. Fits, with nothing to spare.
  • Pool 10: 60. Comfortable. Is 10 enough? A pool of 10 serving requests that take 20 ms each can sustain about 500 requests per second per task (10 connections divided by 0.02 s). Most APIs need far fewer connections than people think.

The helper in the Complete code section turns this into a function you can call from a test or a design review. The key insight: a smaller pool, with callers queueing briefly, is almost always better than a larger pool that tips the database over. Latency degrades smoothly. Exhaustion fails everyone at once.

With RDS Proxy the budget moves. Clients may open many more connections to the proxy, because those are cheap. What must fit in the budget is MaxConnectionsPercent of the database limit, and that is a single number you control, shared by every client.

Application pools: what the defaults really are

SettingSqlClient (Microsoft.Data.SqlClient)Npgsql 10
Max pool sizeMax Pool Size, default 100Maximum Pool Size, default 100
Min pool sizeMin Pool Size, default 0Minimum Pool Size, default 0
Wait for a free connectionConnect Timeout, default 15 sTimeout, default 15 s
Retire connections by ageLoad Balance Timeout (default 0 = never)Connection Lifetime, default 3,600 s
Retire idle connectionsPruned automatically after a few minutes (not configurable)Connection Idle Lifetime, default 300 s, checked every Connection Pruning Interval (10 s)
Reset state on returnRuns sp_reset_connectionResets session state when returned, unless No Reset On Close=true

Those Npgsql numbers come from the Npgsql connection string documentation; confirm against your Npgsql version ⚠ VERIFY, because defaults have moved between major versions.

Three habits that matter on AWS:

  1. One NpgsqlDataSource per database, registered as a singleton. It owns the pool. Creating a new one per request creates a new pool per request, which is the exact failure you are avoiding.
  2. Set Maximum Pool Size from the budget, not from the default.
  3. Set Connection Lifetime (the lab uses 900 s) so connections retire, re-authenticate with fresh credentials, and rebalance after a failover. When you use RDS Proxy, keep it below 24 hours and Connection Idle Lifetime below the proxy's idle timeout.

Lambda: pool size one, and a proxy

A Lambda execution environment handles one request at a time. A pool larger than 1 or 2 in a Lambda function is wasted memory. But the number of environments equals your concurrency, so the connection count equals your concurrency. This is the strongest argument for RDS Proxy: your function opens one cheap client connection to the proxy, and the proxy shares database connections between many environments. Also note that a frozen environment can come back with sockets the server closed long ago, so the first query after a thaw can fail. Short idle lifetimes and retry logic (EF Core's execution strategy, or Polly) cover that.

What pinning is, and the Npgsql trap

The RDS Proxy documentation lists the PostgreSQL behaviours that pin a client connection to one database connection until it disconnects. In summary (checked 2026-11-18):

  • SET commands, and set_config, including resetting a parameter.
  • PREPARE, EXECUTE, DEALLOCATE and DISCARD used to manage prepared statements.
  • Creating temporary tables, views or sequences; declaring cursors; LISTEN.
  • Calling nextval and setval, and session-level advisory locks (pg_advisory_lock). Transaction-level advisory locks (pg_advisory_xact_lock) do not pin.
  • Loading a library module such as auto_explain.
  • Any statement whose text is longer than 16 KB.
  • A pooling library that sends DISCARD ALL as its reset query. The documentation says the proxy pins the connection when it is released.

That last item is the trap for .NET developers. Npgsql, by default, resets a connection's session state when it returns to the pool. Under RDS Proxy this can pin every connection, so multiplexing disappears. The documented mitigation for pooler-style setups in Npgsql is No Reset On Close=true ⚠ VERIFY on your Npgsql version and read the Npgsql docs on PgBouncer. The condition for turning it on is yours to guarantee: your code must never leave session state behind (no SET, no temp tables) on a pooled connection.

For SQL Server, the documentation lists different pinning triggers: MARS, DTC, temporary tables, cursors and prepared statements, and several SET options such as SET NOCOUNT, SET XACT_ABORT and SET ROWCOUNT. Tracked (non-pinning) options include SET TRANSACTION ISOLATION LEVEL, USE and SET LOCK_TIMEOUT. So code that begins every batch with SET NOCOUNT ON; SET XACT_ABORT ON;, which is common in T-SQL stored procedures, can pin connections. Move such settings into the proxy's initialization query (a proxy property that runs once when the proxy creates a database connection) so clients can stop sending them.

You can see how bad pinning is in CloudWatch: the metric DatabaseConnectionsCurrentlySessionPinned should be a small fraction of DatabaseConnections.

Step-by-step implementation

Measure first: run the pool probe locally

Start the module project's PostgreSQL container and run the probe (code below) with a pool of 20 and 200 concurrent callers. Confirm that the peak number of server connections is 20, not 200, and that latency grows for the callers who queue. This is the behaviour you are sizing.

Write the budget down

Query SHOW max_connections on the real instance. Use ConnectionBudget to compute the pool per task for your peak instance count including deployment overlap. Record the numbers in the ADR.

Create a dedicated database user and secret for the proxy

The proxy logs in to the database as a user whose password is in a Secrets Manager secret in the standard {"username","password"} JSON shape. Use a separate secret per database user, with the same KMS key as the database. The module project's ledger_app user is IAM-authenticated and has no password, so for the proxy create ledger_app with a password secret (see Encryption and access), or use end-to-end IAM authentication where your engine and version support it ⚠ VERIFY.

Create the proxy, its role and its security group

The proxy needs: a service role trusted by rds.amazonaws.com that can read the secret and decrypt via Secrets Manager; a security group that admits only the application; subnets in at least two Availability Zones; and TLS required.

Register the database as the target and set pool limits

Set MaxConnectionsPercent to a value that leaves the database headroom for administration and migrations, start with 50 for a shared database, then watch DatabaseConnections against MaxDatabaseConnectionsAllowed.

Point the application at the proxy endpoint

Change only the host (and use the proxy as the IAM token's host, because the token is signed for the proxy endpoint). Turn on No Reset On Close, keep Connection Lifetime under 24 hours, and confirm that DatabaseConnectionsCurrentlySessionPinned stays near zero under load.

AWS Console instructions

Create the proxy at Console → RDS → Proxies → Create proxy:

  1. Engine compatibility: PostgreSQL. Proxy identifier: hl-m06-ledger.
  2. Idle client connection timeout: leave 30 minutes, or lower it if you want stale clients cleared sooner.
  3. Target group: choose the database instance hl-m06-ledger. Open Connection pool configuration and set Connection pool maximum connections to 50 and Connection borrow timeout to 30 seconds.
  4. Authentication: choose the secret for the database user. Set IAM authentication to Required.
  5. Connectivity: choose the proxy's IAM role (or let the console create one), the subnets, the proxy security group, and tick Require Transport Layer Security.
  6. Additional configuration: leave Enable enhanced logging off unless you are debugging. It records SQL text.
  7. Create, then wait for Available. Open the proxy and copy the Proxy endpoint.

Monitor at Console → RDS → Proxies → hl-m06-ledger → Monitoring and chart DatabaseConnections, ClientConnections, DatabaseConnectionsCurrentlySessionPinned and DatabaseConnectionsBorrowLatency.

AWS CLI

PROFILE=learn-sandbox
REGION=us-east-1

# What does this instance actually allow? (Run from inside the VPC with psql.)
# psql "host=<db-endpoint> dbname=ledger user=ledger_admin sslmode=verify-full sslrootcert=global-bundle.pem" -c "SHOW max_connections;"

# 1) Create the proxy. The SECRETS scheme tells the proxy which database user + password to use;
#    IAMAuth=REQUIRED means clients must present an IAM token to the proxy.
aws rds create-db-proxy \
  --db-proxy-name hl-m06-ledger \
  --engine-family POSTGRESQL \
  --auth "AuthScheme=SECRETS,SecretArn=<app-secret-arn>,IAMAuth=REQUIRED" \
  --role-arn <proxy-role-arn> \
  --vpc-subnet-ids <subnet-a> <subnet-b> \
  --vpc-security-group-ids <proxy-sg-id> \
  --require-tls \
  --idle-client-timeout 1800 \
  --no-debug-logging \
  --tags Key=Project,Value=cea-lab Key=Lab,Value=m06-project \
  --profile $PROFILE --region $REGION

# 2) Register the database instance as the proxy's target (it joins the "default" target group).
aws rds register-db-proxy-targets \
  --db-proxy-name hl-m06-ledger \
  --db-instance-identifiers hl-m06-ledger \
  --profile $PROFILE --region $REGION

# 3) Cap how much of max_connections the proxy may use, and fail fast when it is saturated.
aws rds modify-db-proxy-target-group \
  --db-proxy-name hl-m06-ledger \
  --target-group-name default \
  --connection-pool-config MaxConnectionsPercent=50,MaxIdleConnectionsPercent=25,ConnectionBorrowTimeout=30 \
  --profile $PROFILE --region $REGION

# 4) Wait for the proxy, then look at the target's health and the endpoint.
aws rds describe-db-proxies --db-proxy-name hl-m06-ledger \
  --query 'DBProxies[0].[Status,Endpoint,RequireTLS]' --output text \
  --profile $PROFILE --region $REGION
aws rds describe-db-proxy-targets --db-proxy-name hl-m06-ledger \
  --query 'Targets[].[RdsResourceId,TargetHealth.State,TargetHealth.Reason]' --output table \
  --profile $PROFILE --region $REGION

The service role the proxy assumes (trust policy principal rds.amazonaws.com) needs only these two statements:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "secretsmanager:GetSecretValue",
      "Resource": "arn:aws:secretsmanager:us-east-1:111122223333:secret:hl-m06/ledger-app-AbCdEf"
    },
    {
      "Effect": "Allow",
      "Action": "kms:Decrypt",
      "Resource": "arn:aws:kms:us-east-1:111122223333:key/11111111-2222-3333-4444-555555555555",
      "Condition": { "StringEquals": { "kms:ViaService": "secretsmanager.us-east-1.amazonaws.com" } }
    }
  ]
}

To clean up: aws rds delete-db-proxy --db-proxy-name hl-m06-ledger. Targets are removed with it.

Infrastructure as Code

The stack imports the existing database (created by the module project) rather than owning it, creates the proxy and its security group, and grants the application role rds-db:connect through the proxy. It compiles with Amazon.CDK.Lib 2.277.0.

infra/cdk/LedgerProxyStack.cs
using Amazon.CDK;
using Amazon.CDK.AWS.EC2;
using Amazon.CDK.AWS.IAM;
using Amazon.CDK.AWS.RDS;
using Amazon.CDK.AWS.SecretsManager;
using Constructs;

namespace Harbourline.Infra;

public sealed class LedgerProxyStackProps : StackProps
{
    public required string VpcId { get; init; }
    public required string[] SubnetIds { get; init; }
    public required string[] AvailabilityZones { get; init; }
    public required string DbInstanceIdentifier { get; init; }
    public required string DbEndpointAddress { get; init; }
    public required string DbSecurityGroupId { get; init; }
    public required string AppSecurityGroupId { get; init; }

    /// <summary>Secret holding {"username","password"} for the application's database user.</summary>
    public required string AppSecretArn { get; init; }
    public required string AppRoleArn { get; init; }
}

/// <summary>An RDS Proxy in front of the ledger database, reachable only from the application tasks.</summary>
public sealed class LedgerProxyStack : Stack
{
    public LedgerProxyStack(Construct scope, string id, LedgerProxyStackProps props) : base(scope, id, props)
    {
        var vpc = Vpc.FromVpcAttributes(this, "Vpc", new VpcAttributes
        {
            VpcId = props.VpcId,
            AvailabilityZones = props.AvailabilityZones,
            PrivateSubnetIds = props.SubnetIds,
        });

        var dbSg = SecurityGroup.FromSecurityGroupId(this, "DbSg", props.DbSecurityGroupId);
        var appSg = SecurityGroup.FromSecurityGroupId(this, "AppSg", props.AppSecurityGroupId);

        var proxySg = new SecurityGroup(this, "ProxySg", new SecurityGroupProps
        {
            Vpc = vpc,
            Description = "RDS Proxy for the ledger: PostgreSQL from the application only",
            AllowAllOutbound = false,
        });
        proxySg.AddIngressRule(appSg, Port.Tcp(5432), "Ledger API tasks");
        proxySg.AddEgressRule(dbSg, Port.Tcp(5432), "Proxy to the database");

        // The database must admit the proxy (the application no longer needs direct access).
        _ = new CfnSecurityGroupIngress(this, "DbFromProxy", new CfnSecurityGroupIngressProps
        {
            GroupId = props.DbSecurityGroupId,
            SourceSecurityGroupId = proxySg.SecurityGroupId,
            IpProtocol = "tcp",
            FromPort = 5432,
            ToPort = 5432,
            Description = "RDS Proxy",
        });

        var db = DatabaseInstance.FromDatabaseInstanceAttributes(this, "Db", new DatabaseInstanceAttributes
        {
            InstanceIdentifier = props.DbInstanceIdentifier,
            InstanceEndpointAddress = props.DbEndpointAddress,
            Port = 5432,
            SecurityGroups = [dbSg],
            Engine = DatabaseInstanceEngine.Postgres(new PostgresInstanceEngineProps { Version = PostgresEngineVersion.VER_17 }),
        });

        var proxy = new DatabaseProxy(this, "Proxy", new DatabaseProxyProps
        {
            ProxyTarget = ProxyTarget.FromInstance(db),
            Secrets = [Secret.FromSecretCompleteArn(this, "AppSecret", props.AppSecretArn)],
            Vpc = vpc,
            SecurityGroups = [proxySg],
            RequireTLS = true,
            IamAuth = true,                          // clients present IAM tokens to the proxy
            IdleClientTimeout = Duration.Minutes(30),
            MaxConnectionsPercent = 50,              // share of max_connections the proxy may use
            MaxIdleConnectionsPercent = 25,
            BorrowTimeout = Duration.Seconds(30),    // fail fast instead of waiting the 120 s default
            DebugLogging = false,                    // true logs SQL text, so keep it off outside a lab
        });

        var appRole = Role.FromRoleArn(this, "AppRole", props.AppRoleArn);
        proxy.GrantConnect(appRole, "ledger_app");

        Amazon.CDK.Tags.Of(this).Add("Project", "cea-lab");
        Amazon.CDK.Tags.Of(this).Add("Lab", "m06-project");
        _ = new CfnOutput(this, "ProxyEndpoint", new CfnOutputProps { Value = proxy.Endpoint });
    }
}
infra/cdk/Program.cs
using Amazon.CDK;
using Harbourline.Infra;

var app = new App();

string Ctx(string key) =>
    app.Node.TryGetContext(key)?.ToString() ?? throw new InvalidOperationException($"Pass -c {key}=...");

_ = new LedgerProxyStack(app, "hl-m06-ledger-proxy", new LedgerProxyStackProps
{
    Env = new Amazon.CDK.Environment { Region = "us-east-1" },
    VpcId = Ctx("vpcId"),
    SubnetIds = Ctx("subnetIds").Split(','),
    AvailabilityZones = Ctx("azs").Split(','),
    DbInstanceIdentifier = "hl-m06-ledger",
    DbEndpointAddress = Ctx("dbEndpoint"),
    DbSecurityGroupId = Ctx("dbSg"),
    AppSecurityGroupId = Ctx("appSg"),
    AppSecretArn = Ctx("appSecretArn"),
    AppRoleArn = Ctx("appRoleArn"),
});

app.Synth();

Note the tagging call: inside a Stack, plain Amazon.CDK.Tags.Of(this) resolves to the stack's own Tags property and fails with CS0176, so the fully qualified Amazon.CDK.Tags.Of(this) is required.

Complete code

Two small pieces live in a console project you can run locally. The first is the pure budget calculator, the second is the pool probe that demonstrates the ceiling against a real PostgreSQL. Both compile on .NET 10 with Npgsql 10.0.3.

Pooling/Pooling.csproj
<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net10.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
    <TreatWarningsAsErrors>true</TreatWarningsAsErrors>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Npgsql" Version="10.0.3" />
  </ItemGroup>
</Project>
Pooling/ConnectionBudget.cs
namespace Harbourline.Pooling;

/// <summary>
/// Back-of-the-envelope maths for "how many connections can my fleet open at worst?".
/// Pure functions with no I/O, so they are trivial to unit test and to run in a design review.
/// </summary>
public static class ConnectionBudget
{
    /// <summary>Connections left for the application after the database's own needs.</summary>
    public static int AvailableForApplications(int maxConnections, int reservedForOperations = 10)
    {
        if (maxConnections <= reservedForOperations)
        {
            throw new ArgumentOutOfRangeException(nameof(maxConnections), "max_connections must exceed the operations reserve.");
        }

        return maxConnections - reservedForOperations;
    }

    /// <summary>Worst case: every instance's pool is completely full at the same moment.</summary>
    public static int WorstCase(int instances, int maxPoolSizePerInstance) => checked(instances * maxPoolSizePerInstance);

    /// <summary>
    /// Largest Max Pool Size that keeps the fleet inside the budget even while it scales out to
    /// <paramref name="peakInstances"/>, with a margin for rolling deployments (old and new tasks overlap).
    /// </summary>
    public static int MaxPoolSizePerInstance(int maxConnections, int peakInstances, double deploymentOverlap = 0.5, int reservedForOperations = 10)
    {
        if (peakInstances < 1)
        {
            throw new ArgumentOutOfRangeException(nameof(peakInstances));
        }

        var budget = AvailableForApplications(maxConnections, reservedForOperations);
        var instancesDuringDeploy = (int)Math.Ceiling(peakInstances * (1 + deploymentOverlap));
        return Math.Max(1, budget / instancesDuringDeploy);
    }

    public static string Describe(int maxConnections, int peakInstances, int poolSize)
    {
        var worst = WorstCase(peakInstances, poolSize);
        var budget = AvailableForApplications(maxConnections);
        var verdict = worst <= budget ? "fits" : "EXCEEDS the budget";
        return $"{peakInstances} instances x pool {poolSize} = {worst} connections worst case; budget {budget} of {maxConnections}: {verdict}.";
    }
}
Pooling/Program.cs
using System.Collections.Concurrent;
using System.Diagnostics;
using Harbourline.Pooling;
using Npgsql;

// Pool probe: fires many concurrent short queries through one NpgsqlDataSource and shows that the
// database never sees more connections than "Max Pool Size", while excess callers queue in the pool.
//
//   $env:HL_PG = "Host=localhost;Port=55432;Database=ledger;Username=postgres;Password=local-only-password"
//   dotnet run -- --workers 200 --pool 20 --hold-ms 50 [--proxy-safe]

var connectionString = Environment.GetEnvironmentVariable("HL_PG")
    ?? throw new InvalidOperationException("Set HL_PG to a PostgreSQL connection string (a throwaway local database).");
var workers = ReadInt("--workers", 100);
var poolSize = ReadInt("--pool", 20);
var holdMs = ReadInt("--hold-ms", 50);
var proxySafe = args.Contains("--proxy-safe");

var workloadBuilder = new NpgsqlConnectionStringBuilder(connectionString)
{
    MaxPoolSize = poolSize,
    ApplicationName = "pool-probe",
    Timeout = 30,
    // Behind RDS Proxy: do not send DISCARD ALL when a connection returns to the pool, because the
    // proxy pins a session that does. Only safe if your code never leaves session state behind.
    NoResetOnClose = proxySafe,
};
await using var workload = NpgsqlDataSource.Create(workloadBuilder);

var monitorBuilder = new NpgsqlConnectionStringBuilder(connectionString) { MaxPoolSize = 1, ApplicationName = "pool-monitor" };
await using var monitor = NpgsqlDataSource.Create(monitorBuilder);

var peak = 0;
using var stop = new CancellationTokenSource();
var watcher = Task.Run(async () =>
{
    while (!stop.IsCancellationRequested)
    {
        await using var cmd = monitor.CreateCommand("SELECT count(*) FROM pg_stat_activity WHERE application_name = 'pool-probe'");
        var seen = Convert.ToInt32(await cmd.ExecuteScalarAsync());
        peak = Math.Max(peak, seen);
        await Task.Delay(20);
    }
});

var latencies = new ConcurrentBag<double>();
var started = Stopwatch.StartNew();
await Parallel.ForEachAsync(
    Enumerable.Range(0, workers),
    new ParallelOptions { MaxDegreeOfParallelism = workers },
    async (_, ct) =>
    {
        var sw = Stopwatch.StartNew();
        await using var cmd = workload.CreateCommand("SELECT pg_sleep(@seconds)");
        cmd.Parameters.AddWithValue("seconds", holdMs / 1000.0);
        await cmd.ExecuteNonQueryAsync(ct);
        latencies.Add(sw.Elapsed.TotalMilliseconds);
    });
started.Stop();

await stop.CancelAsync();
await watcher;

var sorted = latencies.Order().ToArray();
double Percentile(double p) => sorted[(int)Math.Min(sorted.Length - 1, Math.Floor(p * sorted.Length))];
Console.WriteLine($"{workers} callers, Max Pool Size {poolSize}, each query holds a connection for {holdMs} ms");
Console.WriteLine($"Peak database connections seen from this app: {peak}");
Console.WriteLine($"Latency ms  p50={Percentile(0.50):F0}  p95={Percentile(0.95):F0}  max={sorted[^1]:F0}  total={started.Elapsed.TotalMilliseconds:F0}");
Console.WriteLine(ConnectionBudget.Describe(maxConnections: 112, peakInstances: 4, poolSize: poolSize));
Console.WriteLine($"Largest safe pool for 4 instances on max_connections=112: {ConnectionBudget.MaxPoolSizePerInstance(112, 4)}");
return 0;

int ReadInt(string name, int fallback)
{
    var i = Array.IndexOf(args, name);
    return i >= 0 && i + 1 < args.Length && int.TryParse(args[i + 1], out var v) ? v : fallback;
}

Run it against the module project's local PostgreSQL:

cd labs/m06-banking-data-store
docker compose up -d
$env:HL_PG = "Host=localhost;Port=55432;Database=ledger;Username=postgres;Password=local-only-password"
dotnet run --project Pooling -- --workers 200 --pool 20 --hold-ms 50

Illustrative output (your numbers will differ):

200 callers, Max Pool Size 20, each query holds a connection for 50 ms
Peak database connections seen from this app: 20
Latency ms  p50=260  p95=480  max=520  total=545
4 instances x pool 20 = 80 connections worst case; budget 102 of 112: fits.
Largest safe pool for 4 instances on max_connections=112: 17

Code explained line by line

  1. ConnectionBudget is deliberately free of I/O. AvailableForApplications subtracts a reserve for administration, monitoring and migrations, because the database's own tools need connections too.
  2. MaxPoolSizePerInstance multiplies the peak instance count by 1 + deploymentOverlap. During a rolling deployment ECS runs old and new tasks side by side, so for a moment you have more instances than your steady-state maximum.
  3. In Program.cs, NpgsqlConnectionStringBuilder sets MaxPoolSize and an ApplicationName. The application name lets the monitor count only this program's connections in pg_stat_activity.
  4. NoResetOnClose = proxySafe is the switch for the pinning trap described above, off by default so the probe is safe against a plain database.
  5. NpgsqlDataSource.Create builds the pool. Both the workload and the monitor are await using so the pools are disposed cleanly.
  6. The monitor polls pg_stat_activity every 20 ms with its own single-connection pool, so measuring does not disturb the pool being measured.
  7. Parallel.ForEachAsync with MaxDegreeOfParallelism = workers launches all callers at once. Each runs pg_sleep to hold a connection for holdMs. Callers beyond the pool size wait inside Npgsql for a free connection (up to Timeout), which is where the latency tail comes from.
  8. The summary prints percentiles from the sorted latencies and then uses ConnectionBudget with an illustrative max_connections of 112.

Security considerations

⚠ Learning shortcut — not for production

The local probe uses a fixed password in an environment variable against a throwaway container. In AWS the application never holds a password: it presents an IAM token (to the proxy) or reads a rotated secret. Do not copy the local connection string pattern into a task definition.

  • The proxy is a new trust point. It can read the database user's secret, so scope its role to exactly that secret ARN and to kms:Decrypt via Secrets Manager only, as in the policy above.
  • Require TLS on both hops. Set RequireTLS on the proxy (client to proxy), and keep rds.force_ssl=1 on the database. A proxy does not remove the need to verify the server certificate in the client: use SSL Mode=VerifyFull. Note that RDS Proxy presents a certificate issued through AWS Certificate Manager, so per the RDS documentation you do not need the RDS CA bundle for proxy connections (the system trust store is enough), whereas a direct connection to the database needs the bundle.
  • Turn off enhanced logging. The proxy's debug logging records SQL statements, which for a bank can include account numbers. Use it for a bounded debugging session with a short log retention, then switch it off.
  • IAM authentication to the proxy removes passwords from the application tier, but the proxy-to-database leg still uses the secret (or end-to-end IAM where available ⚠ VERIFY). Rotating that secret is covered in Encryption and access.
  • Blast radius. A proxy per application or per database user group limits what a compromised client can reach. One shared proxy for the ledger and a reporting tool lets the reporting load drain the pool the ledger needs. Give the reporting tool its own proxy endpoint, user and MaxConnectionsPercent budget, or run it on a read replica.
  • No compliance claim. Connection limits and TLS are technical controls only. Operational resilience and third-party risk reviews decide what is acceptable for your institution.

Banking use case

The payments platform has three workloads sharing one Aurora PostgreSQL cluster: the ledger API (latency-critical, steady), card authorisation callbacks (bursty, Lambda) and nightly statements (heavy, scheduled). The design:

  • The ledger API connects through its own proxy user with a small MaxConnectionsPercent-guaranteed share by running its own proxy. Its pool is sized from the budget formula, 10 per task.
  • Card callbacks run as Lambda with Maximum Pool Size=1 and connect through the proxy, so 800 concurrent invocations become a few dozen database connections.
  • Statements run on a reader endpoint with a separate proxy and database user that has only SELECT. When they stampede at 02:00, the ledger's proxy is untouched.
  • The ADR records the three budgets and the metric alarms: DatabaseConnectionsBorrowLatency above a threshold, and DatabaseConnectionsCurrentlySessionPinned above 20% of DatabaseConnections.

Common mistakes

  • Leaving Max Pool Size at 100 on an autoscaling service. The pool is a ceiling per instance, so the fleet's ceiling is a multiple of it. Compute the budget before the first scale-out.
  • Creating NpgsqlDataSource or a connection string with changing values per request. Each distinct connection string creates a distinct pool. Log lines like ApplicationName=req-1234 multiply pools.
  • Adding a proxy and changing nothing else. If every transaction starts with SET or you leave Npgsql's reset on, connections pin and you pay for a proxy that multiplexes nothing.
  • Setting MaxConnectionsPercent to 100 on a database that others use. Nothing is left for migrations, the DBA, replication slots or another service. Docs recommend headroom.
  • Retrying without a ceiling. Retry storms double the load at the worst moment. Cap retries and add jitter (the EF Core strategy does).
  • Ignoring idle timeouts. A pool that keeps connections for an hour behind a proxy that drops idle clients at 30 minutes produces errors that vanish on retry and are blamed on the network.
  • Running migrations through the proxy. Migrations use SET lock_timeout, long transactions and sometimes advisory locks, all of which pin or block. Run them over a direct connection with an admin identity.

Troubleshooting

SymptomLikely causeFix
53300: sorry, too many clients alreadyFleet worst case exceeds max_connections, or a connection leak (missing await using)Compute the budget, lower Maximum Pool Size, find leaks with pg_stat_activity grouped by application_name and state
The connection pool has been exhausted (Npgsql timeout)Pool too small for the load, or connections held across slow callsRelease connections quickly (open late, close early), never hold one across an HTTP call, raise the pool only within budget
Proxy client waits then fails after about two minutesConnection borrow timeout reached because the database limit is saturatedLower ConnectionBorrowTimeout to fail fast, raise MaxConnectionsPercent within budget, check for pinning
DatabaseConnectionsCurrentlySessionPinned close to DatabaseConnectionsSession state: SET, temp tables, prepared statements, DISCARD ALL resetRemove the SETs, use No Reset On Close, move constant settings to the proxy initialization query
Sporadic connection reset after a quiet periodClient idle lifetime longer than proxy IdleClientTimeoutSet Connection Idle Lifetime below the proxy's timeout, keep Connection Lifetime under 24 hours
password authentication failed through the proxySecret username does not match the client's user, or the secret was rotated and the proxy still holds the old one brieflyCheck the secret JSON, confirm the proxy role can read the current version
PAM authentication failed through the proxyIAM token signed for the DB host, not the proxy host, or the role lacks rds-db:connect on the prx- resource idGenerate the token for the proxy endpoint, fix the policy ARN
no pg_hba.conf entry ... no encryptionClient did not use TLSUse SSL Mode=VerifyFull (system trust store for the proxy endpoint, RDS CA bundle for a direct database connection)
Latency rose after adding a proxyPinning, an extra hop, or a pool that is still too smallMeasure DatabaseConnectionsBorrowLatency, compare with direct connection, fix pinning first

Cost and cleanup

This lesson's local work is free: the probe runs against a Docker container.

An RDS Proxy is a paid, hourly resource. It bills per vCPU-hour of the target database for provisioned instances, per ACU-hour for Aurora Serverless, with 1-second granularity and a 10-minute minimum after a billable status change (create, start or modify), per the RDS Proxy pricing page, checked 2026-11-18. That page did not list per-hour rates in the version fetched, so use the AWS Pricing Calculator for your Region and treat any figure you remember as ⚠ VERIFY. As a rough order of magnitude, a proxy in front of a 2 vCPU db.t4g.micro costs cents per hour, tens of dollars per month if left running. Read-only or read-write extra endpoints create a PrivateLink interface endpoint with its own charge.

Cost label: cost: free for the local probe; credits (cents) if you create a proxy and delete it the same day. If you create a proxy at all, delete it before you stop working.

PROFILE=learn-sandbox
REGION=us-east-1

# Delete the proxy (targets go with it) and confirm nothing remains.
aws rds delete-db-proxy --db-proxy-name hl-m06-ledger --profile $PROFILE --region $REGION
aws rds describe-db-proxies --query 'DBProxies[].DBProxyName' --profile $PROFILE --region $REGION

# CDK / Terraform equivalents:
#   cdk destroy hl-m06-ledger-proxy        (from the CDK app folder)
#   terraform destroy                      (from infra/terraform)

# Local cleanup:
#   docker compose down -v

Then delete the proxy's IAM role and security group if you created them by hand.

Exercises

  • 🟢 Guided: run the probe three times with --pool 5, --pool 20 and --pool 100 and 200 callers. Write down peak connections, p95 latency and total time. Then change the probe's query from pg_sleep to a CPU-bound query (for example SELECT count(*) FROM generate_series(1, 2000000)) and explain why a bigger pool stops helping.
  • 🟡 Challenge: the statements service autoscales to 40 tasks and the payments API to 12. The database is a db.r6g.large whose max_connections is 1,600. Choose a pool size for each service, justify the deployment-overlap factor, and decide whether a proxy is justified. Show the arithmetic with ConnectionBudget.
  • 🔴 Live problem: after enabling a proxy for the ledger API, the team sees DatabaseConnectionsCurrentlySessionPinned at 95% and no drop in database connections. The code base has SET statement_timeout in an interceptor, uses pg_advisory_lock for per-account serialisation, and Npgsql runs with defaults. Write the investigation plan, the fixes in order of effort, and what you would change to avoid advisory-lock pinning.

Assignment

Write docs/adr-0002-connection-management.md for the module project. It must contain the measured or documented max_connections for the chosen instance class, the budget table with deployment overlap, the chosen Maximum Pool Size, a clear proxy yes/no decision with the pinning risks examined, and the alarms you would create (DatabaseConnections against MaxDatabaseConnectionsAllowed, borrow latency, pinned connections). Add your probe output as evidence.

Solutions

Knowledge check

Interview questions

  • How would you decide whether a service needs RDS Proxy? Compute worst-case connections from instance count, pool size and deployment overlap, compare with max_connections minus a reserve, then look at the compute model (Lambda or aggressive autoscaling strongly favour a proxy) and at session-state usage. If the code pins connections, fix that first or the proxy gives little benefit.
  • What would you monitor? DatabaseConnections versus MaxDatabaseConnectionsAllowed, DatabaseConnectionsBorrowLatency, DatabaseConnectionsCurrentlySessionPinned, and application pool wait times.
  • A team sets Max Pool Size=500 to "fix" timeouts. Response? The timeouts are a symptom of slow queries or held connections. Raising the pool moves queueing into the database, risks exhaustion for other services and usually makes p99 worse. Find what holds connections and shorten it.
  • How does a proxy help during failover? Clients stay connected to the proxy while it re-establishes database connections to the new primary, so applications see a short pause rather than a wave of reconnects. Applications still need retries.

Certification practice

Completion checklist

  • I can compute a worst-case connection count and choose a pool size from it
  • I ran the pool probe and saw the ceiling hold
  • I can list four things that pin an RDS Proxy session on PostgreSQL
  • I know the proxy's default idle timeout and borrow timeout, and why pool lifetimes must be shorter
  • I deleted any proxy I created and confirmed nothing is billing

Key takeaways

  • A database connection is a scarce, memory-heavy resource. Budget it: worst case equals instances (with deployment overlap) times pool size.
  • Smaller pools, with brief queueing, beat bigger pools that exhaust the database.
  • RDS Proxy multiplexes at transaction boundaries and helps most with Lambda, bursty autoscaling and failover, but it pins on session state, including Npgsql's default reset, and it costs money by the vCPU-hour.
  • Pool lifetimes must be shorter than the proxy's idle timeout and its 24-hour maximum connection life.
  • Run migrations over a direct connection, never through the proxy, and keep proxy debug logging off.

Further reading

Independent learning resource — not affiliated with, endorsed or sponsored by Amazon Web Services. AWS and related marks are trademarks of Amazon.com, Inc. or its affiliates. All banking data in examples is fictional. Sample architectures are for learning and are not automatically compliant with GDPR, PCI DSS or any banking regulation.

On this page