Storing Images in DB - Yea or Nay?

2019-09-06 18:32发布

So I'm using an app that stores images heavily in the DB. What's your outlook on this? I'm more of a type to store the location in the filesystem, than store it directly in the DB.

What do you think are the pros/cons?

30条回答
Rolldiameter
2楼-- · 2019-09-06 18:48

I'm in charge of some applications that manage many TB of images. We've found that storing file paths in the database to be best.

There are a couple of issues:

  • database storage is usually more expensive than file system storage
  • you can super-accelerate file system access with standard off the shelf products
    • for example, many web servers use the operating system's sendfile() system call to asynchronously send a file directly from the file system to the network interface. Images stored in a database don't benefit from this optimization.
  • things like web servers, etc, need no special coding or processing to access images in the file system
  • databases win out where transactional integrity between the image and metadata are important.
    • it is more complex to manage integrity between db metadata and file system data
    • it is difficult (within the context of a web application) to guarantee data has been flushed to disk on the filesystem
查看更多
淡お忘
3楼-- · 2019-09-06 18:48

In my experience, sometimes the simplest solution is to name the images according to the primary key. So it's easy to find the image that belongs to a particular record, and vice versa. But at the same time you're not storing anything about the image in the database.

查看更多
smile是对你的礼貌
4楼-- · 2019-09-06 18:49

I have recently created a PHP/MySQL app which stores PDFs/Word files in a MySQL table (as big as 40MB per file so far).

Pros:

  • Uploaded files are replicated to backup server along with everything else, no separate backup strategy is needed (peace of mind).
  • Setting up the web server is slightly simpler because I don't need to have an uploads/ folder and tell all my applications where it is.
  • I get to use transactions for edits to improve data integrity - I don't have to worry about orphaned and missing files

Cons:

  • mysqldump now takes a looooong time because there is 500MB of file data in one of the tables.
  • Overall not very memory/cpu efficient when compared to filesystem

I'd call my implementation a success, it takes care of backup requirements and simplifies the layout of the project. The performance is fine for the 20-30 people who use the app.

查看更多
\"骚年 ilove
5楼-- · 2019-09-06 18:49

Second the recommendation on file paths. I've worked on a couple of projects that needed to manage large-ish asset collections, and any attempts to store things directly in the DB resulted in pain and frustration long-term.

The only real "pro" I can think of regarding storing them in the DB is the potential for easy of individual image assets. If there are no file paths to use, and all images are streamed straight out of the DB, there's no danger of a user finding files they shouldn't have access to.

That seems like it would be better solved with an intermediary script pulling data from a web-inaccessible file store, though. So the DB storage isn't REALLY necessary.

查看更多
干净又极端
6楼-- · 2019-09-06 18:50

At a company where I used to work we stored 155 million images in an Oracle 8i (then 9i) database. 7.5TB worth.

查看更多
趁早两清
7楼-- · 2019-09-06 18:53

The problem with storing only filepaths to images in a database is that the database's integrity can no longer be forced.

If the actual image pointed to by the filepath becomes unavailable, the database unwittingly has an integrity error.

Given that the images are the actual data being sought after, and that they can be managed easier (the images won't suddenly disappear) in one integrated database rather than having to interface with some kind of filesystem (if the filesystem is independently accessed, the images MIGHT suddenly "disappear"), I'd go for storing them directly as a BLOB or such.

查看更多
登录 后发表回答