43

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?

1
  • 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, 2011 at 17:36

7 Answers 7

31

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.

4
  • and sql server 2k8 too?
    – garik
    Jan 20, 2011 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. Jan 20, 2011 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, 2011 at 4:05
  • 3
    +1 for Denali feature (dba.stackexchange.com/q/401/81), reference added.
    – garik
    Jan 21, 2011 at 7:05
17

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.

1
  • 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, 2011 at 19:28
13

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.

7
  • 2
    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. Jan 20, 2011 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, 2011 at 20:06
  • 3
    @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, 2011 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, 2011 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, 2011 at 14:04
9

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.

1
  • 1
    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. Sep 8, 2011 at 12:26
8

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.

1
  • 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, 2011 at 22:07
5

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.

5

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.

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