It is my understanding that when a query is issued the backend runs the
query and accumulates the results in memory and when it completes it
transmits the entire result set to the front end.
For selects with large result sets this creates large demands for memory,
first in the back end and then in the front end.
Is there a mechansism to avoid this? In particular I am looking for a
mechanism where the backend generates rows to fill a relatively small buffer
and blocks while waiting for the front end to drain that buffer. In this
interface the front end would only need to present one row at a time to the
application. I understand that there might be limitations on the kind or
complexity of a query that usses this mode of operation.
I have studied the documentation and found Cursors and Asyncronous Query
Processing. Cursors seems to solve the problem on the front end but I get
the impression the back end will buffer the entire result until the cursor
is closed.
Asyncronous Query Processing as I understand it is more about not blocking
the client during the query and it does not fundementally alter the result
buffering on either end.