Managed Databases

A managed database service gives you a database endpoint instead of a server. The provider runs the engine, applies its patches, takes backups on a schedule, and can move the database to standby hardware when the primary fails. You connect with a connection string and write queries, exactly as before.

The work that disappears is the work nobody enjoys and everybody postpones: patching a database engine, verifying that last night's backup actually completed, and building a failover you hope never runs. The work that remains is the work that needs your knowledge of the data — the schema, the indexes, the queries, and who is allowed to connect.

One constraint catches almost everyone moving to a managed database: connections are limited, and the limit is tied to the tier you bought. A small tier may allow a couple of hundred connections in total, and that total covers your API replicas, background jobs, reporting tools and whoever is connected with a query window open.

Opening a connection is also not cheap. It involves a network round trip, a TLS handshake and authentication — commonly tens of milliseconds. Doing that per request adds latency to every request for no benefit.

A connection pool solves both problems. The pool opens a small number of connections, keeps them open, and lends one to each request that needs it. Your code opens and closes a connection as usual; the pool intercepts and hands back a live one. Modern data access libraries pool by default, which means the mistake is rarely forgetting to pool — it is failing to notice that the pool size multiplied by the replica count exceeds what the database allows.

// Budget: the database tier allows 200 connections.
// Reserve some for jobs and humans, then divide the rest by the replica ceiling.
//   200 total - 40 reserved = 160, across 8 replicas = 20 per replica.
var connectionString = new NpgsqlConnectionStringBuilder
{
    Host = builder.Configuration["Database:Host"],
    Database = "employees",
    Username = "employees_api",
    Password = employeesDbPassword,   // from the secret store, never from a file
    SslMode = SslMode.Require,
    Pooling = true,
    MinPoolSize = 2,
    MaxPoolSize = 20,
    Timeout = 15,          // seconds to wait for a free pooled connection
    CommandTimeout = 30    // seconds a single query may run
}.ConnectionString;

builder.Services.AddDbContext<EmployeesContext>(options =>
    options.UseNpgsql(connectionString, npgsql =>
    {
        // Failover drops open connections. Retry the transient case,
        // so a standby promotion is a blip rather than an outage.
        npgsql.EnableRetryOnFailure(maxRetryCount: 3, maxRetryDelay: TimeSpan.FromSeconds(5),
            errorCodesToAdd: null);
    }));

• The arithmetic in the comment is the part worth copying. Pool size is a per-process ceiling, so the number that matters is pool size times maximum replicas, plus everything else that connects. Autoscaling makes this easy to get wrong, because the replica count that breaks the limit only appears under load.
• Reserving headroom keeps a path open for migrations, background jobs and a human investigating an incident. A database at its connection limit refuses new connections, including the one you need to diagnose it.
• Timeout is how long a request waits for a free connection from the pool. A short value turns pool exhaustion into fast, visible failures rather than requests that hang until the client gives up. CommandTimeout is a separate limit on one query, and leaving it unbounded lets a single bad query hold a connection indefinitely.
• EnableRetryOnFailure exists because managed databases move. Failover and maintenance both drop connections, and retrying the transient errors turns a few seconds of disruption into something users may not notice. It retries only errors the provider marks as transient, so it will not mask a genuine query fault.
• The password comes from a secret store, which the next lesson covers. It is worth noting here because a connection string is the most common place a credential ends up committed.