Image storage: Database or File system?
From my experience, storing images in a database is a lot easier to manage than storing them in the file system. Here are a few of the benefits that I love the most:
- Related information is automatically kept in sync.
- It’s easier to backup your data when it’s all in one place.
- It’s easier to maintain an independent development environment.
- You can add additional file servers without having to deal with on-the-fly file replication.
Of course, the classic argument against storing images in a database is that it is slow. Retrieving images from a database takes longer than retrieving an image from the file system. At least, that’s what they say.
I decided to do a benchmark test for myself to see just how much of a difference it makes in performance. The results were surprising.
First, I created three different sized thumbnails of this picture and stored them in a directory on my file system. Then I took the same three images and stored them in a mySQL database. I then measured the total time for Firefox to display each image. I ran each benchmark 10 times for better accuracy.
Here are the results:
- Large – average of 7.87E-05 seconds
- Medium – average of 7.77E-05 seconds
- Small – average of 6.65E-05 seconds
- Large – average of 6.68E-05 seconds
- Medium – average of 6.69E-05 seconds
- Small – average of 5.90E-05 seconds
Wow! The images retrieved from the database were actually displayed faster than those retrieved from the file system!
Of course, we still haven’t proved this is scalable. I only had 3 rows in my table. What would happen if you have 120,000 images in your database? There’s one way to find out. Test it.
I inserted my picture into the database table 120,000 times (yes, that took a while). When I ran my benchmark test again, the average time was 6.07E-05 seconds! So much for being “slow”.
I’m not sure what would happen if my table contained over 120,000 images. For now I’m not too concerned – we’re still a few weeks away from reaching that milestone.