Why use a database instead of just saving your data to disk?
programmers.stackexchange.com
programmers.stackexchange.com
1. Your FS has a minimum blocksize, often somewhere around 4k, you'll waste disk space with small files
2. Your OS MMAPs/FS caches pages with its page size, ~4-8k, you'll waste ram with small files. (MongoDB also makes this mistake)
3. Reading/writing data from files directly requires syscalls, a DB can cache a lot in memory and minimize syscalls.
4. Atomic transactions are hard, even with a database. Are you actually sure you know how fsync works? (see the famous ext4 fsync issue: http://blogs.gnome.org/alexl/2009/03/16/ext4-vs-fsync-my-tak...)
5. Distributed operation is hard, take a look at a databases like couchbase, elasticsearch or S3, where data is replicated across machines and datacenters in a highly reliable way
6. Consistent backups on a live app are hard. Using postgres or another MVCC DB? Just perform a dump, it will be a true snapshot.
7. Do you need to coordinate writes across multiple app instances (E.G. every web-app ever), you'll have to figure out your own record locking system. Need a high-performance counter atomically incrementable from multiple clients? Easy in SQL (UPDATE foo SET counter=counter+1 WHERE id=1), try doing that in a simple way with plain-text files.
The list just goes on and on and on. The kicker is even if you understand these issues, it's hard enough building a non-buggy implementation of these basics. Oh, and lastly, even if you do build your own mediocre DB, future devs will have to learn your crazy system rather than leveraging all the existing SQL knowledge they currently have.
Super important point that sometimes trumps everything else when considering any new technology that people often forget. Some of the first servers that I purchased were SGI running Irix. Turns out that the person who advised that didn't consider the fact that while the Sun's weren't "as good" he also didn't consider that there were so many more packages available on Sun's as well as sysadmin's as well as information (at that time) on the net.
What I've found over time is that unless there is a compelling reason to do otherwise you go with the ubiquitous solution to a problem.
Back in the day: Or the solution that there was an O'Reilly book on or a row of books on the bookshelf explaining (when that is essentially the knowledge base).
1-2 scenario: many many small files.
3: continuously accessing your data
4: concurrent connections
5: distributed application
6: never-stopping application
7: concurrent writes to mutable data
You may need a DB, if you want several of these to happen. This might be true for many applications, but for many applications this is equally not true.
Most often than youd' expect, using a DB makes understanding an application _harder_ because the data in the application tends to get structured as in the database: only ids, ints, strings and maybe date, no graphs or algebraic data types such as sum types, optional types, etc.
Having said that, your last point is 100% valid. Don't reinvent the wheel. SQL is good for most things. Even SQLite is super awesome for slightly busy websites(depending on how you use it).
1. What about a database saves you disk space? Whatever it is, the filesystem can do the same thing. Perhaps you had a preconceived notion of a specific filesystem and OS in mind when you wrote this.
2. Your database MMAPs/caches pages with its custom page buffer too, and that wastes ram with small records.
3. Reading/writing data from a database requires syscalls too. An OS can cache a lot in memory and minimize disk writes. Arguably the database is duplicating the caching logic of the OS and slows things down.
4. Filesystems have atomic transactions too. You can use the flock call to lock files, and rename is atomic. It's a rather befitting hack for HackerNews which "atomically" renames a file to update it.
5, 6 and 7: You can get distribution, replication, fault-tolerance, availability, and snapshots for free out of a filesystem like GlusterFS too. (Also note SQL is crap; it's a bad serialization of a data structure.)
I wonder if the real reason we have better databases than better filesystems is people didn't want to program for the kernel, which is where filesystems had typically ran.
People not grasping you can store data on a filesystem is probably one of the most valuable things a new programming language would have going for it. It would be a staggering reduction in complexity if a language made data manipulation easy, perhaps at the dismay of programmers stripped of the amusing complexity of using this separate thing called a database.
> Reading/writing data from a database requires syscalls too
Not for stuff that's in the DB's page cache.
> Arguably the database is duplicating the caching logic of the OS and slows things down.
That's a pretty biased assessment. A 'real' database like Postgres will use calls that bypass the OS page cache, so there's no duplication there. It also understands its usage patterns better, so it can do generally better than a general-purpose cache.
> and rename is atomic
Assuming your file system doesn't atomically give you a truncated file post-system-crash :-).
> (Also note SQL is crap; it's a bad serialization of a data structure.)
Agreed that SQL is not the best language, but is this an argument against set-based query languages, or just SQL specifically?
Databases make some things that are really hard less hard. Managing concurrent read/writes over sets of files (say) is currently difficult. Sure, your FS could have deadlock detection, MVCC and so on built into it, but that would be turning the file system into even more of a database system than it already is. Database systems are complicated because people often want to do complicated things.
> Not for stuff that's in the DB's page cache.
The database has to log what was written. It does use syscalls to write the write-ahead log. Agreed it can save read syscalls. Then again one process sending a query to another process to read something generates even more syscalls than having everything (app+db) all in the same process.
You are right about 2. I'm playing devils advocate here jumping around assumptions. I could ask why you assume an OS will use a page per file, you can say Linux does this, I can say why is your OS so heavy and point to Exokernel, you can say that's side-stepping the issue. I could say user-space filesystem having its own page cache; in the end we are talking about the same thing. In the end: the less layers the better.
When I look at the kernel I see something like the Berlin wall. A barrier that was there since you were born and which you never asked for. A kernel being hard to hack and monolithic is bound to push developers away, but it won't stop developers from building what they want in the end.
So, you mean, like a query language?
"perhaps at the dismay of programmers stripped of the amusing complexity of using this separate thing called a database."
Or facing the prospect of learning another database system, and unsure what the gain is.
"1. You can query data in a database (ask it questions)." If you use your file system as a "database" you can ask it questions too with file paths
"2. You can look up data from a database relatively rapidly." Given that lines of text can match some problems quite well, a text file can be MUCH faster.
"3. You can relate data from two different tables together using JOINs." Denormalization makes this unnecessary.
"4. You can create meaningful reports from data in a database." I work as a data analyst and make meaningful reports from event logs all the time. To be fair, I do prefer them being in a DB usually. ;)
"5. Your data has a built-in structure to it." Just because it has a structure doesn't mean an RDBMS is the right place for it to be codified. Also let's be clear the poster of this answer is clearly referring to an RDBMS.
"6. Information of a given type is always stored only once." It's important to note, this only matters if your data is not immutable. There are many applications where this is the case, but also many where it is not.
"7. Databases are ACID." Meh. This matters but after the advent of NoSQL era I now realize this matters much less.
"8. Databases are fault-tolerant." There are tradeoffs made in this name as well. Sometimes you can do better by letting your app be fault tolerant of your data instead.
"9. Databases can handle very large data sets." Files on disk can't? No I think here a valid point would be a database can span multiple disks and make them appear cohesive. That's a very nice bonus. For 95% (worst case) of the world though, that's not a concern.
"10. Databases are concurrent; multiple users can use them at the same time without corrupting the data." Again there are tradeoffs made in the name of this in an RDBMS (like locking). Reading immutable data scales across multiple users just fine.
"11. Databases scale well." CDNs have shown that files can scale even better. Again immutability is a constraint but I think it's an interesting constraint to explore for many systems.
Just to be clear I'm not saying databases are usually the wrong choice. Not at all. Instead they're just all too often the wrong first choice. Start with files, iterate from there.
After the advent of NoSQL, ACID matters not much less, but in fact a lot more. Startups lose data all around the world because their shiny new database doesn't support the basic principles of databases.
Startups have been losing data all around the world with SQL databases as well e.g. Dribbble.
The implementation of ACID is just as important as the concept itself.
When dealing with crucial data where robustness and correctness are paramount, an RDBMS may not be a bad fit. I cringe at the thought of an insurance claims system being done in NoSQL or JSON instead of a more robust system. Things are probably different in the startup world, but I'd rather take a huge hit to performance than a multi-million fine and audit for losing records from my DB.
"If you use your file system as a "database" you can ask it questions too with file paths"
You'll be much more limited in the kind of queries you can reasonably make. You'll have to decide on them ahead of time. This might be what you want, but it might not.
"Denormalization makes this unnecessary."
Denormalization means making your data match your application rather than the other way around. You can make stuff faster this way sometimes, but again, you're limiting the kinds of queries and access patterns you can make in the future.
"It's important to note, this only matters if your data is not immutable. There are many applications where this is the case, but also many where it is not."
Wouldn't a flat file which continually accrued immutable data technically be called a log? You can do analysis on flat log files, but it will get slow if your files are sufficiently large. Indexing can help here.
"CDNs have shown that files can scale even better. Again immutability is a constraint but I think it's an interesting constraint to explore for many systems."
What? CDNs improve performance by placing the point of origin for data physically closer to the client, thus reducing lag. What does this have to do with the difference between files and databases? Do you mean that static or cached webpages serve faster and use fewer resources than ones generated from sql queries at the time of request? Duh, but that doesn't really contribute to the discussion.
The idea that storing often requested data in something other than a database and that being a simpler solution than tuning the database was a revelation to me at the time when I learned this. The overarching point is a database is not a panacea for data storage.
I agree so much with this. I run a small service that looks like it would be a perfect use-case for a database. Even though all the data is beautifully normalized and instantly searchable, using a database directly would be impossible. Instead it starts as a series of flat text files. From these flat text files, the database is automatically generated. Three big gains here:
Version control on the text files works perfectly as expected.
Creating ad-hoc and one-off columns (while figuring out how to best normalize unfamiliar data) is trivial and painless.
Comments! It is so easy to write in-line notes. This alone is the killer feature of text files. Normalizing and curating data requires making a lot of choices and comments let me keep track of these.
Of course there are downsides. You must edit the text file to add data. (With some exceptions, such as updating live prices/inventory.) If you have equal numbers of writes and reads, this system is not for you. But if you have infrequent (daily) writes then it is no problem.
Other problems including loosing data if your application crashes and isn't able save it's data, performance on very large datasets, lack of concurrent access, etc
Generally, flat files are fine for low availability applications with small datasets. As your application grows however, you generally need to find more robust methods of persisting your application. Picking a persistence engine/database which matches you application will be essential at some point. It need not be a RDBMs, as 'NoSQL' databases and object databases may fit your application better.
One more thought: A lot of the fuss about 'NoSQL' vs RDBMs come from people trying to treat RDBMs strictly as persistence engines. That is, they start with an application that run well in memory and then realize that they need to deal with one or more of the issues I raised above. They pull out a RDBM and are annoyed that they now have map the data from the perfectly fine datastructures they were already using to a normalized tabular structure.
The reason this happens is because RDBMs were created to solve a different class of problems than simple persistence. They are for when you are starting out with data that needs to be housed and organized, as opposed to an application that simply needs to save its data.
The disk paging system is but one of the components of a database, but what makes a database a database is the leverage that you get over your data, versus just a blob on disk.
As far as I know, no, all of them are lazy and load only what you query into the memory. But if your data fits in memory, it'll stay there, and subsequent queries will be faster.
Also, you can take the quotation marks from database, you have some quite generic requisites, most of them implement all requisits, except for the caching at startup one.
The meat, on the implmentation of ACID in sqlite3, is around 14:20 in. More complexity than you want to think about is the answer. You could do the same without a db-as-such of course.
Anyone remember the talk I mean? It would have been around the same time - 2009ish.
It's by Stewart Smith, it's called "Eat My Data: How Everybody gets File I/O wrong", and it's well worth watching: http://mirror.linux.org.au/pub/linux.conf.au/2007/video/talk...
Seems like that's the kind of thing which is very easy with a database and much less so with flat files.
You could argue that this is another plus for flat files; you have a better appreciation for what operations are hard work, whereas a 'simple' one line select/join can hide a huge performance blocker...
There are many places where you want ad-hoc databases. You better bet on it being read-only or append-only, but in practice it is often so anyway.
There is a lot of benefit to processing your data using a declarative language, which is what SQL is. It's basically prolog, with less recursion and more separation of code and data.
You get all of that power for all of the data manipulation code that you run inside the database. If you have the misfortune to be writing in VB or Java, that's a huge power-up that you gain by using an RDBMS.
But pg is already using a declarative language, because he programs in Lisp. So the advantages are fewer in his case.
So I doubt pg feels significantly empowered by an RDBMS.
In the example you gave you shouldn't have problems if you put the right index on the table.
What really causes the opacity is either (a) the complexity of the query or (b) the guy writing the clearly doesn't know how to write declarative code, so he tries to write eg procedural code in SQL, with cursors and triggers and other horrors.
If you are in scenario (a) and you really do need to do something complex, I'd pick SQL over VB to do it in any day - the non-declarative style leads to huge code with side effects are more opportunities for bugs to creep in.
If you are in scenario (b), the guy writing the queries really needs to read this: http://www.amazon.com/Database-Management-Systems-Raghu-Rama...
Agreed that the distance between Lisp and SQL is a lot shorter so an RDMBS doesn't gain you very much. In functional languages, it's normal to write filters and apply functions to lists. You also end up performing your joins manually, and end up "optimizing the query" as you code.
In SQL, filters and apply (update) are easier to write, but it's also easy to perform a join that is slow.
Look at the different forms of relational calculus (SQL is intended to be basically syntactic sugar on top) or look at some research papers by Fariba Sadri - hell, look at the earliest papers on relational databases by EF Codd. RDBMSs were meant to be logic systems right from the start.
it's also easy to perform a join that is slow
Have you been using MySQL? Use a real database with a working query optimizer eg I know from experience that MS SQL will do its best even when you give it a dog of a query. For example it will re-arrange your "filter" and build a temporary index to make your join go at the fastest possible speed.
If you have a specific use case nailed down you can always make it go faster outside of an RDBMS much the same way that you can always make something go faster if you hand-code it in machine code instead of using a compiler.
But if you can't put that much effort into hand-optimizing things, say because your use case might change so you'd have to change the code, the query optimizer/compiler will do a better job.
And Lisp as a declarative language?
SQL being basically Prolog?
Yep, CRD instead of CRUD. 'U' would be a recipe for disaster.
In it's simplest, this is why NoSQL was created, right?
Formats are straight forward these days. Just write out JSON, XML, HTML5 w/ microformats or even Yaml. The art is in the "schema" or how you store the files. Choose wisely with some knowledge of your requirements and it seems like it can work well.
Files on disk mean you can use all your great UNIX tooling to do all sorts of complex operations that would take you many many hours of skilled development to do with a database. Then there are all the possible things you could do with Git or (imagine!) ZFS! Versioning everything for free? Keep an activity log within a hierarchy. What kind of cool stuff can you do with that? I aim to find out.
And when my data grows to multiple terabytes in size?
If all you need is storage and retrieval.
Take storing project management data as JSON files.
Suddenly the boss bursts in: he needs to know, right now, how many hours Jeremy has spent working on these three projects.
Given that your JSON hierarchy runs project->person and not person->project, you will now have to traverse your entire database to give an answer.
The deep magic of relational algebra or relational calculus is that they privilege no one view of the data over the other.
This is, incidentally, one of the causes of OO/relational mismatch. A class hierarchy locks in a particular view of the problem domain. Relational has many potential views of the problem domain.
The cost and inconvenience of traversing trees for ad hoc queries is one of the big reasons that RDBMSes swept away network and hierarchical databases.
If you are storing a dataset thats write once, read many, then not using a database is great and you're likely to save a lot of work efficiency. However, if you end up doing complex things with your data, its probably not that great to re-invent the wheel. You should also look at the cost of training other people to work around your storage system, if its not actually simple, you waste a lot of time getting people up to speed.
Databases haven't been incredibly complex for years. Anyone can be up and running with MariaDB or psql or even mongodb in literally minutes. Actually using them is absolutely no more difficult than file system operations.
Worse still, avoiding such non-complexity early on almost certain dooms you to serious complexity later on, because soon enough silly things like concurrency pop up -- the sorts of issues that databases solved decades ago.
RDBMSes are like that because in return they make certain guarantees about their behaviour. Most of the time, the gratification from those guarantees is highly delayed and so, humans being humans, we heavily discount their value.
There's a lot less potential pitfalls to using something that has a big community behind it, than a roll-your-own-flat-files-system.
I also happen to think MySQL-backed websites are the bane of the internet, further testament to the pro-db bias of the average web developer.
A properly built sql store (or, increasingly, nosql) is definitely a necessity for large service. But what is "large"?
I once heard the rule of thumb that a db is necessary when the size of the data you store in it becomes larger than the size of the db code. I kind of like it.
In my opinion, they would have been better of with a database, or a more clever design like yours probably is.
A lot of my first work was rewriting PHP3 sites that used text-files to PHP4 with MySQL. Some of them had 200mb articles in text-files, and for every request they parsed this file, and didn't know why it was slow.
If the question is "why use a database", don't you expect pro-DB answers?
Do you mean that dir entry lookup is say O(10,000) when you have 10,000 files in a single dir? If so then I imagine that is the reason that git has a hierarchy, i.e. look in .git/objects/[0-f][0-f]/.
Databases are most valuable when there's more than one user at the same time, doing UN-anticipated things, involving multiple resources, whose uses may conflict with each other.
Databases provide certain services that allow things to run smoothly as concurrency, amount of data, types of data, and unanticipated usage begins to scale up. In exchange, databases impose a tax that includes extra complexity, increased latency, reduced throughput, potential license fees and general bother.
For coders who are just beginning a project, who are short of time and don't feel comfortable with a database, I see no good reason to insist on immediate database use.
If their project scales to many users, a lot of data, and lots of unanticipated queries WITHOUT using any kind of database, the coders would have made a HUGELY interesting discovery - at least as interesting as map-reduce.
A small business has grown up using nothing but files. These files store large blocks of text which cannot be altered, just meta data pulled out (they're flat files, between 10 KB and 5 MB each, which is basically like dumping a large char[] arrays into files, and unfortunately they need to be kept in this format because the formats vary/incompatibilities/flexibility).
Anyway... Basically they have 30+ GB of these flat files. Literally millions of them. All in *.dat files. It works quite well like that. But the downsides as discussed in the link crop up and cause problems.
So we tried to put them into a few SQL databases as LONGBLOB, and after the first couple of gig the thing just became unmanageably slow, badly formed queries would cause it to lock up, and the thing took up way more space on the file-system than files did (and was WAY harder to manage with the performance issues discussed).
So I guess my point is, SQL databases are great for sorted data. They're freaking nightmares for CDATA/BLOBs. Just a complete waste of space. Almost no database is designed to handle that amount of data and continue to work.
But in general even key-value stores are designed to store sorted/structured data. Not really massive BLOBs with a little meta data sprinkled in to explain it.
You just run into serious performance issues when you try and store/access >2 GB of "junk" data via some clever database management engine, which is really more accustomed to storing tiny little columns of explain-able data you can write clever queries to.
We've wound up in a situation now where we just use databases to store the meta-data and have left the actual data in files, which from a programming point of view makes things harder (since you're hooking into two methods of data access as opposed to one).
http://onabai.wordpress.com/2012/05/08/working-with-attachme...
Safari has similar issues. In particular the favicon SQLite database is a common source of slowness - see http://www.chriswrites.com/2012/05/how-to-delete-favicon-cac...
I don't know if these are inappropriate uses of databases, or if these databases could perform well, but are misconfigured in some way. But I have seen SQLite cause performance problems on desktop apps so often that I think there must be something hard about it, that's not hard about other data storage mechanisms.
Disk I/O is slow, so any kind of disk storage will be slow. I'm sure storing web browser's data in some kind of ad-hoc files would be much slower than SQLite. And the fsync() issues are orthogonal to using flat files/DB.
One reason is that SQLite does not use optimal disk access patterns. I know this because I have watched it issue nothing but preads and pwrites for literally hours, on a database that was about 2 GB. This is obviously terribly pathological behavior, and it was in shipping products. (incrVacuumStep is the bane of my existence.)
The second reason is that SQLite provides strong data integrity guarantees by default, at the cost of performance. This has proven to be a bad tradeoff for many applications, including Firefox.
> And the fsync() issues are orthogonal to using flat files/DB
They are not orthogonal, because SQLite calls fsync a lot by default, and it takes work to understand what you're doing that causes it, or even to disable it. See https://bugzilla.mozilla.org/show_bug.cgi?id=421482 for some of the pain this caused.
Notice some of the timing differences - one user reported that disabling SQLite async IO reduced his shutdown times from 1m40s to 5 seconds. This puts the lie to your "any kind of disk storage will be slow" claim: disk access patterns can have enormous impact, and empirically, it's easy to use SQLite in a way that destroys your performance.
There's a bias in favor of databases in the comments here and in the StackOverflow page, which isn't properly tempered by negative experiences. I also see a lot of misinformation, for example, "A database is needed if you have multiple processes modifying the data."
I have seen some desktop apps with terrible performance, that issue disproportionately huge amounts of disk IO, and where backtraces show SQLite functions. I conclude that using SQLite correctly in a desktop app is harder that people say. (Other RDBMSs aren't really in the running on the desktop.)
I have also seen a lot of dog-slow WordPress sites, including my own. Wouldn't you agree that many of them would be faster and more robust if they used a flat-file generator like Jekyll? Mine sure was!
So if databases are overused, what is the explanation? Well, look at the top voted reply: use a database and now your project is fast, ACID, fault-tolerant, can handle very large data sets, is concurrent, and can scale well! And when should you use files? The second reply answers that: it's when you "don't care about scalability or reliability."
If you believe these things, it will lead you to use databases in inappropriate contexts, and make the mistake of thinking that now your I/O performance is good. If anything, it's the opposite: databases abstract I/O, so properly evaluating performance means you need to work harder to understand what's really going on.
Unfortunately, when you don't know what you're doing with them, they're basically a black box where magic happens, and you don't know how to fix any problems that occur. The thing is, that applies to many things. When you don't know what you're doing with concurrent file access, you'll run into even bigger problems than you will on an RDBMS - just last week I was in fact rewriting an otherwise-competent colleague's code, because concurrent read/write to files caused massive unrecoverable corruption issues.
I don't think your wordpress comparison is apples to apples - wordpress is known to be dog-slow, and if you tried similar access patterns over file systems it would be at least as bad. There's little incentive for them to fix it, though, because wordpress sites are read-mostly, and all you have to do to fix all your problems is stick one of the wordpress caches in front of it. Frankly it boggles my mind that they don't ship with a cache on by default.
There's absolutely a place for no-db - if your content is static or write-once, doesn't need indexing over, and you can guarantee that those requirements won't change. In this case, there's no need at all to use a db, and it will simplify your life not to. I'm writing such a site as we speak, in fact! If you have dynamic content, well, I'd use a DBMS personally.
tl;dr: understanding how to use RDBMSs will make the average software engineer's life much easier.
edit: Just a note on SQLite specifically: SQLite can be slow compared to the common file system trick of write new temporary file -> rename, because the file system trick (on ext3 at least) doesn't require an fsync. It does mean that the change is not guaranteed durable, but often for browser data that's OK! An equivalent in database-land would be not writing every single transaction to the transaction log when it commits. That means that on a crash you lose everything that hasn't hit the disk yet, but you maintain consistency. Pre 3.7 SQLite this wasn't supported because SQLite didn't use write-ahead logging, which meant you had to fsync to maintain consistency. I believe in WAL mode this behaviour is easily available now though!
There's a reason it's called SQL: Structured Query Language. If you're interrogating the data, you want SQL (or something like it). If you're just loading stuff / saving stuff to disk, like a Word file, and not performing any sort of random search or query on it, then yeah, using a DB doesn't make sense.
I'm writing a small web app where users have a spaiic list of items. I started with a db but it's so much more complex than a simple file per user. Beside you have single big indexes for all the user data while there is a clear split of information between user. I didn't know how to handle hat efficiently. It turned back to one file per user with the content loaded and cached in memory.
We use database because they allow us to organize a binary file. It's a standard, but it's only relevant if you need to use those kind of data patterns.
You also have to understand regex doesn't scale at all.
However, that was before the current wave of document-based DBs (Redis, Couch, Mongo etc.) so the meaning of a "database" was more targeted at RDBMSs. These days a lot of the reasons listed in the answers are equally applicable to, say, JSON files stored in Redis.
Expressed in English, this says "YAGNI" of all the advanced RDBMS features.
Answer: Yes I freakin' AM gonna need it eventually