What are the pros and cons of storing files in a database?

开发者_运维问答

I'm writing a php application and was wondering if it's a bad idea to store complete files in the database. Files should be around 100-200kb mostly text files (txt, doc, docx and so on) or small image files. Or is it just a plain wrong idea?

---

Advantages:

1. No need to worry about write permissions on the file store.
2. No need to try and synchronise files on filestore with rows in the database, avoiding orphaned files or broken links. For instance, you can automically cascade delete files when related content is deleted.
3. In certain databases (such as Oracle and SQL Server) you can index files and search within them using SQL
4. No need to worry about unique filenames, folders and it can make uploading simpler in some cases
5. Easier to protect access to files so only authorised users can see them

Disadvantages:

1. Performance of serving files often suffers compared to filestore
2. Can lead to large databases. Care needs to be taken when selecting binary columns.
3. More work to link to files and serve the contents - you need specialised handlers etc.

---

It really depends on the situation?

• How the files going to be distributed?
• Are the files used standalone or are they part of an system which might have its own authorization and authentication logic?
• Whats your backup strategy?
• Do you need replication?
• Do you need to support a lot of I/O?
• What about caching?

Having said that, I would lean toward some kind of filesystem for documents over a database.

---

Pro: highly portable.

Con: you can't do anything with it using SQL (indexing, searching, etc) and you'll need to add metadata in other columns (content type, filename, etc) to improve (re)usability and maintainability.

I wouldn't do that. The disk file system is much better suited system for those tasks.

---

Personally, I like the idea, especially if the DB stores them compressed or you compress them manually.

Among other things, it means you don't need to worry about unique names for files, which saves a lot of complexity.