Blob vs file system storage
By manveen on Mar 19, 2008
Images can be stored as a database blob or in the file system. How do you decide what to choose? What are the performance impacts of each one?
Well, there are several reasons why you should not store binary data in your database:
- The whole point of storing data in a SQL database, is to put some kind of ordering and structure on your data, as well as being able to search on these data. But how do you search in the binary data of a picture?
- For large data sets, storing binary data will quickly run up the size of your database files, making it harder to control the size of the database.
- In order to store binary data in the database, you must continually escape and unescape the data to ensure that nothing breaks.
- Storing images on the file system has a marginally faster retrieval rate.
Now here are some reasons why you should:
- There is one good reason why you might want to store the binary data in the database: Replication. Storing images in a database allows for all of your data to be central stored which is more portable, and easy to replicate.
Here's one solution that takes into account the points above:
Store a link (e.g. a file path) to the image file in the database. Whenever you need the image, use the link in whatever program you use to retrieve the file containing the image.
Or you could think of storing your images in the database to gain the benefits there (preferable for smaller images and for limited images), but also use file system caching of these to obtain the performance benefits.
Some tips for getting the best performance out of the file system:
- Limit the number of images in any one directory.
- Include not only an image identifier in the filename, but also a secret code.