ASP.NET MVC image upload store location (db vs filesystem)

Question

I am writing web application using ASP.NET MVC + NHibernate + Postres stack. I wonder if images uploaded should be stored in database as binary blobs or on filesystem (and reference only in db). One advantage of db storage I can think of is easy backup/recovery of all data without reverting to filesystem copy tools. On the other hand I suspect that filesystem access may be faster (but is it especially when dealing with many concurrent requests?) What are your suggestions?

Answer 1

It depends. Do you value being able to directly link to an image, or do you want to always use server side resources to call the DB, and then write the binary data for the image?

Answer 2

I'd do both- ensure the images are stored in the database so that all data is centralised for easy backup, but cache the data externally too, so that repeated requests for large images don't thrash the buffer cache of the database. Done properly, you can bring in new frontend web servers that will transparently populate their local image cache from the database after starting up.

Having a centralised store for the images is also useful for ensuring that you send good Last-Modified and ETag HTTP response headers for images in a system with multiple web servers, as those headers can be made from database contents rather than taken from the local cache objects.

Just an implementation note for PostgreSQL specifically: you can set the "storage mode" of the column containing your image data to "external": this will stop PostgreSQL trying to compress the image data (using zlib, which isn't likely to provide any benefit) and will make it store the image data in an auxiliary TOAST table, providing better performance if you're just querying the image metadata. See the "SET STORAGE" clause of the ALTER TABLE command, e.g.:

ALTER TABLE media.image ALTER COLUMN content SET STORAGE EXTERNAL

Answer 3

We utilize storage of originals in database for backup, but generated scaled down images that is cached on filesystem on the webservers.

However, we try to avoid any roundtrips to the database if possible, because it generates huge load since one page usually invokes several image-request per page-view.

One note on storing blobs in database, I suggest that you stor the actual blob-columns in a dedicated table, with a one-to-one mapping to your entity / entities. This will ease backups, aswell as alteration of your tables. When a table grows big, any changes will take "forever" to complete and locking is a big problem to, even during backup.

If you have all info about the images (except the binary data) selecting that data won't be affected unless you need the binary data (which you rarely need since it will be cached in file-system).

Just my two cents.