Application data objects are getting larger as digital media becomes ubiquitous. So, application designers must decide whether to store large objects (BLOBs) in a filesystem or in a database. Generally, this decision is based on factors such as application simplicity or manageability
It is well known that databases efficiently handle large numbers of small objects, while filesystems are more efficient for large objects. Where is the break even point? Of course, this depends on the particular filesystem, database system, and workload in question.
Often the design decision is based on which technology the designer knows best. Most designers will tell you that a database is probably best for small binary objects and that that files are best for large objects. Even though this is consistent with generally accepted wisdom, it offers little guidance on what to use when designing your own applications.
The prevailing wisdom is that databases are better for small objects while filesystems are better for large objects. The boundary between small and large is usually a bit fuzzy. The usual reasoning is:
• Database queries are faster than file opens. The overhead of opening a file handle dominates performance when dealing with small objects.
• Reading or writing large files is faster than accessing large database BLOBS. Filesystems are optimized for streaming large objects.
• Database client interfaces aren’t good with large objects.
• File opens are CPU expensive, but can be easily amortized over cost of streaming large objects.
None of the above points address the question of application complexity. Applications that store large objects in the filesystem encounter the question of how to keep the database object metadata and the filesystem object data synchronized. A common problem is the garbage collection of files that have been “deleted” in the database but not the filesystem. Also missing are operational issues such as replication, backup, disaster recovery, and fragmentation.
However, studies indicate that fragmentation plays a great part in the decision to blob or not to blob. Studies indicate that if objects are larger than one megabyte on average, Filesystem is the way to go.
If the objects are smaller than 256 kb, then store them as blobs in the database.
