9 ms·
Why use a database instead of just saving your data to disk?
- tptacek 14y agoWorth noting that Hacker News is a "store it all in files" design.
- mootothemax 14y agoDo you know if that's why user comment averages (as on our profile pages) aren't updated in real time? Seems like that's the kind of thing which is very easy with a database and much less so with flat files.
- Detrus 14y agoI think everything is in RAM and backed up to disk, Redis style. Probably only keeps recent things in RAM.
- joosters 14y agoIt might be very easy to code with a database, but the DB still has to do a lot of behind-the-scenes work, so you might not want them updated in real time anyway. (n.b. this is all theoretical, I have no idea about the workings of HN) 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...
- qu4z-2 14y agoI don't really see why that would be significantly harder with flat files.
- guard-of-terra 14y agopg is a big proponent of plain files, as explained in his stories about viaweb. 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.
- ucee054 14y agoI don't think pg understands the issue at a gut-feel level. 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.
- wildgift 14y agoI never thought of SQL as declarative or like Prolog. I think it's more like a filter-writing language, with set functions like join. 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.
- ucee054 14y agoI never thought of SQL as declarative or like Prolog. 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.
- mbesto 14y agoI suspect this is largely due to the nature of the most of the data here, i.e. documents as opposed to data. In it's simplest, this is why NoSQL was created, right?
- stalcottsmith 14y agoWell structured relational databases are an awesome tool. I have built and used hundreds. But they are a bit of cargo cult architecture. Deciding to use one means adding lots of code. It adds a lot of complexity around testing. All of it multiplies. It's another big Artifact to maintain and you may have to decide how you will scale it, replicate it, make it highly-available, etc. NOSQL was the first crack in the seemingly unassailable assumption that you need to use a DB. Now I think a few of us are questioning whether to use a datastore at all. After all, once you've dropped SQL for some functions, why stuff them into yet another opaque datastore? You might end up having to use a relational database anyway at some point and then you're stuck with another piece of junk and all its conceptual overhead and extra code. Start with files and introduce a relational database only when necessary. 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.
- ucee054 14y agoFormats are straight forward these days. Just write out JSON, XML, HTML5 w/ microformats or even Yaml. And when my data grows to multiple terabytes in size?
- jacques_chester 14y agoYour argument makes perfect sense. 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.
- bifrost 14y agoI think this is a really good question, and I'm surprised by the answers, many of them are not particularly accurate. I won't tear into the wrongness of the answers and instead I'll highlight the rightness of the question - why use an incredibly complex data management system when you can store your data simply. IMHO The answer usually is "because thats the way everyone else does it" and thats not really a wrong answer but its not really all that interesting either. 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.
- corresation 14y agowhy use an incredibly complex data management system when you can store your data simply. 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.
- bifrost 14y agoSorry, thats not my model of complex :) If I have to store one line of text IE: "This is a line of text" in a database, the amount of overhead required to do that is huge. To store that simply with a flat file, I just put it into the file, thats a low complexity system.
- corresation 14y agoYour original post referred to it as "incredibly complex". It isn't at all complex. Is it more complex -- at least to start -- that an fopen/fwrite/fclose? Sure, but it's still easier than virtually any other part of any solution worth doing. 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.
- aartur 14y agoI recently did experiments with storing documents (blobs) with UUIDs as files. I stored documents within a hierarchy of a few levels of directories to avoid having too many files in a single directory (UUID abcdefg was stored as a/b/c/d/abcdefg). Performance was surprisingly low. Reading documents resulted in a lot of disk seeks. Also it seems that directory entries lookup is a O(n) operation in a standard ext4. I also did experiments with simple databases like GDBM. The effect was also disappointing. In the end I started to really appreciate RDBMSes design and all theory that is behind them. Creating a highly performant system using I/O operations is HARD.
- chubot 14y agoWhat was your usage pattern? This doesn't sound that different than static web file serving, which can be extremely fast. The file system relies heavily on the buffer cache to be fast, and I know there some options to tune that, although I'm not an expert. 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]/.
- darkxanthos 14y agoThe subtext of the largely upvoted answer is that databases > text files. I may be preaching to the choir BUT: "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.
- pestaa 14y agoYou provide a nice counter-argument in general, but let me nitpick a single concern. 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.
- duncanwilcox 14y agoClearly stackexchange is showing pro-db bias. I'm a huge fan of unstructured data in filesystems as well, that's how the service behind my prototypesapp.com is built. 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.
- kawsper 14y agoIt really depends on your structure I think. In the days of PHP3 I saw a lot of developers that built .txt-backed websites, with complicated parsing that would break when you looked at 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.
- asveikau 14y ago> Clearly stackexchange is showing pro-db bias. If the question is "why use a database", don't you expect pro-DB answers?
- betterunix 14y agoUnstructured data is as bad as unstructured code. Typically, your data will become more complex over time, and structure helps a lot with ensuring that this is not a painful thing to do. Relational databases are well-studied and present a well-understood abstraction, with design patterns (normal forms) that significantly improve the maintainability of databases. Sure, relational DBs are not the only solution; I personally find Lisp s-expressions to be an easier to use Q&D store (of course, I am also a Lisp programmer, and most of my code does not have to deal with untrusted data), and lots of people are using YAML these days (and of course, there is XML).
- nohorse 14y agoPost like this is why I'm less interested in hacker news all the time. I think the only answer is 'learn the difference and use the appropriate tool for the job.'
- nohorse 14y agoPost like this is why I'm less interested in hacker news all the time. I think the only answer is 'learn the difference and use the appropriate tool for the job.'
- andrewvc 14y agoWell, here's some good reasons, your OS doesn't want you to store tons of small pieces of data in many different files. If you're storing a 100 byte record in a file you're doing it wrong because: 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-take/ 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.
- larrys 14y ago"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).
- bazzargh 14y agoThere was a great short talk by (I think) Richard Hipp - or possibly Ted T'so? about mistakes people make trying to 'just save your data to disk'. I can't find the one I remember but this does cover a lot of the same material (in a less snappy way): https://www.youtube.com/watch?v=TxXROT_nHqo https://www.youtube.com/watch?v=TxXROT_nHqo 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.
- bazzargh 14y agoI found the talk (in the comments on Shaver's 2008 blog article that cropped up in another comment - I was close on the time at least) 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/talks/278.ogg http://mirror.linux.org.au/pub/linux.conf.au/2007/video/talk...
- chmike 14y agoIt really depends on the problem. Using a DH well also requires some expertise. 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.
- cschmidt 14y agoI'm thinking about moving some of my data out of my postgres database, and into S3 objects. I think for "documents" that will only accessed by a single user (so record locking etc. doesn't matter), it will help things scale more easily. The web app will be hosted on AWS, so I wouldn't think the S3 retrieval time should be too bad. Anyone have some good guidelines on this?
- astine 14y agoWell here's one good reason: If your data cannot all fit in memory at once, you cannot simply serialize and deserialize it from disk. You will have to create some kind of indexed solution involving reading and writing to files piecemeal and by doing so you'll have created an impromptu database by default. 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.
- lsb 14y agoNo, you'll have created a paging system, and it'll probably fight with your OS's pager. 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.
- Arwill 14y agoActually what i find to be the core question in deciding to use a database or not, is whether all data can be loaded at program start into memory or not. Loading all data at start is pretty simple and efficient. In this regards, there is another problem, getting the changes from memory back to the disk. Writing all data to disk, and even overwriting existing data is out of question, so such a system is needed which saves the changed data incrementally. Is there such a "database", which: a. loads up all the data at the start + loads the incremental changes, b. writes the incremental changes in transactional manner to disk, while not messing up the OS paging? I wonder if there is such a "persistent paging file" library available?
- jokoon 14y agoDatabase is a sophisticated form of compression. Text compression is not so good after all. Database allows you to only keep indexes, and directly access those. Text compression is naive and inefficient, database software guide programmers to optimize their data types themselves. It's either that or storing everything in binary files. 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.
- millstone 14y agoI don't like databases, and the reason I don't like them is that they seem to cause terrible performance problems in web browsers. Firefox's use of SQLite is well known to cause or have caused trouble - for example http://shaver.off.net/diary/2008/05/25/fsyncers-and-curveballs/ http://shaver.off.net/diary/2008/05/25/fsyncers-and-curvebal... . And then you get advice like this: http://savanne.be/153-performance-tip-of-the-day/ http://savanne.be/153-performance-tip-of-the-day/ . 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-caches-from-safari/ 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.
- aartur 14y ago> I don't like databases, and the reason I don't like them is that they seem to cause terrible performance problems in web browsers. 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.
- millstone 14y agoI believe ad-hoc files would handily have outperformed SQLite. 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 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.
- ucee054 14y agoWhy should one use a database instead of just saving the data to disk? Expressed in English, this says "YAGNI" of all the advanced RDBMS features. Answer: Yes I freakin' AM gonna need it eventually
- PommeDeTerre 14y ago"Eventually" will very likely be "immediately" for any system that's remotely serious in any way, and being created by developers who consider themselves professionals in even the slightest sense.
- UnoriginalGuy 14y agoWhat databases seem to be REALLY bad at though is storing large blocks of CDATA/"unsorted data" with meta data attached. 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.
- deleted 14y ago[deleted]
- icebraining 14y agoNot that the filesystem isn't a valid option, but there are more databases than SQL databases. That cases seems great for a key-value store (which is essentially what a filesystem is).
- UnoriginalGuy 14y agoTrue there are, and I'd be lying if I said I had tried everything. 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).
- betterunix 14y agoThe same reason you divide your programs into functions, classes, etc. A well-designed database can change as the data you store needs to change. You also get a nice abstraction for accessing/querying data (e.g. relational algebra/SQL) that is well-studied and widely applicable.
- quizotic 14y agoThe question is like asking "why do I need an operating system"? Or why live in a society with government services and laws instead of living alone off the land? 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.
- lightknight 14y agoDepends entirely on what you're doing. If you're just saving large sets of data to the disk, and doing nothing else with them, then yeah, don't use a DB. If you're running lots of random queries on piece-mail parts of several dozen sets of those files, then use a DB. 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.
- jbl 14y agoThis was my first reaction, as well. This goes especially for large datasets. If you are making linear passes over very large databases, I don't think I see much advantage in using a DB beyond possible benefits of shared storage.
- eliben 14y agoAh nostalgia... I recall wondering about this myself back in the early days of my career. 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.
- petsos 14y agoThat's all good but if your DB can't handle your insert rates you only have files as an option. And most DBs have very poor insert rates.