Should I store files in BLOBs?

[Vaalen]

Hi,

Is it a wise decision to store files in blobs, or would it be wiser to leave the files somewhere on disk and just point to the location?

We are building an application where users can add files to several milestones during the lifetime of an order. This can be .pdfs, outlook messages, excel files, whatever.

I would be interested in your experience with this.

Thanks in advance.

 

[medu]

Unless you have an all-purpose document management solution in place i'll put them in the database to avoid separate back-up/restore plans and have fun with file permissions and everything. If you do have a document management system then store the files in it and save the reference, you'll need a way to retrieve the file from it later on given the reference.

 

[Vaalen]

Thanks Medu.

We do not have any document management system. The only worry would be the database growing from 'all' the files included. My guess is that the documents itself will be rather small.

Any experience with this?

 

[MaximMonin]

It takes few second to save 5 MB document file as blob with copy-lob command. And even less to extract.

Create db segment for this table to controll its size and perform calculation to estimate db segment grow.

 

[medu]

Well, what's the difference if you put them in database or simply on the file system? It will eat up space no matter what and unless you start adding indexes to the file storage table the overhead in terms of space will be unnoticeable, you'll probably have a guid field as primary key and the blob content.

If space is a concern you can look into compressing the files before storing them, you'll have to decompress when you need them... there was a thread on PEG about database compression and probably Tom is right, the bottleneck is on IO operation most of the time not CPU.

Storing them on the database gives you an option later on to index those file content and provide search facilities for those files that makes sense indexing (not the binary ones anyway).