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.

Recently I was asked to do production support on an existing software.

In summary, this system is a web service that allows you to upload a file and provides you with an identifier so that you can ask for your file later.

My concern is that the files are stored as BLOB in an Oracle database.

For the moment, the system is stable, but in the near future our business wants to upload 100.000 to 200.000 PDF per year (10 to 50 Mb each).

When it comes to the Database

  • Should I just care about the disk space?

  • Are there other aspects I should worry about?

  • Or should I really try to switch to file system storage?

A few additional information:

  • There is no need for transactions.
  • All files are unrelated.
  • Files will never be updated.
  • Files will stay there "forever" (5 to 10 years).
  • The software needs to be updated for other reasons anyway.
share|improve this question
1  
Do you need transactional support for this blobs? –  edze May 29 '13 at 19:32
1  
If there are any advantages of storing files outside Oracle, they are probably not sufficient to justify modifying a working application. –  mustaccio May 29 '13 at 20:49
    
The disk space won't be that different between a BLOB in Oracle and the same data stored as a file. –  a_horse_with_no_name May 30 '13 at 6:51
1  
If there is no need for transactional support then let your filesystem do the job it was build for. Additionally you add a meta information file if you need to handle inconsistency later. The major drawback with blobs is a significant increase of your database backup/restore time. –  edze May 31 '13 at 8:01

2 Answers 2

up vote 5 down vote accepted

It really comes down to cost vs. importance. The more important the files are, the more beneficial storing them in a database becomes. Here are some things to consider.

Pros of database file storage:

  • Security - The same security model your data has, same users, etc.
  • Backups - Usually the database is backed up more frequently, tested more frequently, and has more thorough recovery documentation. Backups can also be faster with block change tracking.
  • Recovery - Old versions of files can be restored to any point in time as far back as the backups are kept.
  • Auditing Available.
  • Scalable - The more files you have the more important this becomes.
  • Transactional integration with other data and code.
  • Flashback Query (for Oracle)
  • Locking Available.
  • Read consistency.

Cons of in database file storage:

  • Initial setup may take longer.
  • Specific expertise required.

Tom Kyte said it this way:

If this data is valuable to your business, if this data is necessary for your business, if the loss of this data would damage your ability to do business - it has no business NOT being in the database.

share|improve this answer

I would say that your major concerns are disk space and recovery time. Do you have a requirement to keep full history or are old files removed at some point? Are all files read-write or can old files be read-only? I would partition the table by date (Enterprise Edition required), assign each partition to it's own tablespace and set the tablespaces to read-only when appropriate.

This might be a good fit for 11g:s SecureFiles feature (might require Advanced Compression option), but i have not used it myself.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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