Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Possible Duplicate:
Files - in the database or not?

I was wondering if there's any good reason to still use blob fields in a database. A couple of years ago I worked with a DB with a bunch of images in it, the DB was very slow and I couldn't see any good reason to keep the images inside the DB, so I got the images out and stored the filenames instead.

Was this a smart move? What would you do in my place?

share|improve this question

marked as duplicate by Derek Downey, jcolebrand Oct 5 '11 at 17:36

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

    
Closing as a duplicate so as to constrain discussion on only one post going forward. Not merging because the context here is good. In the future if we need to merge, we can. –  jcolebrand Oct 5 '11 at 17:36

7 Answers 7

up vote 24 down vote accepted

As you can tell from the other answers, this is a big "It Depends." Some other factors might be if you are paying for hosting, do they charge more for file storage or database storage. File storage is typically cheaper, especially for cloud services.

If you are self hosted and using SQL Server, the upcoming version, codename Denali, will extend FILESTREAM to allow access via both TSQL and file system. It also will make sure these stay in sync. You can access, update, delete from either side and it will keep everything organized.

Do your research and find out what's important to you and pick the direction based on that choice.

share|improve this answer
    
and sql server 2k8 too? –  garik Jan 20 '11 at 20:09
    
There is a version of FILESTREAM in 2008 & R2, but the files can get out of sync if modified through the file share. Denali overcomes this problem. It's just not RTM yet. –  Eric Humphrey - lotsahelp Jan 20 '11 at 20:13
1  
+1 for pointing out the issues with FILESTREAM as it is today. I'm interested in playing with what they're doing in Denali but honestly - the idea still gives me chills! ;) –  AndrewSQL Jan 21 '11 at 4:05
3  
+1 for Denali feature (dba.stackexchange.com/q/401/81), reference added. –  garik Jan 21 '11 at 7:05

The reason to use BLOBs is quite simply manageability - you have exactly one method to back and restore up the database, you can easily do incremental backups, there is zero risk of the image and its meta data stored in DB tables ever getting out of sync, you also have one programming interface to run queries or load/save images, so you don't need to give remote clients filesystem access and you know the same GRANTs will apply to images and their associated data. Plus you have one method of storage management (e.g. in Oracle you might put everything on ASM and use Oracle as your LVM for everything).

Another application is to mix relational data with serialized objects (a BLOB can be any binary, it doesn't need to be images). Or you can run a query against relation data and bytes x-y in the BLOB, which might be a file header for example. The applications are in fact endless.

If accessing a BLOB was slower than accessing your filesystem then it is highly likely your database was misconfigured.

share|improve this answer
3  
We changed our database to blobs for this reason. The images were images of customers. They directly related to the data in the database and combining them made backups easier. –  SchwartzE Jan 20 '11 at 19:28

I don't use blobs -- mostly from a backup and restore perspective, as I don't want the blob data slowing down my backups.

I don't store a full URL, however ... I only store the filepath below a certain point, and build the path as I have more than one way in which people & programs access my files (FTP, HTTP, local directory, NFS mounted directories).

... of course, I likely deal with more & larger images than most people ... one of my datasets gets about 700GB of images (compressed) per day. But even the thumbnails for those images, I still store externally.

share|improve this answer
1  
I don't understand your reasoning. Does that mean that you're not backing up images at all? If you back them up, it certainly slows down something, too. –  René Nyffenegger Jan 20 '11 at 19:37
    
@René : Those that are backed up are on completely different storage (SAN). We also have some stuff that's on a different class of storage that can be regenerated (eg, thumbnails), and other data where we are the mirror site for other missions, so if there's a disaster, we can get them to ship us new copies. (and I mean when I say 'ship' I mean we have a bunch of 4-6 disk RAID enclosures we mail when people need lots of data) –  Joe Jan 20 '11 at 20:06
2  
@Gaius : um ... you realize with an incremental you still need to take fulls, right? I mean, you don't want to apply 1400 incrementals to a 4 year old database. And in my case, the database is less than 1/1000 of the size of the images. Also, the class of storage for the images is different than that of the database, and the class of storage for the image backups (when they're backed up), is different from the class of storage for the database backups. They also have different rules for retention, etc. –  Joe Jan 20 '11 at 21:35
2  
+1 as I'm also in the "don't put file blobs in the database" camp. It's messy, quickly unmanagable, and won't scale well without throwing money at the problem. –  AndrewSQL Jan 21 '11 at 4:09
1  
@AndrewSQL : actually, scaling's also a significant part of the issue -- The web servers (and FTP servers) are scaled horizontally and separately tuned. –  Joe Jan 21 '11 at 14:04

I'm a big fan of storing the "reference" copy of the image in the database -- from a managability/disaster recovery standpoint this is really the way to fly.

Now, you can still do lots of things to serve the image out of the filesystem for most applications so you are not putting that much pressure on the database server itself to do things it doesn't really want to do.

share|improve this answer
    
I totally agree with this aproach. Reference copy on my expensive databaseserver and cache the images on relatively cheap webservers or on any client to max performance and let the databaseserver only deliver the image to maintain cache or caches. –  Martin Sjöberg Sep 8 '11 at 12:26

If you are working with linux, storing the images in the filesystem and not in the database has significant better performance, see this excerpt of Brad Ediger's book Advanced Rails.

share|improve this answer
    
Thanks for the link -- I hadn't seen the 'X-Sendfile' approach ... I've always used a redirect, but I've got a couple CGIs where this could be useful. –  Joe Jan 21 '11 at 22:07

I'm not much of a fan of storing images in the database. In a small app with a few users, it seems like an easy solution, but as you start to scale, it makes things more difficult.

My preference is to start out storing images in a folder on the web server, but keep the path in an easily accessible configuration so that when I need to, I can quickly move them to their own dedicated, optimized image server. Later, I might want to move them somewhere else (think S3 or Akamai) in the same way. All of that is much more complicated if I have to change out code that has been expecting to read them from the database.

share|improve this answer

Was this a smart move?

If it was a smart move or not, it depends of your specific case:

If the file location is directly affecting any url structure or if you're storing full file addresses in the database (bad), I can say it was a bad move since you will have trouble in case somebody move or rename some directory.

But as But if your application is built in a way you simply have to point the files directory and the file access logic is dynamic, you made a smart move for the following reasons:

  • With database storing, If there is need to serve a lot of images per request, you will increase the response time of your application since the files are going to be sent synchronously to the network. Also you will add a little more processing to your server.

  • File storing is usually cheaper than database storage.

  • With file storing (unless there are restrictions to image access: only a determined user can access a given group of images), there's no need of any processing to access images or any other kind of file.

  • With database storage it's not possible to access your files using FTP (pretty obvious, but it's good to remember).

  • Database file storage will slow down and / or clutter your database periodic backups.

What would you do in my place?

I would keep that decision unless a contrary dominant factor appears.

Hope that helps.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.