SQLITE Blob OR file system for images

Question

Hello, I am building an application based on a single table with a column with text. Occassionally, an adjacent column will have an image. Is it better to store this image as a BLOB in SQLITE or should I store them on the file system and reference them from my programs. Thanks!

Answer 1

EDIT:

Didnt realize you meant for the iPhone environment specifically. In that case I would use the DB just for the simplicity of having all content in one place. You wont have to worry about scalability because its not like your iphone is going to be used as a server or anything.

Original Response:

I dont have any links to back this up, but I do recall reading in several studies that the "cut off" is 1 MB for blob efficiency. But this can moved up to 10 MB with a fast enough disk array. Totally depends on the system.

So basically, given your efficiency cutoff, any data smaller than that would better be served by the DB, anything larger, just index in the DB and leave in a file cache.

Answer 2

It really depends on your application. Having the images stored in a database will make your life easier as you have them readily accessible in a single point instead of having them in separate files that might gone missing. On the other hand, many images, that are rather large, might prove too much for a SQLITE database. In your situation I would just reference them in the database.

Answer 3

Files will cause you fewer problems in the long run. You really don't want to be serving tons of files from your database server especially as you scale

Answer 4

I like to keep images in the file system because UIImage can cache image files & dump them from memory automatically when necessary. Just be careful not to change or delete an image file that is loaded into a UIImage or you will get crashing or other weird bugs.

Answer 5

Assuming the images you are going to use are not extremely large and there is not an exorbitant number of them I would go with the database.

I am currently using a Sqlite database on several different Windows Mobile and WinCE devices with over 10,000 small images stored as blobs and it is working great.

I have seen software similar to ours running on the same hardware using file based image loading and it was significantly slower. Of course this was on WinCE and different software, so that is not the best test.

I find the single database is much easier to work with than many image files.

Answer 6

Can someone provide an example of how you would read a blob record of an image from the sqlite db and render the image via javascript?

Answer 7

What about if I need to keep 5000 images in my app and access them frequently, AND some of them will be reasonably large - a few 300K.

-sean

Answer 8

if the (compressed) BLOB > 2M, then go file system, else SQLite works fine and fast.

Consult the internal documentation for yserial module for case study and references which leads to that general rule. It also offers a ready-to-go implementation:

"Serialization + persistance :: in a few lines of code, compress and annotate Python objects into SQLite; then later retrieve them chronologically by keywords without any SQL. Most useful "standard" module for a database to store schema-less data."

http://yserial.sourceforge.net

You could easily tag your images and access them via yserial < 10 minutes to implement. Related images can then be retrieved via a dictionary.