What are the possible considerations and drawbacks of storing binary image data directly in a table?

Question

Although related to this question, I have a slightly different angle to approach this issue from. Here is my situation:

I am writing a web application (either in PHP or Python) that manages addons for a desktop application. Users can browse addons, install them, upload them, etc.

I am planning the schema for the database and found myself with a decision to make:

Is it better to store icons (for the addons) in the table itself or instead store them in the filesystem and simply store a filename in the table?

The icons are small (48x48 or close to that) and likely won't take up more than 5 or 6 KB at the most. Are there any serious drawbacks to storing the image data in the table? Are there other implications that I should be aware of? Will performance be a concern? Will storage be an issue?

---

Edit: I am currently looking at MyISAM tables in a MySQL database.

Answer 1

You haven't specified the database platform your considering but at this size/scale, it's unlikely to matter.

5kb per record is trivial. 1 million 5kb records is < 5GB, still trivial. 10 million 5kb records... still not something to lose any sleep about.

If we were to get platform specific, a typically exhaustively researched white paper by Paul Randall on SQL Server filestream storage suggests that it outperforms table storage where files are 1MB or greater in size. Below 1MB file sizes, the positives are primarily around filestream bypassing the buffer pool.

The positives for database storage:

1. Transacted. The binary data is tied to it's metadata, not exposed by a filesystem, safe from accidental deletion.
2. Reduced backup overhead. 1 million records in a 5GB file vs 1 million 5kb files.
3. The natural companion to 2), faster restore.

Edit: The negatives (as suggested by Aaron)

1. Requires programatic access.
2. Cost of storage. More of an issue if you have to deal with platform restrictions on size e.g. 50GB on Azure.

Answer 2

Another interesting aspect that makes storing image data rather challenging is having the correct packet size. I addressed this back on April 27, 2011.

Since images are to be stored in BLOB fields, there will be internal operations and/or external communications of BLOB data via programs (such as mysqldump), infrastructure (such as MySQL Replication), and general query usage (such as having BLOB data in internal temp tables during JOINs and WHERE clause evaultion).

In addition, as mentioned in previous URL, the InnoDB storage engine has a way of handling packets in memory and logs files.

Not be be overlooked is to construct SQL queries that

• avoid performing any WHERE clauses against the BLOB data
• keep BLOB data out of as many temp tables as possible (may call for refactoring queries)

In light of these facts, you will have to configure my.cnf with a number for max_allowed_packet so that a single MySQL packet is large enough to accommodate multiple BLOBs. You must also look into having enough available RAM on the DB Server. Otherwise, handling BLOBs in bulk with constantly moving a single BLOB in and out of a packet will produce unexpected performance bottlenecks.

CONCLUSION

Most of the Considerations/Drawbacks should cancel each other out once you have enough available RAM, properly-sized MySQL packets, and queries that avoiding piling up BLOB data in multiple temp tables.

UPDATE 2011-09-09 12:23 EDT

Another consideration is to remember to use the --hex-blob option in mysqldump. Otherwise, it could make things a little difficult to reload blob depending on certain sequences of characters.