Re: BYTEA - Mailing list pgsql-sql
På mandag 18. januar 2016 kl. 17:30:08, skrev Eugene Yin <eugeneymail@ymail.com>:
To understand it further, let me give an example that I have a group of pictures (assume they a set of house pictures, such as living room, bedroom, kitchen, patio, yard, etc). I first save them (larger size) to the DB. Then when the user retrieve them, the user first see a line-up of small pics, then they pick and click one, the selected one will popup a larger picture.I have two ways to do this:1) BYTEA2) OIDFor BYTEA, the whole set of pictures (larger size) are retrieved (dumped) into the memories on both the Java and Pg sides, occupying the same size as the original picture is. Hence taking up larger memories.For OID, it takes smaller memories on both Java and Pg sides (store the byte array in a buffer?). Whenever the user clicks the smaller pic, it then get the byte array from the buffer and display the larger (original) sized one, hence taking up less memories (avoiding the memory leaking)?
One is always free to design an app in such a way that no matter how you store the images it may still use all available memory. My point is that using BLOB with OID (together with the pgjdbc-ng driver) lets you get away with using as little memory as you choose. It's the same as working with a large file using FileInputStream. Consuming such streams is done by reading chunks of it into a pre-allocated byte-array, which is the common way in most programming-languages. This byte-array is the only extra penalty in you app. How you feed the contents of the stream back to the browser is up to you, and there are lots of ways to do that inefficiently, even if the underlying producer of data is a stream of bytes. If you are in a JEE servlet-environment then writing to ServletOutputStream while reading form the BLOB's inputstream is the most memory-efficient way to stream data from PG to the browser.
Note that reading BLOBs in PG requires a transaction.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963