Managed Databases
Connect EF Core to a managed Postgres: sizing the pool across many pods, retrying transient failures, and using a read replica.
options.UseNpgsql(connectionString, npgsql => npgsql.EnableRetryOnFailure(maxRetryCount: 5, maxRetryDelay: TimeSpan.FromSeconds(10), errorCodesToAdd: null));A managed database is one the provider runs for you: Amazon RDS or Aurora, Azure Database for PostgreSQL. You are given a hostname and a login. Backups, patching and failover are theirs. Two things about that change how you write the code.
Connections are pooled per process
Opening a connection to Postgres is expensive, so the driver keeps a pool of open ones and lends them out for the length of a query. Npgsql pools per connection string, inside one process. Each pod therefore has its own pool. Ten pods with a maximum pool size of 100 can ask for a thousand connections, while a small managed instance may allow a few hundred. Size the pool on purpose and multiply by the replica count.
Transient failures are normal
Failover and maintenance drop connections. A command fails once and then succeeds. EF Core handles this with a retrying execution strategy: EnableRetryOnFailure retries a failed command a few times with a growing delay. On a managed database treat it as required configuration, not tuning.
Read replicas
A read replica is a copy of the database that answers queries but accepts no writes. Reports and list endpoints can go there. The copy lags the primary, so a read straight after a write may not see it. Point a second DbContext at the replica rather than switching one at runtime.
What trips people up
Retries replay a whole command, so anything not safe to repeat needs a transaction around it.
Example
// dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
using Microsoft.EntityFrameworkCore;
WebApplicationBuilder builder = WebApplication.CreateBuilder(args);
string primary = builder.Configuration.GetConnectionString("Primary")
?? throw new InvalidOperationException("ConnectionStrings:Primary is not set.");
builder.Services.AddDbContext<AppDb>(options =>
options.UseNpgsql(primary, npgsql =>
// Five attempts, waiting longer each time, up to ten seconds.
// errorCodesToAdd is for PostgreSQL error codes the provider does not
// already treat as transient; null means add none.
npgsql.EnableRetryOnFailure(
maxRetryCount: 5,
maxRetryDelay: TimeSpan.FromSeconds(10),
errorCodesToAdd: null)));
WebApplication app = builder.Build();
app.MapGet("/orders/count", async (AppDb db, CancellationToken ct) =>
{
int count = await db.Orders.CountAsync(ct);
return Results.Ok(new { count });
});
app.Run();
public sealed class Order
{
public int Id { get; set; }
public required string Reference { get; set; }
}
public sealed class AppDb(DbContextOptions<AppDb> options) : DbContext(options)
{
public DbSet<Order> Orders => Set<Order>();
}When to use it
- Keep serving requests through a managed Postgres failover instead of returning 500s for the next thirty seconds.
- Scale to twenty pods without exhausting the database's connection limit.
- Send a heavy reporting query to a replica so it cannot slow down checkout.
More examples
Building the connection string with the pool sized deliberately
Reach for this when you deploy more than one replica, which is every production service. A short Timeout turns pool exhaustion into a quick, visible error instead of requests piling up invisibly behind a full pool.
using Npgsql;
// The builder is easier to get right than a hand-written string, and it
// keeps the password out of anything you might log.
NpgsqlConnectionStringBuilder connection = new()
{
Host = "orders.postgres.database.azure.com",
Database = "orders",
Username = "app",
Password = password, // from the secret store
SslMode = SslMode.Require,
// Per process. Ten pods at 20 is up to 200 server connections,
// so check it against the instance's own connection limit.
MaxPoolSize = 20,
MinPoolSize = 2,
// Seconds to wait for a free connection before failing fast.
Timeout = 5
};
string result = connection.ConnectionString;A second context for the read replica
Two contexts over two connection strings makes the choice explicit at the call site: an endpoint that injects ReadDb cannot accidentally write. Keep migrations on the primary context only, since the replica rejects schema changes.
using Microsoft.EntityFrameworkCore;
string replica = builder.Configuration.GetConnectionString("Replica")!;
builder.Services.AddDbContext<ReadDb>(options =>
options.UseNpgsql(replica, npgsql => npgsql.EnableRetryOnFailure())
// Nothing here will be saved, so do not pay for change tracking.
.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));
// The explicit constructor form. The primary-constructor shorthand
// `class ReadDb(DbContextOptions<ReadDb> options) : DbContext(options)`
// compiles to exactly this.
public sealed class ReadDb : DbContext
{
public ReadDb(DbContextOptions<ReadDb> options) : base(options)
{
}
public DbSet<Order> Orders => Set<Order>();
}A transaction that survives a retry
This is the fix for "the configured execution strategy does not support user-initiated transactions", which is how that error reads. The delegate must be safe to run more than once, because on a transient failure it will be.
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage;
// With retries enabled, calling BeginTransactionAsync on its own throws:
// the strategy cannot replay half a transaction. Hand it the whole unit
// of work instead, and it replays all of it.
IExecutionStrategy strategy = db.Database.CreateExecutionStrategy();
await strategy.ExecuteAsync(async () =>
{
await using IDbContextTransaction transaction =
await db.Database.BeginTransactionAsync(ct);
db.Orders.Add(new Order { Reference = "A-1001" });
await db.SaveChangesAsync(ct);
await transaction.CommitAsync(ct);
});
Discussion