Save images in SQL or not?

Question

As a rule, I thought it was always better to store images in the file system and refer to it through an entry in the database. However, I am trying to optimize my db design, and I have a few questions.

My images are all very small thumbnails in black and white (not gray, but true B&W) and have a size of 70x70. If we take the images (which are basically a 2D array of 1 and 0), it can be saved as binary data, which will be approximately 600 bytes.

So my question is whether requesting 600 bytes stored in db is faster than requesting a link, followed by access to the file system; assuming there are many "image" requests.

Does anyone have any experience in this area?

If that matters, I use MySQL and MonetDB (separately, but the question is the same for them).

Thanks a lot, Brett

Answer 1

If it is only 600 bytes, I would not worry too much and save them in the database as blob

There is an interesting article about High Scalablilty about how Flickr is archived. This may be useful for you.

Answer 2

Since you have noted the sql-server question, I recommend you read “Blob” or “Not to Blob,” a document written regretfully by Jim Gray. The article describes in detail the topic of storing BLOBs in the file system (NTFS) and database (SQL Server), and you will be surprised how many corners are considered. It MUST be read. But the conclusion is this:

Research shows that if objects are more than one megabyte per medium, NTFS has a clear advantage over SQL Server. If the objects are up to 256 kilobytes, the database is a clear advantage. Within this range, it depends on how you write the intensive workload, and the age at which a typical copy is stored in the system.

Your case clearly falls in the case of "IN BLOB".

Answer 3

As far as I understand, there is no problem storing even large files in the database if you do not use SELECT * for no reason (frankly, there has never been a reason to use SELECT * at all).

BLOB and TEXT are stored separately from other data and do not affect performance unless explicitly requested.

Answer 4

If you are talking about a web application, then storing images in a database is simply stupid. Since you have no advantages that a desktop application can get, but only difficulties.

Answer 5

This depends not only on the file size, but also on the maximum number of records that you expect to have when running the database. We used to use this math for any type of field. Just multiply 600 bytes by the maximum number of records, and if the result is something controllable, don't worry about speed.

As @codeholic says, if you do not use SELECT *, everything goes well.

Answer 6

Saving an image in a database (and servicing it for each request) does not allow you to cache these images on a proxy server (or rather, it complicates the task many times and displays almost all ready-made solutions). The trick is that to measure the impact you need , look at it differently - instead of "how long does one request make to retrieve an image," asks itself "how long does a series (sets a reasonable number here) make requests to get the same set of images." Perhaps also ask yourself: "Do I have to pay the cost to make the return trip to the database and back?".

Not that the idea was meaningless - updating images in one place can be an important factor. In addition, if high availability is a factor, it is much easier to configure using the database as the central data point (placing images in the file system means that they synchronize between nodes when they are updated). Change the tracking, permissions, additional data, avoiding "broken links" - they can also play a role.

All of the above, I had poor experience using the “file system” technique. We are currently considering the transition to the "database" method.