Re: Storing images in SQL database?

On Mon, 04 Oct 2004 19:38:44 -0400, jbringolf1 <bobo_at_booboo.com> wrote:

>Galen Boyer wrote:
>> On 4 Oct 2004, andrew.mccall_at_gmail.com wrote:
>>
>>>I am about to start on a project where I will need to store a
>>>large amount of images and details about images. SQL seemed a
>>>natural choice for this, but I was a little unsure about how to
>>>store the image itself.
>>>
>>>From reading around I have found that there are two techniques
>>>that people normally chose for this sort of work:
>>>
>>>1) To store everything about the image within the SQL database,
>>>and the image itself on a file system. A link to the image in
>>>the file system is also stored within the SQL database.
>>>
>>>2) To store the everything, including the image within the
>>> database.
>>>
>>>I was wondering what people thought about these two techniques,
>>>and which they would use.
>>
>>
>> Choose #2. #1 means that you have to maintain two things
>> seperately as well as keep those two separate things in synch as
>> you maintain them.
>>
>Recommend the first choice. Several reasons. Major failure of your
>database will take days not hours to recover. Harder to move data using
>blob type objects and harder to display and use images in a web
>environment.

 How do you propose to keep the filesystem in sync with the database, given that the filesystem doesn't give transactional guarantees or the ability to rollback?

 How do you restore the filesystem after media failure to be consistent with the database? Do you commit the metadata in the database before making a filesystem change - in which case you might lose filesystem changes, because they weren't made yet when the media failed. Or do you commit after you make filesystem changes, in which case you might have changes saved to the filesystem that are not represented in the database?

 Or do you treat images the same as all other data, and store it in the database, and so everything gets the same transactional and recovery guarantees and stays consistent? I suppose it depends how much the images are worth, and whether you can afford to lose any of them.

 As for it being harder to use in a web environment, if that turns out to be a bottleneck you could always keep a filesystem-based cache, whilst still keeping the database as the master for the image data.

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool