RDBMS vs file system for file storage_问答_开发者

Are there any advantages of storing entire files in an RDBMS over storing the files in the file system with references to the file path in the RDBMS?

Which approach shall be faster? When do I choose one over the other? Does it matter which file system is in use? (say ext3)

I do not expect the files to change at all. The files may be json or xml or might be pdf (less likely). Also, there might be no need to refer t开发者_运维知识库o these files often. They are only meant for archival.

Thank you.

Given that the files are not expected to change, there is limited value in keeping the files in the DBMS. The primary advantage of keeping files in the DBMS is that the DBMS knows how to manage transactions, but if the files won't change, then that advantage becomes minuscule.

Another advantage of storing files in the DBMS is that the database backup will contain the files; with the files stored separately, you have to backup the separate stash of files as well as the DBMS itself to keep all the data secure.

Another advantage of storing files in the DBMS is that the database can enforce more subtle controls on access to the files.

The primary advantage of storing the files in the file system is that it is easy (easier) to see what you've got.

A secondary advantage is that you can back up or manipulate the files outside the DBMS - though that is also a disadvantage from some points of view.

If the files are stored in blobs in the DBMS, then the normal SQL client software can retrieve the contents over a normal SQL connection. If the SQL client software is not on the same machine as the DBMS and the files, then you have to worry about how clients do get hold of the file data.

Another advantage of separating the files from the DBMS is that the files could be stored off the DBMS machine. On the other hand, that then complicates getting the files loaded 'into the DBMS'.

On the whole, given the issues outlined above, there seem to be some advantages with going with the 'files in DBMS' approach. On the other hand, many people do go with 'files in file system' approach, and they survive. It may be that their SQL clients are on the same machine as the DBMS, so the file transfer issues are not insurmountable, but that's the bit that has me most worried.

If file size is less than 1MB you may store them in the RDBMS, but otherwise consider storing them on the file system. See http://technet.microsoft.com/en-us/library/bb933993.aspx

Are there any advantages of storing entire files in an RDBMS over storing the files in the file system with references to the file path in the RDBMS?

if money etc. is no problem, then storing in RDBMS is advantageous, since you will get all the benefit of a RDBMS, plus no overhead of dereferencing the file from the reference stored in the db.

Which approach shall be faster?

RDBMS

When do I choose one over the other?

dictated by practical considerations. consider file system if file is > 1MB. Many shared-hosting providers do not enable FILESTREAM.

Does it matter which file system is in use?

I don't know about this.

To add to what Jonathan Leffler has written:

DBMS are not as efficient when dealing with BLOBs as when dealing with fixed-size objects, so we can say that DBMS don't "like" large objects. Also many DBMS store BLOBs outside of tables, in a separate storage.

If your goal is archiving purposes, it makes sense to store files separately for easier backup and retrieval. Also you can move files to some backend storage and make them "offline" to free space on the server (if needed).