Let's imagine a web site that is a directory of people. For each person there may be a profile photo and a biography.

I'll admit my SQL queries could be better but in general what would be faster and use less processing power.

To check if a file exists and then open it or

check against MySql to see if a bio exists and display it.

I'm pretty sure in the above case the filesystem will smoke the mysql database.

What if I make the database a read only delimited txt file?

What's faster in this case?

Is there a certain point where if the txt file has too many records it's better to use MySql?

  • 4
    Lets say you have 100K people in your directory and you want the bios of those that were born in 1978. Where do you think the smoke will come from? Opening 100K files in the file system or a single query in SQL? Aug 25, 2012 at 9:28
  • 1
    @ypercube - I agree with you but in case of linux OS there is a limit for opened files simultaneously with each processor. Aug 25, 2012 at 12:02

4 Answers 4


The file system is useful if you are looking for a particular file, as operating systems maintain a sort of index. However, the contents of a txt file won't be indexed, which is one of the main advantages of a database. Another is understanding the relational model, so that data doesn't need to be repeated over and over. Another is understanding types. If you have a txt file, you'll need to parse numbers, dates, etc.

So - the file system might work for you in some cases, but certainly not all.

  • +1, also file systems are not good for partial searches on file names or other attributes. When the number of files is so large you may have an issue finding files this way. Having said that it is common to use file system for data that is not transactional in nature and where the content is always accessed as one unit, such as document attachments and image files.
    – NoChance
    Aug 25, 2012 at 11:30

It really depends on what you are doing. In general the speed at which you can open a file for reading will be better than the speed at which you can establish a network connection. So for very simple operations, the filesystem is definitely faster. Filesystems will probably beat an RDBMS for raw read throughput too since there is less overhead. In fact, if you think about it, the database can never be faster than the filesystem it sits on in terms of raw throughput.

For very complex operations, the filesystem is likely to be very slow. For example:

Read 10 lines out of this 1 billion line file and then search for matching lines in this other file. I pity you if you have to do this. A good database server however has strategies for doing this fast and well so you aren't reinventing the wheel.

Additionally you really need to figure out what you are doing. What data are you storing? How are you going to transform it? If it is 100k image files your solution will look very different than if it is a directory for 100k people. (LDAP maybe? Or an SQL database? Depends on what you are doing, perhaps.) The key here is to pick the tools that match what you are doing and which give you room to add more uses, rather than whatever seems fastest for some rather abstract use case. Databases are wonderful tools, but you can't get a good answer to a question like this.

Finally premature optimization is the root of all evil. Choose useful tools now and figure out the rest later.

  • Of course, if you have two virtual instances communicating over a virtual NIC, or a DB running on the same instance as the application server, if you have a reasonable amount of memory you can make sure a database read is faster than an fs read most of the time, because if you rely on the filesystem you're at the mercy of the fs driver's caching / page replacement algorithm, whereas a database can reserve segments of memory such that they're never swapped out, putting your app's latency needs first. Assuming you have swapping enabled. Feb 11, 2016 at 3:42
  • Your last line boosts me... @Chris Travers Dec 19, 2017 at 6:56

The file system might be faster initially, but I doubt it. However, as your data size increases you will likely have to restructure your file system to maintain performance. Besides their obvious ability to index on multiple attributes, databases tend to scale better.

Web caches which work similarly to what you are considering use directory tree to maintain performance. They also tend to be of a relatively fixed scale, so they don't have to deal with a growing scale.

For this kind of application I would start with a database, as it fits better with your needs. It will scale much better in the long run. Compared to most file systems, a database will also be more space efficient.

  • 4
    Well, that's not a problem. Let's just create another file that lists values and seek offsets. In fact we could optimize this for searching with btrees. Then we know where to read the file! Next up, I suppose we should add a declarative query language to our little program capable of joining results between different delimited files and then maybe ACID compliance.... In time, well, why use an RDBMS at all? ;-) Aug 31, 2012 at 13:03
  • @ChrisTravers Been there, done that, and I am much happier using a database.
    – BillThor
    Sep 2, 2012 at 3:19
  • 5
    the idea was along the lines of "Those who do not learn from UNIX are destined to reinvent it badly." Sep 2, 2012 at 3:23

I always love coming to these forums and reading all the heavy database gurus pitching that the file System can't do it as quick as a database. On quite the contrary, a properly laid out tree, well designed hash tables and saving them as an object to a file will yield the same speeds as a database. And from my tests, a properly designed hash table and directory tree will win every time. Way less overhead.

Recently, I have been moving away from database driven programming and more to the file tree for simplicity and program portability. No DB means easy backup - just zip up your tree and go. It is very nice and a recommendation to program in this fashion for one-time clients with small applications.

Look at the big picture: Do I have the time to design my own or just leverage what is already there, like a DB. I personally like saving my objects to file and using them later - just keep an eye on the size of your tables and look into using a RandomAccessFile in order to be able to seek quickly, lay it out like a database and break it up into hash table objects. Enjoy.

Remember whatever data you store in the file will consume double the memory at times depending on your code, the hash table itself and typically where you consume it to view.

  • 3
    The only appropriate response to this I can think of is this. Mar 8, 2013 at 21:56
  • 3
    @MarkStorey-Smith, that's an interesting link, but is it presumptuous to imply this solution as being on the Dunning-Kruger spectrum somewhere? :)
    – David Mann
    Aug 29, 2013 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.