Drawbacks to having (potentially) thousands of directories in a server instead of a database?

Question

I'm trying to start using plain text files to store data on a server, rather than storing them all in a big MySQL database. The problem is that I would likely be generating thousands of folders and hundreds of thousands of files (if I ever have to scale). What are the problems with doing this? Does it get really slow? Is it about the same performance as using a Database?

What I mean: Instead of having a database that stores a blog table, then has a row that contains "author", "message" and "date" I would instead have: A folder for the specific post, then *.txt files inside that folder than has "author", "message" and "date" stored in them.

Answer 1

This would be immensely slower reading than a database (file writes all happen at about the same speed--you can't store a write in memory).

Databases are optimized and meant to handle such large amounts of structured data. File systems are not. It would be a mistake to try to replicate a database with a file system. After all, you can index your database columns, but it's tough to index the file system without another tool.

Databases are built for rapid data access and retrieval. File systems are built for data storage. Use the right tool for the job. In this case, it's absolutely a database.

That being said, if you want to create HTML files for the posts and then store those locales in a DB so that you can easily get to them, then that's definitely a good solution (a la Movable Type).

But if you store these things on a file system, how can you find out your latest post? Most prolific author? Most controversial author? All of those things are trivial with a database, and very hard with a file system. Stick with the database, you'll be glad you did.

Answer 2

It is really depends:

• What is file size
• What durability requirements do you have?
• How many updates do you perform?
• What is file system?

It is not obvious that MySQL would be faster:

I did once such comparison for small object in order to use it as sessions storage for CppCMS. With one index (Key Only) and Two indexes (primary key and secondary timeout).

File System:   XFS     ext3 
-----------------------------
Writes/s:      322     20,000

Data Base \  Indexes:    Key Only   Key+Timeout
-----------------------------------------------
Berkeley DB              34,400      1,450
Sqlite No Sync            4,600      3,400
Sqlite Delayed Commit    20,800     11,700

As you can see, with simple Ext3 file system was faster or as fast as Sqlite3 for storing data because it does not give you (D) of ACID.

On the other hand... DB gives you many, many important features you probably need, so I would not recommend using files as storage unless you really need it.

Remember, DB is not always the bottle neck of the system