Is there an intrinsic limitation that prevents Npgsql from providing multiple simultaneous readers when accessing PostgreSQL from within .NET?

Question

There are a number of Internet sources (Stackoverflow or others) indicating that Npgsql does not support MARS (Multiple Active Readers) as supported by SQLServer. Is this an intrinsic PostgreSQL limitation? Or has it more to do with Npgsql's design?
In other words, could this be addressed by contributing this functionality to Npgsql or is this effort doomed to fail?
(I know that the PostgreSQL community makes a compelling case as per "why would one even need such a thing, PostgreSQL allows for a better formulation of the SQL statements to alleviate the need altogether". Let's just say that this is about caring for systems with tens of thousands of existing SQL statements, and rewriting them one by one is obviously not an option)

Answer 1

The real question is what happens under the hood to support a feature like MARS.

On the wire level, communication between Npgsql and PostgreSQL is extremely simple: there's one TCP socket, Npgsql sends a query to PG, and PG responds with the entire resultset for that query - single roundtrip.

Various models for MARS make Npgsql fetch X rows each time, instead of getting the entire result; Npgsql would buffer X rows in memory, and do a new roundtrip once the user has consumed those rows. This would allow MARS, but at the expense of introducing a lot of new network roundtrips for each single query: instead of a single roundtrip, you now have N roundtrips where N is the number of rows divided by X. Ths perf impact is considerable here. There's also a memory overhead to the row buffering.

Another possibility is to have Npgsql read the entire resultset and buffer it in memory, so each query is consumed immediately, allowing other queries to be executed. The disadvantage here is obviously the potentially huge memory overhead to buffer entire resultset.

So MARS is definitely possible, the question is how it's implemented and what price you pay for it. Every now and then I get this request from devs porting code from SQL Server, but to be honest people simply haven't asked for it a lot.

PS Note that this last option may become a bit more relevant with some perf changes we're exploring for the next major version. But not sure yet.