Storing huge file collection in database

Question

I'm in a planning phase of developing a web site where public users will be able to store their documents, by nature it could be anything like documents, pdf, audio, video or images.

We are expecting large number of files stored in the database like numbers could be a few millions on average file size of 10 Mbs of one file.

I want to know a direction the way i should start learning/thinking on, for example in above case how many files one table should contain and how many such tables should be contained by one database.

What are the best practices while dealing with such scenario. What are the major things i should be taking care of while working on such project, I can use MS SQL Server database, My SQL is not a priority unless it has some advantages (its all based on available resources).

Answer 1

Matigo is correct. A file system is designed to handle storing files best, a database system is designed for storing raw data best, not files. You should use the right tool for the right job.

There are multiple issues you can run into with storing files directly in the database, such as bloated backups which waste space and take much longer to recover with when needed. Also database performance issues can occur either at the query level (because your rows are split across multiple pages to support large objects like files) or even at the database level as a result of contention by the constant growth of the database data file.

There are a multitude of other reasons why it's generally not recommended to store files directly in the database either. One good alternative recommendation is store the files directly on a file system somewhere, with the link to their file path stored in a meta-data table about the files in the database.

Finally, you may find this Brent Ozar article Store Files in a File System, Not a Relational Database. informative on the matter as well.

Answer 2

(In addition to J.D's answer.)

A file bigger than 16MB is difficult to put into a database table; a file bigger than 4GB (a long video) is almost impossible to store -- you must chop it up and be able to put it back together; that is a big mess.

If the files will be fetched from a web page, then the mechanism for <img src=path-to-fil> is really tuned to reaching for a file, not a database entry. (Yes a db can be made to work, but it is clumsy.)

I recommend you store the path to the file in some db table. Don't store the full path; instead, start after some common starting point in the path. This lets you more easily move the documents to another filesystem or even a different server.

For that matter, put all the documents on one server; put the database on another. Again, <img> is quite happy to handle that.

Answer 3

As said, file system is for storing files. Name your files for example with a 16 char pseudorandom (use any hashing algorithm) hex string, then make a directory structure where you have 256 directories, 00 to FF, and under each, again 00 to FF, then place files in this hierarchy using file name's first 4 characters. This way, file named 0123456789ABCDEF.jpg will be stored in root/01/23/0123456789ABCDEF.jpg

This way, you can mount more partitions later to hold part of the files and directory size stays reasonable. If number of files grow, add a 3rd level of directories. Your web server can serve needed file from right directory with a simple rewrite rule.

You can add table or two in your database describing those files: original filename, user who uploaded it, copyright data, descriptive text, keywords, media type etc.