Thread: Managing the memory requierments of large query results

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.

"Bryan White" <bryan@arcamax.com> writes:
> 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.

No, the backend does not accumulate the result; it transmits tuples to
the frontend on-the-fly.  The current implementation of frontend libpq
does buffer the result rows on the frontend side, because it presents a
random-access-into-the-query-result API to the client application.
(There's been talk of offering an alternative API that eliminates the
buffering and the random-access option, but nothing's been done yet.)

> 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.

A cursor should solve the problem just fine.  If you can put your finger
on what part of the documentation misled you, maybe we can improve it.

> 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.

Correct, it just lets a single-threaded client continue to do other
stuff while waiting for the (whole) result to arrive.
        regards, tom lane

> A cursor should solve the problem just fine.  If you can put your finger
> on what part of the documentation misled you, maybe we can improve it.

Thanks for the info.  It was not the documentation that misled me.  Actually
I have not seen much documentation that talks about the database at this
level.  That does not mean its not there.  Sometimes you can't see for
forest for the trees.