Re: long transfer time for binary data

Am 21.01.2016 um 08:44 schrieb George Neuner:
> On Wed, 20 Jan 2016 22:29:07 +0100, Johannes <jotpe@posteo.de> wrote:
>
>> I noticed transferring a large object or bytea data between client and
>> server takes a long time.
>> For example: An image with a real size of 11 MB could be read on server
>> side (explain analyze) in 81ms. Fine.
>>
>> But on client side the result was completed after 6.7 seconds without
>> ssl compression and 4.5 seconds with ssl compression (both via 100MBit
>> ethernet).
>
> I think at ~4 seconds you're actually running pretty close to the
> limit of what is possible.
>
> Remember that, even assuming the execution plan is accurate and also
> is representative of an average request, your 81ms image fetch may be
> arbitrarily delayed due to server load.
>
> Even a quiet network has overhead: layers of packet headers, TCP
> checksums (CRC) and ack packets, etc. ... it's quite hard to sustain
> more than 95% of the theoretical bandwidth even on a full duplex
> private subnet.  So figure 11MB of data will take ~1.2 seconds under
> _optimal_ conditions.  Any competing traffic will just slow it down.
>
> Also note that if the image data was stored already compressed,
> additionally trying to use connection level compression may expand the
> data and increase the transmission time, as well as adding processing
> overhead at both ends.
>
> And then the client has to convert the image from the storage format
> into a display compatible bitmap and get it onto the screen.
>
>
>> Are there any other solutions available to display my images in my
>> client application more quickly? Or are there planned improvements to
>> postgresql (transferring the real binary data)?
>
> You don't say what is the client platform/software or what format are
> the images.  11MB is (equivalent to) 1500+ pixels square depending on
> pixel/color depth.  That's a relatively large image - even from a
> local file, rendering that would take a couple of seconds.  Add a
> couple more seconds for request turn-around and there is your time
> gone.
>
> BMP and GIF repectively are the formats that are quickest to render.
> If your stored images are in different format, it might be worth
> converting them to one of these.
>
> GIF and _some_ BMP formats support direct compression of the pixel
> data.  If you find you must store the pixel data uncompressed, you can
> always gzip the resulting image file and store that.
>
> Then don't use connection level compression.  With images stored
> already compressed the transmitted size is minimized, and you will
> only ever decompress (on the client) data in the critical path to the
> display.
>
>
> Hope this helps,
> George


Thanks for explanation. Im writing a client software in java/jdbc. Most
images are in jpeg format. Some have high quality, most medium.

Rendering this 11MB Image in eog (Eye Of Gome) takes 0.5 sec, in GIMP it
is very fast. In Java the object createion takes nearly all time, the
drawing is done very quickly.

The size of the binary string representation of this image is 22MB. I
guess there are not other special transfer mechanism for binary data
than plain text via sql, or?

Best regards
Johannes