We have a .Net 8 c# database library that is using Linq to Entities via EntityFramework using the Npgsql library, to access a Postgres database on our Azure repositories. All is working, but we have an issue with a function in the library that needs to call the database multiple times to recover the data to be passed onto the Blazor Hybrid front end.

The page in question is displaying multiple grids based on days of the week, where by the days of the week can has filters applied to various data points.

To get this to work, we make one call to the database to pull the core week data, and then a further 7 times to get the individual days of the week. The effect on the database when this is called is the CPU runs at 100% and we start to get what looks like table locks, even though the pgAdmin show no locks.

Rough idea, single user can get results in approx. 5 seconds, whereas when multiple users are just using the system, then the whole application can just crawl along, taking more like 30 – 60 seconds to complete the same.

We have refactored the Linq queries, increased the Postgres Server CPU cores and Memory with little effect, so I am wondering if it could have anything to do with the Npgsql drivers?

Does anyone know of any setting on the connection string that may help in this area, or have any tips on performance tuning Postgres?

Thank you.