Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I was asked an interesting question: Should a database contains all data? Or heavy binary files should be stored in file system?

Example of heavy binary files : videos or heavy pdf files (+200 MB)

With an old aspx web app (1.1) I tried to open a 200MB pdf file stored as a blob in an Oracle 11g database, and it just run out of memory.

However, same asp.net web application had no problem to open same pdf file stored in file system of a server. It could be that maybe there is some proper way to open heavy blobs fields with asp.net.

For integrity reasons, I say that all data should be stored in database, but my described case showed me that maybe it's not the way.

I read once that if your web application goes to cloud, then it would be very difficult to keep references of files stored in file system (file paths like: ../MyFolder001/MyFile.mpg), since you don't know where those files will be distributed.

Should heavy binary files not be stored in database?

share|improve this question

closed as not constructive by Glenn Nelson, Dynamic, Martijn Pieters, ChrisF Jan 28 '13 at 23:34

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance.If this question can be reworded to fit the rules in the help center, please edit the question.

    
at 200mb per entry with heavy downloads you are killing your DB, small binary blobs (<50kB) can be fine if the load isn't too heavy –  ratchet freak Jan 28 '13 at 20:28
    
200 MB is a lot for a BLOB. It's probably possible (but I don't want to promise anything) to do this, but you might need to look into performance tuning tips for very large BLOBs. Also, maybe buy more RAM - maybe Oracle could have done it but you didn't have enough. ;) –  FrustratedWithFormsDesigner Jan 28 '13 at 20:29
1  
SQL server allows the contents of varbinary(max) columns to be stored on the file system, maybe there is something similar in Oracle. –  Lee Jan 28 '13 at 20:30
2  
Closely related question: stackoverflow.com/questions/3748/… –  Keith Palmer Jan 28 '13 at 20:32
3  
nigh dupe in dba.stackexchange.com/questions/2445/… –  ratchet freak Jan 28 '13 at 20:38

5 Answers 5

No, large binary files should generally not be stored in the database.

  • Generally, your filesystem will provide better caching/buffering than your database will for large files
  • Databases are generally designed and optimized around smaller bits of data - large binary files are often not optimized for in databases
  • Database storage is generally more expensive (both in terms of CPU time and disk space) than filesystem storage
  • You often lose opportunities to optimize sending files to a web client when storing them in a database (e.g. sendfile())
  • You're putting an unncessary load on the database by having to store/fetch large binary objects from the db
share|improve this answer
    
In this scenario, backup and recovery should scope not only database but also file system, right? –  user316687 Jan 28 '13 at 20:47
    
Yes, of course. –  Keith Palmer Jan 29 '13 at 3:15

The most important reason not to use large blobs is that you cannot stream them - when you query a database, it's the whole column value or nothing. With files, you can open a handle, then read the data as you go - you never need to have the entire file in memory at the same time.

At the same time, many of the advantages of databases, such as more efficient storage of lots of tiny bits of data and more efficient lookup of such values, ACID guarantees, etc., become less important when large files are concerned.

share|improve this answer
    
With a webserver on a linux machine (admittedly, not case in the question, but applicable to the answer), one doesn't even need to have the file in user memory much at all - sendfile() in linux will copy one file descriptor to another in the kernel. This saves copying from kernel memory with read() into user memory, and then from user memory back to kernel memory with write(). –  MichaelT Jan 28 '13 at 20:58

It's a question of advantages and disadvantages. Binary files in the database will be stored with all the other data, but you have to pay the overhead including it in the database.

In your case, it sounds like the disadvantages include limited support in the tools you're using. Does the value of having the binary data in the database justify the effort needed to work around those limitations?

share|improve this answer

SQL Server 2012 now has the FileTables. That combine the strategy of appearing to have the data in the database but utilizes the file structure behind the scene.

Your other concern:

I read once that if your web application goes to cloud, then it would be very difficult to keep references of files stored in file system (file paths like: ../MyFolder001/MyFile.mpg), since you don't know where those files will be distributed.

For an application that handles a large number of large files, you better find a cloud solution that will allow you to store them. Changing the file paths (which are probably stored in a database) can be handled by:

  1. storing the root folder in a single location in your app and hopefully maintaining the rest of the folder and file naming structure.
  2. updating any other sub folder structurs in the database with a script. This is the piece that databases handle well.
share|improve this answer

In every company I've worked at the general consensus has always been to store file locations within the database and the files elsewhere.

Firstly, you do not know what size of files people are uploading or how many times. There are ways to measure the size of a file before it goes in the database and there will be programmatic ways to stop people uploading multiple versions but the point I'm trying to make is that you don't want your database's performance being hindered by someone uploading a 2GB file, or 20 people trying to download that 2GB file at the same time. The chances are your database is also having to deal with lots of other smaller queries at the same time. Help it to help you!

This leads onto my second point. Separation of concerns. A file server can look after the files, a database can store the locations of those files. Problems with storage of files, detecting and preventing viruses in files can be handled separately.

I've worked mainly with websites and applications so the reasons I'm giving are influenced by that.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.