Whether to use a file-system or a database to store the data of your application has been a contentious issue since the 80s. It was something we worried about even when I was doing my Ph.D. in Databases in the 90s. Now Jaspreet Singh, of Pune-based startup Druvaa has weighed in on this issue on Druvaa’s blog. His post is republished here with permission.
This topic has been on my plate for some time now. It’s interesting to see how databases have come a long way and have clearly out-shadowed file-systems for storing structured or unstructured information.
Technically, both of them support the basic features necessary for data access. For example both of them ensure –
- Data is managed to ensure its integrity and quality
- Allow shared access by a community of users
- Use of well defined schema for data-access
- Support a query language
But, file-systems seriously lack some of the critical features necessary for managing data. Lets take a look at some of these feature.
Atomic transactions guarantee complete failure or success of an operation. This is especially needed when there is concurrent access to same data-set. This is one of the basic features provided by all databases.
But, most file-systems don’t have this features. Only the lesser known file-systems – Transactional NTFS(TxF), Sun ZFS, Veritas VxFS support this feature. Most of the popular opensource file-systems (including ext3, xfs, reiserfs) are not even POSIX compliant.
Databases allow indexing based on any attribute or data-property (i.e. SQL columns). This helps fast retrieval of data, based on the indexed attribute. This functionality is not offered by most file-systems i.e. you can’t quickly access “all files created after 2PM today”.
The desktop search tools like Google desktop or MAC spotlight offer this functionality. But for this, they have to scan and index the complete file-system and store the information in a internal relational-database.
Snapshot is a point-in-time copy/view of the data. Snapshots are needed for backup applications, which need consistent point-in-time copies of data.
The transactional and journaling capabilities enable most of the databases to offer snapshots without shopping access to the data. Most file-systems however, don’t provide this feature (ZFS and VxFS being only exceptions). The backup softwares have to either depend on running application or underlying storage for snapshots.
Advanced databases like Oracle (and now MySQL) also offer clustering capabilities. The “g” in “Oracle 11g” actually stands for “grid” or clustering capability. MySQL offers shared-nothing clusters using synchronous replication. This helps the databases scale up and support larger & more-fault tolerant production environments.
File systems still don’t support this option 🙁 The only exceptions are Veritas CFS and GFS (Open Source).
Replication is commodity with databases and form the basis for disaster-recovery plans. File-systems still have to evolve to handle it.
Relational View of Data
File systems store files and other objects only as a stream of bytes, and have little or no information about the data stored in the files. Such file systems also provide only a single way of organizing the files, namely via directories and file names. The associated attributes are also limited in number e.g. – type, size, author, creation time etc. This does not help in managing related data, as disparate items do not have any relationships defined.
Databases on the other hand offer easy means to relate stored data. It also offers a flexible query language (SQL) to retrieve the data. For example, it is possible to query a database for “contacts of all persons who live in Acapulco and sent emails yesterday”, but impossible in case of a file system.
File-systems need to evolve and provide capabilities to relate different data-sets. This will help the application writers to make use of native file-system capabilities to relate data. A good effort in this direction was Microsoft WinFS.
The only disadvantage with using the databases as primary storage option, seems to be the additional cost associated. But, I see no reason why file-systems in future will borrow features from databases.
Druvaa inSync uses a proprietary file-system to store and index the backed up data. The meta-data for the file-system is stored in an embedded PostgreSQL database. The database driven model was chosen to store additional identifiers withe each block – size, hash and time. This helps the filesystem to –
- Divide files into variable sized blocks
- Data deduplication – Store single copy of duplicate blocks
- Temporal File-system – Store time information with each block. This enables faster time-based restores.
Related articles by Zemanta
- Cloud Database Standard Needed (continuations.com)
- The Commoditization of Massive Data Analysis (radar.oreilly.com)
- Speeding Up Database Piggies (arnoldit.com)
- Programmers take to the clouds (theregister.co.uk)
- Understanding Data De-duplication (punetech.com)
23 thoughts on “Should you use a file-system or a database”
I think using a mix of both would be right solution. I prefer to use file system for binary data like images, and storing relevant meta-data in the database.
I think file-system is not feasible solution for managing data.While choosing in between file-system and database we should consider two aspects manageability and security.Then third comes to picture ;Performance
Days are gone when DBMS was dependent on underlaying file-system for storing data.With latest technology DBMS are working on raw devices as well.
Generally speaking transactions and / or high speed concurrent random IO needs DB. But DBs sometimes get overused usually around the CLOB / BLOB columns.
Sorry, everyone. This article got posted without any content, and that is how it appeared on the website for the first 1 hour. So the first three comments (@Amit, @Kasim and @Dhananjay) were really responding to just the title of the article (“Should you use a file-system or a database”) without the content. Keep that in mind while reading those comments.
(Hmmm… but given the great comments, maybe I should do this more often – posting just a question and asking people to respond. That would be an interesting experiment.)
@Amit – I would be wary of using a mix especially if you forsee an export/import of the DB to a different host.
Sophisticated (and robust) flat file based data retrieval systems like Berkeley DB have also thrived.
Nice post. Been following this blog and am a good friend of Jaspreet. Just wanted to share some more info about a relatively new class of data management solution which we now call the Enterprise Data Fabric. It is essentially an in memory distributed transactional data management solution used for high performance computing. The company I work for, GemStone http://www.gemstone.com, has a product called Gemfire which is in that space. The technology has been adopted by all major financial institutions in the world and is taking over active data management from the traditional DBs and Files. Let me not do too much marketing here :-), anyone interested in knowing more can visit the website.
My apologies, but this seems to be apples to oranges comparison.
File systems and DB are designed to address completely different needs. There are some situations where either could be used, but when you compare the two on parameters like ease of use, performance, etc the answer gets more obvious.
In my previous company, we crawled a few hundred million webpages. Here DB and FS both are unacceptable solutions. (Thought exercise: How will you store a billion web pages?) Right at the start we had to create proprietary solution for this.
A similar debate goes for full-text indexing engine (Lucene, etc) vs DB. Yes, you could store data with indexing engine and do full text search with DB, but both are lousy with non-core features.
Simply put, they may borrow features from each other, but they should stick to what they do best.
@Shashi, the question starts making more sense if we reframe it as: “When I have some data to store, what are the conditions under which I choose to store it in a file system, and when should I use a database, or SimpleDB, or BigTable, or a full text indexing engine like lucene, or a special purpose database like Gemfire or Vertica.” Given the number of options (and a lot of marketing FUD) the answers are no longer obvious.
Hence, I was hoping that someone here as looked at these options long enough to be able to write a nice article giving the various alternatives and the pros and cons of each.
Interesting question. Not claiming that this is the answer, just that it might be of interest in this context:
Actually, this is no longer a two-horse race.
A third option appears to have emerged thanks to alternatives like Google’s BigTable and Amazon’s SimpleDB. These essentially provide much lower functionality and features than a standard database, but make up for it in terms of scalability, and for some cases, ease of use.
Wonder if someone here has enough expertise to write an article on the various options available in the “not-quite-database” space, the pros and cons, and which one is right for me.
I think the most dominant demand in the market is for black and white colours. However there is a ton of niche demand for all the colours in-between.
I think arguments which suggest filesystems should be more db like or vice versa probably miss the point that these are serving their entrenched markets quite well. Its the new and refining markets (eg elastic cloud architectures) which are requring some new feature portfolios to be assembled which are leading to all the newer colours. This is a many many horse race with two very dominant horses.
@mitul what you refer to has been around for decades especially in telecom. There are very good reasons why such solutions exist but I don’t quite subscribe to the attribute “taking over active data management” – if true that can only be in specific niches.
@unmesh, @shashi, concur with your thoughts.
@Dhananjay: Agreed. They may have been there for over a decade agreed but they are now becoming mainstreaming. Earlier they were custom built and now we have enterprise products. For my reference, can you please point me the solution you used (or know) in Telecom? “For active data management”, you are right in niche areas where performace is absolutely critical. Also, I was not implying GemFire necessarily but the new class of memory based distributed data management solution. One of the fathers of the RDBMS technology, Stonebraker, also thinks the classical DBs have come of age and is himself working on a project at MIT called H-Store : http://db.cs.yale.edu/hstore/
@navin: I can help give my two cents on distributed data management technologies available with limited insights into BigTable, Hadoop, Gemfire and its competitors. Though my views will always be more biased to GemFire 🙂
@Dhananjay, Agreed that doing a good in-depth analysis is easily a one-semester course! However, I think there is still value in giving a 30,000-foot view of this area. I mean one paragraph on each of the major approaches that appear to be emerging.
@Mitul, what you are describing sounds fine – can you write an article for PuneTech on that topic?
@navin, thats seems to me too onerous a task (would love to be proven wrong) eg. just look at the wikipedia page which compares only the file systems – http://en.wikipedia.org/wiki/Comparison_of_file_systems
However I would recommend someone seriously interested in understanding the various options spend about 3-4 hrs on wikipedia to get a good handle on the various options.
There is a dark side to the cloud as well. We investigated a lot in this space. And I plan to write on this soon.
My reasons –
1. Its something which is critical to your business and you can’t control – I know 3 startups which went out of business because of cloud downtimes and data losses.
Unlike hosted email, which is there for the past 20 years. This space would take a long time to mature. Besides,no one minds if the email server goes down for some time.
2. Applications need to be changed to adjust to cloud API.
3. Performance – Its a big question here.
4. Its a vendor lockin. You can’t change options tomorrow.
We investigated on a lot of options and benchmarked – SQLite, MSSQL, MySQL, PostgreSQL and Oracle. Then finally settled on PostgreSQL. Will share this investigation sometime.
@sashi, look at the disclosure.
Good article, always nice to refresh oneself on the “classic” db terms.
I’d like to add my two cents to the discussion.
The author mentioned Replication as a means to have disaster recovery plans, but replication can and more often does go beyond it. Replication is a very essential indgredient for managing distributed databases. DoubleD’s are common now a days and file system db’s will find it hard to match this requirement.
I wonder how XML’s would be rated then? Just another means of EDI?
@mitul, you are right these solutions have been proprietary for long (hence no links to them). A distributed, transactional, in-memory caching solution I know has been around since 2004 at least is Tangosol. An open source solution I am aware of which is moving parts of data management from the database into a distributed in memory storage is TerraCotta. These may not serve exactly the same set of purposes as GemFire would, but the general class of distributed transactional in memory storage has been active for some time.
Shall look forward to your post.
@Dhananjay: GemFire has been there for more than 6 years as well and Tangosol’s Coherence is our direct competitor. By the way, Tangosol was acquired by Oracle and it is now Oracle Coherence. You are right about TerraCotta, though it is more like a shared JVM as opposed to a typical distributed data management solution but definitely in a similar space.
@Navin: I sent a mail on your smriti.com mail id. Did you get it?
Most of the applications tend to use both file system as well as database system for various reasons. It is equally important to abstract operations. Consider using something similar to Apache Jackrabbit (JCR implementation); we had excellent results while accessing half-GB size images (on file system) along with typical application data from relational databases thru JCR. Just for clarification, we were not storing webpages; we were storing typical application data.
I like the articles as well as every comments. I want to clarify that i myself is an engineering student and i want to know the pros and cons of flat file system and database system . Bcoz this article don’t clear the pros and cons . So if somebody reading this blog know the answer plz write an article on this topic .
Hi Navin and Jaspreet.. Nice article.. Quite informative ..
But, at the same time, I sense that you have missed out on the complete holistic picture from the FS perspective. Just adding my bits on it.
Lets have a look on the features on which you have compared FS and DB:
1. Transaction Support: As you yourself said that ZFS, VxFS, AdVFS are the industry standards which support this feature very well.
2. Snapshots: For a FS, it is mostly expected from the backup application running on top of it to do the snapshotting. At the same time, VxFS, AdVFS etc have taken it to the FS level.
3. Clustering: You have yourself given the examples of FS that support it.
How about the different RAID levels.?
PS: Navin can you please explain the motivation behind trying to compare FS and Db when they have been exiting for serving altogether a different organization of data.
Just trying to understand if I am missing on some trivial point. Or, are you just concentrating on the limitation of most of the opensource OS. Check for AdVFS (tru64 UNIX) which is too opensource.
And what are we trying to build actually siting the limitations of FS ?
As I said in an earlier comment, the question to be answered is this: “When I have some data to store, what are the conditions under which I choose to store it in a file system, and when should I use a database”. Now file-systems and databases start looking comparable. There are a very large number of applications that don’t really need all the complexity, and overheads of a database, but use databases instead of file-systems because they need one feature (maybe indexing, or maybe transactions) that is missing from their file-system.
Why don’t they use ZFS, VxFS etc? My guess is this: MySQL, Oracle, or Sybase will give me a way to get the feature I want without forcing the hardware/OS on me, or without forcing my customers’ admins to learn an obscure filesystem (come on, VxFS _is_ obscure compared to the databases I listed). Also, I can get the features I need from the database in a “standard” way, where as in a file-system I’m stuck with proprietary interfaces for getting any of these features.
I think what Jaspreet is arguing for (and others have been saying since early 80s) is that having a “standard” way to do some of these things in file-systems will be a much better solution for many app vendors than being forced to use databases.