BLOB vs FileSystem

Question

Although this question has been appear in past previous post, but different scenario and different consideration decide which one is the best.

I need to implement a system whereby it can handle 200GB - 400GB size of images yearly(approximately < 1mb per image). It is P&C images which only allowed for authorised personal to access and VIEW only. I am planning to use an application based of system to INSERT to MYSQL database and using PHP web based application for VIEW only.

I am thinking to use FILESYSTEM because it is easy to do backup & restore on the images and no need to worry on the size of the MYSQL database.

I am using MySQL + Apache + PHP running in Windows Server.

Your advice and input is very much appreciated.

Thank you.

Regards, Desmond

Answer 1

Also worth reading: Best Practice in File Storage while Building Applications - Database (Blob Storage) Vs File System

BLOB Storage as the Best Solution

• For better scalability. Although file systems are designed to handle a large number of objects of varying sizes, say files and folders, actually they are not optimized for a huge number (tens of millions) of small files. Database systems are optimized for such scenarios.

• For better availability. Database servers have availability features that extend beyond those provided by the file system. Database replication is a set of solutions that allow you to copy, distribute, and potentially modify data in a distributed environment whereas Log shipping provides a way of keeping a stand-by copy of a database in case the primary system fails.

• For central repository of data with controlled growth. DBA has the privilege to control and monitor the growth of database and split the database as and when needed.

• For full-text index and search operations. You can index and search certain types of data stored in BLOB columns. When a database designer decides that a table will contain a BLOB column and the column will participate in a full-text index, the designer must create, in the same table, a separate character-based data column that will hold the file extension of the file in the corresponding BLOB field. During the full-text indexing operation, the full-text service looks at the extensions listed in the character-based column (.txt, .doc, .xls, etc.), applies the corresponding filter to interpret the binary data, and extracts the textual information needed for indexing and querying.

File System Storage as the Best Solution

• For the application in which the images will be used requires streaming performance, such as real-time video playback.
• For applications such as Microsoft PhotoDraw® or Adobe PhotoShop, which only know how to access files.
• If you want to use some specific feature in the NTFS file system such as Remote Storage.