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.

What is the best place for storing binary files that are related to data in your database? Should you:

  1. Store in the database with a blob
  2. Store on the filesystem with a link in the database
  3. Store in the filesystem but rename to a hash of the contents and store the hash on the database
  4. Something I've not thought of

The advantages of (1) are (among others) that atomicity of transactions is preserved. The cost is that you might dramatically increase storage (and associated streaming/backup) requirements

The goal of (3) is to preserve atomicity to some degree - if you can enforce that the filesystem you are writing to does not allow files to be changed or deleted, and always has the correct hash as filename. The idea would be to write the file to the filesystem before permitting the insert/update referencing the hash - if this transaction fails after the filesystem write but before the database DML, that is fine because the filesystem is 'faking' being the repository of all possible files and hashes - it doesn't matter if there are some files in there that are not being pointed to (and you could clean them up periodically if you are careful)

EDIT:

It looks like some RDBMSs have this covered in their individual ways - I'd be interested to know how others do it - and particularly in a solution for postgres

share|improve this question
3  
This question has a duplicate here: Is it better to store images in a blob or just the url? that was closed in favor of this one, as this one being more outstanding. Please be sure to read both questions for more insight! –  Marian Apr 29 '11 at 18:51
    
@Marian - thanks for the link, the answers/comments there are indeed helpful (and not just relevant for images as I first thought) –  Jack Douglas Apr 29 '11 at 20:19
    
@Jack Douglas: any answer worth accepting here? –  gbn Sep 4 '11 at 13:17
1  
@gbn I've come to think it is not such a good question because the 'right' answer might depend on RDBMS - eg the lack of proper incremental backup in postgres is an issue for me if I decide to keep files in the db. If there was one outstanding answer I'd still accept it, but I'm not sure that would add any value as things stand... –  Jack Douglas Sep 5 '11 at 7:54

11 Answers 11

  1. Store in the database with a blob

    A disadvantage is that it makes your database files quite large and possibly too large to back up with your existing set up. An advantage is integrity and atomicity.

  2. Store on the filesystem with a link in the database

    I've come across such horrible disasters doing this, and it scares me that people keep suggesting it. Some of the disasters included:

    • One privileged user who would rearrange the files and frequently break the links between the paths in the DB and where they now are (but somehow this became my fault).
    • When moving from one server to another, the ownership of some of the files was lost as the SID for the old machine's administator account (what the old website was running on) was not part of the domain and so the copied files had ACLs that could not be resolved thus presenting users with the username/password/domain login prompt.
    • Some of the paths ended up being longer than 256 characters from the C:\ all the way to the .doc and not all versions of NT were able to deal with long paths.
  3. Store in the filesystem but rename to a hash of the contents and store the hash on the database

    The last place I worked at did this based on my explanation of the above scenarios did this. They thought it was a compromise between the organization's inability to obtain experience with large databases (anything larger than about 40G was ordained to be "too big"), the corporate inability to purchase large hard drives, and the inability to purchase a more modern back up solution, and the need to get away from risks #1 & #3 that I identified above.

My opinion is that storing in the DB as a blob is a better solution and more scalable in a multi-server scenario, especially with failover and availability concerns.

share|improve this answer
1  
I'm not sure the backup size is an issue; data needs to be backed up however it's stored. The same differential vs full decision gets made whether we're talking about a FS or a DB. I do note that this is presented a possible argument, not your point of view. –  Phil Lello Apr 29 '11 at 16:48
1  
I once had a issue where hundreds of megabytes were written to each row thousands of times a day. They were storing a GZIP file in the DB as a binary for 10000 servers, but a bug was introduced where every server recorded info for every server, per alert. It was horrible. After that incident, I became adamant about 'no (MAX) data types unless it's extremely justified'. –  Ali Razeghi Jan 29 '13 at 0:20
    
The whole "link breaking" is an application issue and not a database issue. The database is doing it's job (serving pure data) while the application isn't (serving mixed file types). The application should take have responsibility of serving files. By storing an abstract route path in the database that would work no matter where the file gets stored on the server internally (ala Symfony2 routing). This would abstract away the native paths, make the application more portable, maintainable and allow to switch to any kind of filesystem without breaking anything. –  Tek Jul 31 at 18:28

Number 1 for complete data integrity. Use the other options if you don't care about data quality. It's that simple.

Most RDBMS have optimizations for storing BLOBs (eg SQL Server filestream) anyway

share|improve this answer
    
what is it about (3) specifically that puts data integrity at risk? (assuming you get your transactional API right) –  Jack Douglas Apr 29 '11 at 13:05
4  
@JackPDouglas: you have hash which is the not the correct data and still has an external dependency for dats integrity –  gbn Apr 29 '11 at 14:02
4  
@JackPDouglas There's also the possibility that the server admin and DBA are different teams, with the associated risk that files get deleted in error, or not backed-up as they're thought of as temporary files. –  Phil Lello Apr 29 '11 at 16:23

If going for oracle, take a look at dbfs and Secure Files.

Secure Files says it all, keep ALL your data safe in the database. It is organized in lobs. Secure Files is a modernized version of lobs, that should be activated.

dbfs is a filesystem in the database. You can mount it similar like a network filesystem, on a Linux host. It is real powerful. See blog It also has a lot of options to tune to your specific needs. Being a dba, given a filesystem (based in the database, mounted on Linux), I created an Oracle Database on it without any problems. (a database, stored in a ... database). Not that this would be very useful but it does show the power.

More advantages are: availability, backup, recovery, all read consistent with the other relational data.

Sometimes size is given as a reason not to store documents in the database. That data probably has to be backed up any way so that's not a good reason not to store in the database. Especially in a situation where old documents are to be considered read only, it is easy to make big parts of the database read only. In that case, those parts of the database no longer have a need for a high frequent backup.

A reference in a table to something outside the database is unsafe. It can be manipulated, is hard to check and can easily get lost. How about transactions? The database offers solutions for all these issues. With Oracle DBFS you can give your docs to non database applications and they wouldn't even know they are poking in a database.

share|improve this answer
6  
+1 for stating a good rule "A reference in a table to something outside the database is unsafe." –  909 Niklas Sep 24 '11 at 12:23

Although it partly depends on the application/environment (people included), I'd go for the blob.

Keeping everything in the database means replication works for file data. You'd need a separate mechanism to synchronise FS files.

In some applications, the filesystem shouldn't be modified anyway. For example, on a production website, I'd avoid ever using the filesystem for any non-disposable data (site lives under a SCM, data in a database).

Assuming we've got multiple users/applications with separate permissions, then any filesystem storage provides an opportunity for differences in DB and FS access rights.

The refinement I'd consider making to BLOB storage is to chunk data if it makes sense; if you only need 512 bytes from a 20Mb BLOB, this sector-like access is a real boon, especially if you're dealing with remote clients (and again, a partial update creates much less replication traffic).

share|improve this answer

Back in the day, Microsoft hyped up the ability to store images (and similar blob data types) in the database. The was a cool new feature of SQL Server 2000 (I am pretty sure it was 2000, not 7.0) and many people jumped on the bandwagon.

Storing BLOBS in the database has advantages and disadvantages:

On one hand, all your data and related images or documents can be stored and accessed in one place. Application user's do not require special network permissions, as it is SQL that is serving up the images/files/documents.

On the other hand, your database can grow quite large, depending on the size and number of BLOBS you are storing. This affects backups, storage requirements, time sensitive recovery operations, etc.

SQL 2008 introduced file streaming. The database contains pointers to the files, the files reside on the server not in the database, but when you backup the database the files are backed up as well.

Your backups can get quite large, but you don't end up with orphaned files/documents/blobs/images.

My personal preference has been to let the database store pointers/network locations, and let a file server handle the files. File servers are better optimized for such tasks anyway.

share|improve this answer
3  
Never mind that if you don't own the server you're going to pay a heck of a lot more per MB for database space vs. file space. Also having the file on disk makes it much easier to troubleshoot - how do you SELECT image FROM table in SSMS and validate that the right image is there? –  Aaron Bertrand Sep 26 '11 at 15:07

I think the right answer here depends a lot on your application, and how important those documents are.

For a document management system, or a system where recoverability of the stored documents is critical (so most things financial, HR or CRM related), storing documents inline or using your favourite DB vendor's proprietary document tech seems like the Right Thing To Do.

However, there are many applications where I believe that the opposite decision is appropriate.

Helpdesk systems and wiki-type systems are ones where I think it makes a lot of sense to keep the data out of the database. I believe some, like Jira, actually provide an option to choose whether you want to store documents inline or not.

For a medium sized business, storing documents for a ticketing system inline can mean the difference between a compressed backup measured in megabytes, and one measured in gigabytes.

I would personally prefer to bring a ticketing system back online in a few minutes and wrestle with the (generally less important) documents for a few hours, than increase my "it's broken and the CTO is breathing down my neck" RTO by having to restore and replay logs from a much larger backup.

There are other benefits of keeping documents separate.

  • You can easily run separate processes that catalog document metadata, perform virus scanning, perform keyword indexing, etc.
  • You can take advantage of tools to assist with backups or recovery - rsync, storage snapshots, etc. - which lend themselves much better to files than databases
  • You can actually use storage that supports compression or deduplication (the stuff that your SAN admins have been blubbering about for years, aka the bane of database administrators worldwide)
  • For an installation across multiple sites, you can complement a centralised database with a distributed file system

I think a hybrid combination of #2 and #3 might be clever. Keep the original filenames, but calculate and store a hash/checksum of the document, so that you have some reference point that will assist recovery in case someone moves or renames the file.

Storing the files with their original filenames means that applications can literally yank them straight from a file system and send them over the wire, or in a thick client world, maybe even point the user directly to the file server.

share|improve this answer

For postgres:

It's actually straight foreward. There is a BYTEA type that can be used for storing binary strings. Per default, there are no build in utilites like the ones mentioned for MS or Oracle. So storing lots of big files and retrieving them can get tedious. You also need to do the conversion of the files within the application (like with a ByteStream or similar, no idea though how this works with the specific MS/Oracle file<->database solutions). There is also a lo type, that helps with the work of managing BLOBs since some of the internal management of these types may not keep track of the references.

share|improve this answer

My vote would be for neither. Store the data in a system like Amazon S3 or Microsft's CDN and store that URL in the database.

This way you get reliability of having the data always accessible without having monster sized databases to deal with.

share|improve this answer

I want to add my experience here as to the tradeoffs. In PostgreSQL, at least, the performance impacts are quite minimal in terms of the db server. Large blobs are stored in separate files, not in the main heap tables so as to move them out of the way of operations that may count large numbers of records. Other dbs may do something similar.

The major advantage is the ability to keep all related data in one place for atomicity and backup purposes. This greatly reduces the chance of something going wrong.

The major disadvantage is not one I have seen covered above, and that's memory usage on the front-end. I don't know exactly how every db handles this so this may depend on implementation but for PostgreSQL, the data comes in as an escaped ASCII string (possibly hexadecimal, possibly with inlined escapes). This then has to be converted back to binary in the front end. Many frameworks I have seen for doing this involve passing the value (not as a reference) and then constructing a new binary string based on it. I calculated that using Perl to do this ended up using many times the memory of the original binary to accomplish.

Verdict: If the files are only being occasionally accessed I would store in the db. If they are being frequently and repeatedly accessed, at least with PostgreSQL, I think the costs outweight the benefits.

share|improve this answer

Share my experience of Ms SQL server and a huge number of files. We save the files on a file server. Database has two tables, one for the file folders and access credentials, one for the filename. It is easy to maintain the database and files. You can easily move the files even cross the servers, just need to modify the folders table.

share|improve this answer

Don't do it.

There really isn't an upside to having files stored in the database.

Doesn't it already feel weird and fishy when you think to yourself:

Should I store files in a database or a filesystem?

Even better, say it out loud.

On to the facts:

Using the database

"PROS"... but not quite:

  • "Atomicity" which is correct but it's a double edged sword. Because it drags cons along with it.
  • Integrity. Same as above.

I really don't want to be biased but I don't think there's more to add. The pros aren't really that great if you think about it.

If I forgot something comment below, in the meanwhile keep reading below.

CONS:

  • Wrong tool for the job
  • Harder to maintain
  • Slow
  • Forget about storing hundreds of MB/gigabytes of data PER user.
  • Backing up rapidly growing sites will be a nightmare.
  • Restoring/moving will also suck.

Using the filesystem

PROS:

  • Way easier to maintain
  • Fast
  • Database back ups don't have anything to do with this
  • Arguably more portability*

CONS:

  • None*

*Fine print

Right now you're asking yourself, hold on you mean there's no cons?! Howcome?

The biggest mistakes here is that people are trying to screw a screw with a hammer.

The main reason and I'd go as far to say only reason this is being asked is because of file links.

This is a problem that the database isn't meant to solve. It even sounds silly if you think about it.

"The database will fix my file linking problems."

When in reality, logically the application should actually be in charge of handling and serving links.

A Solution:

  1. Make your application handle URL requests with custom routes.
  2. Save this route to your database.
  3. Internally every time this route is called map it to the file you want.
  4. If you ever move your files elsewhere just change the filename value of the route and that route will always serve the same file no matter where it's stored or referenced across the web.

This would also abstract away the native paths, make the application more portable, maintainable and allow to switch to any kind of filesystem without breaking anything.

As for how to implement it is beyond the scope of this answer but you can take a look at a general example in arguably the most widely used web language (PHP):

https://github.com/symfony/Routing

https://github.com/kriswallsmith/assetic

Both of these together are really powerful.

share|improve this answer
1  
have a +1 for being brave enough to state your opinions. –  Max Vernon Jul 31 at 21:53
1  
Really posted this to hopefully have the community involved on a point of view. Instead I got downvoted with no comments :( Thanks by the way. –  Tek Aug 1 at 1:21
1  
Don't let the haters deter you. –  Max Vernon Aug 1 at 2:04
    
You might be interested in this: research.microsoft.com/apps/pubs/default.aspx?id=64525 a research by Microsoft that shows that storing blobs in the database is actually faster than in the file system (for some sizes of blobs at least). This is in line with my tests that showed that for medium sized blobs (< ~1MB) e.g. Postgres is also faster than a filesystem. For Oracle it's about the same performance but I haven't tested the new securefile storage format yet (but they claim it's faster than the old storage format) –  a_horse_with_no_name Aug 1 at 5:56
    
I saw that, which is why I talked about large files. Plus OP didn't specify a database vendor so performance may differ between vendors and thus my advice is more general. –  Tek Aug 1 at 13:37

protected by Paul White Jul 31 at 21:52

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

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