Storing Binaries? In the Database, of course!

This blog is driven by WordPress. As far as I can see WordPress would never try to save pictures, videos or pdf files into its MySQL database – in the database you find only links to the file system. This makes sense to me. I cannot imagine a better way to deliver web pages fast and reliable, especially when you have scenarios with many requests per second.

In my job we normally do not program web pages. Will the principle “Do not store binaries in the database” always fit? I remember the time two years ago when I began to design a document management system. It should store 1.6 million pages per year, with multi-user access in a terminal server environment, powered by an SQL Server database on a 64 GByte machine as back end. The customer’s hardware provider gave me two advices: 1. Use transactions. 2. Store the documents in the database. Well, the system is now working productive for one year, and I am very grateful that I followed his hints.

Today I can warmly recommend storing binary data in the database. It is reliable and stable. You can use complex database transactions that include storing the binary, storing process information, storing foreign key relations and so on. You can easily handle concurrent access. And last but not least: The user experience when saving and retrieving documents is not “very slow”. It is just a bit slower compared to working with documents in the file system. I admit there is one restriction. You should not handle 500 MByte videos this way. The documents I am speaking about are 100 – 500 KByte, mostly PDF format. So this is not a good solution for YouTube…

There is a very good article from Microsoft Research that made me to dare this approach in a production environment. I also included the storing of binaries in my NotReallyORM framework.

What are your experiences on that? So far I have tried it only for Microsoft SQL Server. Would be interesting to know how a MySQL database would behave, for example.