Putting files into a database

So, once again, I was listening to the Pro::PHP Podcast (or is it Newscast?  guys?).  They were talking about putting files in to a database table.  Now, most people will say you should never do this.  And lots of time they are right.  And once upon a time I agreed with them without question.  Then I started living in the real world where sometimes you have to do things you never thought you would.  Here are the two places where I stores files in a database.

Phorum

Ever wrote software that has to be installed on n + infinity environments.  It really sucks.  Phorum version 3.3 or 3.4 was the first to allow attachments.  We put them on disk.  The instructions for setting up a spot on disk for attachments was longer than all the rest of the install document.  The support questions were even worse.  And there there were the people that left their old host and didn’t take the files with them.  Just the database.  DOH!  So, in Phorum 5 we decided that for ease of use, there would be no need for Phorum to write to disk.  Well, we tried really hard anyway.  Its almost true.  We only write cache data to disk.  No config is written by the applicaiton and no permanent storage is written to disk.  Phorum 5.2 (near beta) has a new file storage system that allows for modules to be written to store the data wherever you want it.  There is already a disk storage module.  But, already there are people asking really naive questions about it.  Probably because someone told them to never store files in a table.

Replication

At dealnews we have a lot of images.  Every deal gets its own image.  We do 100+ deals a day.  So, getting those images distributed to all the servers is a task.  A deal could go from idea to live in 2 minutes.  So, we decided to store the images in the database.  3 versions of the image in fact.  With a little mod_rewrite magic we can pull the image from the DB and put it on disk on a front end server if its not on the server already.  From that point forward the file is served off of disk.  So, its a one time db hit per image per server.  Not that big of a deal.  We use a CDN now too, so it’s really not a big deal.  We could probably skip the on disk part all together.  Backups are easy.  All the attributes of a deal are in the database.  Not some in the db and some on some disk.  We can just import the db to a test machine and have a fully functioning set of data to work with.

About these ads

14 Responses to Putting files into a database

  1. Bill Karwin says:

    I also heard the blanket assertion given on the phpPro podcast that one should “always” put files (for example, images) outside the database. I replied to them, and even recorded audio at their request, but they didn’t use it on their next podcast.

    My point was that a database should obey ACID properties, and when you store data externally instead of in a blob, you compromise all four ACID properties when inserting, updating, or deleting these values.

    This isn’t necessarily important in all applications, and there are some conveniences you gain by storing attachments outside the database. So it’s certainly not accurate to say that it’s always a good idea or always a bad idea to do this.

  2. Lukas says:

    As you rightfully point out there are several good reasons to store binaries in the database:

    1) backup
    2) access rights
    3) replication
    4) ACID
    5) OS portability (most RDBMS are fairly portable)

    As for the performance impact. This can be worked around in some cases (like using mod_rewrite and caching in the FS as you mention) or with an RDBMS that supports LOB streaming (MySQL is getting this feature). It all depends on your use case and tools available.

  3. I guess the best way to put it would be “never serve files from the database”, with emphasis on the “serve from” part ;)

  4. Lukas says:

    Well thats not true either .. when using the mod_rewrite trick to cache in the FS, on the first request you would serve out of the database :)

    Anyways, never say never.

  5. doughboy says:

    Never say never is the way to think about it. You never know what you will need to do to solve a problem.

  6. Indeed, Large number of concerns comes from the serving issue and in many cases if you can cache things you’re fine.

    For large systems however the database size also becomes the problem. Neither forums nor dealnews have too many images so it is not the problem but think about almost any social networking side having terabytes of images – fitting them inside the database may be quite a hell making things unmanagable. Plus you either need double storage needs or you will still have to many hits to database.

  7. Brian Moon’s Blog: Putting files into a database

  8. [...] a recent blog post, Brian Moon talks about doing something he never thought he’d do – storing files in a [...]

  9. Ren says:

    I can see some value in putting smaller files (like images) into a database.

    But if have to deal with larger files, like audio and video, it doesn’t seem such a good idea.

    And as Peter points out, there does come a point where you start hitting limits of what a database can do. One application that has hit the limitation is Wikipedia, which has had to move the article texts out of the database. (see page 22 of http://dammit.lt/uc/workbook2007.pdf )

  10. [...] files into a database Posted in October 24th, 2007 by admin in News, PHP News In a recent blog post, Brian Moon talks about doing something he never thought he’d do – storing files in a [...]

  11. Rick says:

    The one thing you should always do is create a decent abstraction layer for your file i/o. That way, no matter what you do, the rest of your app won’t be affected by changes in storage, retrieval and possible caching of files. (Like apparently you’ve already done in Phorum).

    In my experience, the circumstances that determine if it’s more effective to store files in the filesystem or in the database are exactly those that are often subject to change (scale, usage, infrastructure). Never bet on one single solution.

  12. Naveen says:

    I need the query on how to insert files into a database!

    Ofcourse i know that it couldn’t be a better choice but still i need it in my project!

    So can anyone help me?

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: