Thread: COPY, bytea streaming and memory footprint

I am trying to understand/optimize how a COPY operation behaves when transfering a bytea from the database to a client.

For simplicity, I'll consider that I have only one bytea _image_ in the _images_ table.

Starting with

COPY (SELECT image FROM images) TO STDOUT BINARY

I understand that :

 - the server will create a linear buffer on the server side holding the whole image and then start sending it over the network in one big copyData message chunked in 64KB network chunks

 - the client can manage to extract this copyData payload by re-assembling those chunks in memory or by streaming the relevant data parts of the chunks elsewhere.

so the problem I see in a streaming situation is that the server actually needs to buffer the whole image in memory.

Now the image is already compressed so if I

ALTER TABLE images ALTER image SET STORAGE EXTERNAL

I can use the fact that substring on non compressed toasted values will fetch only the needed parts and do

COPY (

  SELECT (

    SELECT substring(image from n for 65536) from images)

    FROM generate_series(1, (select length(image) from images), 65536) n

  ) TO STDOUT BINARY

As I understand it, this would be less memory intensive on the server side if the server starts sending rows before all rows of the subselect are built because it would only need to prepare a sequence of  65536 bytes long buffers for the rows it would decide to have in memory.

but is there a way to know if such a COPY/SELECT statement will indeed start sending rows before they are all prepared on the server ? Does it depend on the request and  is there a difference if I add an order by on the select versus the natural order of the table ?

How many rows will be needed in memory before the sending begins ?

I hope my explanation was clear. I am looking for help in better understanding how the server decides to stream the COPY data out of the server vs the internal retrieval of the COPY'd subselect.

Thank you

Jérôme