On Wed, 20 Jan 2016 22:29:07 +0100, Johannes <jotpe@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
