I’m seeing strange load balancing behaviour when using Npgsql. I have one primary and three standby servers. As the primary can change (if it goes down and a standby is promoted), I’ve included all four servers in my connection string, but specified that Standby should be used.

However, the round-robin implementation seems not to balance load evenly. In the following example, where the Primary is host-2, host-3 gets double the amount of connections of the other standby servers. It looks like the round-robin checks the status of host-2 each time it is host-2’s turn, and seeing that it is Primary, selects host-3 instead. The round-robin then continues its cycle with host-3 again, meaning that host-3 always gets two connections in succession during the round-robin.

What am I doing wrong here?

public async Task TestConnections(CancellationToken ct = default)
{
    var dataSourceBuilder = new NpgsqlDataSourceBuilder("Host=host-1,host-2,host-3,host-4;Username=***;Password=***;Database=mydb;Load Balance Hosts=True");
    await using var dataSource = dataSourceBuilder.BuildMultiHost()
        .WithTargetSession(TargetSessionAttributes.Standby);

    await using var connection1 = await dataSource.OpenConnectionAsync(ct); // host-1
    await using var connection2 = await dataSource.OpenConnectionAsync(ct); // host-3
    await using var connection3 = await dataSource.OpenConnectionAsync(ct); // host-3
    await using var connection4 = await dataSource.OpenConnectionAsync(ct); // host-4
    await using var connection5 = await dataSource.OpenConnectionAsync(ct); // host-1
    await using var connection6 = await dataSource.OpenConnectionAsync(ct); // host-3
    await using var connection7 = await dataSource.OpenConnectionAsync(ct); // host-3
    await using var connection8 = await dataSource.OpenConnectionAsync(ct); // host-4
    await using var connection9 = await dataSource.OpenConnectionAsync(ct); // host-1
    await using var connection10 = await dataSource.OpenConnectionAsync(ct); // host-3
    await using var connection11 = await dataSource.OpenConnectionAsync(ct); // host-3
    await using var connection12 = await dataSource.OpenConnectionAsync(ct); // host-4
}