On Oct 5, 2006, at 16:18 , Merlin Moncure wrote:
>> I see little value to storing the images in the database. For me
>> that's
>> a general statement (I'm sure others will disagree); but
>> especially in
>> your case, where you have a high volume and only want to store
>> them for
>> a couple days. Why incur all the overhead of putting them in the DB?
>> You can't search on them or sort on them. I would just store them in
>> the file system and put a reference in the DB.
>
> no, you can't search or sort on them but you can put metadata on
> fields and search on that, and you can do things like use RI to delete
> images that are associated with other things, etc. this would
> probably fit the OP's methodogy quite nicely.
I second this sentiment; there is a lot to be said for keeping your
data together in a unified storage/retrieval system with ACID
semantics. There is nothing inherently wrong about this model.
[...]
> i'm wondering if anybody has ever attempted to manage large
> collections of binary objects inside the database and has advice here.
We have a production system containing 10,000 images (JPEG and PNG of
various sizes) totaling roughly 4GBs. We have Lighttpd running
against a couple of Rails processes which crop, scale and convert
images on the fly using ImageMagick; converted images are cached in
the file system and subsequently served directly by Lighttpd.
Functionally I have absolutely no quibbles with this system;
PostgreSQL stores the data smoothly and everything works as designed.
Performance-wise, I'm not sure; the amount of data seems to put a
certain load on the database server, though it's impossible to tell
how much. Backups are hell, taking hours and hours to do just a
single dump of the database. Rails' PostgreSQL adapter uses SQL for
inserts and quotes every byte as an octal escape sequence; storing a
single image can take several seconds. Single-image retrieval is
similarly slow, but since the adapter uses bindings that talk
directly to libpq4, I believe it's caused by the overall load on the
database.
Because of this, we see no recourse but to move the images into the
file system. Since our cluster consists of three separate machines
all running the same Rails application, with no dedicated box
handling the image storage, such a solution requires the use of NFS
or other type of shared storage for centralized image storage; we're
not sure yet about what we will end up with.
Alexander.