Re: BYTEA - Mailing list pgsql-sql
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) 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)?
Thanks
Eugene
On Monday, January 18, 2016 7:51 AM, Andreas Joseph Krogh <andreas@visena.com> wrote:
På mandag 18. januar 2016 kl. 16:40:26, skrev Eugene Yin <eugeneymail@ymail.com>:
"if you only work with small-ish binary data, yes - BYTEA does the job."--The goal is to save/retrieve the user's photos (JPEG, TIFF, GIF) and PDF files. The size of each file is less than5 MB. For such purpose, is BYTEA ok? If not, then how about 1 MB each?
Again, that depends. If you plan on having 1000 simultaneous users then having everything in memory might not be a good idea. Remember the memory consumed by the JVM is quite a lot more the the raw byte-size of each image/blob.
"whole byte-array is kept in memory, both in the JAVA-app and in PG"--I believe in Java the GarbageCollector will clean it up (?). "Who" will then clean up the Pg side?
It will clean it up, if it's not referenced anymore. PG will clean up its side at the end of the transaction (at least).
"whole byte-array is kept in memory, both in the JAVA-app and in PG"--Does that mean for the OID, byte-array is NOT kept in memory (of JAVA-app or PG)? If so, where is it kept? And how they are got cleaned up?
As reading BLOBs (using the OID-type) really means your processing a stream of bytes it's really up to you to decide the size of the byte-buffer you want to use. Only this byte-buffer is kept in memory (and is freed by the GC when not referenced anymore), which often is much less than the whole BLOB.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963