Thread: 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@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.
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
"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
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 >