What is the best place for storing uploaded images, SQL database or disk file system? [closed]

Question

I'm writing an application that allows users to upload images onto the server. I expect about 20 images per day all jpeg and probably not edited/resized. (This is another question, how to resize the images on the server side before storing. Maybe someone can please drop a .NET resource for that in the comment or so). I wonder now what the best place for storing uploaded images is.

• Store the images as a file in the file system and create a record in a table with the exact path to that image.

• Or, store the image itself in a table using an "image" or "binary data" data type of the database server.

I see advantages and disadvantages in both. I like a) because I can easily relocate the files and just have to change the table entry. On the other hand I don't like storing business data on the web server and I don't really want to connect the web server to any other datasource that holds business data (for security reasons) I like b) because all the information is in one place and easily accessible by a query. On the other hand the database will get very big very soon. Outsourcing that data could be more difficult.

Answer 1

I generally store files on the file-system, since that's what its there for, though there are exceptions. For files, the file-system is the most flexible and performant solution (usually).

There are a few problems with storing files on a database - files are generally much larger than your average row - result-sets containing many large files will consume a lot of memory. Also, if you use a storage engine that employs table-locks for writes (ISAM for example), your files table might be locked often depending on the size / rate of files you are storing there.

Regarding security - I usually store the files in a directory that is outside of the document root (not accessible through an http request) and serve them through a script that checks for the proper authorization first.

Answer 2

The only benefit for the option B is having all the data in one system, yet it's a false benefit! You may argue that your code is also a form of data, and therefore also can be stored in database - how would you like it?

Unless you have some unique case:

• Business logic belongs in code.
• Structured data belongs in database (relational or non-relational).
• Bulk data belongs in storage (filesystem or other).

It is not necessary to use filesystem to keep files. Instead you may use cloud storage (such as Amazon S3) or Infrastructure-as-a-service on top of it (such as Uploadcare):

https://uploadcare.com/upload-api-cloud-storage-and-cdn/

But storing files in the database is a bad idea.