17 ms·

Comparing Database Types

521 points by ironcutter 7y ago

AlphaWeaver 7y ago
This article comes from the team at Prisma, who are doing some really cool work building "database schema management and design with code" tools. They're working on a new version of their library right now (Prisma 2) and are regularly giving updates to the community and providing test versions. Most everything they make is open source and really well designed. Would recommend checking it out!
- nikolasburk 7y ago
  Nikolas from the Prisma team here! Thanks a lot for the endorsement, we're indeed super excited about the current database space and the tooling we see emerging. For anyone that wants to check out what we're up to, you can find everything you need in this repo: https://github.com/prisma/prisma2 https://github.com/prisma/prisma2
- 616c 7y ago
  I am curious about Prisma2 because I tried to build a server side API with v1 as a novice to graph systems and it became an unwieldy nightmare. Partially my fault for wanting to do it without the SaaS they provide but trying to build with something complicated and Apollo on the frontend with a skilled FE dev got me so confused I put it off.
  - SPascareli13 7y ago
    I was looking into Prisma + Apollo, can you expand more on your problems with it? To be it seemed really magical at first, but I don't know how it really works in production.
  - nikolasburk 7y ago
    Prisma 1 indeed has a couple of quirks that we're currently ironing out with Prisma 2 (or the "Prisma Framework" as we now call it). Would love to hear from your whether the new version actually solves your pain points! Feel free to reach out to me: burk@prisma.io or @nikolasburk on the Prisma Slack https://slack.prisma.io https://slack.prisma.io
_Understated_ 7y ago
I'm curious... what did the author mean by this: > Legacy database types represent milestones on the path to modern databases. These may still find a foothold in certain specialized environments, but have mostly been replaced by more robust alternatives for production environments. I didn't notice anything that went into any detail about legacy database types. Any idea what the author means by a "Legacy Database"?
- tempguy9999 7y ago
  He actually tells you in the article, straight after (flat file, hierarchical...)
  - _Understated_ 7y ago
    Aww man. I am a dumbass... I never equated that section of things like Network databases and such as legacy. Dunno how I missed it :( *Must read slower...
    - tempguy9999 7y ago
      We've all done it :) No worries!
- AlphaWeaver 7y ago
  The article gives some examples of these, it seems they're mostly referring to things we wouldn't consider "databases" like flat files.
- AtlasBarfed 7y ago
  Mainframe days
marknadal 7y ago
What a lovely article! It should be emphasized that graph databases can do all other types of databases (relational, document, key/value, etc.) as you can see demonstrated in this article (https://gun.eco/docs/Graph-Guide https://gun.eco/docs/Graph-Guide). This makes graphs a superior data structure. If you think about the math, any document is a trie, and tables are a matrix. Both trees and matrices can be represented as graphs. But not all graphs can be represented as a tree or graph. This gets even more fun when you get into hypergraphs and bigraphs, which are totally possible with property graph databases where nodes have type!
- danenania 7y ago
  “It should be emphasized that graph databases can do all other types of databases (relational, document, key/value, etc.)” Not to knock graph dbs, but isn’t the reverse also true?
  - namelosw 7y ago
    Some databases design like extremely simple key-value databases cannot efficiently express joining relation unless load all the data in memory. The same could be said for column based databases etc. I guess that's quite a difference.
    - danenania 7y ago
      "key-value databases cannot efficiently express joining relation unless load all the data in memory" With the right design can't you store the graph data across multiple keys and then load pieces of it selectively into memory? I get that a graph db specializes in this pattern and makes it more efficient, easier to query, etc., but that doesn't mean it's the only type of db that can model a graph.
      - namelosw 7y ago
        Technically it could be, just loading all the keys in a value, which could be massive. The design of relational databases indicates the database is responsible to do a lot of logic according to the query language. Graph databases usually also include their own query language in order to do the same thing. For example, from trillions of records there are 100 records with field x equals to value y. And the job is to get all the 100 records instead of trillions of them. The database would take a short query and interpret the predicate logic in the database process, instead of sending the data back to client which usually located in another physical machine.
muydeemer 7y ago
Just a quick remark on graph dbs. Titan which is mentioned in the article as an example of a graph db is dead. Its successor is the Janus graph (https://github.com/JanusGraph/janusgraph https://github.com/JanusGraph/janusgraph).
- fitzoh 7y ago
  There's also Datastax Enterprise Graph (commercial) from the team behind Titan after they were acquired by Datastax. https://www.datastax.com/products/datastax-graph https://www.datastax.com/products/datastax-graph https://venturebeat.com/2015/02/03/datastax-acquires-aurelius-the-startup-behind-the-titan-graph-database/ https://venturebeat.com/2015/02/03/datastax-acquires-aureliu...
- planck01 7y ago
  I am surprised Dgraph isn't mentioned as an example. It is the most starred graph db on Github, and I think it is the best one in terms of performance and scalability.
  - mdaniel 7y ago
    Strange that they have to have such a non-standard license, when they go out of their way to mention Apache 2 several times: https://github.com/dgraph-io/dgraph/blob/master/LICENSE.md https://github.com/dgraph-io/dgraph/blob/master/LICENSE.md Contrast that with Orient, who also have an Enterprise version, and they just straight-up say "Apache 2, no drama" https://github.com/orientechnologies/orientdb/blob/develop/license.txt https://github.com/orientechnologies/orientdb/blob/develop/l... We had an absolutely miserable experience trying to get Janus to behave rationally, and thus far have had zero drama with Orient; we skipped dgraph because it does not appear to work with Gremlin, meaning one must use vendor-specific APIs to use dgraph. Their client reminds me of the days before ORM: write a big string literal and send it to the server: https://github.com/dgraph-io/dgraph4j#running-a-query https://github.com/dgraph-io/dgraph4j#running-a-query
    - FridgeSeal 7y ago
muydeemer 7y ago
The article reminds of the work of Stonebraker and Hellerstein - What Goes Around Comes Around, which gives a description of how the database world goes in cycles (can be found here: https://people.cs.umass.edu/~yanlei/courses/CS691LL-f06/papers/SH05.pdf https://people.cs.umass.edu/~yanlei/courses/CS691LL-f06/pape...)
chrisweekly 7y ago
I didn't RTFA, but based on titles alone, isn't "Object Database" missing from the list?
- marcosdumay 7y ago
  Aren't those a special case of hierarchical databases?
rainyMammoth 7y ago
What about time series databases that are fairly common nowadays ?
- manigandham 7y ago
  Time-series is more about a specific use-case about data that has a primary time component (like sensor metrics). You can store it in any database, although the common ones are usually some sort of key/value or relational with specific features for time-based queries. Hbase/Bigtable/DynamoDB/Cassandra are key/value. InfluxDB is key/value. Timescale is an extension to Postgres.
  - jnordwick 7y ago
    The big time TS databases (Sybase, KDB, Informix Datawarehouse) are column-based, not key value or traditional relational row-oriented. The ones you list are all lower-tier trying to shoehorn a time field on another model.
    - manigandham 7y ago
      Those are still relational databases, just with column-oriented/column-store tables. I don't see how the storage layer changes the database type. For example, MemSQL has both rowstores and columnstores. Postgres 12 has pluggable storage with column-store (zedstore).
      - shakkhar 7y ago
        > I don't see how the storage layer changes the database type. It does, because it leads to other types of optimization. LittleTable [0], for example, keeps adjacent data in time domain adjacent in disk. So querying large amount of data that are close to each other is efficient even on slow (spinning) disk. Vertica [1] does column compression which allows it to work with denormalized data (common in analytics workload) efficiently. In an ideal world, you could have a storage layer sitting below a perfect abstraction; orthogonal to higher levels. In the real world, column-based and row-based are two completely different categories serving very different use-cases. [0] https://meraki.cisco.com/lib/pdf/trust/lt-paper.pdf https://meraki.cisco.com/lib/pdf/trust/lt-paper.pdf [1] http://vldb.org/pvldb/vol5/p1790_andrewlamb_vldb2012.pdf http://vldb.org/pvldb/vol5/p1790_andrewlamb_vldb2012.pdf
Nican 7y ago
I am really tired of articles that talk about the different types of databases. People can make a graph databases act like relational databases, and vice-versa. Computers, in the end, are just a Turing machine. Just pay attention that the query that you are executing is actually doing the optimal solution. I wish more time would be spent talking about the underlying algorithms that the different query languages use to accomplish the tasks. It is important for developers to understand the execution complexity of queries, and how data is distributed across a cluster. For example, I am usually surprised when people talk about "web-scale", but they do not understand the difference between a "merge-join" and a "hash-join". Or when people do not realize that a sort requires the whole result set to be materialized and sorted.
- shostack 7y ago
  Do you have any articles you'd suggest that in your opinion cover the appropriate underlying guts of them?
  - Nican 7y ago
    Unfortunately, this has something that has been on my backburner for a while. I do not know of any articles that fill exactly the need that I am talking about. :(
- paulddraper 7y ago
  > I am really tired of articles that talk about the different types of databases. People can make a graph databases act like relational databases, and vice-versa. > I wish more time would be spent talking about the underlying algorithms That's precisely the difference. How they store data is fundamental to what kinds of operations are fast. A row-based and columnar database can look rather similar (PostgreSQL/Redshift). But the performance characteristics are far different. Did the article come up short?
  - Nican 7y ago
    I think part of the problem is that databases can have a mix-and-match of features, and it is hard to classify one database into a single category. I think the article does little in actually helping a developer make an informed decision about what underlying data structures actually fits best with their scenario. To give some examples, CockroachDB both has column-families and NoSQL characteristics. A column can be specified to be a JSON column, and it can have an inverted index. Or MemSQL has both row-based and column-based tables, and they use an unorthodox index called "skip lists". CockroachDB and MemSQL both have different applications and characteristis, but they are just cluttered under "NewSQL", as if it was just was some kind of "SQL but better".
tabtab 7y ago
I'd like to see "dynamic relational" implemented. It's conceptually very similar to existing RDBMS and can use SQL (with some minor variations for comparing more explicitly). You don't have to throw away your RDBMS experience and start over. And you can incrementally "lock it down" so that you get RDBMS-like protections when projects mature. For example, you may add required-field constraints (non-blank) and type constraints (must be parsable as a number, for instance). Thus, it's good for prototyping and gradually migrating to production. It may not be as fast as an RDBMS for large datasets, though. But that's often the price for dynamicness. (A fancy version could allow migrating or integrating tables to/with a static system, but let's walk before we run.) https://stackoverflow.com/questions/66385/dynamic-database-schema#46202802 https://stackoverflow.com/questions/66385/dynamic-database-s... Some smaller university out there can make a name for themselves by implementing it. I've been kicking around doing it myself, but I'd have to retire first.
- asah 7y ago
  I've done this (commercially!) with PostgreSQL - just start with a single table, with one JSON field, and as you want performance, integrity, etc, add expression indexes, break out frequently used expressions into columns etc. On large tables, obviously there's a cost for this reorganization but you can partition the data first, and only reorg the most recent data (e.g. range partitioning by time). https://www.google.com/search?q=expression+index+postgres https://www.google.com/search?q=expression+index+postgres https://www.postgresql.org/docs/10/ddl-partitioning.html https://www.postgresql.org/docs/10/ddl-partitioning.html
  - ucarion 7y ago
    Do you have any resources online for how well this approach works in practice? I've been thinking about doing this in replacing a MongoDB database.
    - cagmz 7y ago
      Anecdotally, it's worked out well. Before storing a JSON object, we validate it at the app-level using a JSON schema (which has types, required keys, etc). This lets us write without worrying to much. Once we felt that the schema wasn't changing as much, had too many concerns, etc, we made tables and wrote to them (instead of the JSON column).
bryanlarsen 7y ago
The description of flat-file database seems too restrictive. In my experience, flat files with fixed record lengths and no delimiters were far more common than variable-length delimited formats like CSV. File sizes were often much larger than computer memory size, so random read & write was necessary.
- kps 7y ago
  Yes. The origin of the flat-file database is fixed-format unit record equipment¹, predating computers. COBOL is essentially a language designed for processing fixed-format files. ¹ https://en.wikipedia.org/wiki/Unit_record_equipment https://en.wikipedia.org/wiki/Unit_record_equipment
- imchairmanm 7y ago
  That's definitely a good point. I'll try to update the article to reflect that soon. Thanks for the feedback!
gibsonf1 7y ago
The discussion of graph dbs completely misses the semantic rdf graph approach and how that differs greatly from the property graph (which is discussed). So important is not having to have a custom schema for each application that does not communicate with any other app as opposed to using standard ontologies with relationships and classes that are known and allow interoperability between systems (Linked Data Platform - Solid)
- planck01 7y ago
  Do you know of any successfully semantic RDF graph databases, I guess with OWL support? Because I personally don't. If not, it probably is rightfully too much an academic niche to be discussed in the article.
  - hmottestad 7y ago
    Stardog is quite successful.
  - dehrmann 7y ago
    Does Top Quadrant have anything that does this?
  - kthejoker2 7y ago
    Stardog, MarkLogic, Virtuoso, AllegroGraph, and RDF4J all have commercial applications, but yeah in general semantic RDF is dying on the vine.
  - gibsonf1 7y ago
    We are using Blazegraph and Neptune in production as well as Allegrograph. With Neptune, we tested scale by putting the entire dbpedia on one 4 core machine with 16G of ram. It handled 2.7 billion statements without any issues (we ran out of time with the test - sure it can handle more)
sourcepath 7y ago
What happened with Prisma being all about graphql?
- nikolasburk 7y ago
  GraphQL is a really important use case for Prisma. That is using Prisma as the "data layer" on top of your database when implementing a GraphQL server (e.g. using Apollo Server). However, it's not the only use case since you can effectively use it with any application that needs to access a database (e.g. REST or gRPC APIs). We actually wrote a blog post exactly on this topic: https://www.prisma.io/blog/prisma-and-graphql-mfl5y2r7t49c/ https://www.prisma.io/blog/prisma-and-graphql-mfl5y2r7t49c/ You can also find examples for the various use cases here: https://github.com/prisma/prisma-examples/tree/prisma2 https://github.com/prisma/prisma-examples/tree/prisma2 Please let me know if that clarifies it or if you have more questions! :)
pjungwir 7y ago
Codd's 1979 paper "Extending the Relational Model" [1] is really interesting, especially the second half. The first half is about nulls and outer joins, and I think that steals everyone's attention. But the second half basically gives a way to turn your RDBMS into a graph database by (among other things) letting you query the system catalog and dynamically construct your query based on the results. This would never work with today's parse-plan-optimize-execute pipelines, but it's a really cool idea, and I've certainly often wished for something like it. I'd love to know if anyone has followed up on these ideas, either in scholarship or in built tools. [1] https://gertjans.home.xs4all.nl/usenet/microsoft.public.sqlserver.programming/codd1979.pdf https://gertjans.home.xs4all.nl/usenet/microsoft.public.sqls...
nudpiedo 7y ago
All of them are in fact graph databases, they just didn't realize about it and got lost giving the implementation the category of design for many reasons specific to the context in which they were created. I think we should think more often as mathematicians and a little bit less as "hackers"
- edmundsauto 7y ago
  If they are all described as graph databases, we lose the usefulness of understanding the differences between them. I think understanding these differences are at least interesting, and possibly useful.
- TheMiller 7y ago
  I think this is a mischaracterization. The relational model which motivated relational DMBSs is based on predicate logic. Mappings to graphs are obvious, but are not the organizing principle. This was one of the strengths of the relational model, encouraging a more flexible view of the data than graph databases had previously offered. In a complex relational schema, you can discover and work with all kinds of implicit graphs that were not originally intended by the schema design.
pressurefree 7y ago
it's: relational vs heirarchical centralized vs decentralized ordered vs unordered folders vs tags csv/sql vs xml/json
nailer 7y ago
> Relational vs. Document Tabular vs Document. Having relations is orthogonal to the shape of your data. There are document databases with relations - RethinkDB was pretty popular. Mongo sadly doesn't have them but will probably eventually get them too.
- takeda 7y ago
  The adjective relational in a relational database comes from mathematical relations, tuples i.e. data in tables. It's common misconception that it is from foreign keys.
  - nailer 7y ago
    That's interesting - it seems to both be backed by and conflict with a lot of https://en.wikipedia.org/wiki/Relational_model https://en.wikipedia.org/wiki/Relational_model but maybe that's wrong. I'd still avoid the word 'relational' though - obvious many people will assume 'relational' is related to DB relations rather than tuples (assuming you're right about 'relations' meaning tuples, a lot of the wikipedia contributors are included).
taffer 7y ago
> Relational databases get their name from the fact that relationships can be defined between tables. This is a widespread misconception. Relational databases get their name from relations in the mathematical sense[1], i.e. sets of tuples containing facts. The basic idea of the relational model is that logical predicates can be used to query data flexibly without having to change the underlying data structures. The basic paper by Codd[2] is really worth reading and describes, among other things, the problems of hierarchical and network databases that the relational model is meant to solve. [1] https://en.wikipedia.org/wiki/Finitary_relation https://en.wikipedia.org/wiki/Finitary_relation [2] https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf
- nikolasburk 7y ago
  Thanks for the hint, we'll update the article! :)
  - triska 7y ago
    Related to the logical view, it would also be great to include deductive databases: https://en.wikipedia.org/wiki/Deductive_database https://en.wikipedia.org/wiki/Deductive_database Deductive databases derive logical consequences based on facts and rules. Datalog and its superset Prolog are notable instances of this idea, and they make the connection between the relational model and predicate logic particularly evident. Codd's 1979 paper Extending the Database Relational Model to Capture More Meaning contains additional information about this connection. For example, quoting from Section 3 Relationship to Predicate Logic: "We now describe two distinct ways in which the relational model can be related to predicate logic. Suppose we think of a database initially as a set of formulas in first-order predicate logic. ..."
    - mitchtbaum 7y ago
      It seems these rules refine the data outside the database itself. But they're so tightly integrated between the database and the application that the lines separating them become blurred.
woolcap 7y ago
> Relational databases get their name from the fact that relationships can be defined between tables. Relational databases get their name from the mathematical concept of a relation, used by the Relational Model, "an approach to managing data using a structure and language consistent with first-order predicate logic, first described in 1969 by English computer scientist Edgar F. Codd, where all data is represented in terms of tuples, grouped into relations." [1][2] (emphasis added) Recommended Reading: Database In Depth, by Chris Date. [1] https://en.wikipedia.org/wiki/Relational_model https://en.wikipedia.org/wiki/Relational_model [2] https://en.wikipedia.org/wiki/Relation_(database) https://en.wikipedia.org/wiki/Relation_(database)
- reilly3000 7y ago
  I did a MOOC on relational algebra that made me much more productive in SQL and better appreciate the gravity of what RDBMS really offer. Understanding relational algebra helps demystify the magic or query planners and grok why they both add and reduce latency based on use cases.
  - edmundsauto 7y ago
    Mind sharing the course? Sounds useful.
    - tacotime 7y ago
      here is one: https://lagunita.stanford.edu/courses/DB/RA/SelfPaced/about https://lagunita.stanford.edu/courses/DB/RA/SelfPaced/about
    - reilly3000 7y ago
      https://www.coursera.org/learn/data-manipulation https://www.coursera.org/learn/data-manipulation
intellix 7y ago
Skipped through it looking for an answer but didn't see it: where are unions in Prisma?! Was looking for some big reveal about an underlying choice that enables what everyone is begging for
- matthewmueller 7y ago
  I've been mapping out union types at Prisma. Are you in our Slack? I'm @mattmueller at https://prisma.slack.com https://prisma.slack.com. I'd love to chat with you to better understand your use cases, so we can make sure we're designing it for you.
kristoff_it 7y ago
> To store data, you provide a key and the blob of data you wish to save, for example a JSON object, an image, or plain text. To retrieve data, you provide the key and will then be given the blob of data back. The database does not evaluate the data it is storing and allows limited ways of interacting with it. Definitely not a good description of Redis, even though they cite it as the first example of a Key-Value DB.
minitoar 7y ago
How would you categorize something like ClickHouse or Interana or Druid? Columnar I guess, but then the description of Column-family in the article doesn't match up with my experience of how those work.
- imchairmanm 7y ago
  Hello, author here. That's a good question and something I had a hard time sorting out as I worked on this. I think those fall into a different category confusingly sometimes called column-oriented databases. They're primarily used for analytic-focused tasks and get their name from storing data by column instead of by row (all data in a single column is stored to disk together). I didn't include those as a separate category here because they're basically relational databases with a different underlying storage strategy to allow for easier column-based aggregation and so forth. My colleague shared this article [1] with me, which definitely helped inform how I distinguished between the two in my head. [1] http://dbmsmusings.blogspot.com/2010/03/distinguishing-two-major-types-of_29.html http://dbmsmusings.blogspot.com/2010/03/distinguishing-two-m...
  - minitoar 7y ago
    That makes sense, they really are just relational databases optimized for certain tasks, with corresponding limitations e.g. they don't support arbitrary joins.
    - barrkel 7y ago
      There's nothing intrinsic about not supporting joins, in a columnar store; it's just that you lose a huge amount of the linear scanning performance if you have to do joins for each value. Most columnar stores I've used (primarily Impala, SparkSQL and Clickhouse) all support joins, but they materialize one side of the join as an in-memory hash table, which limits the allowable size of the join, and is a cost multiplier for a distributed query. I believe per the docs that MemSQL can mix and match row-based with columnar more easily, but joins are always going to be really slow compared to the speed you can get from scanning the minimum number of columns to answer your question.
bryanrasmussen 7y ago
Again a renaming that makes what the article is actually about less clear.
honkycat 7y ago
There is a great chapter in "Designing Data Intensive Applications" about this very subject
victor106 7y ago
Can anyone here point to a resource that gives a comprehensive treatment (use cases, pros and cons etc) of all the types (Nosql, NewSQL, relational, timeseries) of databases being used today?
galaxyLogic 7y ago
What happened to Object-Oriented Databases?
- AtlasBarfed 7y ago
  Document databases kind of killed them I would specualte. Since JSON serializes with objects so much better than (ugh) XML, the Relational impedence is gone (well, a lot of it).
dehrmann 7y ago
The column-family databases mentioned (Cassandra, HBase) are both just fancy key-value stores that add semantics for separate tables and cell-level data so you're not rolling it yourself.
einpoklum 7y ago
The document completely overlooks Columnar databases, which are focused on analytics and are much faster than (most, not all) general-purpose DBMSes. See: https://en.wikipedia.org/wiki/Column-oriented_DBMS https://en.wikipedia.org/wiki/Column-oriented_DBMS and https://www.slideshare.net/arangodb/introduction-to-column-oriented-databases https://www.slideshare.net/arangodb/introduction-to-column-o... or get: http://www.nowpublishers.com/article/Details/DBS-024 http://www.nowpublishers.com/article/Details/DBS-024 Examples: * MonetDB * SAP Hana * Actian Vector (formerly Vectorwise) * Oracle In-Memory
- kthejoker2 7y ago
  Also Druid, HBase, Vertipaq (engine behind PowerBI), Redshift, Azure SQL DW, etc Columnar compression is a really interesting engineering problem
  - shrumm 7y ago
    ClickHouse is another favourite
    - einpoklum 7y ago
      Clickhouse is a columnar system, yes, but is not a full-fledged DBMS. Specifically, I don't think it can join tables.
  - NotSammyHagar 7y ago
    memsql (which was in there for new sql).
    - einpoklum 7y ago
      Eh... not quite. It's in-memory representation is row-based. It seems it uses columnar secondary storage. At least - that's what it says here: https://en.wikipedia.org/wiki/MemSQL https://en.wikipedia.org/wiki/MemSQL
  - nicoburns 7y ago
thekhatribharat 7y ago
[Shameless Plug] A summary of the evergrowing NoSQL and NewSQL market: https://medium.com/open-factory/nosql-newsql-a-smorgasboard-market-e8cbce4ae8a9 https://medium.com/open-factory/nosql-newsql-a-smorgasboard-...
CMCDragonkai 7y ago
There's also column-oriented or array databases like MonetDB and Rasdaman.
- zbentley 7y ago
  And the elephant in the room: Cassandra.
neop1x 7y ago
No Elastic among the examples while highly popular and nice :( Great article overall, though!

521 points by ironcutter 7y ago

This article comes from the team at Prisma, who are doing some really cool work building "database schema management and design with code" tools. They're working on a new version of their library right now (Prisma 2) and are regularly giving updates to the community and providing test versions. Most everything they make is open source and really well designed. Would recommend checking it out!

nikolasburk 7y ago
Nikolas from the Prisma team here! Thanks a lot for the endorsement, we're indeed super excited about the current database space and the tooling we see emerging. For anyone that wants to check out what we're up to, you can find everything you need in this repo: https://github.com/prisma/prisma2 https://github.com/prisma/prisma2
616c 7y ago
I am curious about Prisma2 because I tried to build a server side API with v1 as a novice to graph systems and it became an unwieldy nightmare. Partially my fault for wanting to do it without the SaaS they provide but trying to build with something complicated and Apollo on the frontend with a skilled FE dev got me so confused I put it off.
- SPascareli13 7y ago
  I was looking into Prisma + Apollo, can you expand more on your problems with it? To be it seemed really magical at first, but I don't know how it really works in production.
- nikolasburk 7y ago
  Prisma 1 indeed has a couple of quirks that we're currently ironing out with Prisma 2 (or the "Prisma Framework" as we now call it). Would love to hear from your whether the new version actually solves your pain points! Feel free to reach out to me: burk@prisma.io or @nikolasburk on the Prisma Slack https://slack.prisma.io https://slack.prisma.io

_Understated_ 7y ago

I'm curious... what did the author mean by this: > Legacy database types represent milestones on the path to modern databases. These may still find a foothold in certain specialized environments, but have mostly been replaced by more robust alternatives for production environments. I didn't notice anything that went into any detail about legacy database types. Any idea what the author means by a "Legacy Database"?

tempguy9999 7y ago
He actually tells you in the article, straight after (flat file, hierarchical...)
- _Understated_ 7y ago
  Aww man. I am a dumbass... I never equated that section of things like Network databases and such as legacy. Dunno how I missed it :( *Must read slower...
  - tempguy9999 7y ago
    We've all done it :) No worries!
AlphaWeaver 7y ago
The article gives some examples of these, it seems they're mostly referring to things we wouldn't consider "databases" like flat files.
AtlasBarfed 7y ago
Mainframe days

marknadal 7y ago

What a lovely article! It should be emphasized that graph databases can do all other types of databases (relational, document, key/value, etc.) as you can see demonstrated in this article (https://gun.eco/docs/Graph-Guide https://gun.eco/docs/Graph-Guide). This makes graphs a superior data structure. If you think about the math, any document is a trie, and tables are a matrix. Both trees and matrices can be represented as graphs. But not all graphs can be represented as a tree or graph. This gets even more fun when you get into hypergraphs and bigraphs, which are totally possible with property graph databases where nodes have type!

danenania 7y ago
“It should be emphasized that graph databases can do all other types of databases (relational, document, key/value, etc.)” Not to knock graph dbs, but isn’t the reverse also true?
- namelosw 7y ago
  Some databases design like extremely simple key-value databases cannot efficiently express joining relation unless load all the data in memory. The same could be said for column based databases etc. I guess that's quite a difference.
  - danenania 7y ago
    "key-value databases cannot efficiently express joining relation unless load all the data in memory" With the right design can't you store the graph data across multiple keys and then load pieces of it selectively into memory? I get that a graph db specializes in this pattern and makes it more efficient, easier to query, etc., but that doesn't mean it's the only type of db that can model a graph.
    - namelosw 7y ago
      Technically it could be, just loading all the keys in a value, which could be massive. The design of relational databases indicates the database is responsible to do a lot of logic according to the query language. Graph databases usually also include their own query language in order to do the same thing. For example, from trillions of records there are 100 records with field x equals to value y. And the job is to get all the 100 records instead of trillions of them. The database would take a short query and interpret the predicate logic in the database process, instead of sending the data back to client which usually located in another physical machine.

muydeemer 7y ago

Just a quick remark on graph dbs. Titan which is mentioned in the article as an example of a graph db is dead. Its successor is the Janus graph (https://github.com/JanusGraph/janusgraph https://github.com/JanusGraph/janusgraph).

fitzoh 7y ago
There's also Datastax Enterprise Graph (commercial) from the team behind Titan after they were acquired by Datastax. https://www.datastax.com/products/datastax-graph https://www.datastax.com/products/datastax-graph https://venturebeat.com/2015/02/03/datastax-acquires-aurelius-the-startup-behind-the-titan-graph-database/ https://venturebeat.com/2015/02/03/datastax-acquires-aureliu...
planck01 7y ago
I am surprised Dgraph isn't mentioned as an example. It is the most starred graph db on Github, and I think it is the best one in terms of performance and scalability.
- mdaniel 7y ago
  Strange that they have to have such a non-standard license, when they go out of their way to mention Apache 2 several times: https://github.com/dgraph-io/dgraph/blob/master/LICENSE.md https://github.com/dgraph-io/dgraph/blob/master/LICENSE.md Contrast that with Orient, who also have an Enterprise version, and they just straight-up say "Apache 2, no drama" https://github.com/orientechnologies/orientdb/blob/develop/license.txt https://github.com/orientechnologies/orientdb/blob/develop/l... We had an absolutely miserable experience trying to get Janus to behave rationally, and thus far have had zero drama with Orient; we skipped dgraph because it does not appear to work with Gremlin, meaning one must use vendor-specific APIs to use dgraph. Their client reminds me of the days before ORM: write a big string literal and send it to the server: https://github.com/dgraph-io/dgraph4j#running-a-query https://github.com/dgraph-io/dgraph4j#running-a-query
  - FridgeSeal 7y ago

muydeemer 7y ago

The article reminds of the work of Stonebraker and Hellerstein - What Goes Around Comes Around, which gives a description of how the database world goes in cycles (can be found here: https://people.cs.umass.edu/~yanlei/courses/CS691LL-f06/papers/SH05.pdf https://people.cs.umass.edu/~yanlei/courses/CS691LL-f06/pape...)

chrisweekly 7y ago

I didn't RTFA, but based on titles alone, isn't "Object Database" missing from the list?

marcosdumay 7y ago
Aren't those a special case of hierarchical databases?

rainyMammoth 7y ago

What about time series databases that are fairly common nowadays ?

manigandham 7y ago
Time-series is more about a specific use-case about data that has a primary time component (like sensor metrics). You can store it in any database, although the common ones are usually some sort of key/value or relational with specific features for time-based queries. Hbase/Bigtable/DynamoDB/Cassandra are key/value. InfluxDB is key/value. Timescale is an extension to Postgres.
- jnordwick 7y ago
  The big time TS databases (Sybase, KDB, Informix Datawarehouse) are column-based, not key value or traditional relational row-oriented. The ones you list are all lower-tier trying to shoehorn a time field on another model.
  - manigandham 7y ago
    Those are still relational databases, just with column-oriented/column-store tables. I don't see how the storage layer changes the database type. For example, MemSQL has both rowstores and columnstores. Postgres 12 has pluggable storage with column-store (zedstore).
    - shakkhar 7y ago
      > I don't see how the storage layer changes the database type. It does, because it leads to other types of optimization. LittleTable [0], for example, keeps adjacent data in time domain adjacent in disk. So querying large amount of data that are close to each other is efficient even on slow (spinning) disk. Vertica [1] does column compression which allows it to work with denormalized data (common in analytics workload) efficiently. In an ideal world, you could have a storage layer sitting below a perfect abstraction; orthogonal to higher levels. In the real world, column-based and row-based are two completely different categories serving very different use-cases. [0] https://meraki.cisco.com/lib/pdf/trust/lt-paper.pdf https://meraki.cisco.com/lib/pdf/trust/lt-paper.pdf [1] http://vldb.org/pvldb/vol5/p1790_andrewlamb_vldb2012.pdf http://vldb.org/pvldb/vol5/p1790_andrewlamb_vldb2012.pdf

Nican 7y ago

I am really tired of articles that talk about the different types of databases. People can make a graph databases act like relational databases, and vice-versa. Computers, in the end, are just a Turing machine. Just pay attention that the query that you are executing is actually doing the optimal solution. I wish more time would be spent talking about the underlying algorithms that the different query languages use to accomplish the tasks. It is important for developers to understand the execution complexity of queries, and how data is distributed across a cluster. For example, I am usually surprised when people talk about "web-scale", but they do not understand the difference between a "merge-join" and a "hash-join". Or when people do not realize that a sort requires the whole result set to be materialized and sorted.

shostack 7y ago
Do you have any articles you'd suggest that in your opinion cover the appropriate underlying guts of them?
- Nican 7y ago
  Unfortunately, this has something that has been on my backburner for a while. I do not know of any articles that fill exactly the need that I am talking about. :(
paulddraper 7y ago
> I am really tired of articles that talk about the different types of databases. People can make a graph databases act like relational databases, and vice-versa. > I wish more time would be spent talking about the underlying algorithms That's precisely the difference. How they store data is fundamental to what kinds of operations are fast. A row-based and columnar database can look rather similar (PostgreSQL/Redshift). But the performance characteristics are far different. Did the article come up short?
- Nican 7y ago
  I think part of the problem is that databases can have a mix-and-match of features, and it is hard to classify one database into a single category. I think the article does little in actually helping a developer make an informed decision about what underlying data structures actually fits best with their scenario. To give some examples, CockroachDB both has column-families and NoSQL characteristics. A column can be specified to be a JSON column, and it can have an inverted index. Or MemSQL has both row-based and column-based tables, and they use an unorthodox index called "skip lists". CockroachDB and MemSQL both have different applications and characteristis, but they are just cluttered under "NewSQL", as if it was just was some kind of "SQL but better".

tabtab 7y ago

I'd like to see "dynamic relational" implemented. It's conceptually very similar to existing RDBMS and can use SQL (with some minor variations for comparing more explicitly). You don't have to throw away your RDBMS experience and start over. And you can incrementally "lock it down" so that you get RDBMS-like protections when projects mature. For example, you may add required-field constraints (non-blank) and type constraints (must be parsable as a number, for instance). Thus, it's good for prototyping and gradually migrating to production. It may not be as fast as an RDBMS for large datasets, though. But that's often the price for dynamicness. (A fancy version could allow migrating or integrating tables to/with a static system, but let's walk before we run.) https://stackoverflow.com/questions/66385/dynamic-database-schema#46202802 https://stackoverflow.com/questions/66385/dynamic-database-s... Some smaller university out there can make a name for themselves by implementing it. I've been kicking around doing it myself, but I'd have to retire first.

asah 7y ago
I've done this (commercially!) with PostgreSQL - just start with a single table, with one JSON field, and as you want performance, integrity, etc, add expression indexes, break out frequently used expressions into columns etc. On large tables, obviously there's a cost for this reorganization but you can partition the data first, and only reorg the most recent data (e.g. range partitioning by time). https://www.google.com/search?q=expression+index+postgres https://www.google.com/search?q=expression+index+postgres https://www.postgresql.org/docs/10/ddl-partitioning.html https://www.postgresql.org/docs/10/ddl-partitioning.html
- ucarion 7y ago
  Do you have any resources online for how well this approach works in practice? I've been thinking about doing this in replacing a MongoDB database.
  - cagmz 7y ago
    Anecdotally, it's worked out well. Before storing a JSON object, we validate it at the app-level using a JSON schema (which has types, required keys, etc). This lets us write without worrying to much. Once we felt that the schema wasn't changing as much, had too many concerns, etc, we made tables and wrote to them (instead of the JSON column).

bryanlarsen 7y ago

The description of flat-file database seems too restrictive. In my experience, flat files with fixed record lengths and no delimiters were far more common than variable-length delimited formats like CSV. File sizes were often much larger than computer memory size, so random read & write was necessary.

kps 7y ago
Yes. The origin of the flat-file database is fixed-format unit record equipment¹, predating computers. COBOL is essentially a language designed for processing fixed-format files. ¹ https://en.wikipedia.org/wiki/Unit_record_equipment https://en.wikipedia.org/wiki/Unit_record_equipment
imchairmanm 7y ago
That's definitely a good point. I'll try to update the article to reflect that soon. Thanks for the feedback!

gibsonf1 7y ago

The discussion of graph dbs completely misses the semantic rdf graph approach and how that differs greatly from the property graph (which is discussed). So important is not having to have a custom schema for each application that does not communicate with any other app as opposed to using standard ontologies with relationships and classes that are known and allow interoperability between systems (Linked Data Platform - Solid)

planck01 7y ago
Do you know of any successfully semantic RDF graph databases, I guess with OWL support? Because I personally don't. If not, it probably is rightfully too much an academic niche to be discussed in the article.
- hmottestad 7y ago
  Stardog is quite successful.
- dehrmann 7y ago
  Does Top Quadrant have anything that does this?
- kthejoker2 7y ago
  Stardog, MarkLogic, Virtuoso, AllegroGraph, and RDF4J all have commercial applications, but yeah in general semantic RDF is dying on the vine.
- gibsonf1 7y ago
  We are using Blazegraph and Neptune in production as well as Allegrograph. With Neptune, we tested scale by putting the entire dbpedia on one 4 core machine with 16G of ram. It handled 2.7 billion statements without any issues (we ran out of time with the test - sure it can handle more)

sourcepath 7y ago

What happened with Prisma being all about graphql?

nikolasburk 7y ago
GraphQL is a really important use case for Prisma. That is using Prisma as the "data layer" on top of your database when implementing a GraphQL server (e.g. using Apollo Server). However, it's not the only use case since you can effectively use it with any application that needs to access a database (e.g. REST or gRPC APIs). We actually wrote a blog post exactly on this topic: https://www.prisma.io/blog/prisma-and-graphql-mfl5y2r7t49c/ https://www.prisma.io/blog/prisma-and-graphql-mfl5y2r7t49c/ You can also find examples for the various use cases here: https://github.com/prisma/prisma-examples/tree/prisma2 https://github.com/prisma/prisma-examples/tree/prisma2 Please let me know if that clarifies it or if you have more questions! :)

pjungwir 7y ago

Codd's 1979 paper "Extending the Relational Model" [1] is really interesting, especially the second half. The first half is about nulls and outer joins, and I think that steals everyone's attention. But the second half basically gives a way to turn your RDBMS into a graph database by (among other things) letting you query the system catalog and dynamically construct your query based on the results. This would never work with today's parse-plan-optimize-execute pipelines, but it's a really cool idea, and I've certainly often wished for something like it. I'd love to know if anyone has followed up on these ideas, either in scholarship or in built tools. [1] https://gertjans.home.xs4all.nl/usenet/microsoft.public.sqlserver.programming/codd1979.pdf https://gertjans.home.xs4all.nl/usenet/microsoft.public.sqls...

nudpiedo 7y ago

All of them are in fact graph databases, they just didn't realize about it and got lost giving the implementation the category of design for many reasons specific to the context in which they were created. I think we should think more often as mathematicians and a little bit less as "hackers"

edmundsauto 7y ago
If they are all described as graph databases, we lose the usefulness of understanding the differences between them. I think understanding these differences are at least interesting, and possibly useful.
TheMiller 7y ago
I think this is a mischaracterization. The relational model which motivated relational DMBSs is based on predicate logic. Mappings to graphs are obvious, but are not the organizing principle. This was one of the strengths of the relational model, encouraging a more flexible view of the data than graph databases had previously offered. In a complex relational schema, you can discover and work with all kinds of implicit graphs that were not originally intended by the schema design.

pressurefree 7y ago

it's: relational vs heirarchical centralized vs decentralized ordered vs unordered folders vs tags csv/sql vs xml/json

nailer 7y ago

> Relational vs. Document Tabular vs Document. Having relations is orthogonal to the shape of your data. There are document databases with relations - RethinkDB was pretty popular. Mongo sadly doesn't have them but will probably eventually get them too.

takeda 7y ago
The adjective relational in a relational database comes from mathematical relations, tuples i.e. data in tables. It's common misconception that it is from foreign keys.
- nailer 7y ago
  That's interesting - it seems to both be backed by and conflict with a lot of https://en.wikipedia.org/wiki/Relational_model https://en.wikipedia.org/wiki/Relational_model but maybe that's wrong. I'd still avoid the word 'relational' though - obvious many people will assume 'relational' is related to DB relations rather than tuples (assuming you're right about 'relations' meaning tuples, a lot of the wikipedia contributors are included).

taffer 7y ago

> Relational databases get their name from the fact that relationships can be defined between tables. This is a widespread misconception. Relational databases get their name from relations in the mathematical sense[1], i.e. sets of tuples containing facts. The basic idea of the relational model is that logical predicates can be used to query data flexibly without having to change the underlying data structures. The basic paper by Codd[2] is really worth reading and describes, among other things, the problems of hierarchical and network databases that the relational model is meant to solve. [1] https://en.wikipedia.org/wiki/Finitary_relation https://en.wikipedia.org/wiki/Finitary_relation [2] https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf

nikolasburk 7y ago
Thanks for the hint, we'll update the article! :)
- triska 7y ago
  Related to the logical view, it would also be great to include deductive databases: https://en.wikipedia.org/wiki/Deductive_database https://en.wikipedia.org/wiki/Deductive_database Deductive databases derive logical consequences based on facts and rules. Datalog and its superset Prolog are notable instances of this idea, and they make the connection between the relational model and predicate logic particularly evident. Codd's 1979 paper Extending the Database Relational Model to Capture More Meaning contains additional information about this connection. For example, quoting from Section 3 Relationship to Predicate Logic: "We now describe two distinct ways in which the relational model can be related to predicate logic. Suppose we think of a database initially as a set of formulas in first-order predicate logic. ..."
  - mitchtbaum 7y ago
    It seems these rules refine the data outside the database itself. But they're so tightly integrated between the database and the application that the lines separating them become blurred.

woolcap 7y ago

> Relational databases get their name from the fact that relationships can be defined between tables. Relational databases get their name from the mathematical concept of a relation, used by the Relational Model, "an approach to managing data using a structure and language consistent with first-order predicate logic, first described in 1969 by English computer scientist Edgar F. Codd, where all data is represented in terms of tuples, grouped into relations." [1][2] (emphasis added) Recommended Reading: Database In Depth, by Chris Date. [1] https://en.wikipedia.org/wiki/Relational_model https://en.wikipedia.org/wiki/Relational_model [2] https://en.wikipedia.org/wiki/Relation_(database) https://en.wikipedia.org/wiki/Relation_(database)

reilly3000 7y ago
I did a MOOC on relational algebra that made me much more productive in SQL and better appreciate the gravity of what RDBMS really offer. Understanding relational algebra helps demystify the magic or query planners and grok why they both add and reduce latency based on use cases.
- edmundsauto 7y ago
  Mind sharing the course? Sounds useful.
  - tacotime 7y ago
    here is one: https://lagunita.stanford.edu/courses/DB/RA/SelfPaced/about https://lagunita.stanford.edu/courses/DB/RA/SelfPaced/about
  - reilly3000 7y ago
    https://www.coursera.org/learn/data-manipulation https://www.coursera.org/learn/data-manipulation

intellix 7y ago

Skipped through it looking for an answer but didn't see it: where are unions in Prisma?! Was looking for some big reveal about an underlying choice that enables what everyone is begging for

matthewmueller 7y ago
I've been mapping out union types at Prisma. Are you in our Slack? I'm @mattmueller at https://prisma.slack.com https://prisma.slack.com. I'd love to chat with you to better understand your use cases, so we can make sure we're designing it for you.

kristoff_it 7y ago

> To store data, you provide a key and the blob of data you wish to save, for example a JSON object, an image, or plain text. To retrieve data, you provide the key and will then be given the blob of data back. The database does not evaluate the data it is storing and allows limited ways of interacting with it. Definitely not a good description of Redis, even though they cite it as the first example of a Key-Value DB.

minitoar 7y ago

How would you categorize something like ClickHouse or Interana or Druid? Columnar I guess, but then the description of Column-family in the article doesn't match up with my experience of how those work.

imchairmanm 7y ago
Hello, author here. That's a good question and something I had a hard time sorting out as I worked on this. I think those fall into a different category confusingly sometimes called column-oriented databases. They're primarily used for analytic-focused tasks and get their name from storing data by column instead of by row (all data in a single column is stored to disk together). I didn't include those as a separate category here because they're basically relational databases with a different underlying storage strategy to allow for easier column-based aggregation and so forth. My colleague shared this article [1] with me, which definitely helped inform how I distinguished between the two in my head. [1] http://dbmsmusings.blogspot.com/2010/03/distinguishing-two-major-types-of_29.html http://dbmsmusings.blogspot.com/2010/03/distinguishing-two-m...
- minitoar 7y ago
  That makes sense, they really are just relational databases optimized for certain tasks, with corresponding limitations e.g. they don't support arbitrary joins.
  - barrkel 7y ago
    There's nothing intrinsic about not supporting joins, in a columnar store; it's just that you lose a huge amount of the linear scanning performance if you have to do joins for each value. Most columnar stores I've used (primarily Impala, SparkSQL and Clickhouse) all support joins, but they materialize one side of the join as an in-memory hash table, which limits the allowable size of the join, and is a cost multiplier for a distributed query. I believe per the docs that MemSQL can mix and match row-based with columnar more easily, but joins are always going to be really slow compared to the speed you can get from scanning the minimum number of columns to answer your question.

bryanrasmussen 7y ago

Again a renaming that makes what the article is actually about less clear.

honkycat 7y ago

There is a great chapter in "Designing Data Intensive Applications" about this very subject

victor106 7y ago

Can anyone here point to a resource that gives a comprehensive treatment (use cases, pros and cons etc) of all the types (Nosql, NewSQL, relational, timeseries) of databases being used today?

galaxyLogic 7y ago

What happened to Object-Oriented Databases?

AtlasBarfed 7y ago
Document databases kind of killed them I would specualte. Since JSON serializes with objects so much better than (ugh) XML, the Relational impedence is gone (well, a lot of it).

dehrmann 7y ago

The column-family databases mentioned (Cassandra, HBase) are both just fancy key-value stores that add semantics for separate tables and cell-level data so you're not rolling it yourself.

einpoklum 7y ago

The document completely overlooks Columnar databases, which are focused on analytics and are much faster than (most, not all) general-purpose DBMSes. See: https://en.wikipedia.org/wiki/Column-oriented_DBMS https://en.wikipedia.org/wiki/Column-oriented_DBMS and https://www.slideshare.net/arangodb/introduction-to-column-oriented-databases https://www.slideshare.net/arangodb/introduction-to-column-o... or get: http://www.nowpublishers.com/article/Details/DBS-024 http://www.nowpublishers.com/article/Details/DBS-024 Examples: * MonetDB * SAP Hana * Actian Vector (formerly Vectorwise) * Oracle In-Memory

kthejoker2 7y ago
Also Druid, HBase, Vertipaq (engine behind PowerBI), Redshift, Azure SQL DW, etc Columnar compression is a really interesting engineering problem
- shrumm 7y ago
  ClickHouse is another favourite
  - einpoklum 7y ago
    Clickhouse is a columnar system, yes, but is not a full-fledged DBMS. Specifically, I don't think it can join tables.
- NotSammyHagar 7y ago
  memsql (which was in there for new sql).
  - einpoklum 7y ago
    Eh... not quite. It's in-memory representation is row-based. It seems it uses columnar secondary storage. At least - that's what it says here: https://en.wikipedia.org/wiki/MemSQL https://en.wikipedia.org/wiki/MemSQL
- nicoburns 7y ago

thekhatribharat 7y ago

[Shameless Plug] A summary of the evergrowing NoSQL and NewSQL market: https://medium.com/open-factory/nosql-newsql-a-smorgasboard-market-e8cbce4ae8a9 https://medium.com/open-factory/nosql-newsql-a-smorgasboard-...

CMCDragonkai 7y ago

There's also column-oriented or array databases like MonetDB and Rasdaman.

zbentley 7y ago
And the elephant in the room: Cassandra.

neop1x 7y ago

No Elastic among the examples while highly popular and nice :( Great article overall, though!