Re: JDBC and processing large numbers of rows

Guido Fiala wrote:
> Reading all this i'd like to know if all this isn't just a tradeof between
> _where_ the memory is consumed?
>
> If your JDBC-client holds all in memory - it gets an OutOfMem-Exception.

Yes. The current driver attempts to hold the entire resultset in heap if
not using cursors. In theory, the driver could try to spill large
resultsets to disk (but see below).

> If your backend uses Cursors - it caches the whole resultset and probably
> starts swapping and gets slow (needs the memory of all users).

As I understand it (break out the salt!), the backend's cursor behaviour
depends on both the query and the cursor type. For a NO SCROLL cursor,
the backend is doing no more real work than for normal query retrieval
(it's just changing *when* the rows are retrieved). For a SCROLL cursor,
the backend may or may not need additional storage depending on the
complexity of the query. When the backend does need to store the
resultset, it will spill large resultsets to disk rather than keep them
in-memory (and possibly this storage is more efficient than just storing
the raw tuples, as it could just reference the original tuples in the
main table itself -- not sure if this is how it actually works though).
For a WITH HOLD cursor, in addition to the SCROLL/NOSCROLL behaviour the
backend will preserve a copy of the resultset when it can no longer
safely derive the results directly from the database (i.e. when the
enclosing transaction commits).

The DECLARE documentation discusses some of this.

> If you use Limit and Offset the database has to do more to find the
> data-snippet and in worst case (last few records) still needs temporary the
> whole resultset? (not sure here)

I'd guess that in the non-zero OFFSET case the backend skips and
discards the initial rows (why would it need to store them?), i.e. it's
similar in cost to doing a DECLARE/MOVE FORWARD/FETCH FORWARD sequence.
But you end up rerunning the start of the query every time you want some
more data..

> Is that just a "choose your poison" ? At least in the first case the memory of
> the Client _gets_ used too and not all load to the backend, on the other side
> - most the the user does not really read all the data at all, so it puts
> unnecessary load on all the hardware.

I lean towards using cursors. The backend can handle some cases more
efficiently than a client can, simply because it knows more about how to
generate the data and where it might already be stored. Also it seems a
bit redundant to do the same work to deal with large datasets (spill to
disk, etc) on both the client and the server.

-O