The requirements for my document management system were:
- Must be secure from theft by simple copying of directories, files etc.
- Must be secure against traditional virus infection (infection of physical file)
- Must be fast to retrieve
- The repository must not be visible to casual (directory) browsing users etc.
I have decided to store all documents (and scanned images) as blobs in the database and so far my experience is wonderful and document retrieval is blindingly fast as well – it meets all the criteria from above and there are even a couple of additional advantages, such as autostoring documents together with the entity it relates to, easy and fast seaching of contents, removing of all sorts of user activities around opening and naming of documents etc. etc.
My question is – are there any serious risks or things that I overlooked with this design and implementation?
EDIT Note: DB is PostgreSQL, handles BLOBS very well and scales exceptionally well. The environment is Multi-user.
Best Answer
When your DB grows bigger and bigger it will become harder to backup. Restoring a backup of a table with over 100 GB of data is not something that makes you happy.
Another thing that get is that all the table management functions get slower and slower as the dataset grows.
But this can be overcome by making your data table just contain 2 fields: ID and BLOB.
Retrieving data (by primary key) will likely only become a problem long after you hit a wall with backing up the dataset.