Re: long transfer time for binary data

Johannes wrote:

> psql
> select lo_get(12345);
> +ssl -compression 6.0 sec
> -ssl 4.4 sec

psql requests results in text format so that SELECT does not
really test the transfer of binary data.
With bytea_output to 'hex', contents are inflated by 2x.

Can you tell how fast this goes for you, as a comparison point:
\lo_export 12345 /dev/null
?

Many client interfaces use the text format, but you want to
avoid that if possible with large bytea contents.
In addition to putting twice the data on the wire, the server has to
convert the bytes to hex and the client has to do the reverse operation,
a complete waste of CPU time on both ends.

At the SQL level, the DECLARE name BINARY CURSOR FOR query
can help to force results in binary, but as the doc says:

http://www.postgresql.org/docs/current/static/sql-declare.html

"Binary cursors should be used carefully. Many applications, including
psql, are not prepared to handle binary cursors and expect data to
come back in the text format."

Personally I don't have experience with JDBC, but looking at the doc:
https://jdbc.postgresql.org/documentation/94/binary-data.html

I see this:

"To use the Large Object functionality you can use either the
LargeObject class provided by the PostgreSQL™ JDBC driver, or by using
the getBLOB() and setBLOB() methods."

If the data lives on the server as large objects, I would think that
this LargeObject class has the best potential for retrieving them
efficiently, as opposed to "SELECT lo_get(oid)" which looks like
it could trigger the undesirable round-trip to the text format.
You may want to test that or bring it up as a question to JDBC folks.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite