Best Database For Storing Images
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?
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? MySQL or Postgres are the obvious choices, and google has a few examples of code for both. I've only ever done this with Microsoft SQL server though. It has a "FILESTREAM" option where you manipulate the images as though they were columns in a database table, but they are actually stored in a directory in the filesystem, which has some advantages. If your project is really only small, storing them in the database shouldn't present a problem. I would not recommend the approach I have used in the past which is to store the images in the filesystem yourself and maintain links to them in the database. If you do that you need a way to keep everything consistent. James
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. MySQL or Postgres are the obvious choices, and google has a few examples of code for both. I've only ever done this with Microsoft SQL server though. It has a "FILESTREAM" option where you manipulate the images as though they were columns in a database table, but they are actually stored in a directory in the filesystem, which has some advantages. If your project is really only small, storing them in the database shouldn't present a problem. I would not recommend the approach I have used in the past which is to store the images in the filesystem yourself and maintain links to them in the database. If you do that you need a way to keep everything consistent.
Thank you, this is what I suspected but thanks for the confirmation.
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. MySQL or Postgres are the obvious choices, and google has a few examples of code for both.
If it is a small database and will not be accessed by many users, Sqlite might be sufficient - it has blob data type. Daniel I've only ever done this with Microsoft SQL
server though. It has a "FILESTREAM" option where you manipulate the images as though they were columns in a database table, but they are actually stored in a directory in the filesystem, which has some advantages. If your project is really only small, storing them in the database shouldn't present a problem. I would not recommend the approach I have used in the past which is to store the images in the filesystem yourself and maintain links to them in the database. If you do that you need a way to keep everything consistent.
Thank you, this is what I suspected but thanks for the confirmation. _______________________________________________ luv-main mailing list luv-main@luv.asn.au https://lists.luv.asn.au/cgi-bin/mailman/listinfo/luv-main
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. Regards, Andrew McNaughton
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
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
On Mon, Jun 13, 2016 at 06:30:46PM +1000, Andrew McN wrote:
On 13/06/16 17:21, James Harper wrote:
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).
I'm not convinced either. Same as I'm never convinced by the recurring idea of using an SQL database as a mail-storage backend. IMO the best option is to use a database to store information about the image (path and filename or URI, size, width, height, geo-location, description, and whatever other details are required. Maybe even a small thumbnail image), whilst storing the actual image in either: - a filesystem, with a hashed directory structure to avoid having too many files in one directory. e.g. .../images/{00-ff}/{00-ff}/{00-ff}/imagefile.png If you're worried about the image pathname getting out of sync with the database, you could write your own FUSE fs layered on top of the actual fs to automatically update the database if a file is renamed or moved. BTW, there are FUSE modules for perl and python to make this easier if you don't want to use C. - An object store like Amazon S3, Openstack's swift, or ceph. BTW, there are existing FUSE modules for these that could be modified to update a database if an object is changed. For a very large number of images, an object store is the best option...you get hugely scalable performance, redundancy, and storage capacity. craig -- craig sanders <cas@taz.net.au> BOFH excuse #302: microelectronic Riemannian curved-space fault in write-only file system
On Tue, Jun 14, 2016 at 04:12:21PM +1000, Craig Sanders via luv-main wrote:
IMO the best option is to use a database to store information about the image [...] whilst storing the actual image in [...] a filesystem, with a hashed directory structure [...]
Agreed.
If you're worried about the image pathname getting out of sync with the database, you could write your own FUSE fs layered on top of the actual fs to automatically update the database if a file is renamed or moved. [...]
Rather than writing a FUSE, you could also use one of the inotify(7) tools to set up watches on these directories, see; inotifywait(1), inotifywatch(1) or incrond(8)/incrontab(5) – although that is Linux specific, and you'd need to handle directory tree recursion yourself. ~Joel
have a look at OMERO then. that's what it does - it's a metadata manager. (fundamentally though, any DB is a front end to a filesystem anyway...- even if it is it's own "special" FS) On Tue, Jun 14, 2016 at 4:12 PM, Craig Sanders via luv-main < luv-main@luv.asn.au> wrote:
On Mon, Jun 13, 2016 at 06:30:46PM +1000, Andrew McN wrote:
On 13/06/16 17:21, James Harper wrote:
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).
I'm not convinced either. Same as I'm never convinced by the recurring idea of using an SQL database as a mail-storage backend.
IMO the best option is to use a database to store information about the image (path and filename or URI, size, width, height, geo-location, description, and whatever other details are required. Maybe even a small thumbnail image), whilst storing the actual image in either:
- a filesystem, with a hashed directory structure to avoid having too many files in one directory. e.g.
.../images/{00-ff}/{00-ff}/{00-ff}/imagefile.png
If you're worried about the image pathname getting out of sync with the database, you could write your own FUSE fs layered on top of the actual fs to automatically update the database if a file is renamed or moved.
BTW, there are FUSE modules for perl and python to make this easier if you don't want to use C.
- An object store like Amazon S3, Openstack's swift, or ceph.
BTW, there are existing FUSE modules for these that could be modified to update a database if an object is changed.
For a very large number of images, an object store is the best option...you get hugely scalable performance, redundancy, and storage capacity.
craig
-- craig sanders <cas@taz.net.au>
BOFH excuse #302:
microelectronic Riemannian curved-space fault in write-only file system _______________________________________________ luv-main mailing list luv-main@luv.asn.au https://lists.luv.asn.au/cgi-bin/mailman/listinfo/luv-main
-- Dr Paul van den Bergen
On 13/06/16 15:16, Andrew McN via luv-main 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.
Yep, it probably makes more sense just to store the unique filename.
participants (7)
-
Andrew McN -
Craig Sanders -
Daniel Jitnah -
David Zuccaro -
James Harper -
Joel W. Shea -
Paul van den Bergen