Ask HN: Why are relational DBs are the standard instead of graph-based DBs?
I've been recently exposed to some informational systems where all the domain data was modeled as one graph instead of a set of inter-related tables.
I worked with RDBs (primarily Postgres) for 5+ years and I cannot say that it ever felt wrong, but the more I think about modeling data as graphs, the more it makes me confused why it's not the default way.
Graphs seemed to be: (1) Easier to scale (both storage-wise and complexity-wise). (2) Closer to how we model the world in our minds, hence easier to reason about. (3) Easier to query (felt more like GraphQL than SQL if it makes any sense).
The way I see it, there are two major ways to connect singular entities in a data model: 1. Lists (aka tables) that allow you to sort, filter, and aggregate within a set of entities of the same kind. 2. Relations (aka graph edges or foreign keys) to connect singular entities of different kinds.
... And I can imagine relational DBs being List-first Relation-second, and graph DBs being the opposite. But maybe that's too much of a simplification.
Anyway, looking back at different domains I worked with, it felt like I had spent much more time working with relations than with lists.
Another signal: I have an intern developer, and it took him 1 minute to understand the basics of how graphs work, but then I spent two hours explaining why we needed extra tables for many-to-many relations and how they worked.
Any thoughts? What am I missing? Are RDBs the default way mostly due to historical reasons?
Discussion on this topic that I could find: https://news.ycombinator.com/item?id=27541453
177 comments
[ 3.1 ms ] story [ 202 ms ] threadThe point of a DBMS is to do this for you, via the SQL table declarations. AIUI, NoSQL / graph databases cannot do this at the system level and needs to be done by an application-level framework.
I don't think you can enforce a constraint on which nodes are supposed to have which edges, etc.
I'm curious: is there a reason why you're putting NoSQL and graph databases into the same bucket, other than them both having the aforementioned limitation?
I think the relational _schema_ is a graph, but not the incremental data stored within. The nodes are entity types, not instances, and thus recursion is irrelevant.
So, kinda? But a lot of categorical differences.
I’d happy if someone could elaborate more one that, but to me it seems like if we do the higher normal form things eventually we get to a place very similar to tables being graph node/edge types and the table rows being the insances of nodes/edges. Even the join table in lower normal forms is clearly like an edge type, but ofc with tables I think it sucks more, because when you join tables it costs a lot, but in case of graphs the information is local to the graph node (table row) and there is no complexity boom issue.
Also, if we’d take it really far we could say every SQL Table shall only have 1 row meaning one node/object. I don’t think it’s partical, just a though lt experiment, wondering about its meaning.
relational is good for lookups and adjacency queries, and thats the main query semantic in CRUD
Hmm, why aren't graph queries useful for CRUD? I don't get it.
C — create a new node (possibly with some edges).
R — get one or a list of nodes fitting some criteria (including being connected to other nodes).
U — update a given node and possible its edges, and also possible some adjacent nodes.
D — drop the node and its edges.
Which of these is conceptually more difficult with graph DBs?
When someone needs to store today's office visitors, they don't need to know who shared the visitor log on Facebook and how many people liked it, or how many visitors removed from Kevin Bacon they all are, it just needs a list with a bit more structure than CSV, and that kind of use dominates what people do with databases.
What does <a graph database> add that I can't get out <the relational one> I already use, and does <that thing> matter in the context I'm building in enough to justify the cost of migration/maintenance?
In a surprising number of contexts, the answer is nothing, in which case you stop, or something, and no, which is also a stop.
The Architect's point of view should take into account the relative availability of talent and cost of materials to get the job done. This is part of what makes them the Aspirant Developer's mortal enemy.
This way of formulating a question though, despite it's frustrating effectiveness at short-circuiting new-shiny for new-shiny sake, also excells for getting juniors on the road to becoming technical experts.
Releational can model a graph but can only answer queries like X is an immediate neighbour of Y. It lacks the walks (SQL recursive is kinda there but not really).
The primary purpose of a DB is to serve queries. But graph DB's queries are not so useful for most "lookup" type workloads that dominate business. So a graph DB is overkill, and when you get into the nitty gritty, cannot do many performance optimizations as a result. So a graph DB is a worse relational DB. But immediate relations are what is interesting to business.
It sure helps, because you got literally generations of developers trained on how to best utilise them.
Additionally, you can use ORMs, GraphQL adaptors or similar abstractions to build graphs on top of relational storage, and keep your existing infrastructure, which makes hybrid setups a lot more attractive than graph-first ones for non-startup environments.
join table1, table2 where table1.id = table2.customer_id
type operations would have a tape for table1 in one drive, and a tape for table2 in the other drive. Things like fixed length records emerged to make it possible to fast forward the tape a specific number of inches to the point where the next record would begin, facilitating non-linear access.
Once that model was completely baked into the tooling, it didn't go away when the data moved to HDs then SSDs. The paradigms have outlived the hardware.
It's a bit like the save icon still being a floppy disk.
No assumptions on how or where the data is physically stored is made by the model. Besides, it seems more plausible to me that Codd would be working on machines with disks, not tapes, by the time he proposed the model. See this, for example: https://www.theregister.com/2013/11/20/ibm_system_r_making_r...
So my understanding is relational databases were born on disks, not on tapes, but that distinction doesn't really matter to it. Additionally, it is the physical independence of the model that let all implementations based on it adapt when SSDs (and newer) storage systems arrived.
Finally, saying "the paradigms have outlived the hardware" doesn't make sense in this context. To repeat: the relational model was proposed precisely to isolate logical database design from hardware details. As a paradigm, it has been independent of hardware from conception, so of course it would outlive any specific hardware incarnation.
Facebook famously uses a graph database called TAO [1] with an ORM called Ent sitting on top of it. Everything lives on that - internal and external applications. Every dev at FB writing backend code uses it. Can’t talk too much and how these work since they’re internal and proprietary, but looks like we open sourced a Go ORM that’s Ent-inspired that shows the basics [2]. It’s definitely a weird mental model to get used to, but the combination of deep business logic in schema definitions, complex joins, and insane query performance make it super powerful. We’re definitely one of the bigger non-SQL shops, with thousands of developers using this every day as their primary data store.
[1] https://engineering.fb.com/2013/06/25/core-data/tao-the-powe...
[2] https://entgo.io/docs/getting-started/
All graph databases? With just a bit of googling I can find ones that support migrations through ETL with foreign keys translated to edges at that point, and claim to have transactions with immediate consistency—even some claiming ACID.
Joins are not even a thing in graph databases; being able to directly query across edges obviates the entire concept.
Nah, you do need joins if you don't only want to walk the graph from some well known roots. E.g. queries about properties of connected subgraphs
In an graphdb the engine would deduct that by the way of the query language (no explicit joins).
I'm not arguing for explicit joins, which are an artifact of SQL and not even the relational model e.g. Datalog as a explicit join free relational language.
You don't need to have an explicit `JOIN` operator in your language, but any query that goes beyond unconditional tree walking will benefit from using joins under the hood.
Whenever you are combining two partial query results into a coherent whole answer you need to perform some form of join.
For example you could view a MongoDB map-reduce step with a subquery as a naive indexless scan join.
In traditional join sense, a RDBMS first gets two or more result sets, then filters and matches one, both or neither to appropriate rows in the other one. It is a different concept entirely.
Suffice to say, one of these is conceptually easier to parallelize and distribute.
You'll realize that all these paradigms are more related than they appear on the surface, that the obvious and naive thing is also often prohibitively slow and limited, and that NoSQL doesn't mean NoJOIN ;)
Yeah, with joins, which this entire discussion is about.
> the latter
Is completely tangential.
Joins are the central thing in graph databases, even if you don't think of it in those terms or materialize some of the join operations. Being able to directly query across edges is a logical operation, it doesn't imply the underlying implementation. While the simple "node/edge" model in memory seems easy, it also has pathologically poor locality so most graph databases use less direct representations (traversed with join operations under the hood) with much higher average performance.
This isn't a fundamental limitation right?
its just that sql doesn't normally support cyclic evaluation. that's really only the only fundamental difference in a 'graph database'
that said, recursion is really handy and SQL is a really crap environment for doing anything but the most basic of evaluations.
Same query in Cypher (the language used by e.g. Neo4J, RedisGraph):
SQL needs 48 chars:
Cypher needs 57 chars: I would be very hesitant to use a language that needs 19% more code to express the same thing.JOIN ProductCategory pc1 ON (p.CategoryID = pc1.CategoryID) JOIN ProductCategory pc2 ON (pc1.ParentID = pc2.CategoryID AND pc2.CategoryName = "Dairy Products")
JOIN ProductCategory pc3 ON (p.CategoryID = pc3.CategoryID) JOIN ProductCategory pc4 ON (pc3.ParentID = pc4.CategoryID) JOIN ProductCategory pc5 ON (pc4.ParentID = pc5.CategoryID AND pc5.CategoryName = "Dairy Products");
-----------------------------------------------
MATCH (p:Product)-[:CATEGORY]->(l:ProductCategory)-[:PARENT*0..]->(:ProductCategory {name:"Dairy Products"}) RETURN p.name
They were largely replaced with relational systems for more or less exactly the reason Codd laid out in his classic paper[1]: If efficient processing of logical operations depends on a predetermined physical structure of data, the range of practical applications for your database is severely constrained.
That suggests that the proper niche for graph databases contains those applications with a predefined set of highly recursive operations, which is more or less where we find them today.
[1] http://db.dobo.sk/wp-content/uploads/2015/11/Codd_1970_A_rel...
A lot of network databases store graphs as pointer-bogs which are hard to process and query, but that does not diminish the virtues of the graph model itself or preclude a representation based on joins.
I'd argue that a 5th normal form graph representation contains a lot less implementation detail and is a lot more flexible than a regular old buch of tables.
Not quite. The definition of 5NF depends on real-world constraints on valid combinations of attributes in the database; it has nothing to do with any pre-defined data model such as RDF. There can be "wide", horizontally-modeled tables (quite unlike the RDF model) which are in 5NF simply because no real-world constraints apply to the data beyond those that are implied by the candidate keys.
I don't think it was replaced for lack of flexibility.
AFAIKT it was replaced (by those who could replace it. There are many that still use it. Like banks) because IBM didn't let it progress, basically froze it in the cobol era, tied it with their hardware, and continued to cash-in as long as they could.
Unlike some sister comment, I think it was conceptually great... Only the tooling and ecosystem were/are terrible.
At the query language level, a graph query language has to somehow represent the notion of a list + sort/filter/aggregate too. Haven't seen convincing improvements over the relational model, and arguably there are none: the concept of list + sort/filter/aggregate is fundamental.
The use-case where graph dbs might have an edge is when querying recursive relations, but recursive query constructs are conceivable in a table-first approach as well. Possibly also creating / querying relations (tables) on-the-fly without the 'CREATE TABLE ... / JOIN ON ...' ceremony is handy
I wouldn't look at SQL in particular for a modern instantiation of a relational algebra query language. Modern relational algebra libraries like dplyr in R ecosysyem are better.
I have not found yet uses for graph dbs. But I am curious to see why people find graph dbs "easier", preferably with concrete examples.
[1] Arguably 'primary key' terminology is confusing, as it may denote either an ID column (node) or a multi-dimensional FK tuple (hyperedge), so I prefer to avoid using it.
You’re right that graph db is very easy to use a lot of the times.
It would probably be more productive to compare RDB and Graph with certain workload examples (OLTP, OLAP, joins, scans, etc.).
Thanks, this is a great question with many technical, social, and commercial aspects to it.
TLDR: the relational model has a super power for data management systems: it decouples the logical from the physical representation and will eventually always win. There are technical reasons why it was hard until recently to build a graph database based on the relational model.
Database were not always relational: In the 1960s databases actually had a navigational paradigm and used a hierarchical or network data model (not unlike some current graph databases).
The 1970s saw the rise of relational database management systems with early proofpoints of Ingres and System R. The important improvement here was that the physical organization of data is separated from its logical organization in relations. This is the super power of the relational model. This innovation led to an explosion of commercial activity with Oracle, DB2, Sybase (licensed to become Microsoft SQL Server) and some more. Many of these are now still industry giants.
The 90s was a big hype of objected-oriented programming and some got the idea that database management systems should be following the object-oriented model as well. This was mostly a catastrophic failure and instead systems based on the relational model kept improving and won in the end.
In the 2000s there was a large emphasis on scalability to large numbers of users and data, and the development of NoSQL systems started. Most of these did not follow the relational model. These are the key value stores, document databases etc. Key value stores addressed the problem of poor scalability but compromised on the data model and transactions. Document databases have better locality of data and made schema changes easier. They all had something in common: compromise on the relational model to gain an advantage over existing relational systems. However, systems based on the relational model kept improving the meantime and have gradually started to gain market (or mind) share again (eg Aurora, Snowflake, Spanner, CockroachDB).
In my opinion, graph databases are next. Graph databases identified a weakness, which in this case is modelling and the inability of current relational systems to handle graph structured data well. Graph data involves many joins and often recursive computations, which current commercial SQL relational systems do not do well. However, the new graph databases are not superior universally. For example, take TPC-H (OLAP - analytical) or TPC-C (OLTP - transactional), put that on a graph database and you'll typically see pretty terrible performance even though the data can easily be modeled as a graph. Several popular graph database systems do not even scale well beyond a single node.
I think you are absolutely right that graph data models are easier to work with. Starting from an ER diagram (or similar) it's not straightforward to go to tables. Assuming that your ER model is a good model, you're grouping stuff into tables based on functional dependencies. Tables here are a collection of relations, eg for an _order_, the SQL table might include relations to the customer, order date, etc. It does not include relations to products included in the order, because these have a different primary key. This is difficult for users to understand.
Predictably, the relational model is catching up though with graph database systems. A major research innovation in databases from recent years are better join algorithms, specifically for joins involving many relations, self-joins and skewed data. They're called worst-case optimal join algorithms (WCOJ) and several early prototype systems have shown promising results with these.
Based on these ideas, RelationalAI ( https://docs.relational.ai/ , j-pb ↗ Hey Martin, mbravenboer ↗ Thanks, nice to hear! j-pb ↗ Thanks for the insight!
Having the best and brightest of the west coast database departments at your disposal allows for some different tradeoffs I guess ^^'! mbravenboer ↗ Stay tuned for that :). RelationalAI is developed as a multi-tenant service that has a pay-as-you-go model. This should make it possible for everybody to use the product. I expect we will have some free tier options too, but we have not decided on the exact model for that yet. We are starting to onboard individual preview users already. j-pb ↗ Can't wait! HelloNurse ↗ Looking at RelationalAI documentation, it's fairly clear that you designed a more powerful and "cleaner" query language than SQL, good for graph-oriented tasks and without many of the traditional limitations and compromises found in typical databases. mbravenboer ↗ This is really hard to answer in a short comment, but I'll try to highlight a few things:
I'm a huge fan of LogicBlox and RelationalAI, and the research done at both had a huge impact on me as an undergrad!
While you're here, could you provide some insight on why you folks went with arbitrary width tuples for your datamodel? Limiting youself to triples seems to drastically ease the implementation of WCOJ algorithms without much loss of expressivity, and provides compatibility with existing RDF research.
E.g. in our system we materialize the 3!(6) indices required, which allows us to pick variable/intersection orderings on the fly without a planner.
With triples you need to reify hyper edges into nodes (a relation is just a hyper edge). This can lead to pretty awkward models, for example for temporal data. Most graph databases (eg Neo4J) have the same limitation and as a result struggle with temporal data or other higher-dimensional data.
We prefer to support general relations (hyper edges) and face the complications of query planning. We really want a general relational system that decouples the complications in query processing from the way you need to model your data and write your queries
This gets particularly important for what we do besides graphs: we also aim to support machine learning workloads (we have some references to papers on our website).
We still do automatic index selection, but indeed it is important that you do not create too many of these unnecessarily if you relations with arity bigger than triples.
I'm actually surprised that RelationalAI and Materialize Inc haven't merged yet, for ultimate world domination.
It's a shame though that both LogicBlox and RelationalAI historically only target large enterprise customers.
It forces us commoners that want to get out of the tarpit too to bolt relational operators to our existing languages. :D
We're good friends with Materialize.io and use similar techniques for general incremental view maintenance, but their product is quite a bit different: their purpose is to design for SQL. RAI offers a different programming model to capture complex application and business logic (the language for that is called Rel, and docs are public).
But this is the front end. In the back end that writes files, elaborates query execution plans and executes concurrent transactions, what price are you paying on the performance side? What are the unavoidable costs of graph queries?
- We use compute/storage separation as in Snowflake and Aurora. This means that data is stored in object storage (for example S3) and computing resources are transient. This is the de facto standard now for new database management systems. Systems that are not designed for object storage are gradually going to be less relevant. The Snowflake papers are a great resource on this ( http://pages.cs.wisc.edu/~yxy/cs839-s20/papers/snowflake.pdf ) . It's also a recurring topic in the CMU database talks. Our memory management is based on LeanStore and Umbra.
- The database is entirely versioned with an immutable data structure, so read-only scaling does not require locking: you can provision new compute instances that work on a snapshot of the database that is 100% guaranteed to be consistent. This means that you can run an analytical job on your production database with a 100% guarantee that the performance of your primary system is not impacted. For concurrent writes we aim to use incremental maintenance techniques.
- All our data is indexed so that the WCOJ algorithms can do their job. Indexes maintenance can be expensive, so we have write-optimized data structures for that (B-epsilon trees - http://supertech.csail.mit.edu/papers/BenderFaJa15.pdf ). The performance of those is really good, and they can be compacted in the background. The combination of our graph normal form (narrow relations) and WCOJ addresses the index selection problem in a novel way.
- We use new compiler architecture ideas to make everything live and demand-driven. Our framework for this is Salsa, which is open source ( https://www.youtube.com/watch?v=0uzrH2Ee494 and https://github.com/RelationalAI-oss/Salsa.jl ).
- WCOJ are our work horse join algorithm. We have different query evaluation techniques, including JIT compilation and vectorization. For queries that do not require worst-case optimal joins the plans are similar to more conventional systems due to optimizations applied to the plan (talk: https://www.youtube.com/watch?v=C_mBEq_o4HE )
Graph workloads have a few interesting properties: 1) Analytical queries are often recursive. Systems like Neo4J do not really address this in general because the query language does not support general recursion. Instead, it offers bindings to algorithms implemented in Java. 2) Queries often do joins across many relations. 3) There is lots of skew.
(2) and (3) are handled by WCOJ. For (1) we use algorithms similar differential dataflow. Differential Dataflow already has great proof points for graph analytical queries (mainly created by Frank McSherry)
In the end, of course all that matters are the actual benchmarks. We are developed those for standard relational workloads (like TPC-H) and graph workloads (for example LDBC SNB, graph analytical workloads). It's a little too early for us to claim results at scale, but we have isolated proof points that the design works.
The advantage of relational was also you could store reference data in one table - not all through the graph, another problem with ISAM. Also often when reporting you don't really know the relationships you want to report on, if this is in a hard wired graph then extracting that data and putting it in the form you want is hard, this is the power of joins - joins allow you to make the relationship you want at query time.
Graphs assume you know the relationships and they're fixed in time, this is seldom the case when building a relational DB, thats why joins come in handy - you can restructure your tables without deleting the data, or not much deleting. You can add views in to, to present the data in the old way and so on.
There's lots more, but that is the big ticket items.
And the price you pay for that is speed. Direct arc traversal is always O[1]. Querying is O[all_over_the_map] and all traversal in an RDBMS is essentially querying.
But your point is well taken: Hardcoded arcs move the speed problem to update time. However modern graph databases use a number of techniques to mitigate this problem like indirect pointers and reified relation objects. Yes, this slows things down to something like O[2] (which is of course the same thing as O[1] but I'm pointing out that it's about half as fast) but it preserves traversal speed predictability (minimizing traversal variance) while also allowing update flexibility.
At the same time modern RDBMSs optimize candidate key traversal queries to the point that they're essentially O[1] too so it's kind of a potato/potahto situation.
2. "Closer to how we model the world in our minds, hence easier to reason about" I don't think i agree. I would argue, most peoples mental model of data is strongly inspired by tables. Thus, the big success of excel.
3."Easier to query" ok maybe I am just too used to SQL databases but i would very strongly disagree there. SQL or using any ORM its super easy to query. Of course some joins might get complicated. But if you always have to run a lot of complicated joins you can probably work on the data model, or use caching.
That's the thing, this is not always true. It's true that generally you can throw money and hardware at a problem, but at some point you just reach the fundamental limit of the technology, at which point throwing money at the problem doesn't work.
In fact, being in a position where you can throw money at the problem is _brilliant_, as spending money is easy. So the point of _scalable_ technologies is to enable you to scale just by throwing money at the problem.
Case in point, at my old company we had this _massive_ Pg database running in RDS. It did not scale. Performance was terrible, and made the user experience shit. We were basically using the biggest instance size we could, with a few replicas powering read only endpoints. So at some point we migrated parts of it to Dynamo, and poof all of our problems went away, including operational ones.
The US military has been willing to write a blank check to anyone that builds a graph database that actually scales for decades. No one has collected on that.
While there are some genuine problems that require extending or adjusting the model (e.g., temporal databases), all the problems people normally complain about relational databases are implementation, not model problems.
A common misunderstanding I find is that people think the relational model does not scale, but that makes no sense. The relational model is a logical model meant to, among other things, provide independence of the logical representation of data from its physical storage. A loose but hopefully useful analogy would be that the relational model is like arithmetic, while any given database product is like a calculator. At some point, you'll hit the limits of a calculator and will get an error as a result to an operation. That won't cause you to say "arithmetic does not scale!". Instead, you'll probably try a different calculator, or perhaps even end up implementing a new piece of software to let you handle that particular computation.
It is similar with the relational model. You may hit the limits of a specific implementation (can't scale beyond X cores, can't transparently partition data across multiple nodes, etc.), but that's an implementation limitation. The relational model has nothing to say about hardware, so those are not limitations in the model.
I think it's good pragmatic engineering to use the best tool for the job, and that includes using non-relational databases (nitpick: I don't think any of the mainstream databases considered relational are a good representation of the model. SQL, in particular, is very bad at the job. But we're probably stuck with calling them "relational databases") when they're more suitable for the task at hand. However, the important consideration is that the relational model itself can be used as a foundation to represent whatever data you need to represent, including graphs. It's just that there may be no suitable implementation for your needs right now.
One last thing, when you say Relations are like graph edges or foreign keys, I wonder if perhaps you're misunderstanding the relational model? The 'relational' in it is not about linking from one entity to another. It's about a relation from a set of attributes and types, to a set of values for those. In SQL-parlance: you can have a relational database with a single table, no need for foreign keys (even to itself) for it to be relational.
[0]: https://blog.jooq.org/creating-tables-dum-and-dee-in-postgre...
Yes and no. There are plenty of data-modelling scenarios that, while theoretically possible, are utterly impractical, when using Codd's model[1]: For example, neither of Codd's then-descriptions of relational-algebra and relational-calculus can be used to express a recursive query through a graph relation[2] - and it took until 1999 for ISO SQL to add support for CTEs to facilitate recursive-queries, before that the only way to recursively traverse a graph in an off-the-shelf table-based RDMBS was to either use a CURSOR or dump the entire table into memory.
[1] Only 5th Normal Form of course: I'm in the school-of-thought that 6th Normal Form is a regressive step - or a poor man's substitute for sparse-table support.
[2]Using Codd's definition of "relation" to mean "table" in SQL. I wish he had chosen a better name than that because 90% of the people working with databases without the benefit of a formal database-theory education all think "relation" refers specifically to a SQL foreign-key constraint - which is a very different thing.
The fact is[0] that even though Codd’s contributions effectively created the field, they were also vague in some key aspects.
[0]: that may come across as arrogant. It should be expanded to “in the humble opinion of this informed practitioner, it seems the fact is…”
Then consider non-hierarchical markup documents ( https://en.wikipedia.org/wiki/Overlapping_markup ) which has elements that span two or more arbitrary points in a document. Fortunately I've never encountered this problem yet so I've never had to put much thought into it.
[1]e.g. think of XML, JSON, and YAML files used for web-application configuration or a SOAP request message (but not those XML/JSON files being abused as containers for tabular data).
Every data model is shoehorned into the relational model via normalization which involves a ton of judgement calls about how the data will be used at the time the schema is defined. And then this becomes extremely difficult to change.
It’s a big trade-off between schema flexibility and performance.
I’ve always felt we would be better off querying at a higher level (say the logical db schema) and then the physical db schema is managed for us behind the scenes. But then there is also a further level up at arguably the purest representation of data as triplets, which is why Datalog is called out so much.
But that's *exactly* what an RDBMS does: it handles the low-level record data-structures on-disk and presents to you a relatively cushy set of tables that you can design how you like them.
...otherwise please clarify what you mean by "logical db schema" vs. "physical db schema"?
Your talk of there being a "physical" design harkens back to those myriad "4GL" database platforms but those are all completely obsolete (despite what Progress's sales people will tell you). Y'see, database application developers haven't needed to concern themselves with physical records on-disk since dBase stopped being cool around 1990 (and the poor sods still using 4GL systems through the 1990s and even into the 2000s failed to see the industry shifts towards open databases (hence the "Open" in ODBC) so I don't feel too sympathetic for their cause.
-----------
> I think this is a reason NoSQL became so widespread.
I attribute it to too many CS/SE grads who didn't take database-theory electives and so never got to properly grok SQL (never mind how SQL is horribly, horribly mis-designed!) but they got hired by cool startups but for 90% of their use-cases the notion of a database behaving like memcachd is very, very enticing.
You may have noticed that the hype around "traditional" NoSQL engines (like Redis, MongoDB, CouchDB, etc) started receding probably about 3-4 years ago (and PostgreSQL is cool again!) after all those companies that built their entire business around a NoSQL database suddenly find themselves being mature businesses that now need to hire normal business-people who need to get that business data into Excel, or an OLAP warehouse, or otherwise get real-time views of data in a tabular form - those are all things that will make upper-management decide to hire people to transition the company off NoSQL and back to RDBMS.
This isn't anything new: a couple of decades ago there was (and still is) a popular in-process key-value (i.e. NoSQL) database called Berkeley DB[1] that had plenty of use-cases and applications as a more flexible alternative to some stock dBase library - or as the years went by: Microsoft JET (aka Access MDB). Though in this case, the funny thing is that dBase is also "NoSQL" because application programmers have to query data by manually iterating over fixed-size records at known offsets in a one-file-per-table system - it's really primitive.
[1]Now also owned by Oracle, natch: https://en.wikipedia.org/wiki/Berkeley_DB
Logical schema is an ER diagram, physical schema is how that is translated to a DB-specific DDL, not to be confused with the physical data storage layer.
Think about all the different ways you can model a list in SQL. E.g. jsonb, array, hstore, 1-M FKs, M-M join table, and all the different levels of normalization.
> I attribute it to too many CS/SE grads who didn't take database-theory electives
Completely agree! But it also makes you realize how the relational model is a poor fit for a lot of data models too - which again many are unaware of. It's not perfect, but its a nice sweet spot for now.
I think we are at the other end of another pendulum swing of databases though. But it's hard to innovate when the prerequisite for success of any new database remains as SQL compatibility.
(Yes, this reeks of https://xkcd.com/927/ - but the aim isn’t to abstract-away different RDBMS’s dialect’s features, but instead to massively improve QoL)
Ping me if you’re interested! My email address is “[my-first-name]@[my-first-name].me” (and my first name is “Dai”)
I think PSQL can only take you so far though, I think we will move to something new eventually.
For most apps you want to watch a query for changes. SQL is a pain for that. If you can get inside the query tree though, you can do some caching and materialization to help. And I want to run my db in my browser too.
The problem with graph dbs is trying to return something that is not a graph. Like a count. Or derived information. And which graph model do you use? There’s more than one. Lots of information is very poorly modeled in graph dbs. Temporal organization, for example.
Ultimately, graphs are a way to use relations. But relations allow you much more flexibility to associate information (subject to the issue of transitive relationship traversal).
Mixed graph-relational is perfectly reasonable. Reasonable start here: [https://age.apache.org/]
Relational DB's resemble ledgers from a business perspective. Most business apps and nearly all financial apps think in terms of ledgers of transactions. Go one to two degrees of separation from a financial transaction in any web app these days and the users of that system will want to line up other activity/transactions in the app with the financial ledger. Then they want to ask questions and have reports on things like: how many users for that customer? how many purchases this month for that customer? what did they purchase? etc.
I think its historical and historical also implies the vast majority of systems aren't likely to change if there is some-other-tech that may be easier to work with. Its a safer decision to pick well known tech because you'll know the trade-offs, support issues, hiring parameters, etc. from the history of all that came before you. To reverse that point - picking some esoteric language/db solution increases the risk of a product/project failing, not because of the technology but all the other factors that surround that technology.
- trying to use it with data where tables are a poor fit, or
- trying to use it with data that can't be reasonably represented in more than two or three tables
I still use spreadsheets for simple "databases," but I can't think offhand of any time I've a spreadsheet that requires more than two interlinked tables/sheets.
Network databases, which seem quite similar to graph databases, were standardized (https://en.wikipedia.org/wiki/CODASYL).
Both the hierarchical and network models had low-level query languages, in which you were navigating through the hierarchical or network structures.
Then the relational model was proposed in 1970, in Codd's famous paper. The genius of it was in proposing a mathematical model that was conceptually simple, conceivably practical, and it supported a high-level querying approach. (Actually two of them, relational algebra and relational calculus.) He left the little matter of implementation as an exercise to the reader, and so began many years of research into data structures and algorithms, query processing, query optimization, and transaction processing, to make the whole thing practical. And when these systems started showing practical promise (early 80s?), the network model withered away quickly.
Ignoring the fact that relational databases and SQL are permanently entrenched, an alternative database technology cannot succeed unless it also supports a high-level query language. The advantages of such a language are just overwhelming.
But another factor is that all of the hard database research and implementation problems have been solved in the context of relational database systems. You want to spring your new database technology on the world, because of its unique secret sauce? It isn't going anywhere until it has a high-level query language (including SQL support), query optimization, internationalization, ACID transactions, blob types, backup and recovery, replication, integration with all the major programming languages, scales with memory and CPUs, ...
(Source: cofounder of two startups creating databases with secret sauces.)
I learned almost none of it in my formal computer science education in the laste 90s -- maybe because at that time it was recent enough that "middle-aged prime of their career" people still remembered it, it didn't seem necessary to teach it to newcomers as "history".
I wonder how much post-1950 history is taught in current undergrad CS programs, like if a "databases" course will include a summary of any of the material you summarize.
I taught an intro database course recently, and only touched on some of that history. It wasn't the main point, and it was an undergrad course.
But you point out a real problem. There is so much reinvention by people who simply don't know what was done previously.
* I have seen research I did in the 70s/80s get redone in the last 20 years, with no awareness that the problem had already been solved.
* I saw a horrifying takedown at an academic conference, (late 80s?) Someone on the original System R research team was pretty furious at a young researcher for being completely oblivious to the fact that he had repeated some System R work. (He blamed the conference reviewers as much as the author.)
Although I very well can imagine the frustration, as I have seen the same things with libraries being reinvented, I think there is value in revisiting some problems in a different context. Maybe the problems are just not that of a big deal anymore in newer languages/eco systems even though they are fundamentally the same problem as in the 70s.
(Not denying that reimplementing things can be fun ...)
I suspect some of it is just the joy of solving (or trying to solve the problem) means the unsexy work of digging through hundreds or thousands of old papers gets forgotten.
And looking at things with a fresh view (which isn’t going to happen after looking at all those old papers) does have value.
Software "engineering" isn't. It's all about processes and very little about standardization of process and verification of outcomes.
Databases are interesting because they were the forefront of computing when it was invented, and they still are today. The computer science sorting algorithms and trees (and trie) go directly to how good (for all the different kinds of definitions of good) a database is.
MongoDB threw out a ton of history in order to invent a different wheel, and it's faster for interesting reasons, but it's also for a different era than single monolith computing (aka Oracle DB). That doesn't mean MongoDB is appropriate for all situations, but that a fundamental precept of computing has changed. Used to be, there was room for maybe five computers in the whole world. Today, my computer has multiple virtual computers inside of it and they are treated as cattle, not pets. The work being redone is because (sometimes) the work has been invalidated by newer experiments.
We're going to have to "redo" a lot work once dark matter is found as well.
What it did have (which no relational database had at the time) was JSON as a coin of the realm and a distributed database model. Run the clock forward and MongoDB now competes on equal footing with relational databases for all those features but it still has the key competitive advantages of JSON as a native format and a distributed architecture.
https://blog.eutopian.io/the-next-big-thing-go-back-to-the-f...
You're going to be lucky if a paper from the 70s or 80s is available in a searchable database at all. That means someone bothered to scan it in, and OCR it since then. Even for the few papers that are searchable, they are old enough that they probably won't catch anyone's eye unless they are desperate.
Of course then there's also the problem of knowing what to search for. Programmers love to invent, reinvent, and re-reinvent terminology. It's only gotten worse with every other developer running a blog trying to explain complex ideas in simple terms.
The entire field of ML is a perfect example of this. I remember talking to my father about all sorts of new developments in ML back in the early 2010s, and I was quite surprised when he told me that he learned a lot of the things I was talking about back in the 80s just named a bit differently.
In most cases it ends up being a question of how much time you can put into any given problem. If I spend two weeks to find a paper that would have taken me a week to reinvent, then am I really ahead? If the knowledge wasn't important to enough make it into textbooks/classes/common knowledge then attempting to find it is akin to searching for a particular needle in a pile of needles.
If you're doing databases then you've almost certainly been exposed to Codd's work, if not through his papers and books, then at least through textbooks and lectures. There are countless blogs, lecture series, and presentations that will happily direct you there.
The challenge is that there's also a mountain of work that never really got much popularity for whatever reason. Say a paper was ahead of it's time, or was released with bad timing, or simply kept the most interesting parts until the end where few people might have noticed. It's these sort of gems that are hard to find. It's hard to even know how many of these there are, because they are by definition not popular enough for most people to know about them.
I've often wondered if and where the boundaries of human knowledge will run into this very problem. Mostly we rely on the concept of reductionism and hope that will generalize and scale but it rarely does. We often have different theories at different scales for different things we to want model and explain with science in general. If it's found that these ideas don't connect and we have to forever add more and more generalized nuggets of knowledge, models, etc for specific case, even if they are reductionist in nature, we may get to a point where there's just so much information the act of discovery of prior art may be more work than simply redoing the experiment against the ultimate judge (reality) and see if it flies.
I used to work with a handful of bioinformaticists in the era of the affordable sequencing boom and there was a lot of debate about what to do with all the sequence data. Do they archive it and pay the cost to manage, index, and make it searchable or does it make more sense to simply pay the couple thousand to rerun the experiment and assure yourself that no process flaws were introduced in a prior paper, that you eventually agree with the prior work, and so on. There's almost a little bit of built in reproducibility in this unintended act of duplicating prior work.
Software is especially bad because it's such a relatively new domain and is rarely done rigorously so people just run through cycles over and over again, assuming something people did before was wrong or that the environment changed and that approach was just inappropriate for it's time period. Plus, people in this industry seem to be driven to do innovative new work so they have no interest in looking backwards even if they should.
[1] http://www.redbook.io/
For the history of data model proposals in particular, there is this paper: https://scholar.google.com/scholar?cluster=73661829057771494...
So the info is definitely out there if you are interested in that sort of thing.
Its archives are primary sources for this history. The database debate was carried out mostly in ACM SIGMOD, the "Special Interest Group for Management of Data".
1. https://www.acm.org/
I strongly recommend everyone pick up basic set theory notation so that you can read seminal papers like these. There is so much amazing information in them, yet they’re treated as if they’re incomprehensible. You can understand the relational model with undergraduate-level discrete math and set theory knowledge.
These are the papers that formed our industry. Let’s not forget them.
My databases course (circa 2014 or so) covered the gist of this; we didn't spend much time talking about database systems before it, but we read the Codd paper and discussed how various companies began working on implementations over the next couple of decades.
This sort of touches upon the general inadequacy of CS education in general.
The way I learned all this history is by digging it up myself, that was in early 2000s - old journals, papers, listening to people who witnessed all this themselves.
That's the cornerstone skill that should be taught - research and the understanding that no single course can give you much in actual knowledge, only skill to acquire it.
You seem to be ignoring the success of highly scalable managed NoSQL databases that has been all the rage when building high TPS services. What is your opinion on those?
All that said, the world of successful solutions is not mutually exclusive or limited to only one winner.
Have a lot of data you need stored where there is limited relation between them directly, and attempting to be transactionally consistent across the boundaries there and the rest of the system is going to very expensive? Then doing some part of the data in a nosql db and the rest in a ACID compliant database is probably a good idea.
Similarly, putting 50GB vm images into a nosql db is probably pretty silly when it should probably be on a proper filesystem, or at most copied out and written back periodically.
And putting a bunch of data that is tightly coupled together and needs to be transactionally updated to ensure your dataset doesn’t turn to gibberish in a NoSQL backend is going to be a nightmare.
I think a lot of the attraction of NoSQL databases is that they are seductive for people who don't know about the problems inherent in working with shared, persistent, long-lived data, (i.e. years). A NoSQL system lets such people get started easily. They start down the path, and then they run into dragons, and muggers, and hostile aliens. This is a really good description of what I mean: http://www.sarahmei.com/blog/2013/11/11/why-you-should-never....
disclaimer: work there
I would seriously argue that all of these are necessary for a database to be useful. The sad fact is that no graph databases in existence truly act as graph database, without some weird caveat like "you must have schemas".
The tree becomes lumber.
The lumber becomes a table.
At that point, the data on the table become is suitable for human consumption.
As to sibling comment question about how to keep up on this stuff, the preceding info was in the "tar pit" paper that has been discussed a lot here on HN. So I'd say, your already doing it. Continuing education is part of any profession and this is probably a better way than some.
Yes. I think that this access path independence is an essential part of the relational query approach, (of which SQL is one example), being high-level. And once you have such a language, query optimization becomes essential.
Did you mean Mosley and Marks' "Out of the Tar Pit" http://curtclifton.net/papers/MoseleyMarks06a.pdf?
Neo4j's Cypher query language is beautiful to work with!
Hierarchical and Network databases assume you can anticipate future uses of your data.
That didn't go anywhere. Review a few years later show all those buggy, crashed frequently and worst -- slower than just storing the hierarchical data into an RDBMS.