Re: BYTEA

På mandag 18. januar 2016 kl. 17:30:08, skrev Eugene Yin <eugeneymail(at)ymail(dot)com
<mailto:eugeneymail(at)ymail(dot)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 ofsmall pics, then
they pick and click one, the selected one will popup a larger picture. 
 
I have two ways to do this:
 
1) BYTEA
 
2) OID
 
For 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
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>