Re: To BLOB Or Not To BLOB

-> It seems that the issue with large objects is "Why do you want the info in a
-> database?"

To organize them, of course.

-> It seems to me that the point of a database is its ability to order and
-> relate data. If you want to retrieve the "large-ish text files" based on
-> their content then I think you need to have the files in the database so
-> they can be searched. However, if you are going to retrieve the files based
-> on something about them that is stored as a separate attribute, such as
-> their title, then I think that storing the files as large objects is
-> unnecessary.

Partly I would like to retrieve them based on their content, and it is
why I wrote (badly) a small extension that searches large objects for
text strings.

-> If you have the option to "[do] as pointers t those files" then I think you
-> are not intending to use any of Postgres's features beyond its ability to
-> store and retrieve data. Surely the file system itself is 'better' at this
-> regardless of how stable Postgres LO's are.

Yes, but the file system is external to the database.

A view that I am beginning to appreciate the more that I use databases
like PG and Oracle is that all of the relations within a database should
be internal.  Want to reference a title?  Reference to the table of titles.
Want to reference a bunch of data?  Point to the bunch of data.  If I
have to use the large object interface to do this, then I will.

This way, you can let the database worry about organizing and storing the
data, and all you need to do is worry about backing that database up --
not about making sure that the permissions on files are right, or that
the directory structure remains the same, or the DB remains synced to
the contents of the files...

This is also why the FOREIGN KEY functionality that e.g. MySQL completely
lacks and PostgreSQL is going to have as of 7.0 is so important -
maintaining referential integrity.

Here are two specific applications for which I am using large objects,
one in Oracle and one in PG, both involving online databases, both
using the ArsDigita Community System.

First of all, I managed the online paper submission and review process
for a conference using Oracle.  For this, it was very convenient to
be able to grab submissions and stuff them into the database, from which
they could be referenced by review etc.  Moreover, because of the
geographical distribution of the review committee, the ability to
grab the papers without giving access to the machine by shell or by
FTP (which is generally a security problem) was excellent.  Finally,
separate revisions of the papers could be kept separate using the
obvious internal mechanisms of the database.

The second application is one I'm working on right now, a genome database
project for the sea urchin.  For this, we need to be able to archive
arbitrarily large amounts of file-oriented data by (for example) sequence
name, and we want to give the option to search it as well.  The
only really convenient way to do this is to store it all in the
database.  (Note that it's not terribly *fast* to do it this way ;).

I would be happy to discuss this more if anyone has suggestions for
alternatives  -- I'm not wedded to the approach, but it seemed to be
the obvious one.

cheers,
--titus