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?
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.
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.
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.
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.
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.
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.
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.
