Skip to Main Content
  • Questions

  • Storing Images in database as BLOB Vs storing images on file system on Application Server

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Vinod .

Asked: May 03, 2001 - 5:12 pm UTC

Last updated: October 14, 2005 - 5:30 pm UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

Hi Tom
We are developing a web based application . We need to store images
which will be accessed by this application . We are wondering what is
the performance difference (and also pros and cons )between storing images in database as blobs vs storing them on web server (appliation server) as files.
When this systen will go in production we will have 30,000 images each one of 50K size.


Thanks.
Vinod Ladda.

and Tom said...

Pro's

o single source of data. You back up your database,you backed up your webserver in effect.

o point in time recovery of a single source -- no need to worry about syncing up a file system and a database

o "flat" but scalable storage -- having 30k, 300k, 300million, 3billion records in a table is just fine. 30k of images in a single directory is NOT advisiable at all (no more then a couple of hundred would be recommended). you would have to figure out HOW to hierarchically distribute the images into many directories.

o browser will cache images from the database just like an image from the file system


Con's

o a database connection to get an image, if you use connection pooling, this is ok if you are using a connetion pool.

o it will take longer to retrieve the image from the database then from the filesystem.



What I do --

I use a mixture. For very heavily accessed images (those on a home page) i keep the "real" copy in the database but also store it on the file system. I reference the file system version in the pages, knowing that If I lose it, need to restore from a backup, whatever -- I have it in the database. For less frequently accessed images (or anything BIG, pdfs, docs, etc) -- i keep them in the database only.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Storing images in database

Vinod Ladda, May 04, 2001 - 10:25 am UTC

Thanks Tom. It is useful information.

cache vs nocache for lob storage

Sam, December 20, 2002 - 5:27 pm UTC

Tom,

We have a shopping catalog application and we store the item images in the database as blobs with the NOCACHE option specified. Currently we only display the item image when a user drills down to see the details of an item. The search results page does not show any item images. Since many users never drills down to the item details page, the images are not fetched that frequently.

We are developing a new feature where we will show thumbnail images on the search results page. This will result in the images being fetched much more frequently.

We are thinking about switching to CACHE READ for the image lob column (our lobs are updated very infrequently), but we are a little concerned over what that would do to the data buffer hit ratios and the impact on the rest of the application. Assuming 20-30% of the catalog is accessed much more frequently than the rest, do you think it's a good idea to use CACHE READ? Please also let us know if there are any differences between 8.1.7 and 9i or 9iR2 related to lob retrieval.

Longer term we will start caching some image files on the file system, but for now we need to figure out the most appropriate cache setting.

Tom Kyte
December 20, 2002 - 8:16 pm UTC

well, there is the WEB CACHE as well -- which could help dramatically (see the 9iAS docs for more details)....


If you access the images frequently -- well, just like rows of data -- would you not want it cached in the buffer cache? Why would a thumbnail (pretty small, less then 16k typically) be any different then a block or two of frequently accessed data?

oracle 9i multiblock feature and blobs

Marvin, May 12, 2004 - 8:05 am UTC

Hi

I remember BLOBS have better performance using bigger database blocks. Would this performance gain be noticed if I stored BLOBS in 16k tablespaces and data in 8k tablespace under 9i R2?

Tom Kyte
May 12, 2004 - 11:20 am UTC

benchmark it :) that is what i would do.

Performance on retrieving images from filesystem or db

Iru, October 14, 2005 - 12:13 pm UTC

Hi Tom.
Sorry to post a comment on such an old thread but due to the different hours we have (i am in GMT time), the advice on your page always say "Sorry, i am too busy now" to me!.

I have a simple question: would you advice to put an image in a blob field on a table if this image is going to be accesed from a report that is going to be executed, say, about 1000 times on a row, in a client-server scenario?

Personally i think it would be faster than putting the file name in a varchar2 field and accesing it via filesystem, because the image itself would be stored in cache, am i right?

If that is the case, would the gain in performance would be critical?

A list of pros and cons would be great, but i think you already posted it above in this thread? If you have something to add, please do.

The application would be used by very few users (tipically less than 20). DB is 9ir2.

Thank you very much. Love your site, im just now trying to convince my boss to buy your books.

Tom Kyte
October 14, 2005 - 5:30 pm UTC

why wouldn't the image be stored in a cache coming from the database? I assume you mean a browser cache, or a caching web server or even the buffer cache - it can be and would be if you wanted it to be.



Thanks

Iru, October 17, 2005 - 2:29 am UTC

Thank you Tom. You resolved my basic doubt, now i'll investigate a bit more and present my list of pros and cons to my boss.

Again, thanks.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here