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

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