Steps to reproduce

Add a dummy host to the beginning of the host list in the connection string, then continually make queries which require a connection from the pool, and you'll see a delay every 10 seconds.

The issue

We’ve spent a couple of days troubleshooting a performance issue while connecting to a PostgreSQL cluster from .NET, and believe it might be due to a bug, or possible improvement which could be made in the Npgsql library.

If you have multiple hosts defined in your connection string, then by default, Npgsql attempts to refresh its hosts state cache every 10 seconds (configurable via the ‘Host Recheck Seconds’ connection string parameter). We are seeing that during this time, connections are blocked from being released from the pool (or possibly the pool is emptied?), so queries cannot be performed. If the first host in the list is unavailable for any reason, then this refresh could take a significant amount of time before it gives up and tries the next host. Usually a couple of seconds, but we’ve seen some delays of 10 seconds or more.

So rather than testing hosts in the order defined in the connection string, wouldn’t it make more sense to test a known, working host first? In our case, the NpgsqlDataSource is defined using TargetSessionAttributes.Primary, so all it needs to do is re-test the current primary host (and only look at the others if that host is no longer primary). Instead, we’re seeing long blocks every 10 seconds while Npgsql keeps testing the first (unavailable) host.

Further technical details

Npgsql version: 8.0.3
PostgreSQL version: 16.3
Operating system: Win10