Re: long transfer time for binary data - Mailing list pgsql-general

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