File Storage for Web Applications: Filesystem vs DB vs NoSQL engines

Question

I have a web application that stores a lot of user generated files. Currently these are all stored on the server filesystem, which has several downsides for me.

• When we move "folders" (as defined by our application) we also have to move the files on disk (although this is more due to strange design decisions on the part of the original developers than a requirement of storing things on the filesystem).
• It's hard to write tests for file system actions; I have a mock filesystem class that logs actions like move, delete etc, without performing them, which more or less does the job, but I don't have 100% confidence in the tests.
• I will be adding some other jobs which need to access the files from other service to perform additional tasks (e.g. indexing in Solr, generating thumbnails, movie format conversion), so I need to get at the files remotely. Doing this over network shares seems dodgy...
• Dealing with permissions on the filesystem as sometimes given us problems in the past, although now that we've moved to a pure Linux environment this should be less of an issue.

So, my main questions are

• What are the downsides of storing files as BLOBs in MySQL?
• Do the same problems exist with NoSQL systems like Cassandra?
• Does anyone have any other suggestions that might be appropriate, e.g. MogileFS, etc?

Answer 1

maybe a hybrid solution.

Use a database to store metadata about each file - and use the file system to actually store the file.

any restructuring of 'folders' could be modelled in the DB and dereferenced from the actual OS location.

Answer 2

If the OS or application doesn't need access to the files, then there's no real need to store the files on the file system. If you want to backup the files at the same time you backup the database, then there's less benefit to storing them outside the database. Therefore, it might be a valid solution to store the files in the database.

An additional downside is that processing files in the db has more overhead than processing files at the file system level. However, as long as the advantages outweigh the downsides, and it seems that it might in your case, you might give it a try.

My main concern would be managing disk storage. As your database files get large, managing your entire database gets more complicated. You don't want to move out of the frying pan and into the fire.

Answer 3

Not a direct answer but some pointers to very interesting and somehow similar questions (yeah, they are about blobs and images but this is IMO comparable).

What are the downsides of storing files as BLOBs in MySQL?

• Storing Images in DB - Yea or Nay?
• Images in database vs file system
• http://stackoverflow.com/search?q=images+database+filesystem

Do the same problems exist with NoSQL systems like Cassandra?

• NoSQL for filesystem storage organization and replication?
• Storing images in NoSQL stores

PS: I don't want to be the killjoy but I don't think that any NoSQL solution is going to solve your problem (NoSQL is just irrelevant for most businesses).

Answer 4

You can store files up to 2GB easily in Cassandra by splitting them into 1MB columns or so. This is pretty common.

You could store it as one big column too, but then you'd have to read the whole thing into memory when accessing it.