On 13/06/16 17:21, James Harper wrote:
On 11/06/16 12:20, David Zuccaro via luv-main wrote:
On 11/06/16 12:12, James Harper wrote:
Is there a database that can be used to store images? If so, what is the best one to use for a small web project I have in mind? Do you mean like an SQL database with an IMAGE/BLOB type? Yes.
While you can do things this way, it's typically better to store the images in a file system, or even on a remote CDN, and use your relational database for storing information about the images, but not the actual image content.
Loading images from SQL BLOB storage means loading the whole image into memory at once, which is not good for larger images, particularly if you are serving multiple images on a page. It also saves SQL query overhead per image, and whatever processes might sit between the DB and the web server for the image requests.
I don't think that's been true for a while, at least not for the bigger database products. This article explores some of the advantages and disadvantages of each approach, from a postgres point of view https://wiki.postgresql.org/wiki/BinaryFilesInDB
TL;DR - the answer is "it depends" :)
James
I find that article unconvincing, but my concerns are mostly around performance. If performance is of no concern at all, then the atomicity/maintenance argument might be significant (though not the stuff about storing as a text data type). In many use cases, where the same image gets read many times, the performance issues can be made manageable with an appropriate caching layer. That caching might well use filesystem storage, allowing rapid spooling from db to file by your web app, and then handing off delivery to your web server, freeing up the web app and sql resources quickly. Or you might just rely on a caching front end web proxy. such an approach gives you the integrity advantages of keepingyour image with your other data, without relying on it for delivery. In terms of integrity you might need to look carefully at cache expiry, though it's often sufficient to stop serving the URL for old images on your web pages. An interesting case to consider is storage of captcha images. These are generally accessed very rapidly after they are stored, and are likely to only be used once, so on-demand caching doesn't help. storage to the file system in advance of the image being requested is fine for performance, but in multi-server architectures it sometimes creates a problem if there is no shared file system, and/or files are synced between servers but not instantly. The images are small. Storing those images in the database might make sense. There are other sound approaches, but they would be require more work to implement in architectures like some I've worked on. Regards, Andrew McNaughton