in reply to Is it a good idea to store images in a RDBMS?

Depending on your choice of RDBMS, you may find it is advantageous. Your application, and the planned development thereof, would also influence your decision.

It would be fairly trivial to write a Java or VB client for your DB, and it could view the images by retrieving them from the DB quite easily. However, if these were stored in some alternate method (a.k.a. files on disk), then you would need to use HTTP or some other mechanism to transport them, which would be more complicated.

Retrieval Time
Remember that after you put 10,000 images in a single directory, your OS may have trouble looking up filenames. Quite often these directories are not indexed, so finding a file takes, to put it in math terms, O(n) time, which is, to put it in simple English, really awful. You may find that a simple lookup in a large directory could take 1-2 seconds. In a properly indexed DB, retrieval time should always be fairly quick.

Of course, you can always get around this by sorting your images into different directories using a hash-technique, or some creative variation. 100 directories with 100 files each is much, much faster than 10,000 files in a single directory. The downside is more programming.

Storage Space
Your DB might actually be a better way to store images than your filesystem, if the block sizes for "BLOB" fields are small enough. It is not uncommon to see people using 64K blocks, which means that a 2K GIF image actually uses 64K of disk space. A lot of tiny images can fill up a disk, even though their aggregate size is much smaller. A DB with a 1K block would actually save disk space.

Of course, if you were planning ahead, you could format your filesystem with the appropriate block size, if your OS allows for such a thing (i.e. mkfs -b 1024). This, though, is a lot of work for something that should be quite easy.

Access Control
Implementing a DB-level access control, especially using an RDBMS's own methods, is fairly easy. Reimplementing this on the filesystem level can be quite tricky, especially if system accounts are involved.

Your decision should be based on careful analysis of your immediate and planned requirements. The DB solution works, and the filesystem one does too. Personally, if you want a more "elegant" solution, the DB route does keep things much more managable, since in effect you can query your filesystem.

Replies are listed 'Best First'.
Re2: Is it a good idea to store images in a RDBMS?
by pmas (Hermit) on Jul 21, 2001 at 19:17 UTC
    Your embedded images and sound will be accessed via HTML tags, rigth? Consider, that if you store them in database, you will need to extract them into temporary files each time you neet to generate page containing them. So you need to find the filename of .gif file stored in DB, extract large binary contens and write it to temporary file, and delete afterwards. Looks like potential bottleneck for me.
    But, if you store in your DB just file name, you need just print filename into proper HTML tag, what you are doing anyway.
    But, as tadman noted, too much files in one directory can slow you down, too.
    This leaves you with spreading them into many directories. How to do it most efficiently?
    This was also asked here, one smart proposal what I liked was to place file foo.gif into /f/fo/foo.gif. Straightforward and error-prone, even if file is moved into wrong directory by mistake. Sorry I cannot remember who proposed this naming scheme.
    Remember, if something can go wrong, it will.

    pmas
    To make errors is human. But to make million errors per second, you need a computer.

[reply]
[d/l]
[select]
      Consider, that if you store them in database, you will need to extract them into temporary files each time you neet to generate page containing them.

      Not necessarily. One could write a CGI / handler that fetches a picture directly from the database, which to a browser, would look no different than a request for a file. This sort of thing was recently discussed here. 

         MeowChow                                   
               s aamecha.s a..a\u$&owag.print
[reply]
        ++ for you MeowChow! I did not know trick from your link!
        Now I recall: It is because every item is loaded from HTTP server to browser separately, by separate request, and only on client side rendered together to make a page. So you can create CGI script which will look like one .gif file for browser. I *read* about it, but knowledge was not digested. Now it is!

        So much more to learn!

        pmas

        To make errors is human. But to make million errors per second, you need a computer.

[reply]

In Section Meditations