agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-sql@postgresql.org
Subject: Re: BYTEA
Date: Mon, 18 Jan 2016 18:26:32 +0100 (CET)
Message-ID: <VisenaEmail.ce.469a2cd6e8503cc8.15255bae04c@tc7-visena> (raw)
In-Reply-To: <614277427.7358116.1453134608680.JavaMail.yahoo@mail.yahoo.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
På mandag 18. januar 2016 kl. 17:30:08, skrev Eugene Yin <eugeneymail@ymail.com
<mailto: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 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@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
view thread (17+ messages)
Message-ID: <VisenaEmail.ce.469a2cd6e8503cc8.15255bae04c@tc7-visena>
Permalink: ../VisenaEmail.ce.469a2cd6e8503cc8.15255bae04c@tc7-visena/
Also on: postgresql.org/message-id/VisenaEmail.ce.469a2cd6e8503cc8.15255bae04c@tc7-visena
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreas@visena.com
Subject: Re: BYTEA
In-Reply-To: <VisenaEmail.ce.469a2cd6e8503cc8.15255bae04c@tc7-visena>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox