Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Is it appropriate to store the image files in the database? Or it would be better to store only the path of the file in the database, while keeping the file itself on the server?

Are there any other methods for doing this right?

share|improve this question
add comment

3 Answers

up vote 12 down vote accepted

I strongly advise you to store the images in the filesystem and not in the database.

Storing images in the database has several disadvantages:

  • The database might grow unexpectedly large. Sometimes space is an issue. For example with SQLServer express you have a 4GB limit.

  • Data migrations can become a pain, for example if you switch from SQLServer to Oracle

  • Queries can become very slow and you'll have a high database load

  • Interoperability with other applications is better if the images are on the filesystem and other applications use a different database. You can also access them directly and do not need database tools.

  • Worse performance in general

  • You'll probably have to create temporary files when retrieving the images from the database anyway. That's unnecessary.

These disadvantages far outweigh the cost of keeping the paths to the images stored in the database synchronized with the filesystem. There're only few special cases in which it's better to store the images in the database.

share|improve this answer
add comment

Research on SQL Server 2005 and the NTFS file system (Microsoft) to compare CRUD performance: To BLOB or not to BLOB. This study was also done in a web application. You listed another database (MySQL) and I'll assume you're not using Windows Server for your PHP site, so it would be interesting if someone has done a similar study on different technologies.

Turns out that it depends on the size of the files. SQL Server favors (2x better) blobs of 256K or less and the file system favors files of 1MB+. File systems in this study handle fragmentation better than the database, so if you are constantly updating these files or this system grows over time, fragmentation is going to be a bigger factor which the file system will do a better job.

You need to determine how responsible your site is for maintaining these files. If you're buidling a site for insurance claims agents to upload photos of an accident, you better have transaction control and make sure those files are on the server. Good databases do this for you, so as the developer, you have added pressure.

Replication, backup, disaster recovery, fragmentation, remaining disk capacity, and performance over time should be considered during design. This is just one study on the previous version of SQL Server and like other database manufacturers, I'm guessing handling large files and binaries is a major competitive arena.

share|improve this answer
    
You might also want to consider integrated solutions. For example, on SQL Server there are FILESTREAM and Remote Blob Store. –  Fernando Correia Jul 16 '12 at 11:17
add comment

If you are going to have a massive number of images, storing them in a database might remove problems with running out of inodes at the file system level.

However this problem would best be solved by using a more appropriate filesystem and whatever measures are advised with that filesystem on organisation.

Otherwise, storing images in a database is a complete waste of resources. Store the path as you said.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.