Re: Storing images in PostgreSQL databases (again)

On Oct 4, 2006, at 12:56 PM, Guy Rouillier wrote:

> TIJod wrote:
>> I need to store a large number of images in a
>> PostgreSQL database. In my application, this
>> represents a few hundreds of thousands of images. The
>> size of each image is about 100-200 Ko. There is a
>> large turnover in my database, i.e. each image stays
>> about 1 week in the database, then it is deleted.
>
> 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.
>
>> but this wouldrequire a more tricky implementation, and ACID-ity
>> would be difficult to ensure -- after all, a database
>> should abstract the internal storage of data, may it
>> be images).
>
> I can't get excited about this. First, given the amount of overhead
> you'll be avoiding, checking the return code from storing the image in
> the file system seems relatively trivial. Store the image first,
> and if
> you get a failure code, don't store the rest of the data in the DB;
> you've just implemented data consistency. That assumes, of course,
> that
> the image is the only meaningful data you have, which in most
> situations
> is not the case. Meaning you'd want to store the rest of the data
> anyway with a messages saying "image not available."

Combine that with an on delete trigger that adds the filename
to a deletion queue (within the transaction) and a separate
process that runs through the deletion queue occasionally
and you get something quite useable, while still being able
to use sendfile() to throw the image over the wire rather than
squeezing all that data through the database.

Cheers,
Steve