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.
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 Sizethat 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
- Multi-AZ, read replicas, backups and point-in-time recovery: failover is where pools and proxies earn their keep
- Amazon RDS for SQL Server and PostgreSQL and Amazon Aurora
- Module 2: Networking: private subnets and security groups
- Tools: .NET 10 SDK or Docker. For the local probe: Docker Desktop and the module project's
docker-compose.yml. AWS steps are optional.
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:
- A pool in your application (ADO.NET
SqlClient, Npgsql). Closing a connection in your code just hands it back to the pool; the nextOpenAsyncreuses a live one. This is what you already rely on in .NET. - 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):
| Setting | Default | Meaning |
|---|---|---|
IdleClientTimeout | 1,800 s (30 min) | How long a client connection may sit idle before the proxy closes it |
MaxConnectionsPercent | 100 (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) |
MaxIdleConnectionsPercent | 50% of the max (5 for SQL Server) | How many idle database connections it keeps warm |
ConnectionBorrowTimeout | 120 s | How long a client waits for a free database connection before an error |
| Client connection max life | 24 h, not configurable | Pools 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 equivalent | What's different |
|---|---|---|
SqlClient pool, Max Pool Size=100 default, one pool per connection string | Npgsql pool, Maximum Pool Size=100 default, one pool per connection string / NpgsqlDataSource | Same 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 Proxy | Azure'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-pause | Aurora Serverless v2 | v2 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 VM | RDS Proxy | Managed and highly available. PgBouncer gives more knobs (session vs transaction mode, server_reset_query) and costs nothing but your time |
| Managed identity to Azure SQL | IAM database authentication to the DB or the proxy | A 15-minute signed token replaces the password. Supported for PostgreSQL, MySQL and MariaDB, not RDS for SQL Server |
Connection resiliency (EnableRetryOnFailure) | Same EF Core setting | Retries 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 <= budgetWorked 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
| Setting | SqlClient (Microsoft.Data.SqlClient) | Npgsql 10 |
|---|---|---|
| Max pool size | Max Pool Size, default 100 | Maximum Pool Size, default 100 |
| Min pool size | Min Pool Size, default 0 | Minimum Pool Size, default 0 |
| Wait for a free connection | Connect Timeout, default 15 s | Timeout, default 15 s |
| Retire connections by age | Load Balance Timeout (default 0 = never) | Connection Lifetime, default 3,600 s |
| Retire idle connections | Pruned automatically after a few minutes (not configurable) | Connection Idle Lifetime, default 300 s, checked every Connection Pruning Interval (10 s) |
| Reset state on return | Runs sp_reset_connection | Resets 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:
- One
NpgsqlDataSourceper 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. - Set
Maximum Pool Sizefrom the budget, not from the default. - 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 andConnection Idle Lifetimebelow 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):
SETcommands, andset_config, including resetting a parameter.PREPARE,EXECUTE,DEALLOCATEandDISCARDused to manage prepared statements.- Creating temporary tables, views or sequences; declaring cursors;
LISTEN. - Calling
nextvalandsetval, 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 ALLas 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:
- Engine compatibility: PostgreSQL. Proxy identifier:
hl-m06-ledger. - Idle client connection timeout: leave 30 minutes, or lower it if you want stale clients cleared sooner.
- 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. - Authentication: choose the secret for the database user. Set IAM authentication to Required.
- 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.
- Additional configuration: leave Enable enhanced logging off unless you are debugging. It records SQL text.
- 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 $REGIONThe 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.
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 });
}
}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.
<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>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}.";
}
}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 50Illustrative 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: 17Code explained line by line
ConnectionBudgetis deliberately free of I/O.AvailableForApplicationssubtracts a reserve for administration, monitoring and migrations, because the database's own tools need connections too.MaxPoolSizePerInstancemultiplies the peak instance count by1 + 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.- In
Program.cs,NpgsqlConnectionStringBuildersetsMaxPoolSizeand anApplicationName. The application name lets the monitor count only this program's connections inpg_stat_activity. NoResetOnClose = proxySafeis the switch for the pinning trap described above, off by default so the probe is safe against a plain database.NpgsqlDataSource.Createbuilds the pool. Both the workload and the monitor areawait usingso the pools are disposed cleanly.- The monitor polls
pg_stat_activityevery 20 ms with its own single-connection pool, so measuring does not disturb the pool being measured. Parallel.ForEachAsyncwithMaxDegreeOfParallelism = workerslaunches all callers at once. Each runspg_sleepto hold a connection forholdMs. Callers beyond the pool size wait inside Npgsql for a free connection (up toTimeout), which is where the latency tail comes from.- The summary prints percentiles from the sorted latencies and then uses
ConnectionBudgetwith an illustrativemax_connectionsof 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:Decryptvia Secrets Manager only, as in the policy above. - Require TLS on both hops. Set
RequireTLSon the proxy (client to proxy), and keeprds.force_ssl=1on the database. A proxy does not remove the need to verify the server certificate in the client: useSSL 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
MaxConnectionsPercentbudget, 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=1and 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:
DatabaseConnectionsBorrowLatencyabove a threshold, andDatabaseConnectionsCurrentlySessionPinnedabove 20% ofDatabaseConnections.
Common mistakes
- Leaving
Max Pool Sizeat 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
NpgsqlDataSourceor a connection string with changing values per request. Each distinct connection string creates a distinct pool. Log lines likeApplicationName=req-1234multiply pools. - Adding a proxy and changing nothing else. If every transaction starts with
SETor you leave Npgsql's reset on, connections pin and you pay for a proxy that multiplexes nothing. - Setting
MaxConnectionsPercentto 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
| Symptom | Likely cause | Fix |
|---|---|---|
53300: sorry, too many clients already | Fleet 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 calls | Release 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 minutes | Connection borrow timeout reached because the database limit is saturated | Lower ConnectionBorrowTimeout to fail fast, raise MaxConnectionsPercent within budget, check for pinning |
DatabaseConnectionsCurrentlySessionPinned close to DatabaseConnections | Session state: SET, temp tables, prepared statements, DISCARD ALL reset | Remove the SETs, use No Reset On Close, move constant settings to the proxy initialization query |
Sporadic connection reset after a quiet period | Client idle lifetime longer than proxy IdleClientTimeout | Set Connection Idle Lifetime below the proxy's timeout, keep Connection Lifetime under 24 hours |
password authentication failed through the proxy | Secret username does not match the client's user, or the secret was rotated and the proxy still holds the old one briefly | Check the secret JSON, confirm the proxy role can read the current version |
PAM authentication failed through the proxy | IAM token signed for the DB host, not the proxy host, or the role lacks rds-db:connect on the prx- resource id | Generate the token for the proxy endpoint, fix the policy ARN |
no pg_hba.conf entry ... no encryption | Client did not use TLS | Use SSL Mode=VerifyFull (system trust store for the proxy endpoint, RDS CA bundle for a direct database connection) |
| Latency rose after adding a proxy | Pinning, an extra hop, or a pool that is still too small | Measure 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 -vThen 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 20and--pool 100and 200 callers. Write down peak connections, p95 latency and total time. Then change the probe's query frompg_sleepto a CPU-bound query (for exampleSELECT 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.largewhosemax_connectionsis 1,600. Choose a pool size for each service, justify the deployment-overlap factor, and decide whether a proxy is justified. Show the arithmetic withConnectionBudget. - 🔴 Live problem: after enabling a proxy for the ledger API, the team sees
DatabaseConnectionsCurrentlySessionPinnedat 95% and no drop in database connections. The code base hasSET statement_timeoutin an interceptor, usespg_advisory_lockfor 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
Peak connections track the pool size: 5, 20 and, with only 200 callers, up to 100. With pg_sleep the work uses almost no CPU, so a larger pool does shorten total time: 200 callers take about 40 rounds of 50 ms with a pool of 5, about 10 rounds with 20, and about 2 with 100. With the CPU-bound query the database's cores are the bottleneck, so extra connections only add contention and memory, and total time stays flat or worsens. The lesson: a larger pool moves queueing from your application to the database, where it is harder to see and can hurt every other tenant. Choose the smallest pool that meets your latency target.
Budget: 1,600 minus a reserve of 50 gives 1,550. Payments: peak 12 tasks, overlap 0.5 gives 18 instances, so a pool of 20 uses 360. Statements: peak 40, overlap 0.5 gives 60 instances. With a pool of 10 that is 600; with the default 100 it would be 6,000, which exceeds the budget four times over. Total 960 of 1,550, which fits with margin. Without a proxy this works. A proxy is justified only if either service moves to Lambda, or failover reconnect storms are a real risk. Otherwise the proxy adds cost and a pinning risk for no gain. If statements ran in Lambda at 1,000 concurrency, a proxy (with a lower MaxConnectionsPercent for its own proxy) would be required.
Start with evidence: the proxy's enhanced logging (briefly) or the CloudWatch pinned-connection metric broken down per proxy, and a test of each suspect in isolation. The three causes, in increasing effort: (1) Npgsql's reset on return: set No Reset On Close=true once you confirm nothing leaves state behind. (2) SET statement_timeout in an interceptor: SET pins. Set the timeout as the database user's default (ALTER ROLE ledger_app SET statement_timeout = '15s'), in the parameter group, or in the proxy's initialization query, and remove the per-connection SET. Npgsql's CommandTimeout is a separate, client-side setting and does not pin. (3) pg_advisory_lock: session-level advisory locks pin. Switch to pg_advisory_xact_lock, which the documentation says does not pin and which releases at transaction end, or serialise with SELECT ... FOR UPDATE on the account row, or rely on the optimistic concurrency token the module project already uses. Retest with the probe plus a load test and watch the pinned metric drop.
Knowledge check
Answer: The worst case is 30 x 100 = 3,000 connections, so the 200 limit is reached at roughly 2 busy tasks. The first fix is to set Maximum Pool Size from the budget (for example 5 per task: 30 x 5 = 150). A proxy is the second option if per-task pools still cannot fit. Raising the instance class only moves the ceiling.
Answer: When RDS Proxy detects session state that other clients must not inherit (for example SET, temporary tables, prepared statements, session advisory locks), it keeps one client connection on one database connection until the client disconnects. It defeats multiplexing, so many pinned connections make the proxy ineffective.
Answer: The proxy closes client connections idle longer than IdleClientTimeout (30 minutes by default). If the application pool keeps such a connection and reuses it, the first query fails with a reset. Retiring connections earlier than the proxy does avoids it.
Answer: No. The IAM database authentication documentation lists MariaDB, MySQL and PostgreSQL. For SQL Server use Active Directory authentication or Secrets Manager credentials. Proxy features also vary by engine, so check the engine's page.
Answer: Without: up to 500, one per execution environment. With a proxy: 500 client connections to the proxy, but the proxy opens only as many database connections as transactions actually running at once, capped by MaxConnectionsPercent.
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_connectionsminus 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?
DatabaseConnectionsversusMaxDatabaseConnectionsAllowed,DatabaseConnectionsBorrowLatency,DatabaseConnectionsCurrentlySessionPinned, and application pool wait times. - A team sets
Max Pool Size=500to "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
A payments service runs as Lambda functions that read and write an RDS for PostgreSQL database. During traffic spikes the database reports too many connections and failover causes a long outage for callers. What should the architect do?
A. Increase the Lambda memory size B. Place Amazon RDS Proxy between the functions and the database C. Enable a read replica and send writes to it D. Raise the function timeout to 15 minutes
Answer: B. RDS Proxy pools and shares database connections and holds client connections through failover. A does not change connection count. C does not accept writes. D makes failures last longer.
An application uses RDS Proxy for PostgreSQL but CloudWatch shows DatabaseConnectionsCurrentlySessionPinned nearly equal to DatabaseConnections. Which change would MOST reduce pinning?
A. Increase MaxConnectionsPercent to 100
B. Remove per-connection SET statements and session-level prepared statements, or move constant settings to the proxy's initialization query
C. Add a second proxy endpoint
D. Increase the idle client timeout
Answer: B. SET and prepared statement management pin sessions. A, C and D change limits and topology, not the session state that causes pinning.
A container service scales to 20 tasks and uses a connection pool of at most 25 per task against a database with max_connections of 400. During rolling deployments the service briefly runs 30 tasks. What is the most accurate statement?
A. The configuration is safe because 20 x 25 is 500, which is more than 400
B. The configuration can exceed the limit: 30 x 25 = 750 in the worst case
C. The configuration is safe because pools never fill completely
D. Connections are limited by the number of CPUs, not max_connections
Answer: B. Worst case counts deployment overlap. A's arithmetic shows the steady state is already above the limit. C is an assumption, not a guarantee. D is wrong: max_connections is the enforced limit.
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.
Encryption and database access
Encrypt an RDS database at rest with a customer managed KMS key and in transit with verified TLS, then choose between IAM database authentication and rotated Secrets Manager credentials, and prove from C# what the database says about your session.
EF Core on AWS
Choose the right EF Core provider for RDS and Aurora, make units of work retry-safe across failovers, and ship migrations from a pipeline as a one-shot job without locking a busy ledger table.