Re: Image storage questions

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

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

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/

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

> 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/

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

>
> 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/

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)

On Wed, 18 May 2005 14:07:18 -0700, jd(at)commandprompt(dot)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.

I asked this on general, but got no answer on this particular
point, maybe someone here knows. Are blobs are stored in the
shared memory cache upon retrieval?

I ask because we're trying to decide whether to store an enormous
number of images in PostgreSQL, and I'd be concerned that
frequent retrieval of those images would effectively empty the
cache of the rest of the non-image data.

I watched the pg_statio* numbers during some test retrievals, and
it appears the reads and cache hits on the binary image data are
not being counted. I'm wondering if that is the truth of what's
going on, or just an accounting oversight.

Thanks.

"Ed L." <pgsql(at)bluepolka(dot)net> writes:
> I asked this on general, but got no answer on this particular
> point, maybe someone here knows. Are blobs are stored in the
> shared memory cache upon retrieval?

pg_largeobject is treated exactly the same as any other table,
if that's what you are asking.

regards, tom lane

On Friday May 20 2005 10:20 am, Tom Lane wrote:
> "Ed L." <pgsql(at)bluepolka(dot)net> writes:
> > I asked this on general, but got no answer on this
> > particular point, maybe someone here knows. Are blobs are
> > stored in the shared memory cache upon retrieval?
>
> pg_largeobject is treated exactly the same as any other table,
> if that's what you are asking.

Yes, that was my question. Are large object rows also vacuumed
just as the other tables/rows?

Thanks.
Ed