Thread: Image storage questions

Image storage questions

From
"Ed L."
Date:
If I store images as blobs in pgsql, will they be loaded into the
db shared memory cache as they are retrieved?

What are the tradeoffs of storing images as bytea vs blobs?

Thanks.
Ed

Re: Image storage questions

From
"Ed L."
Date:
On Wednesday May 18 2005 2:10 pm, Ed L. wrote:
> What are the tradeoffs of storing images as bytea vs blobs?

Actually, I see you can't store/retrieve blobs remotely, which is
a non-starter.  So the remaining choices seem to be bytea vs.
storing only metadata and keeping the images elsewhere (maybe an
apache image server).  Other thoughts?

Ed



Re: Image storage questions

From
"Joshua D. Drake"
Date:
Ed L. wrote:
> On Wednesday May 18 2005 2:10 pm, Ed L. wrote:
>
>>What are the tradeoffs of storing images as bytea vs blobs?
>
>
> Actually, I see you can't store/retrieve blobs remotely, which is
> a non-starter.

Sure you can. We do it all the time. What language are you using that
doesn't let you do it?

Sincerely,

Joshua D. Drake



So the remaining choices seem to be bytea vs.
> storing only metadata and keeping the images elsewhere (maybe an
> apache image server).  Other thoughts?
>
> Ed
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq


--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

Re: Image storage questions

From
"Ed L."
Date:
On Wednesday May 18 2005 2:40 pm, Joshua D. Drake wrote:
> Ed L. wrote:
> > On Wednesday May 18 2005 2:10 pm, Ed L. wrote:
> >>What are the tradeoffs of storing images as bytea vs blobs?
> >
> > Actually, I see you can't store/retrieve blobs remotely,
> > which is a non-starter.
>
> Sure you can. We do it all the time. What language are you
> using that doesn't let you do it?

I was following this:

http://www.postgresql.org/files/documentation/books/aw_pgsql/node96.html

"Because large objects use the local filesystem, users connecting
over a network cannot use lo_import or lo_export()."

Confirmed using psql.

What api/language are you using?  Is this possible to do through
DBI?  If you don't use lo_import/lo_export, how do you
insert/retrieve images?  Pointers to docs are welcomed!

Thanks,
Ed


Re: Image storage questions

From
"Joshua D. Drake"
Date:
> I was following this:
>
> http://www.postgresql.org/files/documentation/books/aw_pgsql/node96.html
>
> "Because large objects use the local filesystem, users connecting
> over a network cannot use lo_import or lo_export()."
>
> Confirmed using psql.
>
> What api/language are you using?  Is this possible to do through
> DBI?  If you don't use lo_import/lo_export, how do you
> insert/retrieve images?  Pointers to docs are welcomed!

I have done it via PHP and I can definately insert with perl remotely
(although I don't recall ever exporting):

  Creates a new large object and returns the object-id. $mode is a
bit-mask describing different attributes of the new object. Use the
following con-
            stants:

              $dbh->{pg_INV_WRITE}
              $dbh->{pg_INV_READ}

            Upon failure it returns undef.

              $lobj_fd = $dbh->func($lobjId, $mode, ’lo_open’);

            Opens an existing large object and returns an
object-descriptor for use in subsequent lo_* calls. For the mode bits
see lo_create. Returns undef
            upon failure. Note that 0 is a perfectly correct object
descriptor!

              $nbytes = $dbh->func($lobj_fd, $buf, $len, ’lo_write’);

            Writes $len bytes of $buf into the large object $lobj_fd.
Returns the number of bytes written and undef upon failure.

              $nbytes = $dbh->func($lobj_fd, $buf, $len, ’lo_read’);

            Reads $len bytes into $buf from large object $lobj_fd.
Returns the number of bytes read and undef upon failure.

              $loc = $dbh->func($lobj_fd, $offset, $whence, ’lo_lseek’);

            Change the current read or write location on the large
object $obj_id. Currently $whence can only be 0 (L_SET). Returns the
current location and
            undef upon failure.

              $loc = $dbh->func($lobj_fd, ’lo_tell’);

            Returns the current read or write location on the large
object $lobj_fd and undef upon failure.

              $lobj_fd = $dbh->func($lobj_fd, ’lo_close’);

            Closes an existing large object. Returns true upon success
and false upon failure.

              $lobj_fd = $dbh->func($lobj_fd, ’lo_unlink’);

            Deletes an existing large object. Returns true upon success
and false upon failure.

              $lobjId = $dbh->func($filename, ’lo_import’);

            Imports a Unix file as large object and returns the object
id of the new object or undef upon failure.

              $ret = $dbh->func($lobjId, $filename, ’lo_export’);




>
> Thanks,
> Ed


--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

Re: Image storage questions

From
"Ed L."
Date:
On Wednesday May 18 2005 2:57 pm, Joshua D. Drake wrote:
> > What api/language are you using?  Is this possible to do
> > through DBI?  If you don't use lo_import/lo_export, how do
> > you insert/retrieve images?  Pointers to docs are welcomed!
>
> I have done it via PHP and I can definately insert with perl
> remotely (although I don't recall ever exporting):

I see DBD::Pg has an entire API for lo objects.  Gee, I've only
been using that interface for 6 years without having noticed
that.  Thanks!

Can you or anyone share your experiences with using blobs as
opposed to bytea or storing externally?  I'm particularly
interested to know if loading a large number of images is going
to crowd my DB cache?

Ed

Re: Image storage questions

From
"Joshua D. Drake"
Date:
>
> Can you or anyone share your experiences with using blobs as
> opposed to bytea or storing externally?  I'm particularly
> interested to know if loading a large number of images is going
> to crowd my DB cache?

Large Objects stream (for lack of a better term) to the client in 8k?
chunks so it typically isn't too bad.

Bytea gives you everything all at once which is fine for small objects.

External storing is useful but I prefer LO because all my data (binary
and meta) is all in the same place for management.

Sincerely,

Joshua D. Drake


>
> Ed


--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

Re: Image storage questions

From
Alvaro Herrera
Date:
On Wed, May 18, 2005 at 02:49:40PM -0600, Ed L. wrote:

> I was following this:
>
> http://www.postgresql.org/files/documentation/books/aw_pgsql/node96.html
>
> "Because large objects use the local filesystem, users connecting
> over a network cannot use lo_import or lo_export()."

So you use lo_write.

BTW, that book is pretty out of date :-(  (though the LO interface
hasn't changed a lot anyway.)

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"Aprende a avergonzarte más ante ti que ante los demás" (Demócrito)

Re: Image storage questions

From
Noel
Date:
On Wed, 18 May 2005 14:07:18 -0700, jd@commandprompt.com ("Joshua D.
Drake") wrote:

>Large Objects stream (for lack of a better term) to the client in 8k?
>chunks so it typically isn't too bad.

>Bytea gives you everything all at once which is fine for small objects.

Isn't there also a difference between the two in terms of the largest
amount of data they can handle?

>External storing is useful but I prefer LO because all my data (binary
>and meta) is all in the same place for management.

As well as ensuring data integrity, of course.

Re: Image storage questions

From
"Guy Rouillier"
Date:
Joshua D. Drake wrote:

> External storing is useful but I prefer LO because all my data (binary
> and meta) is all in the same place for management.

But if that's a big L in LO, performance and maintenance will be
negatively affected, perhaps significantly.  The DBMS will have to scan
over all that large binary data to extract text or numeric data.  And
backups will copy that static binary data repeatedly.  For those
reasons, if I'm storing very large objects, like images, that I know
I'll never search or update, I prefer to manage them externally.

--
Guy Rouillier


Re: Image storage questions

From
Douglas McNaught
Date:
"Guy Rouillier" <guyr@masergy.com> writes:

> Joshua D. Drake wrote:
>
>> External storing is useful but I prefer LO because all my data (binary
>> and meta) is all in the same place for management.
>
> But if that's a big L in LO, performance and maintenance will be
> negatively affected, perhaps significantly.  The DBMS will have to scan
> over all that large binary data to extract text or numeric data.  And
> backups will copy that static binary data repeatedly.  For those
> reasons, if I'm storing very large objects, like images, that I know
> I'll never search or update, I prefer to manage them externally.

Large objects (and reasonably large text/bytea columns as well) are
stored out-of-line, so normal table scans don't have to read them
unnecessarily.

-Doug

Re: Image storage questions

From
"Joshua D. Drake"
Date:
Guy Rouillier wrote:
> Joshua D. Drake wrote:
>
>
>>External storing is useful but I prefer LO because all my data (binary
>>and meta) is all in the same place for management.
>
>
> But if that's a big L in LO, performance and maintenance will be
> negatively affected, perhaps significantly.

How?

  The DBMS will have to scan
> over all that large binary data to extract text or numeric data.

Ahhh now I see, are you expecting to be able to query your LOs? We only
use Large Objects as a storage mechanism.

   And
> backups will copy that static binary data repeatedly.

Not unless you tell it to. If you don't pass the -b option you are not
going to end up backing up your large objects anyway.

   For those
> reasons, if I'm storing very large objects, like images, that I know
> I'll never search or update, I prefer to manage them externally.

Sincerely,

Joshua D. Drake


>