patternMinor
Are there major drawbacks/risk of using oracle blob to store files
Viewed 0 times
oraclemajorriskarestorefilesusingthereblobdrawbacks
Problem
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:
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.
Solution
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:
Cons of in database file storage:
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.
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.
Context
StackExchange Database Administrators Q#43358, answer score: 6
Revisions (0)
No revisions yet.