Connection Pooling

{
  "ConnectionStrings": {
    // Password comes from the secret store via ConnectionStrings__Shop in production.
    "Shop": "Host=orders-db.internal;Port=5432;Database=shop;Username=orders_api;Password=<from-secret-store>;Maximum Pool Size=17;Minimum Pool Size=0;Timeout=5;Command Timeout=30;Connection Idle Lifetime=300;Connection Lifetime=3600;Application Name=orders-api;Keepalive=30"
  },
  "Database": {
    "MaxPoolSize": 17
  }
}

using Dapper;
using Npgsql;

public sealed record PaymentResult(string Reference, bool Approved);
public interface IPaymentGateway
{
    // The gateway returns the original result, without charging again, for a repeated idempotency key.
    Task<PaymentResult> ChargeAsync(long orderId, decimal amount, string idempotencyKey, CancellationToken ct);
}

public sealed class CheckoutService(NpgsqlDataSource dataSource, IPaymentGateway gateway)
{
    // Broken: the connection, an open transaction and a row lock stay checked out for the
    // whole gateway call. At 2 s per charge and 50 checkouts a second, the pool of 17 is
    // gone and every other endpoint queues behind checkout.
    public async Task PayBrokenAsync(long orderId, decimal amount, CancellationToken ct)
    {
        await using var connection = await dataSource.OpenConnectionAsync(ct);
        await using var tx = await connection.BeginTransactionAsync(ct);
        await connection.ExecuteAsync(new CommandDefinition(
            "SELECT id FROM orders WHERE id = @orderId FOR UPDATE", new { orderId }, tx, cancellationToken: ct));
        var result = await gateway.ChargeAsync(orderId, amount, $"order-{orderId}", ct);
        await connection.ExecuteAsync(new CommandDefinition(
            "UPDATE orders SET status = 'Paid', payment_ref = @Reference WHERE id = @orderId",
            new { result.Reference, orderId }, tx, cancellationToken: ct));
        await tx.CommitAsync(ct);
    }

    // Fixed: claim the order in a short statement, call the gateway with no connection held,
    // then record the result in another short statement.
    public async Task PayAsync(long orderId, decimal amount, CancellationToken ct)
    {
        await using (var connection = await dataSource.OpenConnectionAsync(ct))
        {
            var claimed = await connection.ExecuteAsync(new CommandDefinition(
                "UPDATE orders SET status = 'Paying' WHERE id = @orderId AND status = 'Pending'",
                new { orderId }, cancellationToken: ct));
            if (claimed == 0) return; // already paid or being paid by another request
        }

        // Same key on every attempt for this order, so a retry can never charge twice.
        var result = await gateway.ChargeAsync(orderId, amount, $"order-{orderId}", ct);

        await using (var connection = await dataSource.OpenConnectionAsync(ct))
        {
            await connection.ExecuteAsync(new CommandDefinition(
                "UPDATE orders SET status = @status, payment_ref = @Reference WHERE id = @orderId AND status = 'Paying'",
                new { status = result.Approved ? "Paid" : "Pending", result.Reference, orderId }, cancellationToken: ct));
        }
    }
}

-- Sessions per application and state: who is using the connection budget.
SELECT application_name, state, count(*) AS sessions
FROM pg_stat_activity
WHERE datname = 'shop'
GROUP BY application_name, state
ORDER BY sessions DESC;

-- Transactions left open by application code, oldest first.
SELECT pid, application_name, now() - xact_start AS open_for, left(query, 80) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

-- The limits being budgeted against.
SHOW max_connections;
SHOW superuser_reserved_connections;

-- Backstop: end sessions that sit idle inside a transaction for over a minute.
ALTER DATABASE shop SET idle_in_transaction_session_timeout = '60s';