The Problem (issue)
Okay, so we’re doing our first all-night code session (or hackathon according to the talented folks over at the OpenBSD project) on this new eBusiness web portal I keep telling you all about, and an important discussion ensues between me and Dan – our PHP guru. It’s about whether images for the Bazaar, Art, and Pharmacy modules should be stored as files in well-named folders, or if we should just use a BLOB (Binary Large OBject) field in the database tables. Hmmmn. From my own personal experience, I’ve always felt more comfortable letting the O/S (operating system) handle the static file system chores…until I saw this.
An enlightening SQL dude
Well, being the self-proclaimed Internet whore that I am, I simply went to my Mozilla Firefox search box, which defaults to Google, and entered the following query: storing images in databases.
Here’s the info right from the horse’s mouth:
Many times I have read comments that its better to have a pointer to the image be saved in the database and the image be managed by the operating system.
Here is some insight about the pros and cons of managing images in the database.
Here are some of the Pros for storing the images in the database as a better choice and the application in which the images will be used can count on the benefits of a database system.The benefits of storing the images in the database include:
Scalability: Although file systems are designed to handle a large number of objects of varying sizes, file systems usually are not optimized for a huge number of small files.
Database systems are optimized for such cases.
Availability: SQL server has availability features that extend beyond those provided by the file system.
SQL Server replication is a set of solutions that allow you to copy, distribute and potentially modify data in a distributed environment.
Log Shipping provides a way of keeping a stand by copy of a database in case the primary system fails.
As far as the cons for it are concerned
Storing the image in the database increases the database size dramatically and obviously the overall performance of the database is degraded.
Conclusion
Well, this debate is sure to continue until the cows come home, and most politicians (over HERE) are no longer corrupt. 
Fat chance!
However, I want to give serious props to SQLdude for really giving me some food for thought. And isn’t this what the Internet is supposed to be all about. God damn it, but I totally looooove this cyber space thingy! Anything and everything is available right at your fingertips – merely for the asking (from places like Google). Of course, you have to be ever so careful to avoid it’s “dark” dubious corners (zones).
Oh well…I gotta jet. I’m supposed to finalise the categories for the Bazaar so that our web visitors can post items (kitchenware, electronics, toys, auto parts, clothing, etc.) in appropriate CATEGORIES which should make searching easier for you and me alike.
Happy “spirited” computing!
This is valuble info that i was serching for. Max what about if your site gets lots of trafic? How would this affect database performance?
Neither appraoch is workable in real life high traffic website. Disk or database are both too “slow: to be used for static site content. With a good hosting company, static branding images would ideally be cached in RAM on the webserver as they are a component of every single page.
The idea is that the files/images (stuff like style sheets, branding images etc etc gets read once into system RAM (which is the fastest memory available) when the web server start and is never read again (of course, in reality, there are mechanisms to force the web server to refresh the data from disk).As developers though, you should not assume that your hosting company will implement this for you and should act proactively to make sure it happens.
Read this for some more insights into this.
A question for you if I may, your posts seem to imply that you chose to build a php application using SQLServer for the DB. Any particular reason why you chose this for a stack rather than the standard (and free!!) php/apache/mysql stack?
- Steve
Steve, thanks for the great post. I completely agree with you about handling static images in some sort of RAM cache. I was referring to images that are uploaded by users/sellers in order to sell their products (Real Estate listings, Cars, Clothing, etc.). So, in this regard, what would you suggest?
To answer your question, we ONLY use the LAMP (Linux, Apache, MySQL, PHP) stack. I got into server-side development with SQL Server, Sybase and ASP on Windows NT when I was doing my internship at the Bank of Montreal in TDot (Toronto).
Oh, thanks for the Dylan Drazen links in your other post. I’ve heard 1 or 2 of his mixes. His style is amazing…always upbeat.