storing uploaded photos and documents - filesystem vs database blob

Question

My specific situation

Property management web site where users can upload photos and lease documents. For every apartment unit, there might be 4 photos, so there won't be an overwhelming number of photo in the system.

For photos, there will be thumbnails of each.

My question

My #1 priority is performance. For the end user, I want to load pages and show the image as fast as possible.

Should I store the images inside the database, or file system, or doesn't matter? Do I need to be caching anything?

Thanks in advance!

Answer 1

File system. No contest. The data has to go through a lot more layers when you store it in the db.

Edit on caching: If you want to cache the file while the user uploads it to ensure the operation finishes as soon as possible, dumping it straight to disk (i.e. file system) is about as quick as it gets. As long as the files aren't too big and you don't have too many concurrent users, you can 'cache' the file in memory, return to the user, then save to disk. To be honest, I wouldn't bother.

If you are making the files available on the web after they have been uploaded and want to cache to improve the performance, file system is still the best option. You'll get caching for free (may have to adjust a setting or two) from your web server. You wont get this if the files are in the database.

After all that it sounds like you should never store files in the database. Not the case, you just need a good reason to do so.

Answer 2

While there are exceptions to everything, the general case is that storing images in the file system is your best bet. You can easily provide caching services to the images, you don't need to worry about additional code to handle image processing, and you can easily do maintenance on the images if needed through standard image editing methods.

It sounds like your business model fits nicely into this scenario.

Answer 3

Definitely store your images on the filesystem. One concern that folks don't consider enough when considering these types of things is bloat; cramming images as binary blobs into your database is a really quick way to bloat your DB way up. With a large database comes higher hardware requirements, more difficult replication and backup requirements, etc. Sticking your images on a filesystem means you can back them up / replicate them with many existing tools easily and simply. Storage space is far easier to increase on filesystem than in database, as well.

Answer 4

a DB might be faster than a filesystem on some operations, but loading a well-identified chunk of data 100s of KB is not one of them.

also, a good frontend webserver (like nginx) is way faster than any webapp layer you'd have to write to read the blob from the DB. in some tests nginx is roughly on par with memcached for raw data serving of medium-sized files (like big HTMLs or medium-sized images).

go FS. no contest.

Answer 5

Maybe on a slight tangent, but in this video from the MySQL Conference, the presenter talks about how the website smugmug uses MySQL and various other technologies for superior performance. I think the video builds upon some of the answers posted here, but also suggest ways of improving website performance outside the scope of the DB.

Answer 6

Comment to the Sheepy's answer.

In common storing files in SQL is better when file size less than 256 kilobytes, and worth when it greater 1 megabyte. So between 256-1024 kilobytes it depends on several factors. Read this to learn more about reasons to use SQL or file systems.