16 ms·

Why I love databases

238 points by strzalek 12y ago

metaphorm 12y ago
this was a great article. extremely helpful to me, coming from a position of being a server-side application developer often tasked with getting several disparate data-stores talking to each other. the author does a great job of showing the full depth of the field while providing useful hooks and links for further study. this one made my bookmarks folder. i'll surely be going back to check it out again.
quonn 12y ago
"The study of databases intersects almost every topic in computer science" - I've heard this before, especially for Compilers. But it has been false for a long time, CS is far more diverse now. For example, how do Databases intersect AI/Machine Learning/Computer Vision, Computer Graphics, Numerics/Simulation, Robotics, Bioinformatics, Computer Architecture or Cryptography?
- applecore 12y ago
  All of the fields you've listed are largely about the representation of data. A database is simply data that's structured to support an algorithm that models some aspect of reality based on that data.
  - quonn 12y ago
    Sure you can say, everything inside a computer is about representing data, so all is the same. But it's not particularly useful to do that.
    - pessimizer 12y ago
      >you can say, everything inside a computer is about representing data hence: "The study of databases intersects almost every topic in computer science"
      - quonn 12y ago
        I just claimed this point of view is useless, so you can't just pick it up as if I agreed. Furthermore data representation in that broad sense is more related to data structures, not databases. Really, we all know what a database is. It is something very different from, say, a block cipher.
        deleted 12y ago
        [deleted]
        pessimizer 12y ago
        You claimed that point of view was useless, but you didn't argue why it was useless. My point of view is that none of "AI/Machine Learning/Computer Vision, Computer Graphics, Numerics/Simulation, Robotics, Bioinformatics, Computer Architecture or Cryptography" can be implemented (or even well conceptualized) without figuring out how they are to store, retrieve, encode, efficiently memoize, guarantee consistency or atomicity, or apply bulk transformations to their data. It's silly to dispute that databases touch all areas of computing by simply declaring that databases touching all areas of computing is meaningless, because it's obvious that databases would touch all areas of computing, because computers are data storage, retrieval, and processing devices. Maybe you're restricting your definition to RDMS (while mentally drawing some arbitrary sharp distinction between data structures and databases.) >Really, we all know what a database is. This is simply a declaration. If you know what a database is, define it, then make your case about what areas of computing that it is irrelevant to. Argument by common sense is empty.
- redwood 12y ago
  Taking some leaps here but AI/Machine Learning/Computer Vision - online index selection / query plan optimization. Computer Graphics - geo 3d indexes Numerics/Simulation - query plan optimization Robotics -if you go meta here there is a lot of overlap with distributed system coordination Bioinformatics - minimal except that this by definition requires uniquely rich data structures that need to be able to be accessed and stored in a database Computer Architecture - databases certainly are themselves architected to perform on computer architectures and the reverse is probably true Cryptography - databases often employ encryption and other security capabilities that are of critical importance but true this isn't the field you mention
- JadeNB 12y ago
  > how do Databases intersect AI/Machine Learning I am not in any way a computer-science expert, but surely these topics have substantial overlap? (For example, a machine that is learning must store its accumulated knowledge somewhere.)
  - quonn 12y ago
    There is data mining, which is like machine learning + databases. But for many, many machine learning problems, the learning occurs offline and the data is simply stored in files, then the learning algorithm is run. The result is a model, which in many cases has a small constant representation (common exceptions: kNN and SVM). There is no database involved. Now you can of course try to learn from an existing database (data mining) or from huge amounts of data, probably stored in a distributed database (big data). But many systems will not and even then the database part is usually not of much concern. So I still don't see much overlap.
    - siddboots 12y ago
      The overlap goes well beyond the question of whether an literal database software system is used: Knowledge representation (as in modern AI), feature representation (as in ML), and relational data modelling are essentially all different branches of the same domain of study. Consider, for example, the connection with logic resolution languages like Prolog.
- sampo 12y ago
  > how do Databases intersect ... Bioinformatics You must be kidding. The whole field of bioinformatics was born when the biologists started to have large enough collections of protein and DNA sequences that working with them by hand was not practical anymore.
- matwood 12y ago
  But it has been false for a long time Actually it has been getting more true for a long time. With computers in nearly every aspect of society we have more data than ever to sift through. AI/ML is a big data problem. Numerics and simulations become more effective with large amounts of data. Instead of simulating a result from 1 data point, try 100s, 1000s or millions or data points.
jandrewrogers 12y ago
Nice article. I love databases too for similar reasons but, as someone that designs database engines, some of the technical points are off the mark. I never really stop learning in this area, the technical range is incredibly deep and nuanced. Some of the points that caught my eye as being quite off: - Contrary to footnote 2, modern database designs bypass the OS file system cache and schedule their own I/O. This has an enormous performance impact versus the OS cache (2-3x is pretty typical) and is a good litmus test for the technical sophistication of the database implementation. It is the primary reason many open source database engines, even "write-oriented" ones, have relatively poor write performance to disk. - The three data retrieval models enumerated are textbook but if you were designing a new engine today you probably would not use any of them for a general purpose design. Modern spatial access methods (ex: Hyperdex, SpaceCurve, Laminar) are superior in almost every way though the literature is much sparser. Also, a number of real-time analytical databases use bitmap-structured databases (ex: ParStream), which are incredibly fast for some types of query workloads. - Many distributed database challenges are a side effect of "one server, one shard" type models. It is not necessary to do things this way, it is just simpler to implement; some distributed database systems have thousands of shards per server. The latter model is operationally more robust and better behaved under failure and load skew. - Tombstones are usually trivial if the database engine is properly designed. Complications are a side effect of poor architecture. The big challenge for tombstones is deciding when and how tombstoned records are garbage collected. It is outside the scope of the normal execution pathways but you also don't want a Java-like GC thread in the background. Of course, any of these is a long blog post in itself. :-)
- gaius 12y ago
  Oracle talks directly to NFS servers bypassing the OS file access layer altogether these days, just using a socket, reading and writing from a huge chunk of memory it's grabbed off the OS on startup, and now manages itself.
- ddorian43 12y ago
  by bitmap-structured you mean bitmap indexes? any opensource db that schedule their own io? I guess postgresql, innodb? what is your opinion on tokudb fractal-trees ?
read 12y ago
> You cannot give up partition tolerance. It'd be more accurate to say you don't want to give it up all the time. You don't want CAP; you want PACELC. http://dbmsmusings.blogspot.com/2010/04/problems-with-cap-and-yahoos-little.html http://dbmsmusings.blogspot.com/2010/04/problems-with-cap-an...
- jeeyoungk 12y ago
  This is an interesting categorization - somewhat like adding an axis to the political compass. Thanks.
deleted 12y ago
[deleted]
gfodor 12y ago
"Designing Data-Intensive Applications" is shaping up to be an excellent treatement of modern databases and their underpinnings. It's at an excellent level of abstraction, deep enough to convey database internals while high level enough (so far at least) to be able to cover a wide variety of database systems. It also has its feet firmly planted in database history, and is NoSQL-koolaid free. Highly recommended. http://shop.oreilly.com/product/0636920032175.do http://shop.oreilly.com/product/0636920032175.do
- T-R 12y ago
  When you say "NoSQL-Koolaid free", do you mean "focuses on Relational" or "treats the topic objectively/academically"?
  - andrioni 12y ago
    Not OP, but the latter. The author talks both about data models (relational, document, graph-based, with some nice historical tidbits about IMS and CODASYL) and storage models (from B-trees to SSTables, passing through bitmap encoding and everything else). It is an amazing book so far, and I'm pretty excited for the rest of it to be written. It's the best text I've read so far on databases that gives all the references you need if you want to go deeper, but still allows you to get a very good higher-level understanding without it.
    - T-R 12y ago
      That sounds fantastic. I'll give it a look
  - gfodor 12y ago
    It covers modern NoSQL databases but puts them in their proper historical context by comparing them to the (largely rejected) hierarchical database systems. (At least when we're talking about things like 'document stores.')
Slackwise 12y ago
I've always loved databases, but after having discovered write-only timestamped databases like Datomic, I can't imagine going backwards. It's a real shame that Datomic isn't fully open source. (Aren't BigTable and Spanner also write-only and timestamped?)
- collyw 12y ago
  Ok, assuming write-only means what I think it does, what is the point in a write only database? /dev/null/ is write only.
  - windowsworkstoo 12y ago
    Probably just means "immutable"
  - wtetzner 12y ago
    I think he means append-only. You can add data, and you can query data, but you can't mutate.
  - michaelochurch 12y ago
    Append-only means that every fact is timestamped and nothing is ever thrown away. (Actually, Datomic provides excision, because some regulatory use cases require it, but it's a "black magic" feature.) The idea is that you should never use UPDATE or DELETE, unless it's a hard requirement or a well-studied optimization. The fact that it's 58 degrees today doesn't invalidate the fact that it was 45 degrees yesterday. New facts may replace old ones relative to time-sensitive queries ("what is the most recent temperature?") but you never get rid of the old ones. This means that queries often have an additional parameter, which is the time. So a type signature for a query type (in a Haskell-like language) would be: type Query a = ([Fact], Time) -> a where the typical use case would use all stored facts and the current time. (Obviously, the DB itself isn't going to do a full-table scan for typical queries. There are optimizations involved. But, conceptually, a query is a function over all facts.)
    - dllthomas 12y ago
MrBra 12y ago
Well, intro was promising and full of energy. Like "I am really going to transmit you some good love for databases and explain why it's so stimulating to deal with them!", but then right after this sparkling start it's all just about same old redundancy, consistency, scaling...
pm90 12y ago
What I find both fascinating and scary about databases is how to choose between the wide variety of databases without understanding exactly how they work? And it doesn't help that there are new databases springing up all the time. Is there a way for application developers to understand these databases quickly without spending weeks working with them?
- justinsb 12y ago
  The simple answer is to use PostgreSQL, unless you have a good reason not to. There are definitely cases where PostgreSQL is not the optimal choice, but if you don't know why you're not a good match for Postgres, you're probably not in that group.
  - daigoba66 12y ago
    Postgres, and MSSQL if you can afford it, are the Swiss Army Knives of database systems. And I mean that in a good way.
    - takeda 12y ago
      For a second I thought you said MySQL and was a bit puzzled. I know indirectly that MSSQL is quite good database, but since you are putting Open Source and proprietary database next to each other it makes me wonder why you picked MSSQL and for example skipped its competitor such as Oracle. This is an honest question, I know very little about MSSQL or Oracle so I'm curious why you picked this one. I know that at least 10 years ago MSSQL and Oracle were close competitors, did Oracle lose their race?
      - daigoba66 12y ago
        I mentioned MSSQL specifically because of personal experience with it over the past 8 years. I see PostgreSQL as the best open source alternative. I have little to no experience with Oracle, so I can't speak for it, but I suspect it's just as functional and preformant.
      - 12y ago
rattray 12y ago
I wonder why RethinkDB hasn't gotten a mention here. Have folks here used it? Thoughts?
pavlov 12y ago
I hate databases. People tend to have way too much faith in them (or their surrounding marketing), and thus make poor database choices that don't actually fit the shape of their data. Persistence is fundamentally the programmer's responsibility; a magic box behind a socket can't design it for you. Most applications I've seen wouldn't even need a database, but apparently a lot of programmers are conditioned into believing that writing anything to disk must involve building a database query string and transmitting it over a socket to another process which parses the string, executes it on an interpreter and stuffs the extracted data into a generic 1970s data model that finally gets written to disk in an opaque format from where it can only be retrieved by sending more strings over sockets. This stuff made sense when 1MB was a huge amount of memory, but today it's just not necessary.
- bbcbasic 12y ago
  Hmm. You are not thinking in business terms. You run a software house: do you want your developers reinventing data storage on each application? Or using a fairly decent data storage that is RDBMS. Most of the time RDBMS is a very good choice. Think about the tooling, support, hiring knowledgeable people etc. Lets face it RDBMS are good at the very small (single table, replacing a text file) up to the very large. In the RDBMS/SQL world you have everything from sqllite to save as a single file, to Oracle for your Enterprise app.
  - sklogic 12y ago
    Most of the time a plain text file is good enough. Databases are overrated.
    - ams6110 12y ago
      This is simply not true, otherwise we would all be using text files. Text files can work in some situations. I disagree that this is "most of the time."
      - sklogic 12y ago
marknadal 12y ago
I'm glad he loves databases, databases have been the bane of my existence. However, the torment they have given me has also lead to a similar fascination - and now I'm writing my own database! So I've become very familiar with the topics he writes on, and they are very good points for anybody interested in the subject. Why would I write my own database? Because databases are hard, and I am determined to make them easy (even if that means me sacrificing years of my life into doing all this crazy research). Check out http://github.com/amark/gun http://github.com/amark/gun ! - CAP Theorem, he is correct, P cannot be sacrificed. GUN is AP with eventual consistency. The beauty of this, though, is that you can always build strong consistency out of eventual consistency (it just requires knowing X amount of peers in advance, and doing a trivial lock until you've heard back from all of them - in fact, I do this in one of the example apps) but you can never go from strong consistency down to an eventually consistent system. - Distributed Systems, this is incredibly incredibly important. I cannot repeat this enough times, there should be no "master" or "single source of truth" in any database. If there is, you're going to have a nightmare of a time being woken up at 3am to fix it when it crashes (my personal experience with other databases). Why? Because single points of failures will fail, centralized systems suck. Solution: Distribute and Decentralize! We make this easy for you. - Correctness vs Efficiency, as he says, Paxos is difficult - all of them, Raft, Quorum, leader election, etc. DO NOT USE THEM unless you are Google, Amazon, Walmart, or what not. Even then, do not use them. Instead, I've solved this challenging problem by developing a new Conflict Resolution system that (very poorly) can be summarized as Vector Clocks + Timestamps, you get the advantages of both without either of their weaknesses. What his means is that data integrity is guaranteed because every machine is using a deterministic algorithm, without any extra gossip between machines. Let me repeat, you'll get the same result on every machine, eventually consistent, without any multi-machine coordination. This means every peer is a master, and that is awesome, even if you are running it on an ephemeral server/cloud - completely resilient to failures, terminations, restarts, and reboots. - Empowering the App. Yes. Databases should serve you, not the other way around. Answers to his questions about abstractions are at http://github.com/amark/gun http://github.com/amark/gun . - Operational Challenges. This is where I diverge from him. If something seems wrong, like things suddenly becoming slow, you can easily just restart it without any damage/harm/failure occurring. And then you can look through your logs, taking your time, to see what went wrong. - Basic Building Blocks. Because GUN is a graph database, you get key-value like access, as well as documents and relational styles. That is because mathematically graphs are the superset of relational algebra and hierarchy trees. Happy to answer any questions!
Roboprog 12y ago
I have lived a very sheltered existence. I have never worked on an application which had a database cluster or sharding, rather than just running on a single server. Of course, the servers are a little bigger now than they were back around say 1990.
- ams6110 12y ago
  These things are necessasry only at internet scale. Your typical business application does not need a sharded distributed database even in a large enterprise system.
cfolgar 12y ago
Since we're discussing databases...is there any "golden standard" learning resource/introduction to PostegreSQL? As a college student, I do not have much experience with them yet but I am aware of how important it would be to be comfortable with them in your day to development. Something tells me that my usual approach of diving in and tinkering by building out an idea wouldn't serve me as well for db's; it just seems to me that there's some fundamental database concepts that I would be missing if I went down that path. Any advice as to where to start a structured approach to learning about databases would be highly appreciated :)
- wowmsi 12y ago
  I would recommend "Architecture of a Database System" by Joe Hellerstein, Micheal Stonebraker, and James Hamilton [0] as a start if you want formal perspective without getting too much into theory. [0] http://db.cs.berkeley.edu/papers/fntdb07-architecture.pdf http://db.cs.berkeley.edu/papers/fntdb07-architecture.pdf
  - jbergens 12y ago
    As a sidenote, Michael Stonebreaker himself is nowdays building VoltDb which is a memory-db that he thinks is much more evolved than relational systems. I havn't used it but it looks interesting. I did try OrigoDb which is an open source .NET based memory db and it worked great for my needs and the queries are really fast.
    - bballard 12y ago
      (I work at VoltDB) VoltDB is easy to try. There is a free 30-day trial available on voltdb.com, and a docker-based demo (http://voltdb.com/voltdb-demo http://voltdb.com/voltdb-demo).
- AlisdairO 12y ago
  Apologies for the self-plug, but you might find http://pgexercises.com/ http://pgexercises.com/ useful for the SQL side of learning Postgres. In terms of rest of the concepts, the Postgres documentation is actually really rather good!
jeeyoungk 12y ago
Thank you everybody for your valuable comment. Like I've said in the post, this was my first attempt to explain my passion. I read every one of your comments, and spent the next few days fully understanding their implications. This post is a bit biased towards what I've interacted with. I think I do have a good sample of various workflow, but there are obviously large number of databases and use cases that I'm missing, and my view of what is "modern" may not incorporate a lot of bleeding edge theory and technologies. Operational complexity is definitely my #1 concern. The service I originally maintained is a OLTP system, located at the top of the service & data dependency graph. Availability is the top concern. The current system I'm writing is metrics database. The operational burden is much lighter. It is almost a leaf node in the service & data dependency graph, and I can take downtime to restart the cluster. A very different workflow, indeed. Thanks!
JSno 12y ago
crappy article. Nothing useful. Just all about buzz words. Everybody knows paxos and raft. Everybody knows partitioning and CAP and merkel tree. This article is all about bullshit and nothing substantial.
Pxtl 12y ago
I love databases, but I loathe SQL. And no, I don't mean NoSQL is better - that's throwing out the baby with the bathwater. To me, SQL is the Common Lisp of relational languages - a brilliant invention of its time that has since long-overstayed its welcome and should be replaced by modern considerations of the problem it solves. The difference is that there are a million rethinks and descendents and redesigns of LISP out there that happily threw out the mistakes and made great strides in the language. You could argue that every modern programming language is a descendant of Lisp thanks to the prevalence of great concepts like lexical closures. SQL, on the other hand, has a teeny tiny few spiritual fringe descendents like the various attempts at Date and Darwin's "Tutorial D". I love the relational model, but who says the only way to manage the relational model is this hoary old thing? It's immensely frustrating that every implementation of SQL bolts on a tacky and half-assed procedural language, but doesn't solve simple underlying frustrations. Simply accessing related objects is immensely wordy for a "relational" language. In an algol-derived language, I can say Group.Manager.Person.Address.PostalCode to walk the graph. In SQL, I have to deal with a zillion joins. Yes, some SQL variants let you join by the foreign key name to make the join a little more terse, but it's still hairy compared to every modern functional or procedural language. And the APIs - maybe the reason so many sites have SQL injection problems is the hideous APIs. Ever tried to build a WHERE IN (id1, id2, id3... idN) statement with a proper parametrized queries? Holy crap what a icky mass of boiler plate. I mean, it's not a hard problem, but how many times have you solved it, and how many times have you found a tedious bug in your solution? Just give me a proper way to concatenate the parametrization inline with the query FFS. db.RunQuery(""" SELECT * FROM MYTABLE WHERE ID IN + " + db.SomeParameterListFunc(a, b, c) + " ORDER BY HOLYCRAP_WAS_THAT_SO_HARD" """); The above syntax would be trivial in any language with operator overloading on the "+" sign, on the off chance that your SQL dialect is so messy it's impossible to safely build a properly-escaped initializer for the list containing a,b and c in text form. And that's not even getting into real actual first-class language support like ORMs give you. And speaking of APIs, the fact that a single "SELECT" is the baseline operation... that you work on one resultset at a time. I don't want a single pile of rows. This is not an excel spreadsheet, it's a relational database, and that means I want a graph of data. I don't want to write three queries to get my Customers, their Personnel, and their Addresses, nor do I want a single row of CustomerPersonnelAdddresses. Once and Only Once is good for the data, why the heck isn't it good for result sets? Where's the code reuse? Why can't I have a pile of SELECTs and a pile of WHEREs and combine them however I see fit? Oh right, I can use a VIEW... but see the previous point, a VIEW is a single glorified Excel spreadsheet, not a proper graph of data. If I want to bundle a bunch of SELECTs together, I have to just write a stored procedure, but then I can't use the proc with a JOIN statement against my VIEW that provides a custom WHERE clause. You could do something monstrous with table-valued parameters, I guess, but those aren't generally well-supported at the API level. This is not a hard problem in every modern language (except Go, of course - yes, you do freaking need map/reduce/filter). Namespaces. Real, actual, organizational tools for your giant list of 9000 tables and their related objects. No, schemas don't freaking count - you can't nest them and they're overly tied to the security model - using schemas for organization instead of security leads to madness, besides the fact that you can't nest them. And of course, so many common problems simply aren't nice to work with using the relational model. How do I make a nice audited row where I have the full history of all the row's changes? Well, I can insert it every time, but that's a lot of wasted space. Yes, again, there are ways to do this, but it's something I'd expect to come out-of-the-box since it's such a common problem. Common problems should be solved by the standard library. But SQL can't solve things like this with the standard library, because a SQL standard libraries are limited to crude things like functions and views and procs and not actual large-scale reusable constructs. It's like a programming language where they gave you a bunch of general tools for manipulating unicode points and dynamically sized arrays but no coherent "string" object. Or trees. Holy crap, you have a "relational" database where a relationship like a "tree" is a nightmare to actually query out! I know that's not what "relational" means, but still - this ins't exactly a rare edge-case, y'know? But it's not in the standard library because the standard library is limited to crude objects like data-types, functions, procedures, etc. that work below the row-level. Any concept of reusable schema concepts is completely left off the table. /rant
fogleman 12y ago
I love databases for the data they contain. And for the ability to make sense of that data more easily when it's in a nice, structured format. I don't care as much for the dev-ops side of it.
smartpants 12y ago
This is what i needed. Great read
digital-rubber 12y ago
In my opinion, something more important, does the database love your data? Only then you can truly love your database.
tkyjonathan 12y ago
I like SQL. It makes sense to me, personally. Even when I get really unwieldy, I can always take a step back and break it down to parts. If I'm allowed to use temp tables, I can do almost anything in it and usually a lot faster than developers in their language can.

238 points by strzalek 12y ago

this was a great article. extremely helpful to me, coming from a position of being a server-side application developer often tasked with getting several disparate data-stores talking to each other. the author does a great job of showing the full depth of the field while providing useful hooks and links for further study. this one made my bookmarks folder. i'll surely be going back to check it out again.

quonn 12y ago

"The study of databases intersects almost every topic in computer science" - I've heard this before, especially for Compilers. But it has been false for a long time, CS is far more diverse now. For example, how do Databases intersect AI/Machine Learning/Computer Vision, Computer Graphics, Numerics/Simulation, Robotics, Bioinformatics, Computer Architecture or Cryptography?

applecore 12y ago
All of the fields you've listed are largely about the representation of data. A database is simply data that's structured to support an algorithm that models some aspect of reality based on that data.
- quonn 12y ago
  Sure you can say, everything inside a computer is about representing data, so all is the same. But it's not particularly useful to do that.
  - pessimizer 12y ago
    >you can say, everything inside a computer is about representing data hence: "The study of databases intersects almost every topic in computer science"
    - quonn 12y ago
      I just claimed this point of view is useless, so you can't just pick it up as if I agreed. Furthermore data representation in that broad sense is more related to data structures, not databases. Really, we all know what a database is. It is something very different from, say, a block cipher.
      - deleted 12y ago
        [deleted]
      - pessimizer 12y ago
        You claimed that point of view was useless, but you didn't argue why it was useless. My point of view is that none of "AI/Machine Learning/Computer Vision, Computer Graphics, Numerics/Simulation, Robotics, Bioinformatics, Computer Architecture or Cryptography" can be implemented (or even well conceptualized) without figuring out how they are to store, retrieve, encode, efficiently memoize, guarantee consistency or atomicity, or apply bulk transformations to their data. It's silly to dispute that databases touch all areas of computing by simply declaring that databases touching all areas of computing is meaningless, because it's obvious that databases would touch all areas of computing, because computers are data storage, retrieval, and processing devices. Maybe you're restricting your definition to RDMS (while mentally drawing some arbitrary sharp distinction between data structures and databases.) >Really, we all know what a database is. This is simply a declaration. If you know what a database is, define it, then make your case about what areas of computing that it is irrelevant to. Argument by common sense is empty.
redwood 12y ago
Taking some leaps here but AI/Machine Learning/Computer Vision - online index selection / query plan optimization. Computer Graphics - geo 3d indexes Numerics/Simulation - query plan optimization Robotics -if you go meta here there is a lot of overlap with distributed system coordination Bioinformatics - minimal except that this by definition requires uniquely rich data structures that need to be able to be accessed and stored in a database Computer Architecture - databases certainly are themselves architected to perform on computer architectures and the reverse is probably true Cryptography - databases often employ encryption and other security capabilities that are of critical importance but true this isn't the field you mention
JadeNB 12y ago
> how do Databases intersect AI/Machine Learning I am not in any way a computer-science expert, but surely these topics have substantial overlap? (For example, a machine that is learning must store its accumulated knowledge somewhere.)
- quonn 12y ago
  There is data mining, which is like machine learning + databases. But for many, many machine learning problems, the learning occurs offline and the data is simply stored in files, then the learning algorithm is run. The result is a model, which in many cases has a small constant representation (common exceptions: kNN and SVM). There is no database involved. Now you can of course try to learn from an existing database (data mining) or from huge amounts of data, probably stored in a distributed database (big data). But many systems will not and even then the database part is usually not of much concern. So I still don't see much overlap.
  - siddboots 12y ago
    The overlap goes well beyond the question of whether an literal database software system is used: Knowledge representation (as in modern AI), feature representation (as in ML), and relational data modelling are essentially all different branches of the same domain of study. Consider, for example, the connection with logic resolution languages like Prolog.
sampo 12y ago
> how do Databases intersect ... Bioinformatics You must be kidding. The whole field of bioinformatics was born when the biologists started to have large enough collections of protein and DNA sequences that working with them by hand was not practical anymore.
matwood 12y ago
But it has been false for a long time Actually it has been getting more true for a long time. With computers in nearly every aspect of society we have more data than ever to sift through. AI/ML is a big data problem. Numerics and simulations become more effective with large amounts of data. Instead of simulating a result from 1 data point, try 100s, 1000s or millions or data points.

jandrewrogers 12y ago

Nice article. I love databases too for similar reasons but, as someone that designs database engines, some of the technical points are off the mark. I never really stop learning in this area, the technical range is incredibly deep and nuanced. Some of the points that caught my eye as being quite off: - Contrary to footnote 2, modern database designs bypass the OS file system cache and schedule their own I/O. This has an enormous performance impact versus the OS cache (2-3x is pretty typical) and is a good litmus test for the technical sophistication of the database implementation. It is the primary reason many open source database engines, even "write-oriented" ones, have relatively poor write performance to disk. - The three data retrieval models enumerated are textbook but if you were designing a new engine today you probably would not use any of them for a general purpose design. Modern spatial access methods (ex: Hyperdex, SpaceCurve, Laminar) are superior in almost every way though the literature is much sparser. Also, a number of real-time analytical databases use bitmap-structured databases (ex: ParStream), which are incredibly fast for some types of query workloads. - Many distributed database challenges are a side effect of "one server, one shard" type models. It is not necessary to do things this way, it is just simpler to implement; some distributed database systems have thousands of shards per server. The latter model is operationally more robust and better behaved under failure and load skew. - Tombstones are usually trivial if the database engine is properly designed. Complications are a side effect of poor architecture. The big challenge for tombstones is deciding when and how tombstoned records are garbage collected. It is outside the scope of the normal execution pathways but you also don't want a Java-like GC thread in the background. Of course, any of these is a long blog post in itself. :-)

gaius 12y ago
Oracle talks directly to NFS servers bypassing the OS file access layer altogether these days, just using a socket, reading and writing from a huge chunk of memory it's grabbed off the OS on startup, and now manages itself.
ddorian43 12y ago
by bitmap-structured you mean bitmap indexes? any opensource db that schedule their own io? I guess postgresql, innodb? what is your opinion on tokudb fractal-trees ?

read 12y ago

> You cannot give up partition tolerance. It'd be more accurate to say you don't want to give it up all the time. You don't want CAP; you want PACELC. http://dbmsmusings.blogspot.com/2010/04/problems-with-cap-and-yahoos-little.html http://dbmsmusings.blogspot.com/2010/04/problems-with-cap-an...

jeeyoungk 12y ago
This is an interesting categorization - somewhat like adding an axis to the political compass. Thanks.

deleted 12y ago

[deleted]

gfodor 12y ago

"Designing Data-Intensive Applications" is shaping up to be an excellent treatement of modern databases and their underpinnings. It's at an excellent level of abstraction, deep enough to convey database internals while high level enough (so far at least) to be able to cover a wide variety of database systems. It also has its feet firmly planted in database history, and is NoSQL-koolaid free. Highly recommended. http://shop.oreilly.com/product/0636920032175.do http://shop.oreilly.com/product/0636920032175.do

T-R 12y ago
When you say "NoSQL-Koolaid free", do you mean "focuses on Relational" or "treats the topic objectively/academically"?
- andrioni 12y ago
  Not OP, but the latter. The author talks both about data models (relational, document, graph-based, with some nice historical tidbits about IMS and CODASYL) and storage models (from B-trees to SSTables, passing through bitmap encoding and everything else). It is an amazing book so far, and I'm pretty excited for the rest of it to be written. It's the best text I've read so far on databases that gives all the references you need if you want to go deeper, but still allows you to get a very good higher-level understanding without it.
  - T-R 12y ago
    That sounds fantastic. I'll give it a look
- gfodor 12y ago
  It covers modern NoSQL databases but puts them in their proper historical context by comparing them to the (largely rejected) hierarchical database systems. (At least when we're talking about things like 'document stores.')

Slackwise 12y ago

I've always loved databases, but after having discovered write-only timestamped databases like Datomic, I can't imagine going backwards. It's a real shame that Datomic isn't fully open source. (Aren't BigTable and Spanner also write-only and timestamped?)

collyw 12y ago
Ok, assuming write-only means what I think it does, what is the point in a write only database? /dev/null/ is write only.
- windowsworkstoo 12y ago
  Probably just means "immutable"
- wtetzner 12y ago
  I think he means append-only. You can add data, and you can query data, but you can't mutate.
- michaelochurch 12y ago
  Append-only means that every fact is timestamped and nothing is ever thrown away. (Actually, Datomic provides excision, because some regulatory use cases require it, but it's a "black magic" feature.) The idea is that you should never use UPDATE or DELETE, unless it's a hard requirement or a well-studied optimization. The fact that it's 58 degrees today doesn't invalidate the fact that it was 45 degrees yesterday. New facts may replace old ones relative to time-sensitive queries ("what is the most recent temperature?") but you never get rid of the old ones. This means that queries often have an additional parameter, which is the time. So a type signature for a query type (in a Haskell-like language) would be: type Query a = ([Fact], Time) -> a where the typical use case would use all stored facts and the current time. (Obviously, the DB itself isn't going to do a full-table scan for typical queries. There are optimizations involved. But, conceptually, a query is a function over all facts.)
  - dllthomas 12y ago

MrBra 12y ago

Well, intro was promising and full of energy. Like "I am really going to transmit you some good love for databases and explain why it's so stimulating to deal with them!", but then right after this sparkling start it's all just about same old redundancy, consistency, scaling...

pm90 12y ago

What I find both fascinating and scary about databases is how to choose between the wide variety of databases without understanding exactly how they work? And it doesn't help that there are new databases springing up all the time. Is there a way for application developers to understand these databases quickly without spending weeks working with them?

justinsb 12y ago
The simple answer is to use PostgreSQL, unless you have a good reason not to. There are definitely cases where PostgreSQL is not the optimal choice, but if you don't know why you're not a good match for Postgres, you're probably not in that group.
- daigoba66 12y ago
  Postgres, and MSSQL if you can afford it, are the Swiss Army Knives of database systems. And I mean that in a good way.
  - takeda 12y ago
    For a second I thought you said MySQL and was a bit puzzled. I know indirectly that MSSQL is quite good database, but since you are putting Open Source and proprietary database next to each other it makes me wonder why you picked MSSQL and for example skipped its competitor such as Oracle. This is an honest question, I know very little about MSSQL or Oracle so I'm curious why you picked this one. I know that at least 10 years ago MSSQL and Oracle were close competitors, did Oracle lose their race?
    - daigoba66 12y ago
      I mentioned MSSQL specifically because of personal experience with it over the past 8 years. I see PostgreSQL as the best open source alternative. I have little to no experience with Oracle, so I can't speak for it, but I suspect it's just as functional and preformant.
    - 12y ago

rattray 12y ago

I wonder why RethinkDB hasn't gotten a mention here. Have folks here used it? Thoughts?

pavlov 12y ago

I hate databases. People tend to have way too much faith in them (or their surrounding marketing), and thus make poor database choices that don't actually fit the shape of their data. Persistence is fundamentally the programmer's responsibility; a magic box behind a socket can't design it for you. Most applications I've seen wouldn't even need a database, but apparently a lot of programmers are conditioned into believing that writing anything to disk must involve building a database query string and transmitting it over a socket to another process which parses the string, executes it on an interpreter and stuffs the extracted data into a generic 1970s data model that finally gets written to disk in an opaque format from where it can only be retrieved by sending more strings over sockets. This stuff made sense when 1MB was a huge amount of memory, but today it's just not necessary.

bbcbasic 12y ago
Hmm. You are not thinking in business terms. You run a software house: do you want your developers reinventing data storage on each application? Or using a fairly decent data storage that is RDBMS. Most of the time RDBMS is a very good choice. Think about the tooling, support, hiring knowledgeable people etc. Lets face it RDBMS are good at the very small (single table, replacing a text file) up to the very large. In the RDBMS/SQL world you have everything from sqllite to save as a single file, to Oracle for your Enterprise app.
- sklogic 12y ago
  Most of the time a plain text file is good enough. Databases are overrated.
  - ams6110 12y ago
    This is simply not true, otherwise we would all be using text files. Text files can work in some situations. I disagree that this is "most of the time."
    - sklogic 12y ago

marknadal 12y ago

I'm glad he loves databases, databases have been the bane of my existence. However, the torment they have given me has also lead to a similar fascination - and now I'm writing my own database! So I've become very familiar with the topics he writes on, and they are very good points for anybody interested in the subject. Why would I write my own database? Because databases are hard, and I am determined to make them easy (even if that means me sacrificing years of my life into doing all this crazy research). Check out http://github.com/amark/gun http://github.com/amark/gun ! - CAP Theorem, he is correct, P cannot be sacrificed. GUN is AP with eventual consistency. The beauty of this, though, is that you can always build strong consistency out of eventual consistency (it just requires knowing X amount of peers in advance, and doing a trivial lock until you've heard back from all of them - in fact, I do this in one of the example apps) but you can never go from strong consistency down to an eventually consistent system. - Distributed Systems, this is incredibly incredibly important. I cannot repeat this enough times, there should be no "master" or "single source of truth" in any database. If there is, you're going to have a nightmare of a time being woken up at 3am to fix it when it crashes (my personal experience with other databases). Why? Because single points of failures will fail, centralized systems suck. Solution: Distribute and Decentralize! We make this easy for you. - Correctness vs Efficiency, as he says, Paxos is difficult - all of them, Raft, Quorum, leader election, etc. DO NOT USE THEM unless you are Google, Amazon, Walmart, or what not. Even then, do not use them. Instead, I've solved this challenging problem by developing a new Conflict Resolution system that (very poorly) can be summarized as Vector Clocks + Timestamps, you get the advantages of both without either of their weaknesses. What his means is that data integrity is guaranteed because every machine is using a deterministic algorithm, without any extra gossip between machines. Let me repeat, you'll get the same result on every machine, eventually consistent, without any multi-machine coordination. This means every peer is a master, and that is awesome, even if you are running it on an ephemeral server/cloud - completely resilient to failures, terminations, restarts, and reboots. - Empowering the App. Yes. Databases should serve you, not the other way around. Answers to his questions about abstractions are at http://github.com/amark/gun http://github.com/amark/gun . - Operational Challenges. This is where I diverge from him. If something seems wrong, like things suddenly becoming slow, you can easily just restart it without any damage/harm/failure occurring. And then you can look through your logs, taking your time, to see what went wrong. - Basic Building Blocks. Because GUN is a graph database, you get key-value like access, as well as documents and relational styles. That is because mathematically graphs are the superset of relational algebra and hierarchy trees. Happy to answer any questions!

Roboprog 12y ago

I have lived a very sheltered existence. I have never worked on an application which had a database cluster or sharding, rather than just running on a single server. Of course, the servers are a little bigger now than they were back around say 1990.

ams6110 12y ago
These things are necessasry only at internet scale. Your typical business application does not need a sharded distributed database even in a large enterprise system.

cfolgar 12y ago

Since we're discussing databases...is there any "golden standard" learning resource/introduction to PostegreSQL? As a college student, I do not have much experience with them yet but I am aware of how important it would be to be comfortable with them in your day to development. Something tells me that my usual approach of diving in and tinkering by building out an idea wouldn't serve me as well for db's; it just seems to me that there's some fundamental database concepts that I would be missing if I went down that path. Any advice as to where to start a structured approach to learning about databases would be highly appreciated :)

wowmsi 12y ago
I would recommend "Architecture of a Database System" by Joe Hellerstein, Micheal Stonebraker, and James Hamilton [0] as a start if you want formal perspective without getting too much into theory. [0] http://db.cs.berkeley.edu/papers/fntdb07-architecture.pdf http://db.cs.berkeley.edu/papers/fntdb07-architecture.pdf
- jbergens 12y ago
  As a sidenote, Michael Stonebreaker himself is nowdays building VoltDb which is a memory-db that he thinks is much more evolved than relational systems. I havn't used it but it looks interesting. I did try OrigoDb which is an open source .NET based memory db and it worked great for my needs and the queries are really fast.
  - bballard 12y ago
    (I work at VoltDB) VoltDB is easy to try. There is a free 30-day trial available on voltdb.com, and a docker-based demo (http://voltdb.com/voltdb-demo http://voltdb.com/voltdb-demo).
AlisdairO 12y ago
Apologies for the self-plug, but you might find http://pgexercises.com/ http://pgexercises.com/ useful for the SQL side of learning Postgres. In terms of rest of the concepts, the Postgres documentation is actually really rather good!

jeeyoungk 12y ago

Thank you everybody for your valuable comment. Like I've said in the post, this was my first attempt to explain my passion. I read every one of your comments, and spent the next few days fully understanding their implications. This post is a bit biased towards what I've interacted with. I think I do have a good sample of various workflow, but there are obviously large number of databases and use cases that I'm missing, and my view of what is "modern" may not incorporate a lot of bleeding edge theory and technologies. Operational complexity is definitely my #1 concern. The service I originally maintained is a OLTP system, located at the top of the service & data dependency graph. Availability is the top concern. The current system I'm writing is metrics database. The operational burden is much lighter. It is almost a leaf node in the service & data dependency graph, and I can take downtime to restart the cluster. A very different workflow, indeed. Thanks!

JSno 12y ago

crappy article. Nothing useful. Just all about buzz words. Everybody knows paxos and raft. Everybody knows partitioning and CAP and merkel tree. This article is all about bullshit and nothing substantial.

Pxtl 12y ago

I love databases, but I loathe SQL. And no, I don't mean NoSQL is better - that's throwing out the baby with the bathwater. To me, SQL is the Common Lisp of relational languages - a brilliant invention of its time that has since long-overstayed its welcome and should be replaced by modern considerations of the problem it solves. The difference is that there are a million rethinks and descendents and redesigns of LISP out there that happily threw out the mistakes and made great strides in the language. You could argue that every modern programming language is a descendant of Lisp thanks to the prevalence of great concepts like lexical closures. SQL, on the other hand, has a teeny tiny few spiritual fringe descendents like the various attempts at Date and Darwin's "Tutorial D". I love the relational model, but who says the only way to manage the relational model is this hoary old thing? It's immensely frustrating that every implementation of SQL bolts on a tacky and half-assed procedural language, but doesn't solve simple underlying frustrations. Simply accessing related objects is immensely wordy for a "relational" language. In an algol-derived language, I can say Group.Manager.Person.Address.PostalCode to walk the graph. In SQL, I have to deal with a zillion joins. Yes, some SQL variants let you join by the foreign key name to make the join a little more terse, but it's still hairy compared to every modern functional or procedural language. And the APIs - maybe the reason so many sites have SQL injection problems is the hideous APIs. Ever tried to build a WHERE IN (id1, id2, id3... idN) statement with a proper parametrized queries? Holy crap what a icky mass of boiler plate. I mean, it's not a hard problem, but how many times have you solved it, and how many times have you found a tedious bug in your solution? Just give me a proper way to concatenate the parametrization inline with the query FFS. db.RunQuery(""" SELECT * FROM MYTABLE WHERE ID IN + " + db.SomeParameterListFunc(a, b, c) + " ORDER BY HOLYCRAP_WAS_THAT_SO_HARD" """); The above syntax would be trivial in any language with operator overloading on the "+" sign, on the off chance that your SQL dialect is so messy it's impossible to safely build a properly-escaped initializer for the list containing a,b and c in text form. And that's not even getting into real actual first-class language support like ORMs give you. And speaking of APIs, the fact that a single "SELECT" is the baseline operation... that you work on one resultset at a time. I don't want a single pile of rows. This is not an excel spreadsheet, it's a relational database, and that means I want a graph of data. I don't want to write three queries to get my Customers, their Personnel, and their Addresses, nor do I want a single row of CustomerPersonnelAdddresses. Once and Only Once is good for the data, why the heck isn't it good for result sets? Where's the code reuse? Why can't I have a pile of SELECTs and a pile of WHEREs and combine them however I see fit? Oh right, I can use a VIEW... but see the previous point, a VIEW is a single glorified Excel spreadsheet, not a proper graph of data. If I want to bundle a bunch of SELECTs together, I have to just write a stored procedure, but then I can't use the proc with a JOIN statement against my VIEW that provides a custom WHERE clause. You could do something monstrous with table-valued parameters, I guess, but those aren't generally well-supported at the API level. This is not a hard problem in every modern language (except Go, of course - yes, you do freaking need map/reduce/filter). Namespaces. Real, actual, organizational tools for your giant list of 9000 tables and their related objects. No, schemas don't freaking count - you can't nest them and they're overly tied to the security model - using schemas for organization instead of security leads to madness, besides the fact that you can't nest them. And of course, so many common problems simply aren't nice to work with using the relational model. How do I make a nice audited row where I have the full history of all the row's changes? Well, I can insert it every time, but that's a lot of wasted space. Yes, again, there are ways to do this, but it's something I'd expect to come out-of-the-box since it's such a common problem. Common problems should be solved by the standard library. But SQL can't solve things like this with the standard library, because a SQL standard libraries are limited to crude things like functions and views and procs and not actual large-scale reusable constructs. It's like a programming language where they gave you a bunch of general tools for manipulating unicode points and dynamically sized arrays but no coherent "string" object. Or trees. Holy crap, you have a "relational" database where a relationship like a "tree" is a nightmare to actually query out! I know that's not what "relational" means, but still - this ins't exactly a rare edge-case, y'know? But it's not in the standard library because the standard library is limited to crude objects like data-types, functions, procedures, etc. that work below the row-level. Any concept of reusable schema concepts is completely left off the table. /rant

fogleman 12y ago

I love databases for the data they contain. And for the ability to make sense of that data more easily when it's in a nice, structured format. I don't care as much for the dev-ops side of it.

smartpants 12y ago

This is what i needed. Great read

digital-rubber 12y ago

In my opinion, something more important, does the database love your data? Only then you can truly love your database.

tkyjonathan 12y ago

I like SQL. It makes sense to me, personally. Even when I get really unwieldy, I can always take a step back and break it down to parts. If I'm allowed to use temp tables, I can do almost anything in it and usually a lot faster than developers in their language can.