I'm porting a C# .NET 4.8 app from Sql Server to PostgreSQL (13.3, running on Windows 10), connecting with Npgsql (4.0.11, because of .NET 4.8). The project has hundreds of unit tests, each of which first removes any tables before re-creating the app schema (about 20 tables) then performing pretty trivial DB operations (tiny data volumes to test business logic). Run individually, each test completes at about the same speed as Sql Server. But when running them all using vstest.console command line (or in Visual Studio) the tests gradually run slower and slower - rather than completing in about 10 minutes as expected, it's more like 90. If I halt test execution after (say) 15 minutes, then immediately run the last-completed test again, it runs in a fraction of the time (e.g. 2 seconds instead of 14 seconds). Client-side logging indicates that reading operations run at about the same speed regardless; it's writes/DDL that get slower. Server-side timings from log_statement/log_duration show the same pattern, albeit less markedly. Each test wraps writes in one or more transactions, committing before the test ends, and nothing persists between tests (a test may leave tables/data, but the next test drops all the tables as part of its initialisation). The vstest.console process uses minimal processor and a modest amount of memory. The Npgsql connection string doesn't change the pooling defaults (i.e. connection pooling is in use) and while the tests are running, the pgAdmin dashboard only shows a small number of connections.
So: how is it that halting and re-starting vstest.console makes the same unit test run much faster? It seems like the only thing that would change is that all the pooled connections are closed and Npgsql creates new ones, but what state on a pooled connection would make PostgreSQL writes run slower and slower? The Npgsql default Pooled Connection Reset behaviour hasn't been changed in the connection string, i.e. state shouldn't leak.
Each test is single-threaded, and opens, uses then closes a number of database connections (rarely more than one open at a time). A typical test might do this a dozen times, but some do it hundreds of times. The vstest.console runner may run tests in parallel (process has multiple threads) but pgAdmin shows usually only 1 or 2 connections active at a time.
With connection pooling disabled, the tests run slow straight away (not surprisingly) but seem to complete a little quicker - about 70 mins rather than 90 mins, but nothing like the 10 mins expected. This might suggest that, even when enabled, pooling stops being used after a few minutes, but server logs indicate that writes really are taking longer. Experimentally setting the minimum pool size to 10 doesn't make a noticeable difference, and neither does setting NoResetOnClose. While tests are running, Resource Monitor shows about a dozen 'postgres.exe' processes, but only one of them using processor time, hovering between about 5% and 10%. It's also shown as doing most of the disk reading and writing (much more writing than reading, which is not totally unexpected for the app concerned). Its Working Set size gets rather large, reaching 2.3GB or so before dropping down to 1.7GB, but there's 32GB in the machine with only about 60% physical used, and no storm of page faults. PostgreSQL is running on the same Windows 10 machine as the tests, so network shouldn't be a factor.
Since restarting the client process (and so dumping all pooled connections) makes things fast again, I wondered about using the ConnectionLifetime parameter (not ConnectionIdleLifetime) to get the same effect, as a workaround, but Npgsql says it's not supported any more.
The simple console app at https://github.com/Peter7638546/npgsqltestcore can be used to demonstrate the issue.
Thanks in advance for any leads.