Storing Files in Database / FS
Storing Files in Database - Which is best for what ?
File / DB
While developing there are sometimes we feel confused when to choose the optimal way of storing the files, I realized that understanding the circumstances and requirements paves the way for making the right choice. Folks tells us that databases efficiently handle large numbers of small objects, while filesystems are more efficient for large objects.
File System based Storage
Pros of the File system:
Performance can be better than doing it in db. To justify this, If you store large files in db then it may slow down the performance because a simple query to retrieve the list of files or filename will also load the file data if you used Select * in your query. While Files system accessing a file is quite simple and light weight.
Saving the files and downloading them in the file system is much simpler than database since a simple Save as function will help you out. Downloading can be done by addressing an URL with the location of the saved file.
Migrating the data is an easy process here. You can just copy and paste the folder to your desired destination while ensuring that write permissions are provided to your destination.
Cost effective as It is Economical in most of the cases to expand your web server rather than paying for certain Databases.
Easy to migrate it to Cloud storage like Amazon S3 or CDNs etc in the future.
Cons of the File system:
Loosely packed. No ACID (Atomicity, Consistency, Isolation, Durability) operations relational mapping which mean there is no guarantee. Consider a scenario if your files are deleted from the location manually or by some hacking dudes, you might not know whether the file exists or not. Painful right?
Low Security. Since your files can be saved in a folder where you should have provided write permissions, it is prone to safety issues and invites troubles like hacking. So it is best to avoid saving in FS if you cannot afford to compromise in terms of security.
When is it most preferred
If your application is liable to handle Large files of size more than 5MB and the massive number say thousands of file uploads. If your application can land you on cloud nine, I mean your application will have a large number of users.
Best way to do
Though File System comes with some cost and certain cons, A good Internal Folder Structure and choosing a folder location which may be a little difficult to access by others.
Storing Files in a Database
Pros of Database:
ACID consistency which includes a rollback of an update that is complicated when the files are stored outside the database. Files will be in sync with the database so cannot be orphaned from it which gives you an upper hand in tracking transactions. Backups automatically include file binaries. More Secure than saving in a File System.
Cons of Database:
You may have to convert the files to blob in order to store it in db. Database Backups will become more hefty and heavy. Memory ineffective. To add more, often RDBMS’s are RAM driven. So all data has to go to RAM first. Yeah, that’s right. Had you ever thought about what happens when an RDBMS has to find and sort data? RDBMS tracks each data page even lowest amount of data read/written, and it has to track if it’s in memory or if it’s on disk if it’s indexed or sorted physically etc.
When is it most preferred:
If your user’s file needs to be more tightly coupled, secured and confidential. If your application will not demand a large number of files from a large number of users.
Best way to do:
Be cautious with your Select query, Avoid Unwanted Select * queries which may frequently retrieve the file data unnecessarily. Caching the file data can pave a way to reduce memory and database usage. If you are using SQL server 2008 or higher version, make use of FILESTREAM.
Conclusions
BLOBs smaller than 256KB are more efficiently handled by a database, while a filesystem is more efficient for those greater than 1MB. If objects are larger than 1 MB on average, the file system has a clear advantage. If the objects are under 256 kilobytes, the database has a clear advantage.
Last updated
Was this helpful?