Hi,
I'd like to propose that the pgjdbc is modified to avoid buffering of ResultSets to memory by default, whenever it is safe to do so.
I created a WIP PR that works for many basic scenarios
https://github.com/pgjdbc/pgjdbc/pull/1735 I tried to do a very minimal change that should still be maintainable in the long term. Basically I just moved the protocol parsing variables into a state object and drag it along when processing next row of ResultSet. The effect on applications is that queries have an effective fetch size of 1, without any of the downsides of such setting.
The motivation for the patch is to speed up queries and lower memory usage. The queries become faster indirectly since the query processing is parallelized. Now we do in sequence:
1) backend returns results and jdbc driver parses and buffers result set (which can take a while)
2) application consumes result set
With this patch the steps occur concurrently
1) backend returns results
2) application consumes result set with the jdbc driver parsing rows from tcp buffer on demand
The cursors used by enabling a fetch size do a similar thing, but
- it tells the backend to optimize the query plan for fetching only the first few rows instead of the full result set
- each batch of rows require a round-trip between backend and application during which neither do any useful work
- small fetch size causes many round-trips while large fetch size uses memory and increases latency
The bad thing is that if the application is not consuming the result set fast enough the backend cannot release its locks as fast. But this only effects large results that do not fit entirely into the tcp buffers.
The WIP code sometimes fails if the ResultSet is not properly closed before the next statement is executed. For many tests this happens in the tearDown phase. I also think that currently things might not work correctly if application triggers queries on the connection while processing the results, for example by requesting metadata. In that case we have no other option than to go back to buffering to clear the connection for reuse.
-Mikko