Do PostgreSQL server side cursors store the results to disk?

Question

Do PostgreSQL (server-side) cursors store/materialise the entire results set to disk?

Context: running some queries with a large number of results and getting disk space errors from the server.

Answer

Not specifically. But various operations (large sorts, large multi-batch hash joins, set-returning functions, etc.) will spill to disk on their own. I don’t think server side cursors will cause that to happen when it otherwise would not (other than WITH HOLD, and perhaps SCROLL), but if the cursor is held open it will prevent incidental temp files from getting cleaned up.

Accepted Answer

Not specifically.  But various operations (large sorts, large multi-batch hash joins, set-returning functions, etc.) will spill to disk on their own.  I don&#8217;t think server side cursors will cause that to happen when it otherwise would not (other than WITH HOLD, and perhaps SCROLL), but if the cursor is held open it will prevent incidental temp files from getting cleaned up.

Advertisement

Answer